Insert Multiples Records to SQL Server Database





5.00/5 (11 votes)
Read text file and insert mutiples records in just one SQL Sentence
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
- 16th March, 2017: First version