Insert Multiples Records to SQL Server Database

• Download sample - 752 B
• Download sample - 23.3 MB

Introduction

Sometimes, we need to updates databases by sending more than just one record, so datatables will be very useful in these cases.

I will explain an example about how to insert multiple records from text file to a SQL Server Database.

Background

Datatables were born in SQL Server version 2008, so this object allows to store a great amount of records and sending to a database consuming low resources of memory, this object is suitable if you have to transfer a lot records without breaking down the server.

Using the Code

You will need to create a console application .NET app (C# or VB) to read the text file.

Create a database called NetSamples with the next fields:

After that, you must create the object DataTable like this:

Run this sentence in your database:

CREATE TYPE dbo.tbCountry AS TABLE (
    idCountry smallint,
    name varchar(100)
);

Create a console application and later, add the datatable structure definition in your code:

/// <summary>
/// This example method generates a DataTable.
/// </summary>
static DataTable GetTable()
{
     DataTable table = new DataTable();
     table.Columns.Add("idCountry", typeof(short));
     table.Columns.Add("name", typeof(string));
     return table;
}

Create a stored procedure that will receive a datatable parameter and after this, datatable will be inserted in just one SQL sentence, like this:

CREATE PROCEDURE InsertCountries
@dtCountry dbo.tbCountry READONLY
AS
BEGIN
INSERT INTO Country(idCountry,[name]) SELECT idCountry,[name] FROM @dtCountry
END
GO

Define a function to read the text file and store each record inside the DataTable called table.

public static void readFile() {
            try
            {
                DataTable table = new DataTable();
                table = GetTable();

                // Create an instance of StreamReader to read from a file.
                // The using statement also closes the StreamReader.
                using (StreamReader sr = new StreamReader
                     (System.Environment.CurrentDirectory + @"\Countries.txt"))
                {
                    string line;
                    int i = 1;

                    // Read and display lines from the file until 
                    // the end of the file is reached. 
                    while ((line = sr.ReadLine()) != null)
                    {
                        table.Rows.Add(i, line);
                        Console.WriteLine(line);
                        i++;
                    }
                }
                //Insert datatable to sql Server
                insert(table);
            }
            catch (Exception e)
            {
                // Let the user know what went wrong.
                Console.WriteLine("The file could not be read:");
                Console.WriteLine(e.Message);
            }
            Console.ReadKey();
        }

Define a function to insert dtData (datatable) to SQL Server Database NetSamples.

static void insert(DataTable dtData) {
            SqlConnection con = new SqlConnection(@"Data Source=COBOGPGP8468\SQLSERVER;
                                Initial Catalog=NetSamples;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("InsertCountries", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@dtCountry", dtData);
            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Records inserted successfully!");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

Run the Console Application and you will see:

Search in your database using the SQL sentence: "Select * from Country":

History

• 16^th March, 2017: First version