Insert new records into a database

Article
11/15/2016

Note

This article applies to Visual Studio 2015. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

To insert new records into a database, you can use the TableAdapter.Update method, or one of the TableAdapter's DBDirect methods (specifically the TableAdapter.Insert method).

If your application doesn't use TableAdapters, you can use command objects (for example, SqlCommand) to insert new records in your database .

If your application uses datasets to store data, use the TableAdapter.Update method. The Update method sends all changes (updates, inserts, and deletes) to the database.

If your application uses objects to store data, or if you want finer control over creating new records in the database, use the TableAdapter.Insert method.

If your TableAdapter doesn't have an Insert method, it means that either the TableAdapter is configured to use stored procedures or its GenerateDBDirectMethods property is set to false. Try setting the TableAdapter's GenerateDBDirectMethods property to true from within the Dataset Designer, and then save the dataset. This will regenerate the TableAdapter. If the TableAdapter still doesn't have an Insert method, then the table probably does not provide enough schema information to distinguish between individual rows (for example, there might be no primary key set on the table).

Insert new records by using TableAdapters

TableAdapters provide different ways to insert new records into a database, depending on the requirements of your application.

If your application uses datasets to store data, then you can simply add new records to the desired DataTable in the dataset, and then call the TableAdapter.Update method. The TableAdapter.Update method sends any changes in the DataTable to the database (including modified and deleted records).

To insert new records into a database by using the TableAdapter.Update method

Add new records to the desired DataTable by creating a new DataRow and adding it to the Rows collection. For more information, see How to: Add Rows to a DataTable.
After the new rows are added to the DataTable, call the TableAdapter.Update method. You can control the amount of data to update by passing in either an entire DataSet, a DataTable, an array of DataRows, or a single DataRow.

The following code shows how to add a new record to a DataTable and then call the TableAdapter.Update method to save the new row to the database. (This example uses the Region table in the Northwind database.)
```
// Create a new row.
NorthwindDataSet.RegionRow newRegionRow;
newRegionRow = northwindDataSet.Region.NewRegionRow();
newRegionRow.RegionID = 5;
newRegionRow.RegionDescription = "NorthWestern";

// Add the row to the Region table
this.northwindDataSet.Region.Rows.Add(newRegionRow);

// Save the new row to the database
this.regionTableAdapter.Update(this.northwindDataSet.Region);
```
```
' Create a new row.
Dim newRegionRow As NorthwindDataSet.RegionRow
newRegionRow = Me.NorthwindDataSet._Region.NewRegionRow()
newRegionRow.RegionID = 5
newRegionRow.RegionDescription = "NorthWestern"

' Add the row to the Region table
Me.NorthwindDataSet._Region.Rows.Add(newRegionRow)

' Save the new row to the database
Me.RegionTableAdapter.Update(Me.NorthwindDataSet._Region)
```
If your application uses objects to store data, you can use the TableAdapter.Insert method to create new rows directly in the database. The Insert method accepts the individual values for each column as parameters. Calling the method inserts a new record into the database with the parameter values passed in.

The following procedure uses the Region table in the Northwind database as an example.

To insert new records into a database by using the TableAdapter.Insert method

Call the TableAdapter's Insert method, passing in the values for each column as parameters.

Note

If you do not have an instance available, instantiate the TableAdapter you want to use.

NorthwindDataSetTableAdapters.RegionTableAdapter regionTableAdapter = 
    new NorthwindDataSetTableAdapters.RegionTableAdapter();

regionTableAdapter.Insert(5, "NorthWestern");

Dim regionTableAdapter As New NorthwindDataSetTableAdapters.RegionTableAdapter

regionTableAdapter.Insert(5, "NorthWestern")

Insert new records by using command objects

The following example inserts new records directly into a database using command objects.

The following procedure uses the Region table in the Northwind database as an example.

To insert new records into a database by using command objects

Create a new command object, and then set its Connection, CommandType, and CommandText properties.

System.Data.SqlClient.SqlConnection sqlConnection1 = 
    new System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING");

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT Region (RegionID, RegionDescription) VALUES (5, 'NorthWestern')";
cmd.Connection = sqlConnection1;

sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING")

Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "INSERT Region (RegionID, RegionDescription) VALUES (5, 'NorthWestern')"
cmd.Connection = sqlConnection1

sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()

.NET Framework Security

You must have access to the database you are trying to connect to, as well as permission to perform inserts into the desired table.

Partager via