DataTable - Adding, Modifying, Deleting, Filtering, Sorting rows & Reading/Writing from/to Xml

Creating a DataTable

To create a DataTable, you need to use System.Data namespace, generally when you create a new class or page, it is included by default by the Visual Studio. Lets write following code to create a DataTable object. Here, I have pased a string as the DataTable name while creating DataTable object.

// instantiate DataTable
DataTable dTable = new DataTable("Dynamically_Generated");



Creating Columns in the DataTable

To create column in the DataTable, you need to use DataColumn object. Instantiate the DataColumn object and pass column name and its data type as parameter. Then call add method of DataTable column and pass the DataColumn object as parameter.


// create columns for the DataTable
DataColumn auto = new DataColumn("AutoID", typeof(System.Int32));
dTable.Columns.Add(auto);
// create another column
DataColumn name = new DataColumn("Name", typeof(string));
dTable.Columns.Add(name);
// create one more column
DataColumn address = new DataColumn("Address", typeof(string));
dTable.Columns.Add(address);



Specifying AutoIncrement column in the DataTable

To specify a column as AutoIncrement (naturally it should be an integer type of field only), you need to set some properties of the column like AutoIncrement, AutoIncrementSeed. See the code below, here I am setting the first column "AutoID" as autoincrement field. Whenever a new row will be added its value will automatically increase by 1 as I am specified AutoIncrementSeed value as 1.


// specify it as auto increment field
auto.AutoIncrement = true;
auto.AutoIncrementSeed = 1;
auto.ReadOnly = true;
If you want a particular column to be a unique column ie. you don't want duplicate records into that column, then set its Unique property to true like below.
auto.Unique = true;



Specifying Primary Key column in the DataTable

To set the primary key column in the DataTable, you need to create arrays of column and store column you want as primary key for the DataTable and set its PrimaryKey property to the column arrays. See the code below.


// create primary key on this field
DataColumn[] pK = new DataColumn[1];
pK[0] = auto;
dTable.PrimaryKey = pK;
Till now we have created the DataTable, now lets populate the DataTable with some data.



Populating data into DataTable

There are two ways to populate DataTable.
Using DataRow object
Look at the code below, I have created a DataRow object above the loop and I am assiging its value to the dTable.NewRow() inside the loop. After specifying columns value, I am adding that row to the DataTable using dTable.Rows.Add method.


// populate the DataTable using DataRow object
DataRow row = null;
for (int i = 0; i < 5; i++)
{
     row = dTable.NewRow();
     row["AutoID"] = i + 1;
     row["Name"] = i + " - Ram";
     row["Address"] = "Ram Nagar, India - " + i;
     dTable.Rows.Add(row);
}

Instead of using the column name, you can use ColumnIndex too, however it is not suggested as you might want to add a column in the mid of the table then you will need to change your code wherever you have specified the index of the column. Same applies while reading or writing values into Database column. Asiging the value of column using Arrays In following code, I have specified the values of every column as the array separated by comma (,) in the Add method of the dTable.Rows.

 // manually adding rows using array of values
 dTable.Rows.Add(6, "Manual Data - 1", "Manual Address - 1, USA");
dTable.Rows.Add(7, "Manual Data - 2", "Manual Address - 2, USA");


Modifying data into DataTable

Modifying Row Data To edit the data of the row, sets its column value using row index or by specifying the column name. In below example, I am updating the 3rd row of the DataTable as I have specified the row index as 2 (dTable.Rows[2]).

 // modify certain values into the DataTable
dTable.Rows[2]["AutoID"] = 20;
dTable.Rows[2]["Name"] = "Modified";
dTable.Rows[2]["Address"] = "Modified Address";
dTable.AcceptChanges();


Deleting Row

To delete a row into DataTable, call the rows.Delete() method followed by AcceptChanges() method. AcceptChanges() method commits all the changes made by you to the DataTable. Here Row[1] is the index of the row, in this case 2nd row will be deleted as in collection (here rows collection) count start from 0.

// Delete row
 dTable.Rows[1].Delete();
dTable.AcceptChanges();


Filtering data from DataTable

To filter records from the DataTable, use Select method and pass necessary filter expression. In below code, the 1st line will simply filter all rows whose AutoID value is greater than 5. The 2nd line of the code filters the DataTable whose AutoID value is greater than 5 after sorting it.

