Search a DataRow from DataTable1 into DataTable2

Tip

The situation is something like this. A GridView is getting populated at the PageLoad event. Now one of the Column of the Grid contains the CheckBox to represent the True/False state for that Row. There are 2 tables in the SQL Database. Now, if a DataRow is present in both the tables then, that Row’s checkbox should be marked in the GridView. The GridView Columns which contains the checkbox is being added at the runtime i.e, that column is not present in the SQL DataTable.
The question is how can I search a single DataRow of a DataTable against all the DataRows of another DataTable. Look at the code below


DataTable dt = null;
DataTable dtParent = null;

dt = GetList(); // Get the selected List
dtParent = GetNameList(); //Get the Entire List

// Mark a Column(s) as Primary, which will be searched for the given value.
dtParent.PrimaryKey = new DataColumn[] { dtParent.Columns["ID"] };
foreach (DataRow dr in dt.Rows)
{
	//If that Row is Present in dtParent then, mark the row's checkbox as checked.
	dtParent.Rows.Find(dr["ID"])["chkCheckBox"] = true; 
}
gvStudentDetails.DataSource = dtParent;
gvStudentDetails.DataBind();

Happy Coding …
Advertisements

Change DropDownList SelectedIndex on Gridview SelectedIndexChanging Event

Tip

Well, here I would like to set the SelectedIndex of a DropDownList based on a Column’s Text Value from the GridView’s SelectedIndexChanging event. I guess, this can be achieved by many ways however, what I am trying to show here is to do the same in just 1 line.


ddlDropDown.SelectedIndex = ddlDropDown.Items.
								IndexOf(ddlDropDown.Items.
									FindByText(((Label)gvGridView.Rows[e.NewSelectedIndex].
										FindControl("lblLabel")).Text));

Happy Coding …

Un-Select a Radio-Button by clicking on it

Problem

The situation is kind of odd however, not uncommon, atleast, I would think so. We have got a Radio Button inside each row of a GridView. As per the requirement, user should be able to select only 1 radio button at a time. This has been achieved with javascript. More details can be found here. Now selecting radio button indicates the selection for that row of the gridview. Similarly, there should be an option for dis-selecting the radio button by clicking on the selected radio button (much like the checkboxes). However, this is against the normal behaviour of the radio button, and obviously there was no simple way of doing it. So I decided to put a check on the OnClick property of the Radio Button. The check was like this


