ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

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 条"!