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"

转载于:https://www.cnblogs.com/KSalomo/p/6559797.html