Win编程 订阅所有【Win编程】的日志

DataGridView数据库手动更新示例

编辑:dnawo 日期:2009-10-29

字体大小: 小 中 大
若表是独立存在的(所有字段和其他表都没有关系)，照《SqlDataAdapter+DataTable+DataGridView数据库更新示例》的方法，对表进行增删改查是非常便捷的，反之，若表中的字段有些是和其他表有关系的，例如产品进仓表，添加记录后还得修改产品汇总表中的总量；产品出仓表，添加记录前得判断出仓数量有没有超过当前库存量，用文章中的方法就很难实现了。所以，我们必须在DataGridView进行增删改查操作，然后手动写数据库更新代码。代码如下：

复制内容到剪贴板程序代码 程序代码
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //dataGridView1配置
            dataGridView1.AllowUserToAddRows = true;
            dataGridView1.AllowUserToDeleteRows = true;
            dataGridView1.ReadOnly = false;
            dataGridView1.Columns["ProductID"].ReadOnly = true;

            //数据绑定
            using (SqlConnection conn = new SqlConnection("server=(local);database=Test;user id=sa;password=sa;"))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter("Select * FROM Products orDER BY ProductID DESC", conn))
                {
                    DataTable table = new DataTable();
                    adapter.Fill(table);

                    dataGridView1.DataSource = table;
                }
            }
        }

        /// <summary>
        /// 提交更新
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            if (dataGridView1.DataSource != null)
            {
                DataRow row;
                DataTable table = (DataTable)dataGridView1.DataSource;
                int count = table.Rows.Count;
                int newid = 0;

                using (SqlConnection conn = new SqlConnection("server=(local);database=Test;user id=sa;password=sa;"))
                {
                    SqlCommand cmd;
                    conn.Open();

                    for (int i = 0; i < count; i++)
                    {
                        row = table.Rows[i];

                        if (row.RowState != DataRowState.Unchanged)
                        {
                            //添加
                            if (row.RowState == DataRowState.Added)
                            {
                                using (cmd = new SqlCommand("Insert INTO Products(ProductName,Price,Num,PriceSUM) VALUES(@ProductName,@Price,@Num,@PriceSUM);Select SCOPE_IDENTITY();", conn))
                                {
                                    cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar, 20);
                                    cmd.Parameters.Add("@Price", SqlDbType.Money, 10);
                                    cmd.Parameters.Add("@Num", SqlDbType.Int, 10);
                                    cmd.Parameters.Add("@PriceSUM", SqlDbType.Money, 10);

                                    cmd.Parameters["@ProductName"].Value = row["ProductName"];
                                    cmd.Parameters["@Price"].Value = row["Price"];
                                    cmd.Parameters["@Num"].Value = row["Num"];
                                    cmd.Parameters["@PriceSUM"].Value = row["PriceSUM"];

                                    newid = Convert.ToInt32(cmd.ExecuteScalar());
                                    row["ProductID"] = newid;
                                }
                            }
                            //修改
                            else if (row.RowState == DataRowState.Modified)
                            {
                                using (cmd = new SqlCommand("Update Products SET ProductName=@ProductName,Price=@Price,Num=@Num,PriceSUM=@PriceSUM Where ProductID=@ProductID", conn))
                                {
                                    cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar, 20);
                                    cmd.Parameters.Add("@Price", SqlDbType.Money, 10);
                                    cmd.Parameters.Add("@Num", SqlDbType.Int, 10);
                                    cmd.Parameters.Add("@PriceSUM", SqlDbType.Money, 10);
                                    cmd.Parameters.Add("@ProductID", SqlDbType.Int, 10);

                                    cmd.Parameters["@ProductName"].Value = row["ProductName"];
                                    cmd.Parameters["@Price"].Value = row["Price"];
                                    cmd.Parameters["@Num"].Value = row["Num"];
                                    cmd.Parameters["@PriceSUM"].Value = row["PriceSUM"];
                                    cmd.Parameters["@ProductID"].Value = row["ProductID", DataRowVersion.Original];//取修改前的值

                                    cmd.ExecuteNonQuery();
                                }
                            }
                            //删除
                            else if (row.RowState == DataRowState.Deleted)
                            {
                                using (cmd = new SqlCommand("Delete FROM Products Where ProductID=@ProductID", conn))
                                {
                                    cmd.Parameters.Add("@ProductID", SqlDbType.Int, 10);

                                    cmd.Parameters["@ProductID"].Value = row["ProductID", DataRowVersion.Original];

                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }

                    conn.Close();

                    table.AcceptChanges();
                }
            }
        }
    }
}


上一篇: 获取DataTable单元格修改前的值示例
下一篇: C#自定义类实现集合类
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
最新日志:
评论: 0 | 引用: 0 | 查看次数: 5174
发表评论
请登录后再发表评论！