ASP.NET Tutorial/ADO.net Database/SqlBulkCopy
Bulk loading data from one table to another database (C#)
<source lang="csharp">
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">
protected void btnBulkCopy_Click(object sender, EventArgs e) { String YourDatabaseConString; String NorthWindConString; SqlConnection YourDatabaseCon = new SqlConnection(); SqlConnection NorthwindCon = new SqlConnection(); SqlCommand YourDatabaseCom = new SqlCommand(); SqlDataReader YourDatabaseReader; YourDatabaseConString = ConfigurationManager.ConnectionStrings["DSN_YourDatabase"].ConnectionString; NorthWindConString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString; YourDatabaseCon.ConnectionString = YourDatabaseConString; YourDatabaseCom.Connection = YourDatabaseCon; YourDatabaseCom.rumandText = " SELECT ID, First_Name, Last_Name, "YourDatabase" as Source FROM MailingList_Temp "; YourDatabaseCom.rumandType = CommandType.Text; YourDatabaseCom.Connection.Open(); SqlBulkCopy NorthWindBulkOp; NorthWindBulkOp = new SqlBulkCopy(NorthWindConString, SqlBulkCopyOptions.UseInternalTransaction); NorthWindBulkOp.DestinationTableName = "Employees"; NorthWindBulkOp.ColumnMappings.Add("Id", "EmployeeID"); NorthWindBulkOp.ColumnMappings.Add("First_Name", "FirstName"); NorthWindBulkOp.ColumnMappings.Add("Last_Name", "LastName"); SqlBulkCopyColumnMapping JobTitleColMap; JobTitleColMap = new SqlBulkCopyColumnMapping("Source", "Title"); NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap); NorthWindBulkOp.BulkCopyTimeout = 500000000; NorthWindBulkOp.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied); NorthWindBulkOp.NotifyAfter = 1000; YourDatabaseReader = YourDatabaseCom.ExecuteReader(); try { NorthWindBulkOp.WriteToServer(YourDatabaseReader); } catch (Exception ex) { lblResult.Text = ex.Message; } finally { YourDatabaseReader.Close(); } } private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args) { lblCounter.Text += args.RowsCopied.ToString() + " rows are copied
"; }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Bulk Loading Large Volume Data</title>
</head> <body>
<form id="form1" runat="server">
<asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" />
<asp:Label ID="lblResult" Runat="server"></asp:Label>
<asp:Label ID="lblCounter" Runat="server"></asp:Label>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="DSN_Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" /> <add name="DSN_YourDatabase" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=YourDatabase;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
Bulk loading data from one table to another database (VB)
<source lang="csharp">
<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">
Sub btnBulkCopy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBulkCopy.Click Dim YourDatabaseConString As String Dim NorthWindConString As String Dim YourDatabaseCon As SqlConnection = New SqlConnection() Dim NorthwindCon As SqlConnection = New SqlConnection() Dim YourDatabaseCom As SqlCommand = New SqlCommand() Dim YourDatabaseReader As SqlDataReader YourDatabaseConString = ConfigurationManager.ConnectionStrings("DSN_YourDatabase").ConnectionString NorthWindConString = ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString YourDatabaseCon.ConnectionString = YourDatabaseConString YourDatabaseCom.Connection = YourDatabaseCon YourDatabaseCom.rumandText = " SELECT ID, First_Name, Last_Name, " & _ " "YourDatabase" as Source FROM MailingList_Temp " YourDatabaseCom.rumandType = CommandType.Text YourDatabaseCom.Connection.Open() Dim NorthWindBulkOp As SqlBulkCopy NorthWindBulkOp = New SqlBulkCopy(NorthWindConString, _ SqlBulkCopyOptions.UseInternalTransaction) NorthWindBulkOp.DestinationTableName = "Employees" NorthWindBulkOp.ColumnMappings.Add("Id", "EmployeeID") NorthWindBulkOp.ColumnMappings.Add("First_Name", "FirstName") NorthWindBulkOp.ColumnMappings.Add("Last_Name", "LastName") Dim JobTitleColMap As SqlBulkCopyColumnMapping JobTitleColMap = New SqlBulkCopyColumnMapping("Source", "Title") NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap) NorthWindBulkOp.BulkCopyTimeout = 500000000 AddHandler NorthWindBulkOp.SqlRowsCopied, _ New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied) NorthWindBulkOp.NotifyAfter = 1000 YourDatabaseReader = YourDatabaseCom.ExecuteReader() Try NorthWindBulkOp.WriteToServer(YourDatabaseReader) Catch ex As Exception " Write error handling code here lblResult.Text = ex.Message Finally YourDatabaseReader.Close() End Try End Sub Private Sub OnSqlRowsCopied(ByVal sender As Object, _ ByVal args As SqlRowsCopiedEventArgs) lblCounter.Text += args.RowsCopied.ToString() + " rows are copied
" End Sub
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Bulk Loading Large Volume Data</title>
</head> <body>
<form id="form1" runat="server">
<asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" />
<asp:Label ID="lblResult" Runat="server"></asp:Label>
<asp:Label ID="lblCounter" Runat="server"></asp:Label>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="DSN_Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" /> <add name="DSN_YourDatabase" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=YourDatabase;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>