DataRow[] rows = dTable.Select(" AutoID > 5");
DataRow[] rows1 = dTable.Select(" AutoID > 5", "AuotID ASC");

Note that Select method of the DataTable returns the array of rows that matche the filter expression. If you want to loop through all the filtered rows, you can use foreach loop as shown below. In this code, I am adding all the filtered rows into another DataTable.

foreach (DataRow thisRow in rows)
{
// add values into the datatable
dTable1.Rows.Add(thisRow.ItemArray);
}

Working with Aggregate functions 

We can use almost all aggregate functions with DataTable, however the syntax is bit different than standard SQL.
Suppose we need to get the maximum value of a particular column, we can get it in the following way.

DataRow[] rows22 = dTable.Select("AutoID = max(AutoID)");
string str = "MaxAutoID: " + rows22[0]["AutoID"].ToString();

To get the sum of a particular column, we can use Compute method of the DataTable. Compute method of the DataTable takes two argument. The first argument is the expression to compute and second is the filter to limit the rows that evaluate in the expression. If we don't want any filteration (if we need only the sum of the AutoID column for all rows), we can leave the second parameter as blank ("").

object objSum = dTable.Compute("sum(AutoID)", "AutoID > 7");
string sum = "Sum: " + objSum.ToString();
// To get sum of AutoID for all rows of the DataTable
object objSum = dTable.Compute("sum(AutoID)", "");



Sorting data of DataTable

Oops !. There is no direct way of sorting DataTable rows like filtering (Select method to filter DataRows).
There are two ways you can do this.

Using DataView
See the code below. I have created a DataView object by passing my DataTable as parameter, so my DataView will have all the data of the DataTable. Now, simply call the Sort method of the DataView and pass the sort expression. Your DataView object have sorted records now, You can either directly specify the Source of the Data controls object like GridView, DataList to bind the data or if you need to loop through its data you can use ForEach loop as below.

// Sorting DataTable
DataView dataView = new DataView(dTable);
dataView.Sort = " AutoID DESC, Name DESC";
foreach (DataRowView view in dataView)
{
Response.Write(view["Address"].ToString());
}

Using DataTable.Select() method
Yes, you can sort all the rows using Select method too provided you have not specified any filter expression. If you will specify the filter expression, ofcourse your rows will be sorted but filter will also be applied. A small drawback of this way of sorting is that it will return array of DataRows as descibed earlier so if you are planning to bind it to the Data controls like GridView or DataList you will have for form a DataTable by looping through because directly binding arrays of rows to the Data controls will not give desired results.

DataRow[] rows = dTable.Select("", "AutoID DESC");
Writing and Reading XmlSchema of the DataTable
If you need XmlSchema of the DataTabe, you can use WriteXmlSchema to write and ReadXmlSchema to read it. There are several overloads methods of both methods and you can pass filename, stream, TextReader, XmlReader etc. as the parameter. In this code, the schema will be written to the .xml file and will be read from there.

// creating schema definition of the DataTable
dTable.WriteXmlSchema(Server.MapPath("~/DataTableSchema.xml"));
// Reading XmlSchema from the xml file we just created
DataTable dTableXmlSchema = new DataTable();
dTableXmlSchema.ReadXmlSchema(Server.MapPath("~/DataTableSchema.xml"));



Reading/Writing from/to Xml

If you have a scenario, where you need to write the data of the DataTable into xml format, you can use WriteXml method of the DataTable. Note that WriteXml method will not work if you will not specify the name of the DataTable object while creating it. Look at the first code block above, I have passed "Dynamically_Generated" string while creating the instance of the DataTable. If you will not specify the name of the DataTable then you will get error as WriteXml method will not be able to serialize the data without it.

// Note: In order to write the DataTable into XML,
// you must define the name of the DataTable while creating it
// Also if you are planning to read back this XML into DataTable, you should define the   XmlWriteMode.WriteSchema too
// Otherwise ReadXml method will not understand simple xml file
dTable.WriteXml(Server.MapPath("~/DataTable.xml"), XmlWriteMode.WriteSchema);
// Loading Data from XML into DataTable
DataTable dTableXml = new DataTable();
dTableXml.ReadXml(Server.MapPath("~/DataTable.xml"));

