主从表设计
来源：互联网 发布：linux cpu 编辑：程序博客网 时间：2024/06/09 21:53
通常我们在设计主从表是，有增加删除改。
我采用的方法是单头和单身 和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; }- 主从表XtarGrid设计
- 主从表设计
- 数据库查询优化(主从表的设计)
- 数据库主从设计
- mysql 主从设计
- mysql主从设计
- 主从表设计和编程的几种实现办法
- 主从表设计和编程的几种实现办法
- 主从表
- 水晶报表之主从多表数据源批量预览及打印开发设计
- FastReport for.Net开发指南-主从表(Master/Detail)报表设计详解
- 4数据库设计模式：1.2 主从模式
- 漫游Kafka设计篇之主从同步
- 漫游Kafka设计篇之主从同步
- 漫游Kafka设计篇之主从同步
- 漫游Kafka设计篇之主从同步
- MongoDB分布式设计-主从复制，副本集
- mysql主从-主主架构设计
- 插入排序算法
- 使用div/css布局
- 整理分析自己的软考过程
- makefile基础
- 实验五 数据库完整性(详细解释+错误修正版)
- 主从表设计
- Java字符串排序
- Java: File copy in Java – Benchmark(Java文件复制)
- 关于C#操作IIS问题
- axis2 webservice 小例子
- 感觉又回到了起点...
- oracle表空间操作详解
- SoRayPickAction类的基本使用
- 转 FreeBSD 8下如何最有效率的安装软件(1)
