-
使用Insert循环插入(每次一条)
-
使用BulkCopy写入;
-
1,创建一个DataTable dt 2,using (SqlBulkCopy sbc = new SqlBulkCopy(目标库连接字符串))//目标库的链接字符串 { sbc.BulkCopyTimeout = 600; sbc.BatchSize = dt.Rows.Count; sbc.DestinationTableName = "目标表名";//目标表 sbc.WriteToServer(dt); } }
-
第二种写法 SqlDataReader reader =commandSourceData.ExecuteReader(); using (SqlConnection destinationConnection = new SqlConnection(connectionString)) { destinationConnection.Open(); using (SqlBulkCopy bulkCopy =new SqlBulkCopy(destinationConnection)) { bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"; try { bulkCopy.WriteToServer(reader); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // Close the SqlDataReader. The SqlBulkCopy // object is automatically closed at the end // of the using block. reader.Close(); } } long countEnd = System.Convert.ToInt32( commandRowCount.ExecuteScalar()); Console.WriteLine("Ending row count = {0}", countEnd); Console.WriteLine("{0} rows were added.", countEnd - countStart); Console.WriteLine("Press Enter to finish."); Console.ReadLine(); } }
-
-
使用表值参数写入。
-
SqlParameter[] paramters = new SqlParameter[] { SqlParamHelper.MakeInParam("@dt",SqlDbType.Structured) }; DataSet ds = SqlHelper.ExecuteDataset(ComputingDB_ConnString, CommandType.StoredProcedure, "存储过程名", paramters); string[] sqls = new string[] { @" insert into table1 select * from @dt ", }; using (SqlConnection connection = new SqlConnection(StatDB_ConnString)) { connection.Open(); SqlTransaction trans = connection.BeginTransaction(); try { string[] typeNames = new string[] { "表类型名" }; for (int i = 0; i < sqls.Length; i++) { paramters[0].Value = ds.Tables[i]; paramters[0].TypeName = typeNames[i]; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqls[i], paramters); }; trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw; } }
-
总结:Insert比较适合于少量数据的添加,如果是大批量的数据,只能考虑使用BulkCopy或表值参数方式,后俩者相比于前者会有一个量级的提升,随着数据量的提升这个差别会越来越大