Inserting records using a dataset dataset

Insert statements enable data insertions, and data insertions can be done using DataSet objects. In order to populate the data in the database into a dataset, you must first use the method of the DataAdapter object to implement the population, and when the data is populated, the developer can add records to the DataSet object and then use the Update method to insert the records into the database. The steps to update a record using a dataset are as follows:

Q Create a Connection object.

Q Create a DataAdapter object.

Q Initialize the adapter.

Q Use the Fill method of the data adapter to execute the SELECT command and populate the dataset.

Q Creates a new row using the NewRow method provided by the DataTable object.

Q Sets the field of the data row to the inserted value.

Q Use the Add method of the Datarowadd class to add data rows to the data table.

Q Set the InsertCommand property of the DataAdapter class to the INSERT statement where the record needs to be inserted.

Q Use the Update method provided by the data adapter to insert new records into the database.

Q Use the AcceptChanges method provided by the DataSet class to keep the database consistent with the in-memory data.

Before inserting a record using a dataset, you need to create a connection object to secure the database connection, as shown in the sample code below.

String str = "server= ' (local) ';d atabase= ' mytable '; uid= ' sa ';p wd= ' sa '"; Create a connection string

SqlConnection con = new SqlConnection (str); Create a Connection object

Con.                                                                                                                           Open (); Open connection

The code above creates a database connection and opens the database connection. After the data connection is complete, you need to query the data in the table and initialize the adapter with the DataAdapter object, as shown in the sample code.

            String strSQL = "SELECT * FROM Mynews ";                                                                               //Writing SQL statements

SqlDataAdapter da = new SqlDataAdapter (strSQL, con); Create an Adapter

The DataAdapter object default constructor consists of two parameters, one of which is the SQL statement that needs to be executed, and the other is the connection object. After the adapter is initialized, the corresponding properties of the adapter need to be set, using the SqlCommandBuilder object allows the system to construct the InsertCommand property, as shown in the sample code.

SqlCommandBuilder build = New SqlCommandBuilder (DA); Constructing SQL statements

The dataset dataset can be populated with the Fill method of the adapter, as shown in the sample code below.

            DataSet ds = new DataSet ();                                                                                                  //Create DataSet

            da. Fill (ds, "DataTable");                                                                                                           //Populating data set

DataTable TB = ds.                                                                            Tables["DataTable"]; Create a table

Tb. PrimaryKey = new datacolumn[] {TB.                                            columns["id"}; Create a primary key for a table

The code above creates a DataSet DataSet object, and after the data is populated, the name of the table in the dataset is named DataTable, which does not conflict with the names of the tables in the database. After populating the DataSet data object, you need to use the DataRow object to add data to the dataset, as shown in the sample code below.

DataRow row = ds. Tables["DataTable"].                                                       NewRow (); Create a DataRow

row["title"] = "Insert new row with DataSet"; Assigning new columns

row["id"] = "15";

The code above uses the NewRow method to create a new row to return a DataRow object, and when the corresponding element in the DataRow object is assigned, the new row needs to be added using the Rows.Add method, because only the DataRow object is assigned a value and does not automatically add new rows to the database. The sample code is shown below.

Ds. Tables["DataTable"].                                                                                    Rows.Add (row); Add a new row

The code above updates the data into a dataset dataset and, in order to keep the data in the dataset and the data in the database consistent, use the Update method, as shown in the sample code below.

Da.                                                                                               Update (ds, "DataTable"); Update data

When the Update method is executed, the data in the database is synchronized with the data in the dataset dataset for data updates.

Inserting records using a dataset dataset