DataTable中的数据一次写入到数据库中，也就是只和数据库交互一次，不用循环DataTable一条一条的写入！！！
DataTable是新建的，里面存放的是读取Excel中的数据，有6000多条。
原来是用循环DataTable来一条一条的插入数据的，这样和数据库交互太频繁，有没有方法一次插入全部？？？
SQL语句执行的最好！！！
[解决办法]
- C# code
public DataSet UpdateByDataSet(DataSet ds,string strTblName,string strConnection) { SqlConnection conn = new SqlConnection(strConnection); SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn); SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand ); SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); myAdapter.InsertCommand = myCommandBuilder .GetInsertCommand(); try { foreach(DataRow dr in ds.Tables[0].Rows) { dr.SetAdded();//.net 2.0以上版本才有,如果你是.net 1.1那没此方法 }//加上这段代码后看能插入吗.这个是把行状态置成了Added conn.Open(); myAdapter.Update(ds,strTblName); ds.AcceptChanges(); conn.Close(); return ds; //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds } catch(Exception err) { conn.Close(); throw err; } }
[解决办法]
[解决办法]
或者可以用SqlBulkCopy.WriteToServer(DataTable)一次将数据写入数据库
参看下例:
主要看using (SqlBulkCopy bcp =
new SqlBulkCopy(connection))部分代码即可
- C# code
using System;using System.Data;using System.Data.SqlClient;namespace Microsoft.Samples.SqlServer{ class Program { public static void Main(string[] args) // Define and open a connection to AdventureWorks. { using (SqlConnection connection = new SqlConnection(GetConnectionString())) { connection.Open(); // Perform an initial count on the // destination table. SqlCommand commandRowCount = new SqlCommand( "SELECT COUNT(*) FROM " + "dbo.BulkCopyDemoMatchingColumns;", connection); long countStart = System.Convert.ToInt32( commandRowCount.ExecuteScalar()); Console.WriteLine("Starting row count = " + countStart); // Create a table with some rows. DataTable tableNewProducts = MakeTable(); // Set up the bulk copy object. // Note that the column positions in the // source data reader match the column // positions in the destination table so // there is no need to map columns. using (SqlBulkCopy bcp = new SqlBulkCopy(connection)) { bcp.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"; // Write from the source to // the destination. bcp.WriteToServer(tableNewProducts); } // Perform a final count on the destination // table to see how many rows were added. long countEnd = System.Convert.ToInt32( commandRowCount.ExecuteScalar()); Console.WriteLine("Ending row count = " + countEnd); long countAdded = countEnd - countStart; if (countAdded == 1) { Console.WriteLine("1 row was added."); } else { Console.WriteLine(countAdded + " rows were added."); } Console.WriteLine("Press Enter to finish."); Console.ReadLine(); } } private static DataTable MakeTable() // Create a new DataTable named NewProducts. { DataTable tableNewProducts = new DataTable("NewProducts"); // Add three column objects to the table. DataColumn columnID = new DataColumn(); columnID.DataType = System.Type.GetType("System.Int32"); columnID.ColumnName = "ProductID"; columnID.AutoIncrement = true; tableNewProducts.Columns.Add(columnID); DataColumn columnName = new DataColumn(); columnName.DataType = System.Type.GetType("System.String"); columnName.ColumnName = "Name"; tableNewProducts.Columns.Add(columnName); DataColumn columnProductNumber = new DataColumn(); columnProductNumber.DataType = System.Type.GetType("System.String"); columnProductNumber.ColumnName = "ProductNumber"; tableNewProducts.Columns.Add( columnProductNumber); // Create an array for DataColumn objects. DataColumn[] keys = new DataColumn[1]; keys[0] = columnID; tableNewProducts.PrimaryKey = keys; // Add some new rows to the collection. DataRow row; row = tableNewProducts.NewRow(); row["Name"] = "CC-101-WH"; row["ProductNumber"] = "Cyclocomputer - White"; tableNewProducts.Rows.Add(row); row = tableNewProducts.NewRow(); row["Name"] = "CC-101-BK"; row["ProductNumber"] = "Cyclocomputer - Black"; tableNewProducts.Rows.Add(row); row = tableNewProducts.NewRow(); row["Name"] = "CC-101-ST"; row["ProductNumber"] = "Cyclocomputer - Stainless"; tableNewProducts.Rows.Add(row); tableNewProducts.AcceptChanges(); // Return the new DataTable. return tableNewProducts; }// MARS is turned on in the connection string because this sample // performs a bulk copy in the same database, using the same connection.// However, MARS is not required to use the SqlBulkCopy functionality. private static string GetConnectionString() // To avoid storing the connection string in your code, // you can retrieve it from a configuration file. { return "Data Source=(local);" + "Integrated Security=SSPI;" + "Initial Catalog=AdventureWorks;" + "MultipleActiveResultSets=True"; } }}