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; } }
insert into 表名 select 字段 from 表名 这样可以实现多条记录一次插入,但是我传到后台的是DataTable 怎么让这个DataTable转换为一个表
个人觉得,这句SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn); 改成 SqlCommand myCommand = new SqlCommand("select * from " + strTblName +" where 1<>1", conn); 取个结构更快一些~
Sql语句可以参考下面的方式写 insert into tablename(filed1,filed2) selct '1','1' union all selct '2','3' .........selct '最后一条','最后一条''
insert into tablename(filed1,filed2) select '1','1' union all select '2','3' .........select '最后一条','最后一条''
SqlDataAdapter 利用SqlCommandBuilder 直接插入。
给你我的测试过程~~ 1.建立表test~表中只有一列ID,int型~ 2.建立控制台项目InsertData~ 3.代码如下~ using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data;namespace InsertData { class Program { static void Main(string[] args) { string sqlconn = "server=127.0.0.1;user id=sa;password=xxx;database=test;"; SqlDataAdapter ad = new SqlDataAdapter("select top 1 * from test where 1<>1", sqlconn);//取个结构 DataTable dt = new DataTable(); SqlCommandBuilder cmb = new SqlCommandBuilder(ad);//这一句很关键,必不可少。 ad.Fill(dt); for (int i = 1; i < 10; i++) { DataRow dr = dt.NewRow(); dr["id"] = i; dt.Rows.Add(dr); } ad.Update(dt); Console.WriteLine("OK"); Console.Read(); } } } 运行后test表中有10行新加入数据~ OVER~
或者可以用SqlBulkCopy.WriteToServer(DataTable)一次将数据写入数据库 参看下例:主要看using (SqlBulkCopy bcp = new SqlBulkCopy(connection))部分代码即可 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"; } } }
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;
}
}
这样可以实现多条记录一次插入,但是我传到后台的是DataTable
怎么让这个DataTable转换为一个表
insert into tablename(filed1,filed2)
selct '1','1'
union all
selct '2','3'
.........selct '最后一条','最后一条''
select '1','1'
union all
select '2','3'
.........select '最后一条','最后一条''
SqlDataAdapter 利用SqlCommandBuilder 直接插入。
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;namespace InsertData
{
class Program
{
static void Main(string[] args)
{
string sqlconn = "server=127.0.0.1;user id=sa;password=xxx;database=test;";
SqlDataAdapter ad = new SqlDataAdapter("select top 1 * from test where 1<>1", sqlconn);//取个结构
DataTable dt = new DataTable();
SqlCommandBuilder cmb = new SqlCommandBuilder(ad);//这一句很关键,必不可少。
ad.Fill(dt);
for (int i = 1; i < 10; i++)
{
DataRow dr = dt.NewRow();
dr["id"] = i;
dt.Rows.Add(dr);
}
ad.Update(dt);
Console.WriteLine("OK");
Console.Read();
}
}
}
运行后test表中有10行新加入数据~
OVER~
你这个比较通用~我那个只能做DEMO~~于是就剽窃你的~~我认为用1<>1更好一些,你觉得?
参看下例:主要看using (SqlBulkCopy bcp =
new SqlBulkCopy(connection))部分代码即可
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";
}
}
}
呵呵.我只是逗逗你.
如果是2.0的话.就没必要在用一条select语句去返回一个空的datatable再往里新加数据了.只要遍历table的行.SetAdded()行状态.一下就全搞定了
用这个11楼的方法cs里代码比较简单。
在数据库里还它还是一条条插入的!
批处理执行
怎么才算一条?拼一个2M的insert select union扔上去算一条?
其实要是能把DataTable转换为一个表就好了
比如说我要对表
create table table1
(
id int,
name varchar(50)
)
插入10条记录,那么我写成下面就ok了 DataTable dt = new DataTable()
dt.Columns.Add("id",typeof(int));
for(int i =0;i<=10;i++)
{
DataRow dr = dt.NewRow();
dr[0] = i;
dr[1] = "test"+i;
dt.Rows.Add(dr);
} //我得到了dt,不管这个dt是怎么来的.总之得到了(你的可能是excel读出来的),那只要写几行代码就ok了,如下: foreach(DataRow dr in dt.Rows)
{
dr.SetAdded();
} da.InsertCommand = 一个insert的command
da.Update(dt);
你要是愿意,甚至你可以不写一句sql语句都可以搞定,参看16楼我所说的方法