When I talk about C# Dataset quote that it can also be used for insertion of data into the table too. In this post, we are going to learn how to.
- Data connection with SQL
- Command builder object
In our example, we had MiniPurchaseInfo table and dataset associated with it. First thing first, the dataset
SqlDataAdapter MiniPurchaseInfo = new SqlDataAdapter("select * from minipurchaseinfo", con); MiniPurchaseInfoDataSet = new DataSet(); MiniPurchaseInfo.Fill(MiniPurchaseInfoDataSet, "minipurchaseinfo");
We simply make the query, filling the information to the dataset and so on. The next step is to create a row with the DataRow object and add the data row to our dataSet object.
DataRow dro; dro = MiniPurchaseInfoDataSet.Tables.NewRow(); dro["entryno"] = entryNo; dro["supinvoice"] = txt_invoice.Text.ToString(); dro["supid"] = SUPID; dro["netamount"] = net;
add the row to the dataset.
The final step is to set up the command build which has the capability of insertion and deletion operations with DB adapter and dataset.
System.Data.SqlClient.SqlCommandBuildercmdbuilder = new System.Data.SqlClient.SqlCommandBuilder(MiniPurchaseInfo);
Set insert command of the adapter from the command builder object
MiniPurchaseInfo.InsertCommand = cmdbuilder.GetInsertCommand();
Call the update command of the adapter which will update the new row stored in the dataset into the database table.
int r = MiniPurchaseInfo.Update(MiniPurchaseInfoDataSet.Tables);
The update will return a none zero value when the insertion performed.