Mega Code Archive
Bulk loading data from one table to another database (C#)
<![CDATA[
<%@ 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.CommandText = " SELECT ID, First_Name, Last_Name, 'YourDatabase' as Source FROM MailingList_Temp ";
YourDatabaseCom.CommandType = 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<Br>";
}
</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">
<div>
<asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" />
<br />
<asp:Label ID="lblResult" Runat="server"></asp:Label>
<br />
<asp:Label ID="lblCounter" Runat="server"></asp:Label>
</div>
</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>
]]>