欢迎光临

木子屋

标题: SqlDataAdapter+DataTable+DataGridView数据库更新示例

作者: dnawo

日期: 2009-10-27 02:38 PM

分类: Win编程

内容: 复制内容到剪贴板 程序代码using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private SqlDataAdapter _adapter;
private DataTable _table;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=Test;User ID=sa;Password=sa");
_adapter = new SqlDataAdapter();
//Select
_adapter.SelectCommand = new SqlCommand("Select * FROM Products", conn);
//Insert
_adapter.InsertCommand = new SqlCommand("Insert INTO Products(ProductName,Price,Num,PriceSUM) VALUES(@ProductName,@Price,@Num,@PriceSUM);Select SCOPE_IDENTITY() AS ProductID;", conn);
_adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;//更新返回自动递增的ProductID
_adapter.InsertCommand.Parameters.Add("@ProductName", SqlDbType.NVarChar, 50);
_adapter.InsertCommand.Parameters[0].SourceColumn = "ProductName";
_adapter.InsertCommand.Parameters.Add("@Price", SqlDbType.Decimal, 10);
_adapter.InsertCommand.Parameters[1].SourceColumn = "Price";
_adapter.InsertCommand.Parameters.Add("@Num", SqlDbType.Decimal, 10);
_adapter.InsertCommand.Parameters[2].SourceColumn = "Num";
_adapter.InsertCommand.Parameters.Add("@PriceSUM", SqlDbType.Decimal, 10);
_adapter.InsertCommand.Parameters[3].SourceColumn = "PriceSUM";
//Update
_adapter.UpdateCommand = new SqlCommand("Update Products SET ProductName=@ProductName,Price=@Price,Num=@Num,PriceSUM=@PriceSUM Where ProductID=@ProductID;Select @ProductID AS ProductID;", conn);
_adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
_adapter.UpdateCommand.Parameters.Add("@ProductName", SqlDbType.NVarChar, 50);
_adapter.UpdateCommand.Parameters[0].SourceColumn = "ProductName";
_adapter.UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 10);
_adapter.UpdateCommand.Parameters[1].SourceColumn = "Price";
_adapter.UpdateCommand.Parameters.Add("@Num", SqlDbType.Decimal, 10);
_adapter.UpdateCommand.Parameters[2].SourceColumn = "Num";
_adapter.UpdateCommand.Parameters.Add("@PriceSUM", SqlDbType.Decimal, 10);
_adapter.UpdateCommand.Parameters[3].SourceColumn = "PriceSUM";
_adapter.UpdateCommand.Parameters.Add("@ProductID", SqlDbType.Int, 10);
_adapter.UpdateCommand.Parameters[4].SourceColumn = "ProductID";
_adapter.UpdateCommand.Parameters[4].SourceVersion = DataRowVersion.Original;//使用原来的值,因为 Current 值可能已被修改,可能会不匹配数据源中的值。
//Delete
_adapter.DeleteCommand = new SqlCommand("Delete FROM Products Where ProductID=@ProductID", conn);
_adapter.DeleteCommand.Parameters.Add("@ProductID", SqlDbType.Int, 10);
_adapter.DeleteCommand.Parameters[0].SourceColumn = "ProductID";
_adapter.DeleteCommand.Parameters[0].SourceVersion = DataRowVersion.Original;
_table = new DataTable();
_adapter.Fill(_table);
dataGridView1.DataSource = _table;
//dataGridView1中增、删、改记录...
}
private void button1_Click(object sender, EventArgs e)
{
_adapter.Update(_table);
}
}
}
还有一种简便的方法，使用SqlCommandBuilder自动生成InsertCommand、UpdateCommand和DeleteCommand：
复制内容到剪贴板 程序代码using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private SqlDataAdapter _adapter;
private DataTable _table;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=Test;User ID=sa;Password=sa");
_adapter = new SqlDataAdapter("Select * FROM Products", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);//自动生成InsertCommand、UpdateCommand和DeleteCommand
_table = new DataTable();
_adapter.Fill(_table);
dataGridView1.DataSource = _table;
}
private void button1_Click(object sender, EventArgs e)
{
_adapter.Update(_table);
}
}
}
这种方法有个缺陷：若表中包含自动递增的字段且为主键，添加一条新记录后，再进行修改或删除操作都会出错，提示"违反并发性: UpdateCommand 影响了预期 1 条记录中的 0 条"!

+ 查看当前日志评论 (0)

登录

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 2.297 ms

返回

标题: SqlDataAdapter+DataTable+DataGridView数据库更新示例

你没有权限发表评论

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 2.297 ms

返回

暂无评论

返回

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 2.379 ms

返回