alter TYPE TableType AS TABLE
( Name VARCHAR()
, code VARCHAR() )
GO alter PROCEDURE usp_InsertProductionLocation
@TVP TableType READONLY
AS
SET NOCOUNT ON
if object_id('temp') is not null
begin
drop table temp
end
SELECT * into temp FROM @TVP;
GO
DECLARE @LocationTVP AS TableType; INSERT INTO @LocationTVP (Name, code)
SELECT 'tom',
EXEC usp_InsertProductionLocation @LocationTVP;
GO
cs调用
方法一：
/// <summary>
///
/// </summary>
/// <param name="connectionString">目标连接字符</param>
/// <param name="TableName">目标表</param>
/// <param name="dt">源数据</param>
private static void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlbulkcopy.DestinationTableName = TableName;
for (int i = ; i < dt.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sqlbulkcopy.WriteToServer(dt);
}
catch (System.Exception ex)
{
throw ex;
}
}
}
}
方法二：通过存储过程插入值，存储过程见上面的的sql语句
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("name"));
dt.Columns.Add(new DataColumn("code"));
for (int i = ; i < ; i++)
{
dt.Rows.Add(i.ToString(),i);
} string strCon = @"Server=.;database=MyDb;uid=sa;pwd=sa;";
SqlBulkCopyByDatatableProc(strCon, "usp_InsertProductionLocation", dt);
private static void SqlBulkCopyByDatatableProc(string connectionString, string ProcName, DataTable dt)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
//// Invokes the stored procedure.
using (var cmd = new SqlCommand(ProcName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
//// Adding a "structured" parameter allows you to insert tons of data with low overhead
var param = new SqlParameter("@TVP", SqlDbType.Structured) { Value = dt };
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
}
}