标题: DataGridView数据库手动更新示例
作者: dnawo
日期: 2009-10-29 09:42 AM
分类: Win编程
内容: 若表是独立存在的(所有字段和其他表都没有关系)，照《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();
}
}
}
}
}
+ 查看当前日志评论 (0)
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.064 ms
你没有权限发表评论
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.064 ms
暂无评论
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.079 ms