How to change NewDataSet xml root name while creating xml using WriteXml() method of Dataset?

This is default behavior of WriteXml() method when we create xml then it adds  <NewDataSet> node as root xml node and <Table> as child node. In this article, I am explaining- How to change <NewDataSet> xml root name while creating xml using WriteXml() method of Dataset?

Let first see the default case-

Suppose you we have one DataSet object ds and we are filling it will data source, like this-

Dataset ds= new DataSet();
ds= datasource;

After that we are using WriteXml() method to create XML, like this-

ds.WriteXml("FileName");

Then in this case output will be like this-

<NewDataSet>
  <Table>
    <Emp_ID>717</Emp_ID>
    <Emp_Name>Jitendra Faye</Emp_Name>
    <Salary>35000</Salary>
  </Table>
----------
----------
----------
</NewDataSet>

Here we can see that <NewDataSet> and   <Table> is default tag here.

Now to change these default tag follow this code, Here I am changing <NewDataSet> tag as <EmployeeRecords> tag and <Table> tag as <Employee> tag.

Here is code-

Dataset ds= new DataSet();
ds= datasource;

//Giving Name to DataSet, which will be displayed as root tag

ds.DataSetName="EmployeeRecords";

//Giving Name to DataTable, which will be displayed as child tag

ds.Tables[0].TableName = "Employee";

//Creating XML file
ds.WriteXml("FilePath");

Now after executing this above code generated XML file will be like this-

<EmployeeRecords>
  <Employee>
    <Emp_ID>717</Emp_ID>
    <Emp_Name>Jitendra Faye</Emp_Name>
    <Salary>35000</Salary>
  </Employee>
----------
----------
----------
</EmployeeRecords>


Here we can see that DataSet name is coming as root tag and DataTable name is coming as child tag.

Note-

Before giving name to DataTable (ds.Tables[0].TableName) first check that it should not be null otherwise it will cause an error.



Thanks

How to get Identity column value after inserting record using DataAdapter?

DataAdapter-

DataAdapter is an object which provides facility to interact with database. Using this object we can fill any DataSet class object or we can perform some command over database. Generally it is used for filling DataSet with database result but it can also be used for inserting record to database.

After inserting record, suppose if we want to get identity column value of new record then again we have to query to database. This process degrade the application performance because for this we have to make two trips of database which should not be happen. This task cab be done using one database trip only.  In this article, I am explaining - How to get Identity column value after inserting record using DataAdapter?

Here is code-        

            SqlConnection con = new SqlConnection("Connection string");
            con.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter("select * from TableName", con);
          
            //Assigning schema of Table to DataSet
            da.FillSchema(ds, System.Data.SchemaType.Source, "TableName");
            da.Fill(ds, "TableName");

            //Creating object of CommandBuilder to perform insert operation over DataSet
            SqlCommandBuilder cmdb = new SqlCommandBuilder(da);
          
            //Adding new row to DataSet
            DataRow row = ds.Tables[0].NewRow();
          
            //Filling data to row
            row["col1"] = "value1";
            row["col2"] = "value2";

            //Adding newly created row to DataSet object
            ds.Tables[0].Rows.Add(row);

            //Updating changes to DataSet
            da.Update(ds, "TableName");

            //Getting identity column value of newly added record
            int identityValue = Convert.ToInt32 ( row["identityColunmName"]);

In above code, I am inserting new record to database using DataAdapter, I have used one DataAdapter class object - "da”. First, I am getting record into DataSet object - "ds", but for getting identity column value we need to assign table schema to DataSet object before filling.

Like this-

           //Assigning schema of Table to DataSet
            da.FillSchema(ds, System.Data.SchemaType.Source, "TableName");
            da.Fill(ds, "TableName");

After execution of these lines table schema will be assigned to DataSet object with identity column, so that while adding new row to DataSet we can access identity column value.

Like this-

           //Getting identity column value of newly added record
            int identityValue = Convert.ToInt32 (row["identityColunmName"]);
 
Here "identityColunmName" is name of identity column in the table, once we will add new row to DataSet object we can access identity column value of newly added record.

Note-

In this example it is must that database table should contain identity column otherwise this example will not work. Next value for column can be accessed only when that column is identity column.


Thanks

How to search record in DataTable?

DataTable-

DataTable is server side object in .Net which is collection of Rows and Columns. It represents the in-memory table's data. It can hold data of any single table but using join we can hold data from multiple table. In programming it is used to hold data after getting data from database.