If you are planning to read the xml you have just created into the DataTable sometime later then you need to specify XmlWriteMode.WriteSchema too as the 2nd parameter while calling WriteXml method of the DataTable otherwise normally WriteXml method doesn't write schema of the DataTable. In the abscence of the schema, you will get error (DataTable does not support schema inference from Xml) while calling ReadXml method of the DataTable.

Store XML data into database using stored procedure :

XML variables in SQL Server 2005 make it easy to "shred" XML strings into relational data. The main new methods you'll need to use are value() and nodes() which allow us to select values from XML documents.

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

Which gives us the following three rows:
3
6
15

Here's a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that's done, we can join against the @Products table as if it were any other table in the database.

Alter PROCEDURE SelectByIdList(@productIds xml) AS
begin
DECLARE @Products TABLE (ID int)

INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
select * from @Products
end

To test the stored procedure run the below command:

     EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'

In the page:
//To create a xml file write the below code

public static string BuildXmlString(string xmlRootName, string[] values)
{
    StringBuilder xmlString = new StringBuilder();
    xmlString.AppendFormat("<{0}>", xmlRootName);
    for (int i = 0; i < values.Length; i++)
    {
    xmlString.AppendFormat("<value>{0}</value>", values[i]);
    }
    xmlString.AppendFormat("</{0}>", xmlRootName);
    return xmlString.ToString();
}

Add dynamic textbox & link button to web page

In this article I will explain how to create dynamic TextBox control in asp.net. I will also explain how to retain value across postbacks and also how to recreate the controls on each postback. In addition I will explain how to dynamically attach event handlers to TextBox control in asp.net. 

To start with I added a PreInit event to the Page and added the following snippet in it

C#

Panel pnlTextBox;

protected void Page_PreInit(object sender, EventArgs e)

{

    //Create a Dynamic Panel

    pnlTextBox = new Panel();

    pnlTextBox.ID = "pnlTextBox";

    pnlTextBox.BorderWidth = 1;

    pnlTextBox.Width = 300;

    this.form1.Controls.Add(pnlTextBox);

    //Create a LinkDynamic Button to Add TextBoxes

    LinkButton btnAddtxt = new LinkButton();

    btnAddtxt.ID = "btnAddTxt";

    btnAddtxt.Text = "Add TextBox";

    btnAddtxt.Click += new System.EventHandler(btnAdd_Click);

    this.form1.Controls.Add(btnAddtxt);

   

    //Recreate Controls

    RecreateControls("txtDynamic", "TextBox");

}

As you will notice above I have created the following controls

1 Dynamic panel pnlTextBox and added it to the form control on the page

2. Dynamic LinkButton btnAddTxt attached a Click event btnAdd_Click to it and added to the form control.

3. A function called RecreateControls is being called which I’ll explain later in the article

On the Click event of the dynamic LinkButton I have added the following event

C#

protected void btnAdd_Click(object sender, EventArgs e)

{

    int cnt = FindOccurence("txtDynamic");

    CreateTextBox("txtDynamic-" + Convert.ToString(cnt + 1));

}

As you will notice I am calling two functions

1. FindOccurence

2. CreateTextBox

The FindOccurence function as the name suggests gets the occurrence of the Dynamic TextBox in the Request.Form collection. The basic idea is that I have given ID is a common pattern that is all IDs are of the form txtDynamic e.g. txtDynamic-1, txtDynamic-2 and so on.

C#

private int FindOccurence(string substr)

{

    string reqstr = Request.Form.ToString();

    return ((reqstr.Length - reqstr.Replace(substr, "").Length)

                      / substr.Length);

}

Now the CreateTextBox as the name suggests is used to create dynamic TextBox. The function accepts ID as parameter

C#

private void CreateTextBox(string ID)

{

    TextBox txt = new TextBox();

    txt.ID = ID;

    txt.AutoPostBack = true;

    txt.TextChanged += new EventHandler(OnTextChanged);

    pnlTextBox.Controls.Add(txt);

    Literal lt = new Literal();

    lt.Text = "<br />";

    pnlTextBox.Controls.Add(lt);

}

As you will notice I am creating a new TextBox and adding Items to it. Once done that I am attaching TextChanged Event Handler and setting the AutoPostBackproperty to true. Finally I am adding it to the panel pnlTextBox.

    

