Ado.Net

Question

How to handle millions of records in ado.net in windows forms as well as asp.net web application? I need a concrete solution.

Ramesh Maruthi · Answer

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 :)