Generally we use DataTable to hold result-set (query's result), but in some of the cases we want to perform searching operation on DataTable. Here I am explaining - How to search record in DataTable?

If we want to search any data in DataTable object then it can be done by using looping, but this is not a good approach because DataTable have some inbuilt methods to complete this task.

There are following methods-

1. Find ()  - Using this method we can perform searching on DataTable based on Primary  key, it   
                    returns DataRow object.

2. Select() - Using this method we can perform searching on DataTable based on search criteria   
                    means by using column name we can search , it returns array of DataRow object.

Here I am explaining both methods, Follow this code-
 
 private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                //Creating DataTable object
                DataTable dtRecords = new DataTable();

                //Creating DataColumn object
                DataColumn colName = new DataColumn("Name");
                DataColumn colID = new DataColumn("ID");

                //Adding DataColumns to DataTable
                dtRecords.Columns.Add(colID);
                dtRecords.Columns.Add(colName);

                //Creating Array of DataColumn to set primary key for DataTable
                DataColumn[] dataColsID = new DataColumn[1];
                dataColsID[0] = colID;

                //Setting primary key
                dtRecords.PrimaryKey = dataColsID;

                //Adding static data to DataTable by creating DataRow object
                DataRow row1 = dtRecords.NewRow();
                row1["ID"] = 1;
                row1["Name"] = "Rajendra";
                dtRecords.Rows.Add(row1);

                DataRow row2 = dtRecords.NewRow();
                row2["ID"] = 2;
                row2["Name"] = "Rakesh";
                dtRecords.Rows.Add(row2);

                DataRow row3 = dtRecords.NewRow();
                row3["ID"] = 3;
                row3["Name"] = "Pankaj";
                dtRecords.Rows.Add(row3);

                DataRow row4 = dtRecords.NewRow();
                row4["ID"] = 4;
                row4["Name"] = "Sandeep";
                dtRecords.Rows.Add(row4);

                DataRow row5 = dtRecords.NewRow();
                row5["ID"] = 5;
                row5["Name"] = "Rajendra";
                dtRecords.Rows.Add(row5);

                //Find() Method

                //Finding data in DataTable based on primary key using Find() method
                DataRow FindResult = dtRecords.Rows.Find("3");

                if (FindResult != null)
                {
                   //Showing result to grid
                    DataTable dtTemp = new DataTable();
                    dtTemp = dtRecords.Clone();
                    dtTemp.Rows.Add(FindResult.ItemArray);
                    dataGridFind.DataSource = dtTemp;
                }

               //Select () Method

               //Selecting data from DataTable based on search criteria using Select() method
                DataRow[] SelectResult = dtRecords.Select("name='Rajendra'");

                if (SelectResult != null)
                {
                   //Showing result to grid
                    DataTable dtTemp = SelectResult.CopyToDataTable();
                    dataGridSelect.DataSource = dtTemp;
                }
            }
            catch (Exception)
            {
                //Handle exception here
                throw;
            }
 }

Note -  

Before using Find () of DataTable it is must that we need to Primary key for DataTable object. Because Find () method works based on primary key value only.

In above code, I have created on DataTable object (dtRecords) using DataTable class, and I have filled this DataTable object with some static records. For this I have used DataColumn and DataRow classes. I have set Primary key column for DataTable object, so that we can use Find () method over DataTable.

//Setting primary key
 dtRecords.PrimaryKey = dataColsID;

Here  dataColsID is array of DataColumn. After setting primary key to DataTable, I have Find () method like this-

//Finding data in DataTable based on primary key using Find () method
 DataRow FindResult = dtRecords.Rows.Find("3");

Here Find method will return result as DataRow object, for fetching data from DataTable object using Find () method we need to pass primary key value because based on that it will return result.

For getting data using Select () method, I have written code like this-

//Selecting data from DataTable based on search criteria using Select () method
DataRow[] SelectResult = dtRecords.Select("name='Rajendra'");

Select () method works based on search criteria, Here ("name='Rajendra'“) is search criteria for Select () method, it returns array of DataRow object.

Finally to show result I have used two separate DataGridVIew controls and output will be like this-

As we can see in image, For Find () method, one record in coming because in DataTable object we have only one record with ID=3. And for Select () Method, 2 records are coming because we have 2 records in DataTable obejct with name = "Rajendra".

To perform search operation on DataTable Find() and Select() methods can be used but both methods have different criteria. 

Note - 

1. If we have primary key in table then Find() and Select() both methods can be used
2. If we don't have primary key then we can use only Select() method.


Thanks

How to convert DataTable to HTML Table?

Asp.Net provides different data controls to display data on page but in some cases if you don't want to use those controls then we can simply use Custom table to display data on page. Here I am explaining, How we can convert DataTable (data source) to HTML table?

Asp.Net provides classes like Table, TableRow, TableCell to create table using code, we can easily create table using these classes and we can display data in created table. In this example, I have Used Table, TableRow, TableCell Classes to create Table.

Here is code-

protected void btnGetData_Click(object sender, EventArgs e)
    {
        //Code to get data from database
        SqlConnection cn = new SqlConnection("Connection string");
        cn.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select ID,Name from tabEmployee", cn);
        da.Fill(dt);
      
        //Code to show Data table’s data to HTML table
        Table tbl = new Table();
        tbl.CellPadding = 0;
        tbl.CellSpacing = 0;

       //Flag to add Header for HTML table
        bool AddedColumnName = false;

        //Looping for getting Data table’s data
        foreach (DataRow dtRow in dt.Rows)
        {
            TableRow row = new TableRow();
            foreach (DataColumn col in dt.Columns)
            {
                //Adding heading to HTML table
                if (AddedColumnName == false)
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.BackColor = System.Drawing.Color.Green;
                    cell.ForeColor = System.Drawing.Color.White;
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    cell.Text = col.ColumnName;
                    row.Cells.Add(cell);
                }
                //Adding data to HTML table
                else
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.Text = dtRow[col].ToString();
                    row.Cells.Add(cell);
                }
            }

            tbl.Rows.Add(row);
            AddedColumnName = true;
        }

        //Adding created HTML table to Panel control
        pnlData.Controls.Add(tbl);
    }