The SelectedIndexChanged Event Handler is given below.

C#

protected void OnTextChanged(object sender, EventArgs e)

{

    TextBox txt = (TextBox)sender;

    string ID = txt.ID;

    ClientScript.RegisterClientScriptBlock(this.GetType(), "Alert",

                 "<script type = 'text/javascript'>alert('" + ID  +

                  " fired OnTextChanged event');</script>");

   

    //Place the functionality here

} 

In the above event I am finding the ID of the caller TextBox based by Type Casting the Sender parameter and then firing a JavaScript alert to notify which TextBox fired the event as shown in figure below

Now the most important function is RecreateControls whose job is to recreate all the TextBox on each postback

       

C#

private void RecreateControls(string ctrlPrefix, string ctrlType)

{

    string[] ctrls = Request.Form.ToString().Split('&');

    int cnt = FindOccurence(ctrlPrefix);

    if (cnt > 0)

    {

        for (int k = 1; k <= cnt; k++)

        {

            for (int i = 0; i < ctrls.Length; i++)

            {

                if (ctrls[i].Contains(ctrlPrefix + "-" + k.ToString())

                    && !ctrls[i].Contains("EVENTTARGET"))

                {

                    string ctrlID = ctrls[i].Split('=')[0];

                    if (ctrlType == "TextBox")

                    {

                        CreateTextBox(ctrlID);

                    }

                    break;

                }

            }

        }

    }

}

As you will notice above I first find the occurrence of a particular string here txtDynamic in the Request.Form collection and then I loop through each item and keep adding the TextBox using the CreateTextBox function described earlier.

Add dynamic controls to web page

Suppose your requirement is to add dynamic controls to your web page. Then do the following.
Drag & drop a lable control.
Go to the aspx.cs page & write the below code.


TextBox t1;
protected void Page_Load(object sender, EventArgs e)
{
        if(!IsPostBack)
        t1.Text = "Text Box1";
}
override protected void OnInit(EventArgs e)
{     
        t1 = new TextBox();
        t1.ID = "TextBox1";
        t1.Style["Position"] = "Absolute";
        t1.Style["Top"] = "25px";
        t1.Style["Left"] = "100px";
        form1.Controls.Add(t1);     
        this.t1.TextChanged += new System.EventHandler(this.TextBox_TextChanged);
        base.OnInit(e);
}
private void TextBox_TextChanged(object sender, System.EventArgs e)
{
        TextBox txtBoxSender = (TextBox)sender;
        string strTextBoxID = txtBoxSender.ID;

        switch (strTextBoxID)
        {
            case "TextBox1":
                Label1.Text = "TextBox1 text was changed";
                break;
        }
}

SQL Server Function to Split Comma in Text

create FUNCTION [dbo].[Fn_Split]
(
 @RowData nvarchar(max),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 While (Charindex(@SplitOn,@RowData)>0)
 Begin
  Insert Into @RtnValue (data)
  Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
  Set @Cnt = @Cnt + 1
 End
 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))
 Return
END

To run the code type :

select * from  dbo.Fn_Split ('Item 1,Item 2,Item 3,Item 4,Item 5,Item 6',',' )

Hope this helps

Check TextArea Max Length using Javascript

If u have a textbox and your requirement is to find the length of the textbox & limit the user to enter the number of charecters then do the following .

Write the below javascript code :

<script type="text/javascript">
function ismaxlength(objTxtCtrl,nLength)
{
  if (objTxtCtrl.getAttribute && objTxtCtrl.value.length > nLength)
     objTxtCtrl.value = objTxtCtrl.value.substring(0, nLength);
 if(document.all)
    document.getElementById('lblCaption').innerText=objTxtCtrl.value.length +' Out Of '+nLength;
 else
    document.getElementById('lblCaption').textContent=objTxtCtrl.value.length +' Out Of '+nLength;
}
</script>

Then drag & drop lable & textbox control in your page.

<asp:TextBox ID="TextBox1" runat="server" TextMode="MultiLine" onkeyup="return ismaxlength(this,125)"></asp:TextBox>
<asp:Label ID="lblCaption" style="font-family:Tahoma;font-size:1em;font-weight:bold" runat="server" Text=""></asp:Label>