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