In this code, I have read data from database and stored on one data source- DataTable.
Now using looping, I have iterated all the data to create HTML table. First I am creating header for the table, for this I have written code like this- 

//Adding heading to HTML table
                if (AddedColumnName == false)
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.BackColor = System.Drawing.Color.Green;
                    cell.ForeColor = System.Drawing.Color.White;
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    cell.Text = col.ColumnName;
                    row.Cells.Add(cell);
                }

This line of code will add header to the table by getting column names from result set, These column names cab be changed in select query.

Now to add data of result set I have used this code- 

//Adding data to HTML table
                else
                {
                    TableCell cell = new TableCell();
                    cell.BorderStyle = BorderStyle.Solid;
                    cell.BorderWidth = 2;
                    cell.BorderColor = System.Drawing.Color.Gray;
                    cell.Text = dtRow[col].ToString();
                    row.Cells.Add(cell);
                }

This code will be executed for each row in result set except header row. For creating HTML table, I have used Table, TableRow and TableCell classes, in foreach loop for every row in DataTable I am creating object for TableRow class and for every column object for TableCell class.

To add data to HTML Table cell using this code-

row.Cells.Add(cell);

Here "row" is object of TableRow and "cell" is object of TableCell class.
 
Output-

Thanks

Auto complete TextBox using jquery and Database.

Auto complete TextBox is common for most of the application, but there are different ways to implement this functionality. Here I am explaining this functionality using JQuery and Database. In this example, I am implementing Auto complete functionality for "Name" field in database table. I have used following JavaScript and CSS files to complete this functionality.

JavaScript Files-
jquery-1.4.1.min.js
jquery-ui-1.8.20.custom.min.js

CSS Files-
jquery-ui-1.8.20.custom.css

Now Include these files in your application like this-

Make same directory structure as I have shown you in image.

Now design your .aspx page like this-
 
AutoComplete.aspx

<head runat="server">
    <script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
    <script src="Scripts/jquery-ui-1.8.20.custom.min.js" type="text/javascript"></script>
    <link href="Css/jquery-ui-1.8.20.custom.css" rel="stylesheet" type="text/css" />
    <title>Auto complete TextBox</title>
    <script type="text/javascript">
        function AutoComplete() {
            var names = $('#<%= hdnNames.ClientID %>').val();
            if (names != null && names != "") {
                var arrOfNames = names.split("?");
                $("#<%=txtName.ClientID %>").autocomplete({
                    source: arrOfNames
                });
            }
        }
    </script>
</head>

<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    Enter Name:
                </td>
                <td>
                    <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                    <asp:HiddenField ID="hdnNames" runat="server" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>


In this page I have taken one HiddenField, why- After reading all the names from table I am concatenating names with separator character "?" and  assigning to  HiddenField.

Now in script code (AutoComplete() function ), I am implementing functionality for auto complete TextBox  after splitting value of HiddenField.
 
Server side code-

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetNames();
            ClientScript.RegisterStartupScript(typeof(Page), "AutoComplete", "AutoComplete();",true);
        }
    }
    private void GetNames()
    {
        try
        {
            SqlConnection con = new SqlConnection("Connection string");
            con.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter("select name from employee", con);
            da.Fill(ds);
            if (ds!=null)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    //concatenating value with separator character -"?"
                    hdnNames.Value = hdnNames.Value + row[0].ToString() + "?"; 
                }
            }
        }
        catch (Exception)
        {  
            throw;
        }
    }

In server side code, I have defined one function (GetNames()) to get names form employee table and after concatenating assign   to HiddenField value. The main backbone for this functionality is implementing by using this line-

ClientScript.RegisterStartupScript(typeof(Page), "AutoComplete", "AutoComplete();",true);

Using this line, I am binding Auto complete feature to txtName textBox.

Now done, After running the application output will be like this-

Thanks.