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 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.