if (rdBtn.checked == true {
	rdBtn.checked = false;
}

However, because of this, I was not able to select the Radio Button in the first place. Unlike, checkbox, once checked, a radio button will always return true when it is checked and my script was simply clearing the checked state thereby, not letting me select any option.

Solution

After doing a bit of R&D I realized that instead of making things complicated, I should put a simple check, & instead of relying on the checked property of the radio button I will check the id of the current selected radio button. If the current radio button’s id matches with that of the hiddenField value, I will simply clear the check of that radio button


<script language="javascript" type="text/javascript">
	function SelectSingleRadiobutton(rdbtnid) {
		var hd = document.getElementById('hdnFld');
		var rest = false;
		var rdBtn = document.getElementById(rdbtnid);
		var rdBtnList = document.getElementsByTagName("input");
		for (i = 0; i < rdBtnList.length; i++) {
			if (rdBtnList[i].type == "radio") {
				if (rdBtnList[i].id != rdBtn.id) {
					rdBtnList[i].checked = false;
				}
				else if (hd.value == rdBtnList[i].id) {
					rdBtnList[i].checked = false;
					rest = true;
				}
			}
		}
		hd.value = (rest) ? "" : rdbtnid;
	}
</script>

Inside the GridView’s ItemTemplate I have called the above script for the OnClick event of the Radio Button


<asp:TemplateField>
	<ItemTemplate>
		<asp:RadioButton ID="rdbOptions" runat="server" OnClick="javascript:SelectSingleRadiobutton(this.id)" />
	</ItemTemplate>
	<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>

That’s it …

Make Single RadioButton Selection in Gridview using Javascript

Problem

Here, I will be adding two columns in a GridView. One will contain a RadioButton, and the other one will have some text. The user however, can select only one RadioButton at a time. Multi-select needs to be disabled. The problem is that each row contains a new RadioButton so the user was able to select all the RadioButtons.

Solution

Solution is only one thing, javascript. On click on one of the radio button, all other radiobuttons should be disabled. The javascript code is as follows:-


<script language="javascript" type="text/javascript">
	function SelectSingleRadiobutton(rdbtnid) {
		var rdBtn = document.getElementById(rdbtnid);
		var rdBtnList = document.getElementsByTagName("input");
		for (i = 0; i < rdBtnList.length; i++) {
			if (rdBtnList[i].type == "radio" && rdBtnList[i].id != rdBtn.id)
			{
				rdBtnList[i].checked = false;
			}
		}
	}
</script>

The definition of Grid is defined below. Note: the javascript call for the radio Button:-


<asp:GridView ID="gvdata" runat="server" CssClass="Gridview" AutoGenerateColumns="false" >
	<Columns>
		<asp:TemplateField>
			<ItemTemplate>
				<asp:RadioButton id="rdbUser" runat="server" OnClick="javascript:SelectSingleRadiobutton(this.id)" />
			</ItemTemplate>
		</asp:TemplateField>
		<asp:BoundField DataField="SomeData" HeaderText="Some Text"/>
	</Columns>
</asp:GridView>

 

Thanks…

 

Place a GridView in the Center of a DIV

To place a GridView in the center of a div one thing that we do is to use the property text-align:center. However as per the current css, it won’t work. What we need to do is to use the code margin-left: auto; margin-right: auto;. Either put that in the css Class of the GridView or you can directly put it inside the Grid like the following

<asp:GridView ID="grdExamSchedules" runat="server" ForeColor="#333333" 
              style="margin-left:auto; margin-right:auto" >

Thanks …

Sort Asp.Net GridView Column

Well, it’s a very simple feature, still if you forget it, it may cause you unnecessary trouble. So here’s the code for it.

Here, I will create a custom property of type SortDirection.
Now on GridView’s Sorting event, I will check the ViewState – sortDirection value and accordingly I will sort the given Column.


DataTable dt = ((DataSet)ViewState[“ClientDetails”]).Tables[0];
DataView dView = new DataView(dt);

if (GridViewSortDirection == SortDirection.Ascending)
{
	GridViewSortDirection = SortDirection.Descending;
	dView.Sort = e.SortExpression + ” DESC”;
}
else
{
	GridViewSortDirection = SortDirection.Ascending;
	dView.Sort = e.SortExpression + ” ASC”;
}
grdView1.DataSource = dView;
grdView1.DataBind();

And we also need to declare the property GridViewSortDirection of type SortDirection. which will declare the ViewState – sortDirection as Ascending for the page if its value is found to be null.


public SortDirection GridViewSortDirection
{
	get
	{
		if (ViewState[“sortDirection”] == null)
		ViewState[“sortDirection”] = SortDirection.Ascending;
		return (SortDirection)ViewState[“sortDirection”];
	}
	set
	{
		ViewState[“sortDirection”] = value;
	}
}

That’s it!!!

Execute DataBase Query for Inserted, Modified, & Deleted Rows only with the help of DataSet

Introduction

Here, what I am trying to do is that there will be a textbox in which the User will enter a unique CustomerId and then when he’ll click the Add button, the value should be displayed in a GridView (The GridView will be displaying the existing Customers for that User which, will be loaded from the database).

Similarly, if he wishes, he can add multiple Customers which will be displayed in the GridView.

 

And, he can edit the existing Customer Details also.

Finally, when he’ll click the update button the database query should be executed for only the Added, Modified, & Deleted rows.

Solution

When the Page is loaded for the very first time I will get the Customers details for the current User in a DataSet (which will be the source of the GridView) which I have written the DataSet in the ViewState of that page.


ViewState["customertData"] = ds;

Now whenever user is adding a new Customer by giving its Customer Id I will do the following


DataSet ds = new DataSet();
DataTable dt;

if (ViewState["customertData"] == null)  //If the ViewState is not defined
{
	dt = new DataTable();
	DataColumn uniqueID = new DataColumn("uniqueID");
	DataColumn customerId = new DataColumn("customerId");
	DataColumn excluded = new DataColumn("IsExcluded");
	dt.Columns.Add(customerId);
	dt.Columns.Add(excluded);
	ds.Tables.Add(dt);
}
else
{
	ds = ViewState["customertData"] as DataSet;
	dt = ds.Tables[0];
}

DataRow dRow = dt.NewRow();
dRow["uniqueID"] = System.Guid.NewGuid();
dRow["customerId"] = txtcustomerId.Text;
dRow["IsExcluded"] = false;

dt.Rows.Add(dRow);
grdDsClient.DataSource = ds;
grdDsClient.DataBind();

//store the current changes in the ViewState
ViewState["customertData"] = ds;
txtClientNo.Text = String.Empty;

Similarly In the RowUpdating & RowDeleting event of the gridView, I am modyfying the DataSet value with the updated records & accordingly writing it into the ViewState. NOTE: To Delete a row from the DataSet I am using the DataRow.Delete() method & not DataRow.Remove() OR DataRow.RemoveAt() methods.

RowDeleting 


protected void grdDsClient_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
  DataSet ds = ViewState["customertData"] as DataSet;
  //To get the deleted row in the DataSet.GetChanges method we need to use Delete method
  ds.Tables[0].Rows[e.RowIndex].Delete();

  grdDsClient.DataSource = ds;
  grdDsClient.DataBind();
  ViewState["customertData"] = ds;
  hdnAllowRedirect.Value = "false";
}

 

