Insert Multiples Records to Sql Server Database.

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 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 break down the server.

Using the code

You will need create an console application .net app (c# or vb) to read the text file.

Create a database called NetSamples with the next fields:

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

Run this sentence in your Database:

Hide   Copy Code

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

Create a console applicattion and after add the datatable structure definition in your code :

Hide   Copy 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 inserte in just one Sql sentence , like this :

Hide   Copy Code

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 stored each record inside the DataTable called table.

Hide   Shrink    Copy Code

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.

Hide   Copy Code

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"