Insert new records into a database in .NET Framework applications
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. They 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.
To insert new records into a database with ADO.NET in a .NET Framework project, you can use the TableAdapter.Update
method, or one of the TableAdapter's DBDirect methods (specifically the TableAdapter.Insert
method). For more information, see TableAdapter.
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 action regenerates the TableAdapter. If the TableAdapter still doesn't have an Insert
method, 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).
Note
This article applies to ADO.NET and .NET Framework development. For the same task with Entity Framework 6, see Adding a new entity to the context. For Entity Framework Core, see Adding data.
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, 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).
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.
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 theRegion
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);
To insert new records into a database by using the 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. Calling the method inserts a new record into the database with the parameter values passed in.
- Call the TableAdapter's
Insert
method, passing in the values for each column as parameters.
The following procedure demonstrates using the TableAdapter.Insert
method to insert rows. This example inserts data into the Region
table in the Northwind database.
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");
Insert new records by using command objects
You can insert new records directly into a database using command objects.
To insert new records into a database by using command objects
- Create a new command object, and then set its
Connection
,CommandType
, andCommandText
properties.
The following example demonstrates inserting records into a database using command object. It inserts 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();
.NET security
You must have access to the database you're trying to connect to, and permission to perform inserts into the desired table.
Related content
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for