通常我们在设计主从表是，有增加删除改。
我采用的方法是单头和单身 和DT同时绑定，这样的话就可以通过DT的状态还进行新增、修改、删除
直接上源码了：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SqlServerDAL;
using System.Windows.Forms;
namespace BLL
{
/// <summary>
/// 采购单管理
/// </summary>
public class PO
{
private DataTable dtNav = null; //单据导航
private DataTable dtDD = null; //单头
private DataTable dtDetial = null;//单身明细
private DataSet ds = null;
public PO() //构造函数
{
dtNav = new DataTable();
dtDD = new DataTable();
dtDetial = new DataTable();
DataColumn col;
//导航列表[单别，单号，供应商编码]
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "DocType";
dtNav.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "DocNumber";
dtNav.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "Vendor";
dtNav.Columns.Add(col);
//单头
col = new DataColumn();
col.DataType = Type.GetType("System.Int32");
col.ColumnName = "ID";
dtDD.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "DocType";
dtDD.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "DocNumber";
dtDD.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "Vendor";
dtDD.Columns.Add(col);
//明细表
col = new DataColumn();
col.DataType = Type.GetType("System.Int32");
col.ColumnName = "ID";
dtDetial.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "DocType";
col.ReadOnly = true;
dtDetial.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "DocNumber";
col.ReadOnly = true;
dtDetial.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.Int32");
col.ColumnName = "Line";
col.ReadOnly = true;
dtDetial.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "item";
dtDetial.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "itemdes";
dtDetial.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "supplydate";
dtDetial.Columns.Add(col);
col = new DataColumn();
col.DataType = Type.GetType("System.String");
col.ColumnName = "xx";
dtDetial.Columns.Add(col);
// dtDetial.PrimaryKey = new DataColumn[] { dtDetial.Columns["Line"], };
}
/// <summary>
/// 清除导航表数据
/// </summary>
private void ClearRowFordtNav()
{
if (dtNav.Rows.Count > 0)
{
dtNav.Rows.Clear();
}
}
/// <summary>
/// 清除明细表数据
/// </summary>
private void ClearRowFordtDetial()
{
if (dtDetial.Rows.Count > 0)
{
dtDetial.Rows.Clear();
}
}
/// <summary>
/// 清除单头数据
/// </summary>
private void ClearRowFordtDD()
{
if (dtDD.Rows.Count > 0)
{
dtDD.Rows.Clear();
}
}
/// <summary>
/// 得到最新采购单号
/// </summary>
/// <returns></returns>
public string GetPONumber()
{
/*
int result;
string sql =string.Format("select count(DocNumber) from PO where DocType='{0}' ",model.DocType );
result = (int)SqlHelper.ExecuteScalar(SqlHelper.conn, CommandType.Text, sql);
if (result == 0)
{
return result = 1;
}
sql = string.Format("select max(DocNumber) from PO where DocType='{0}' ", model.DocType);
result = (int)SqlHelper.ExecuteScalar(SqlHelper.conn, CommandType.Text, sql);
return result + 1;
*/
return "";
}
/// <summary>
/// 是否已有ID记录
/// </summary>
/// <param name="strCPLBLX"></param>
/// <returns></returns>
private bool HaveID(String DocType, String DocNumber)
{
string sql = string.Format("select count(DocNumber) from PO where DocType='{0}' and DocNumber='{1}'", DocType,DocNumber);
int result;
result = (int)SqlHelper.ExecuteScalar(SqlHelper.conn, CommandType.Text, sql);
return result > 0;
}
/// <summary>
/// 导航树表
/// </summary>
public DataTable GetDtNav
{
get { return this.dtNav; }
}
/// <summary>
/// 单头
/// </summary>
public DataTable GetDtDD
{
get { return this.dtDD; }
}
/// <summary>
/// 明细表
/// </summary>
public DataTable GetDtDetial
{
get { return this.dtDetial; }
}
/// <summary>
/// 新增一张单，并未在数据库里增加
/// </summary>
/// <returns></returns>
public bool Add( )
{
try
{
this.ClearRowFordtDetial();
this.ClearRowFordtDD();
DataRow dr = null;
dr = dtDD.NewRow();
dr["DocType"] = "PO" ;
dr["DocNumber"] = "2010112" ;
dr["Vendor"] = "A001";
dtDD.Rows.Add(dr);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 单据查找（导航列表）
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool FindForNav(string sql)
{
this.ClearRowFordtNav();
this.ClearRowFordtDetial();
DataTable DtTemp = new DataTable();
try
{
DtTemp = SqlHelper.ExecuteDataset(SqlHelper.conn, CommandType.Text, sql).Tables[0];
DataRow dr = null;
for (int i = 0; i < DtTemp.Rows.Count; i++)
{
dr = DtTemp.Rows[i];
dtNav.ImportRow(dr);
}
return true;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("发生错误：//n" + e.Message, "系统提示!", System.Windows.Forms.MessageBoxButtons.OK);
return false;
}
}
/// <summary>
/// 查找单头
/// </summary>
/// <param name="CGDB"></param>
/// <param name="CHDH"></param>
/// <returns></returns>
public bool FindForDD( Model.PO model)
{
this.ClearRowFordtDetial();
this.ClearRowFordtDD();
DataTable DtTemp = new DataTable();
string spName = "sp_cpb_dd_item";
SqlParameter[] sqlParameters = new SqlParameter[]{
new SqlParameter("@DocType",SqlDbType.Char,2),
new SqlParameter("@DocNumber",SqlDbType.Char,10),
};
sqlParameters[0].Value = model.DocType ;
sqlParameters[1].Value = model.DocNumber;
try
{
DtTemp = SqlHelper.ExecuteDataset(SqlHelper.conn, CommandType.StoredProcedure, spName, sqlParameters).Tables[0];
DataRow dr = null;
for (int i = 0; i < DtTemp.Rows.Count; i++)
{
dr = DtTemp.Rows[i];
this.dtDD.ImportRow(dr);
}
dtDD.AcceptChanges();
return true;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("发生错误：//n" + e.Message, "系统提示!", System.Windows.Forms.MessageBoxButtons.OK);
return false;
}
}
/// <summary>
/// 单据查找（单身）
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public bool FindForDetail( Model.PO model)
{
this.ClearRowFordtDetial();
DataTable DtTemp = new DataTable();
string spName = "sp_cpb_Detail_list";
SqlParameter[] sqlParameters = new SqlParameter[]{
new SqlParameter("@DocType",SqlDbType.Char,2),
new SqlParameter("@DocNumber",SqlDbType.Char,10),
};
sqlParameters[0].Value = model.DocType;
sqlParameters[1].Value = model.DocNumber;
try
{
DtTemp = SqlHelper.ExecuteDataset(SqlHelper.conn, CommandType.StoredProcedure, spName, sqlParameters).Tables[0];
DataRow dr = null;
for (int i = 0; i < DtTemp.Rows.Count; i++)
{
dr = DtTemp.Rows[i];
this.dtDetial.ImportRow(dr);
}
dtDetial.AcceptChanges();
return true;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("发生错误：//n" + e.Message, "系统提示!", System.Windows.Forms.MessageBoxButtons.OK);
return false;
}
}
//行添加
public bool addRow()
{
try
{
DataRow dr = dtDetial.NewRow();
dr["DocType"] = dtDD.Rows[0]["DocType"];
dr["DocNumber"] = dtDD.Rows[0]["DocNumber"];
if (dtDetial.Compute("MAX(Line)+1", "").ToString() == "")
{
dr["Line"] = 1;
}
else
{
dr["Line"] = dtDetial.Compute("MAX(Line)+1", "");
}
/*
if (dtDetial.Compute("MAX(Line)+1", "").ToString() == "")
{
dr["item"] ="m"+ "1";
}
else
{
dr["item"] = "m"+dtDetial.Compute("MAX(Line)+1", "");
}
*/
dtDetial.Rows.Add(dr);
return true;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("在插行时发生错误：//n" + e.Message, "系统提示!", System.Windows.Forms.MessageBoxButtons.OK);
return false;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="DSXH">单身序号ID</param>
/// <returns></returns>
public bool DeleteRow(int DSXH)
{
try
{
dtDetial.Rows.Find(DSXH).Delete();
return true;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("在删行时发生错误：//n" + e.Message, "系统提示!", System.Windows.Forms.MessageBoxButtons.OK);
return false;
}
}
/// <summary>
/// 删除当前单据
/// </summary>
/// <returns></returns>
public bool Delete(Model.PO model )
{
DataRow dr = null;
dr = dtDD.Rows[0];
string spName =string .Empty;
SqlParameter[] sqlParameters = null;
String DocType = dr["DocType"].ToString();
String DocNumber = dr["DocNumber"].ToString();
spName = "sp_cgd_delete";
sqlParameters = new SqlParameter[]{
new SqlParameter("@DocType",SqlDbType.Char,2),
new SqlParameter("@DocNumber",SqlDbType.Char,10),
};
sqlParameters[0].Value = model.DocType ;
sqlParameters[1].Value = model.DocNumber;
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.conn, CommandType.StoredProcedure, spName, sqlParameters);
this.ClearRowFordtDetial();
this.ClearRowFordtDD();
foreach (DataRow drNav in dtNav.Select("DocType='" + model.DocType + "' AND DocNumber=" + model.DocNumber + ""))
{
this.dtNav.Rows.Remove(drNav);
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 保存当当单据
/// </summary>
/// <returns></returns>
//单击保存时单头只有两种情况新增和修改
//单身就有增．删除．修改
public bool Save(Model.PO model)
{
//数据验证
if (dtDD.Rows.Count != 1)
{
System.Windows.Forms.MessageBox.Show("单头上没有资料!!!");
return false;
}
//单头信息验证
DataRow drDD = dtDD.Rows[0];
if (drDD["DocType"].ToString().Trim().Length == 0)
{
System.Windows.Forms.MessageBox.Show("请输入采购单别!!!");
return false;
}
if (drDD["DocNumber"].ToString().Trim().Length == 0)
{
System.Windows.Forms.MessageBox.Show("请输入采购单号!!!");
return false;
}
//以下方法在删除行后，再引用会出错
for (int i = 0; i < dtDetial.Rows.Count; i++)
{
if (dtDetial.Rows[i].RowState == DataRowState.Deleted)
{
if (dtDetial.Rows[i]["ID", DataRowVersion.Original].ToString() == "")
{
System.Windows.Forms.MessageBox.Show("第<" + (1 + i) + ">行没有ID号");
return false;
}
else
{
continue;
}
}
if (dtDetial.Rows[i]["item"].ToString() == "")
{
System.Windows.Forms.MessageBox.Show("第<" + (1 + i) + ">行请输入产品编码");
return false;
}
if (dtDetial.Rows[i]["itemdes"].ToString() == "")
{
System.Windows.Forms.MessageBox.Show("第<" + (1 + i) + ">行请输入产品规格");
return false;
}
}
string spName = "";
SqlParameter[] sqlParameters = null;
if (drDD.RowState == DataRowState.Added)
{
if (this.HaveID(drDD["DocType"].ToString(), drDD["DocNumber"].ToString()))
{
System.Windows.Forms.MessageBox.Show("已有该采购单号的存在!!!");
return false;
}
spName = "sp_cgd_insert";
sqlParameters = new SqlParameter[]{
new SqlParameter("@doctype",SqlDbType.Char,2),
new SqlParameter("@docnumber",SqlDbType.Char,10),
new SqlParameter("@vendor",SqlDbType.Char,10),
};
sqlParameters[0].Value = drDD["doctype"].ToString().Trim() ;
sqlParameters[1].Value = drDD["docnumber"].ToString().Trim() ;
sqlParameters[2].Value = drDD["vendor"].ToString().Trim() ;
}
else if (drDD.RowState == DataRowState.Unchanged) //Unchanged DataRowState.Modified)
{
spName = "sp_cgd_update";
sqlParameters = new SqlParameter[]{
new SqlParameter("@doctype",SqlDbType.Char,2),
new SqlParameter("@docnumber",SqlDbType.Char,10),
new SqlParameter("@vendor",SqlDbType.Char,4)
};
sqlParameters[0].Value = drDD["doctype"].ToString().Trim ();
sqlParameters[1].Value = drDD["docnumber"].ToString().Trim() ;
sqlParameters[2].Value = model.Vendor; //drDD["vendor", DataRowVersion.Current].ToString().Trim(); // drDD["vendor"].ToString().Trim() ;
}
bool saveReault = false;
using (SqlConnection cn = new SqlConnection(SqlHelper.conn))
{
cn.Open();
SqlTransaction transaction = cn.BeginTransaction();
try
{
if (spName != "")
{
SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, sqlParameters);
}
foreach (DataRow drDet in dtDetial.Rows)
{
switch (drDet.RowState)
{
case DataRowState.Added:
spName = "sp_cgdmx_insert";
sqlParameters = new SqlParameter[]{
new SqlParameter("@doctype",SqlDbType.Char,2),
new SqlParameter("@docnumber",SqlDbType.Char,10),
new SqlParameter("@line",SqlDbType.Int),
new SqlParameter("@item",SqlDbType.Char,10),
new SqlParameter("@itemdes",SqlDbType.Char,10),
new SqlParameter("@supplydate",SqlDbType.Char,10),
};
sqlParameters[0].Value = drDet["doctype"].ToString().Trim() ;
sqlParameters[1].Value = drDet["docnumber"].ToString().Trim() ;
sqlParameters[2].Value = Convert.ToInt32(drDet["line"].ToString().Trim());
sqlParameters[3].Value = drDet["item"].ToString().Trim() ;
sqlParameters[4].Value = drDet["itemdes"].ToString().Trim();
sqlParameters[5].Value = drDet["supplydate"].ToString().Trim();
SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, sqlParameters);
break;
case DataRowState.Deleted:
spName = "sp_cgdmx_delete";
sqlParameters = new SqlParameter[]{
new SqlParameter("@doctype",SqlDbType.Char,2),
new SqlParameter("@docnumber",SqlDbType.Char,10),
new SqlParameter("@line",SqlDbType.Int ),
};
sqlParameters[0].Value = drDet["doctype", DataRowVersion.Original].ToString().Trim() ;
sqlParameters[1].Value = drDet["docnumber", DataRowVersion.Original].ToString().Trim() ;
sqlParameters[2].Value = Convert.ToInt32(drDet["line", DataRowVersion.Original].ToString().Trim() );
SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, sqlParameters);
break;
case DataRowState.Detached:
break;
case DataRowState.Modified:
spName = "sp_cgdmx_update";
sqlParameters = new SqlParameter[]{
new SqlParameter("@doctype",SqlDbType.Char,2),
new SqlParameter("@docnumber",SqlDbType.Char,10),
new SqlParameter("@line",SqlDbType.Int),
new SqlParameter("@item",SqlDbType.Char,10),
new SqlParameter("@itemdes",SqlDbType.Char,10),
new SqlParameter("@supplydate",SqlDbType.Char,10),
};
sqlParameters[0].Value = drDet["doctype", DataRowVersion.Current].ToString().Trim ();
sqlParameters[1].Value = drDet["docnumber", DataRowVersion.Current].ToString().Trim ();
sqlParameters[2].Value = Convert.ToInt32(drDet["line", DataRowVersion.Current].ToString().Trim ());
sqlParameters[3].Value = drDet["item", DataRowVersion.Current].ToString().Trim ();
sqlParameters[4].Value = drDet["itemdes", DataRowVersion.Current].ToString().Trim ();
sqlParameters[5].Value = drDet["supplydate", DataRowVersion.Current].ToString().Trim ();
SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, sqlParameters);
break;
case DataRowState.Unchanged:
break;
default:
break;
}
}
transaction.Commit();
saveReault = true;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("在保存采购单时发生错误：/n" + e.Message, "系统提示!", System.Windows.Forms.MessageBoxButtons.OK);
transaction.Rollback();
}
finally
{
transaction = null;
}
}
return saveReault;
}
}
}
----------------------------------------------------------------------
//
BLL.PO po = null;
BindingSource db = null;
private void frm_user_Load(object sender, EventArgs e)
{
po = new BLL.PO();
db = new BindingSource();
db.DataSource = this.po.GetDtDetial;
this.datagv_det.DataSource = db;
this.textBox1.DataBindings.Add("Text", this.po.GetDtDD, "DocType", true, DataSourceUpdateMode.OnPropertyChanged);
this.textBox2.DataBindings.Add("Text", this.po.GetDtDD, "DocNumber", true, DataSourceUpdateMode.OnPropertyChanged);
this.textBox3.DataBindings.Add("Text", this.po.GetDtDD, "Vendor", true, DataSourceUpdateMode.OnPropertyChanged);
//this.comboBox1.DataBindings.Add ("Text", this.po.GetDtDD, "Vendor", true, DataSourceUpdateMode.OnPropertyChanged);
this.datagv_det.AutoGenerateColumns = false;
datagv_det.Columns[0].Visible =false ;
datagv_det.Columns[1].HeaderText = "单据类别";
datagv_det.Columns[2].HeaderText = "单据号码";
datagv_det.Columns[3].HeaderText = "行号";
datagv_det.Columns[4].HeaderText = "品号";
datagv_det.Columns[5].HeaderText = "品名";
datagv_det.Columns[6].HeaderText = "交货日期";
datagv_dnv .SelectionMode = DataGridViewSelectionMode.FullRowSelect ;
datagv_dnv.DefaultCellStyle.SelectionBackColor = Color.YellowGreen;
datagv_det.Columns["line"].SortMode = DataGridViewColumnSortMode.NotSortable;
datagv_det.Columns["item"].SortMode = DataGridViewColumnSortMode.NotSortable;
datagv_det.Columns["itemdes"].SortMode = DataGridViewColumnSortMode.NotSortable;
datagv_det.Columns["supplydate"].SortMode = DataGridViewColumnSortMode.NotSortable;
}
private void toolStripbtnadd_Click(object sender, EventArgs e)
{
Enable();
toolStripbtnsave.Enabled = true;
toolStripbtncancel.Enabled = true;
toolStripbtnnewline.Enabled = true;
this.po.Add();//给单头赋值
}
private void toolStriptbnquery_Click(object sender, EventArgs e)
{
//UnEnable();
//查询导航列表
string strsql = string.Format("select DocType,DocNumber,Vendor from PO");
po.FindForNav(strsql);
db = new BindingSource();
db.DataSource = this.po.GetDtNav;
this.datagv_dnv.DataSource = db;
this.datagv_dnv.ReadOnly = true;
this.tabControl_cgd .SelectedIndex=1;
datagv_dnv.Columns[0].HeaderText = "单据类别";
datagv_dnv.Columns[1].HeaderText = "单据号码";
datagv_dnv.Columns[2].HeaderText = "供应商";
toolStripbtnexcel.Enabled = true;
}
private void toolStripbtnsave_Click(object sender, EventArgs e)
{
//保存之前重新绑定DT
db = new BindingSource();
db.DataSource = this.po.GetDtDetial;
this.datagv_det.DataSource = db;
this.datagv_det.EndEdit(); //插入前结束编辑状态
this.db.EndEdit();
datagv_det.CommitEdit (DataGridViewDataErrorContexts.Commit);
Model.PO pom = new Model.PO();
pom.Vendor = textBox3.Text.Trim();
if (po.Save(pom))
{
//MessageBox.Show("操作成功");
//刷新
Model.PO pom1 = new Model.PO();
pom1.DocType =textBox1 .Text .Trim() ;
pom1.DocNumber = textBox2.Text.Trim();
po.FindForDD(pom1);
po.FindForDetail(pom1);
}
}
private void toolStripbtnnewline_Click(object sender, EventArgs e)
{
this.po.addRow();
textBox1.Enabled = false;
textBox2.Enabled = false;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
