Edit

Share via

Insert new records into a database in .NET Framework applications

  • Article

To insert new records into a database with ADO.NET in a .NET Framework project, the common approach is to use TableAdapter methods. TableAdapters enable communication between your application and database. They provide different ways to insert new records into a database, depending on the requirements of your application. You can use the TableAdapter.Update method or one of the TableAdapter DBDirect methods (specifically, the TableAdapter.Insert method).

This article describes how to insert records into a database for an application built with ADO.NET and the .NET Framework by using Visual Basic (VB) or C#. If your application configuration uses Entity Framework 6, see Adding a new entity to the context, or for Entity Framework Core, see Adding data.

Note

Datasets and related classes are legacy .NET Framework technologies from the early 2000s that enable applications to work with data in memory while the applications are disconnected from the database. The technologies are especially useful for applications that enable users to modify data and persist the changes back to the database. Although datasets have proven to be a very successful technology, we recommend that new .NET applications use Entity Framework Core. Entity Framework provides a more natural way to work with tabular data as object models, and it has a simpler programming interface.

Prerequisites

Choose insertion method

There are different approaches for inserting records into a database based on your application scenario. The following table summarizes the options:

Scenario Approach Notes
App uses datasets to store data Use the TableAdapter.Update method to send all changes to the database Changes include updates, insertions, and deletions.
App uses objects to store data Use the TableAdapter.Insert method to insert new records into the database This approach enables you to have finer control over creating new records.
App uses TableAdapters, Insert method not available Set the TableAdapter GenerateDBDirectMethods property to true from within the Dataset Designer and save the dataset to regenerate the TableAdapter If your TableAdapter doesn't have an Insert method, the TableAdapter is either configured to use stored procedures or the GenerateDBDirectMethods property is set to false.
If the Insert method remains unavailable after regenerating the TableAdapter, the table probably doesn't provide enough schema information to distinguish between individual rows (for example, there might be no primary key set on the table).
App doesn't use TableAdapters Use command objects to insert new records into the database Example: SqlCommand

Insert new records by using TableAdapters

If your application uses datasets to store data, you can 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.

Insert new records with TableAdapter.Update method

The following procedure demonstrates how to insert new records into a database by using the TableAdapter.Update method:

  1. Add new records to the desired DataTable by creating a new DataRow and adding it to the Rows collection.

  2. After you add the new rows to the DataTable, call the TableAdapter.Update method. You can control the amount of data to update by passing one of the following parameter values:

    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);

Insert new records with TableAdapter.Insert method

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. When you call the method, a new record is inserted into the database with the passed parameter values.

  • Call the TableAdapter's Insert method, and pass the values for each column as parameters.

The following procedure demonstrates how to use the TableAdapter.Insert method to insert rows. This example inserts data into the Region table in the Northwind database.

Note

If you don't have an instance available, instantiate the TableAdapter that you want to use.

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

regionTableAdapter.Insert(5, "NorthWestern");

Insert new records with command objects

You can insert new records directly into a database by using command objects.

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

The following procedure demonstrates how to insert records into a database by using command object. This example insert data into the Region table in the Northwind database.

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();

Related content