Ado.Net

Hello Govind,

You can use BulkCopy

using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsoleApplication3.Properties.Settings.daasConnectionString"].ConnectionString))

{

cn.Open();

using (SqlBulkCopy copy = new SqlBulkCopy(cn))

{

copy.ColumnMappings.Add(0, 0);

copy.ColumnMappings.Add(1, 1);

copy.ColumnMappings.Add(2, 2);

copy.ColumnMappings.Add(3, 3);

copy.ColumnMappings.Add(4, 4);

copy.DestinationTableName = "Censis";

copy.WriteToServer(dt);

}

}

Other Way:

using System.Data.SqlClient;

class Program

{

static void Main()

{

string connectionString = GetConnectionString();

// Open a sourceConnection to the AdventureWorks database.

using (SqlConnection sourceConnection =

new SqlConnection(connectionString))

{

sourceConnection.Open();

// Perform an initial count on the destination table.

SqlCommand commandRowCount = new SqlCommand(

"SELECT COUNT(*) FROM " +

"dbo.BulkCopyDemoMatchingColumns;",

sourceConnection);

long countStart = System.Convert.ToInt32(

commandRowCount.ExecuteScalar());

Console.WriteLine("Starting row count = {0}", countStart);

// Get data from the source table as a SqlDataReader.

SqlCommand commandSourceData = new SqlCommand(

"SELECT ProductID, Name, " +

"ProductNumber " +

"FROM Production.Product;", sourceConnection);

SqlDataReader reader =

commandSourceData.ExecuteReader();

// Open the destination connection. In the real world you would

// not use SqlBulkCopy to move data from one table to the other

// in the same database. This is for demonstration purposes only.

using (SqlConnection destinationConnection =

new SqlConnection(connectionString))

{

destinationConnection.Open();

// Set up the bulk copy object.

// Note that the column positions in the source

// data reader match the column positions in

// the destination table so there is no need to

// map columns.

using (SqlBulkCopy bulkCopy =

new SqlBulkCopy(destinationConnection))

{

bulkCopy.DestinationTableName =

"dbo.BulkCopyDemoMatchingColumns";

try

{

// Write from the source to the destination.

bulkCopy.WriteToServer(reader);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

finally

{

// Close the SqlDataReader. The SqlBulkCopy

// object is automatically closed at the end

// of the using block.

reader.Close();

}

// Perform a final count on the destination

// table to see how many rows were added.

long countEnd = System.Convert.ToInt32(

commandRowCount.ExecuteScalar());

Console.WriteLine("Ending row count = {0}", countEnd);

Console.WriteLine("{0} rows were added.", countEnd - countStart);

Console.WriteLine("Press Enter to finish.");

Console.ReadLine();

}

private static string GetConnectionString()

// To avoid storing the sourceConnection string in your code,

// you can retrieve it from a configuration file.

{

return "Data Source=(local); " +

" Integrated Security=true;" +

"Initial Catalog=AdventureWorks;";

}

but instead using Ado.net if you use entity framework we can do easily with in fraction of milli seconds :)

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Ramesh Maruthi12y ago
Hello Govind,

You can use BulkCopy

using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsoleApplication3.Properties.Settings.daasConnectionString"].ConnectionString))
{
cn.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(cn))
{
copy.ColumnMappings.Add(0, 0);
copy.ColumnMappings.Add(1, 1);
copy.ColumnMappings.Add(2, 2);
copy.ColumnMappings.Add(3, 3);
copy.ColumnMappings.Add(4, 4);
copy.DestinationTableName = "Censis";
copy.WriteToServer(dt);
}
}

Other Way:

using System.Data.SqlClient;

class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();

// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);

// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, " +
"ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader =
commandSourceData.ExecuteReader();

// Open the destination connection. In the real world you would
// not use SqlBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
destinationConnection.Open();

// Set up the bulk copy object.
// Note that the column positions in the source
// data reader match the column positions in
// the destination table so there is no need to
// map columns.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";

try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
reader.Close();
}
}

// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
}

private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
but instead using Ado.net if you use entity framework we can do easily with in fraction of milli seconds :)