RowUpdating 


protected void grdDsClient_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
  string key = e.Keys["uniqueID"].ToString();
  string dsClient = ((TextBox)(grdDsClient.SelectedRow.FindControl("txtCustomerId"))).Text;
  DataSet ds = ViewState["customertData"] as DataSet;

  DataView dv = ds.Tables[0].DefaultView;
  dv.Sort = "customerId";
  DataRowView[] drv = dv.FindRows(dsClient);

  if (drv.Count() > 0)
  {
	foreach(DataRowView dvTemp in drv)
	{
	  if (dvTemp["uniqueID"].ToString() != key)
	  {
		lblMessage.Text = "Duplicate value for the field DS-Client# is not allowed.";
		lblMessage.ForeColor = System.Drawing.Color.FromArgb(170, 46, 46);//red
		return;
	  }
	}
  }

  dv.Sort = "uniqueID";
  drv = dv.FindRows(key);
  drv = dv.FindRows(key);

  if (drv.Count() > 0)
  {
	drv[0]["customerId"] = ((TextBox)(grdDsClient.SelectedRow.FindControl("txtCustomerId"))).Text;
	drv[0]["IsExcluded"] = ((CheckBox)(grdDsClient.SelectedRow.FindControl("chkIsExcluded"))).Checked;
  }

  grdDsClient.EditIndex = -1;
  grdDsClient.SelectedIndex = -1;
  grdDsClient.DataSource = ds;
  grdDsClient.DataBind();

  ViewState["customertData"] = ds;
  hdnAllowRedirect.Value = "false";
}

Now finally, when the user clicks the Update Button, the application will fire the database query for only those rows of the DataSet which has been either added, modified, or deleted.


