|
This example provides a framework for the SqlBulkCopy class and its implementation in SQL CLR.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Principal;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void clr_usp_MoveBulkData()
{
DataTable source = new DataTable();
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string sql = "SELECT {columnspec} FROM Source_TableName;";
cn.Open();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataAdapter da = new SqlDataAdapter(sql, cn);
da.Fill(source);
cn.Close();
}
WindowsIdentity currentIdentity = SqlContext.WindowsIdentity;
WindowsImpersonationContext impersonatedIdentity = currentIdentity.Impersonate();
try
{
using (SqlConnection conn = new SqlConnection("Server=ServerName;Database=DestinationDatabase;Integrated Security=true"))
{
conn.Open();
SqlBulkCopy bc = new SqlBulkCopy(conn);
bc.DestinationTableName = "DestinationTable";
bc.BatchSize = source.Rows.Count;
bc.WriteToServer(source);
conn.Close();
}
}
finally
{
impersonatedIdentity.Undo();
}
}
};
|