数据适配器与 Sql 命令
对于 ms-sql 数据库执行插入语句，哪一个更好：
Sql DataAdapter 或 SQL 命令 对象？
仅插入一行和插入多行哪个更好？
代码使用的简单示例：
SQL 命令
string query = "insert into Table1(col1,col2,col3) values (@value1,@value2,@value3)";
int i;
SqlCommand cmd = new SqlCommand(query, connection);
// add parameters...
cmd.Parameters.Add("@value1",SqlDbType.VarChar).Value=txtBox1.Text;
cmd.Parameters.Add("@value2",SqlDbType.VarChar).Value=txtBox2.Text;
cmd.Parameters.Add("@value3",SqlDbType.VarChar).Value=txtBox3.Text;
cmd.con.open();
i = cmd.ExecuteNonQuery();
cmd.con.close();
SQL 数据适配器
DataRow dr = dsTab.Tables["Table1"].NewRow();
DataSet dsTab = new DataSet("Table1");
SqlDataAdapter adp = new SqlDataAdapter("Select * from Table1", connection);
adp.Fill(dsTab, "Table1");
dr["col1"] = txtBox1.Text;
dr["col2"] = txtBox5.Text;
dr["col3"] = "text";
dsTab.Tables["Table1"].Rows.Add(dr);
SqlCommandBuilder projectBuilder = new SqlCommandBuilder(adp);
DataSet newSet = dsTab.GetChanges(DataRowState.Added);
adp.Update(newSet, "Table1");
Which one would be better in executing an insert statement for ms-sql database:
Sql DataAdapter or SQL Command
Object?
Which of them would be better, while inserting only one row and while inserting multiple rows?
A simple example of code usage:
SQL Command
string query = "insert into Table1(col1,col2,col3) values (@value1,@value2,@value3)";
int i;
SqlCommand cmd = new SqlCommand(query, connection);
// add parameters...
cmd.Parameters.Add("@value1",SqlDbType.VarChar).Value=txtBox1.Text;
cmd.Parameters.Add("@value2",SqlDbType.VarChar).Value=txtBox2.Text;
cmd.Parameters.Add("@value3",SqlDbType.VarChar).Value=txtBox3.Text;
cmd.con.open();
i = cmd.ExecuteNonQuery();
cmd.con.close();
SQL Data Adapter
DataRow dr = dsTab.Tables["Table1"].NewRow();
DataSet dsTab = new DataSet("Table1");
SqlDataAdapter adp = new SqlDataAdapter("Select * from Table1", connection);
adp.Fill(dsTab, "Table1");
dr["col1"] = txtBox1.Text;
dr["col2"] = txtBox5.Text;
dr["col3"] = "text";
dsTab.Tables["Table1"].Rows.Add(dr);
SqlCommandBuilder projectBuilder = new SqlCommandBuilder(adp);
DataSet newSet = dsTab.GetChanges(DataRowState.Added);
adp.Update(newSet, "Table1");
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用 DataAdapter 更新数据源要容易得多。进行更改更容易，因为您只需修改 DataSet 并调用 Update。
使用 DataAdapter 与使用命令之间的性能可能没有（或很小）差异。 DataAdapter 在内部使用 Connection 和 Command 对象并执行命令来执行您告诉它们执行的操作（例如 Fill 和 Update），因此它与仅使用 Command 对象几乎相同。
Updating a data source is much easier using DataAdapters. It's easier to make changes since you just have to modify the DataSet and call Update.
There is probably no (or very little) difference in the performance between using DataAdapters vs Commands. DataAdapters internally use Connection and Command objects and execute the Commands to perform the actions (such as Fill and Update) that you tell them to do, so it's pretty much the same as using only Command objects.
我将使用 LinqToSql 和 DataSet 来处理单个插入和大多数数据库 CRUD 请求。它是类型安全的，对于不复杂的查询（例如上面的查询）来说相对较快。
如果您有很多行要插入（1000+）并且您使用的是 SQL Server 2008，我会使用 SqlBulkCopy。您可以使用数据集并输入到存储过程中，然后合并到目标中
对于复杂的查询，我建议使用 dapper 与存储过程结合使用。
I would use LinqToSql with a DataSet for single insert and most Database CRUD requests. It is type safe, relatively fast for non compilcated queries such as the one above.
If you have many rows to insert (1000+) and you are using SQL Server 2008 I would use SqlBulkCopy. You can use your DataSet and input into a stored procedure and merge into your destination
For complicated queries I recommend using dapper in conjunction with stored procedures.
我建议您对与数据库的通信进行某种控制。这意味着抽象一些代码，为此 CommandBuilder 会自动为您生成 CUD 语句。
如果您将该技术与类型化数据集一起使用，那就更好了。然后你就可以对所有列进行智能感知和编译时间检查
I suggest you would have some kind of control on your communication with the database. That means abstracting some code, and for that the CommandBuilder automatically generates CUD statements for you.
What would be even better is if you use that technique together with a typed Dataset. then you have intellisense and compile time check on all your columns