try
{
	foreach (DataRow dr in dsClient.Tables[0].Rows)
	{
		if (dr.RowState == DataRowState.Deleted)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "Update [ManageCustomer] SET IsDeleted = 1, ModifiedBy = @ModifiedByDel, ModifiedOn = @ModifiedOnDel WHERE customerId = @customerIdDel";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add("@customerIdDel", SqlDbType.VarChar, 255).Value = dr["customerId", DataRowVersion.Original];
			cmd.Parameters.Add("@ModifiedByDel", SqlDbType.UniqueIdentifier).Value = varProviderUserKey;
			cmd.Parameters.Add("@ModifiedOnDel", SqlDbType.DateTime).Value = DateTime.Now;cmd.ExecuteNonQuery();RecordUserActivity(varProviderUserKey, "VX", dr["customerId",DataRowVersion.Original].ToString(), con, tran);
		}
		else if (dr.RowState == DataRowState.Added)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "INSERT INTO [ManageCustomer] (uniqueID, customerId, VarID, [IsExcluded], [AddedBy], [AddedOn]) VALUES (@uniqueIDAdd, @customerIdAdd, @VarIDAdd, @IsExcludedAdd, @AddedByAdd, @AddedOnAdd)";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add(new SqlParameter("@uniqueIDAdd", dr["uniqueID"]));
			cmd.Parameters.Add(new SqlParameter("@customerIdAdd", dr["customerId"]));
			cmd.Parameters.Add(new SqlParameter("@VarIDAdd", varProviderUserKey.ToString()));
			cmd.Parameters.Add(new SqlParameter("@IsExcludedAdd", dr["IsExcluded"]));
			cmd.Parameters.Add(new SqlParameter("@AddedByAdd", varProviderUserKey.ToString()));
			cmd.Parameters.Add(new SqlParameter("@AddedOnAdd", DateTime.Now));cmd.ExecuteNonQuery();
			RecordUserActivity(varProviderUserKey, "VA", dr["customerId"].ToString(), con, tran);
		}
		else if (dr.RowState == DataRowState.Modified)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "Update [ManageCustomer] SET customerId = @customerIdNew, IsExcluded = @IsExcludedNew, ModifiedBy = @ModifiedByUpdt, ModifiedOn = @ModifiedOnUpdt WHERE uniqueID = @uniqueIDUpdt";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add("@customerIdNew", SqlDbType.VarChar, 255).Value = dr["customerId", DataRowVersion.Current];
			cmd.Parameters.Add("@IsExcludedNew", SqlDbType.Bit).Value = dr["IsExcluded"];
			cmd.Parameters.Add("@ModifiedByUpdt", SqlDbType.UniqueIdentifier).Value = varProviderUserKey;
			cmd.Parameters.Add("@ModifiedOnUpdt", SqlDbType.DateTime).Value = DateTime.Now;
			cmd.Parameters.Add("@uniqueIDUpdt", SqlDbType.UniqueIdentifier).Value = dr["uniqueID", DataRowVersion.Original];

			cmd.ExecuteNonQuery();

			if(dr["IsExcluded",DataRowVersion.Original] != dr["IsExcluded",DataRowVersion.Current] &&
			((bool)(dr["IsExcluded",DataRowVersion.Current])) == true)
				RecordUserActivity(varProviderUserKey, "VE", dr["customerId"].ToString(), con, tran);
		}
	}
	tran.Commit();
}
catch (Exception ex)
{
	tran.Rollback();
}
finally
{
	con.Close();
}

 

Explanation

Here, when the page is loaded for the very first time, I have loaded the information from the DataBase into a DataSet. This DataSet is then getting added in the ViewState of the page. Now whenever the User is doing some modifications, it will get updated in the ViewState of that Page only. Once the User clicks the Update button, I am checking, inside a seperate function(which is being called from the click event of the Update button), the RowState of each DataRow from the DataSet. More info regarding the enum DataRowState can be found here.

So for the modified rows only I am firing the database query. I have also kept the entire queries in the same transaction tran, so that I can Roll Back all the changes for any errors.

Finally in the click event of the Update Button, I am calling the DataBase.AcceptChanges() method to clear the RowState of all the rows of the DataBase before adding it again to the ViewState of the page.

Convert DateTime.MinValue of a asp.net GridView Column to a String Literal "Never"

Description

Here, the GridView is displaying a standard registered Member Details from SQL Database. One of the fields is the Last Login Date, which displays the member’s last date of login. Now, if the member has never logged in to the system it was displaying the DateTime.MinValue (1/1/0001), which was not optimum. So it was decided that for the DateTime.MinValue the GridView column should display a string “Never”.

Solution

The solution is to apply the if-else conditioning for the ‘Last Login Date’ column. First of all select the option ‘Convert the selected Column into a Template Field‘.

You will see that in the source, certain templates have been added. In the of that column, a Label has been added whose text property is bound to the column Last Login Date. Now for the text property of the Label we write the following code.


<ItemTemplate>
	<asp:Label ID="lblLastLogin" runat="server" Text='<%# (Eval("LastLoginDate", "{0:d}")=="1/1/0001" ? "Never" : Eval("LastLoginDate")) %>'></asp:Label>
</ItemTemplate>

Explanation

This is the standard if-else conditioning. Here we are formatting the Date Time field to Short Date Time ({0:d}) i.e. we are removing the time component from it. Then we’re checking its value against the string ‘1/1/0001’ (min date value). If true, then the value will be replaced by the string ‘Never’ otherwise the full date time value will be displayed (including the time component).