木子屋
 

标题: 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 0.063 ms

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

你没有权限发表评论

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

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.063 ms

暂无评论

返回

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

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.063 ms