You may have already read How to query a SQL database in C#? and How to insert a row into a Microsoft SQL database using C#? but you are looking for more information on how to insert rows using a DataTable.  Well, here is how.

The example code below assumes you have a simple database called ExampleDB, with one table called Person that has an Id (Primary Key and auto increments), a FirstName, and a LastName column.

Further information is in the comments in the sample code below.

using System.Data;
using System.Data.SqlClient;

namespace SQLExamples
{
    public class Program
    {
        static void Main(string[] args)
        {
            // Step 1 - Create the connection with a Connection string

            // Create a connection builder
            var connectionBuilder = new SqlConnectionStringBuilder();
            connectionBuilder.IntegratedSecurity = true;
            connectionBuilder.DataSource = "localhost";
            connectionBuilder.InitialCatalog = "ExampleDB";

            // Create a connection that uses the connection string
            SqlConnection connection = new SqlConnection(connectionBuilder.ConnectionString);

            // Step 2 - Open the Connection
            connection.Open();

            // Step 3 - Perform database tasks with the open connection (Examples provided...)

            //
            // Example 1 - Insert data with a query
            //

            // Create an insert query
            string insertQuery = "Insert into Person (FirstName, LastName) Values ('Jared', 'Barneck')";

            // Create an SqlCommand using the connection and the insert query
            SqlCommand insertCommand = new SqlCommand() { Connection = connection, CommandText = insertQuery };

            // Run the ExecuteNonQuery() method
            int rowsAffected = insertCommand.ExecuteNonQuery();

            //
            // Example 2 - Select * from a table
            //

            // Create a String to hold the query.
            string selectQuery = "SELECT * FROM Person";

            // Create an SqlCommand using the connection and the insert query
            SqlCommand selectCommand = new SqlCommand() { Connection = connection, CommandText = selectQuery };

            // Use the above SqlCommand object to create a SqlDataReader object.
            SqlDataReader queryCommandReader = selectCommand.ExecuteReader();

            // Create a DataTable object to hold all the data returned by the query.
            DataTable dataTable = new DataTable();

            // Use the DataTable.Load(SqlDataReader) function to put the results of the query into a DataTable.
            dataTable.Load(queryCommandReader);

            //
            // Example 3 - Insert many rows DataTable insert
            //

            // Add to the DataTable from example 2
            DataRow row1 = dataTable.NewRow();
            row1["FirstName"] = "John";
            row1["LastName"] = "Johnson";
            dataTable.Rows.Add(row1);

            DataRow row2 = dataTable.NewRow();
            row2["FirstName"] = "Jenni";
            row2["LastName"] = "Jenson";
            dataTable.Rows.Add(row2);

            string adapterInsertQuery = "Insert into Person (FirstName, LastName) Values (@FirstName, @LastName)";
            SqlCommand adapterInsertCommand = new SqlCommand() { Connection = connection, CommandText = adapterInsertQuery };
            adapterInsertCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 255, "FirstName");
            adapterInsertCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 255, "LastName");

            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            adapter.InsertCommand = adapterInsertCommand;
            adapter.Update(dataTable);

            // Step 4 - Close the connection
            connection.Close();
        }
    }
}

You show now have a good starting point for using ADO.NET to manipulate a Database.

Return to ADO.NET and Database with C#