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

Ado.Net

Answers (1)