sql批量插入数据

2023年6月21日发(作者：)

1，使用Bulk插入：public static void BulkToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);

SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);

ationTableName = "BulkTestTable";

ize = ;

try

{

();

if (dt != null && != 0)

oServer(dt);

}

catch (Exception ex)

{

throw ex;

}

finally

{

();

if (bulkCopy != null)

();

}

} 2，使用表值参数，表值参数是SQL Server

2008新特性，简称TVPs。对于表值参数不熟悉的朋友，可以参考最新的book

onlinepublic static void TableValuedToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);

const string TSqlStatement =

"insert into BulkTestTable (Id,UserName,Pwd)" +

" SELECT , me," +

" FROM @NewBulkTestTvp AS nc";

SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);

SqlParameter catParam =

hValue("@NewBulkTestTvp", dt);

ype = ured;

//表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。

me = "t";

try

{

();

if (dt != null && != 0)

{

eNonQuery();

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

();

}

}

2023年6月21日发(作者：)

1，使用Bulk插入：public static void BulkToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);

SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);

ationTableName = "BulkTestTable";

ize = ;

try

{

();

if (dt != null && != 0)

oServer(dt);

}

catch (Exception ex)

{

throw ex;

}

finally

{

();

if (bulkCopy != null)

();

}

} 2，使用表值参数，表值参数是SQL Server

2008新特性，简称TVPs。对于表值参数不熟悉的朋友，可以参考最新的book

onlinepublic static void TableValuedToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);

const string TSqlStatement =

"insert into BulkTestTable (Id,UserName,Pwd)" +

" SELECT , me," +

" FROM @NewBulkTestTvp AS nc";

SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);

SqlParameter catParam =

hValue("@NewBulkTestTvp", dt);

ype = ured;

//表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。

me = "t";

try

{

();

if (dt != null && != 0)

{

eNonQuery();

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

();

}

}