利用DataSet同时处理多表

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace MstDetail
{
 /// <summary>
 /// Form1 的摘要说明。
 /// </summary>
 public class Form1 : System.Windows.Forms.Form
 {
  private string[] TableNameTotal = {"Suppliers","products","Sp"} ;
  //string cString = "database=MyDB;server=localhost;User id = sa;passWord =;";
  string cString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

  DataSet TotalData ;
  BindingManagerBase bm;
  int OldPosition = 0;
  Dataset1 ds1 = new Dataset1();

  private System.Windows.Forms.GroupBox groupBox;
  private System.Windows.Forms.TextBox textBox1;
  private System.Windows.Forms.TextBox textBox2;
  private System.Windows.Forms.TextBox textBox3;
  private System.Windows.Forms.Label label1;
  private System.Windows.Forms.Label label2;
  private System.Windows.Forms.Label label3;
  private System.Windows.Forms.Button button1;
  private System.Windows.Forms.Button button2;
  private System.Windows.Forms.Button button3;
  private System.Windows.Forms.Button button4;
  private System.Windows.Forms.DataGrid dataGrid2;
  private System.Windows.Forms.DataGrid dataGrid3;
  private System.Windows.Forms.Button button5;
  private System.Windows.Forms.Button button6;
  private System.Windows.Forms.Button button7;
  private System.Windows.Forms.Button button8;
  private System.Windows.Forms.Button button9;
  private System.Windows.Forms.Button button10;
  private CrystalDecisions.Windows.Forms.CrystalReportViewer crystalReportViewer1;
 // DataSet ds = new DataSet();

  /// <summary>
  /// 必需的设计器变量。
  /// </summary>
  private System.ComponentModel.Container components = null;
  public Form1()
  {
   //
   // Windows 窗体设计器支持所必需的
   //
   InitializeComponent();

   //
   // TODO: 在 InitializeComponent 调用后添加任何构造函数代码
   //   
  }

  /// <summary>
  /// 清理所有正在使用的资源。
  /// </summary>
  protected override void Dispose( bool disposing )
  {
   if( disposing )
   {
    if (components != null)
    {
     components.Dispose();
    }
   }
   base.Dispose( disposing );
  }

  #region Windows Form Designer generated code
  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.groupBox = new System.Windows.Forms.GroupBox();
   this.label3 = new System.Windows.Forms.Label();
   this.label2 = new System.Windows.Forms.Label();
   this.label1 = new System.Windows.Forms.Label();
   this.textBox3 = new System.Windows.Forms.TextBox();
   this.textBox2 = new System.Windows.Forms.TextBox();
   this.textBox1 = new System.Windows.Forms.TextBox();
   this.button1 = new System.Windows.Forms.Button();
   this.button2 = new System.Windows.Forms.Button();
   this.button3 = new System.Windows.Forms.Button();
   this.button4 = new System.Windows.Forms.Button();
   this.dataGrid2 = new System.Windows.Forms.DataGrid();
   this.dataGrid3 = new System.Windows.Forms.DataGrid();
   this.button5 = new System.Windows.Forms.Button();
   this.button6 = new System.Windows.Forms.Button();
   this.button7 = new System.Windows.Forms.Button();
   this.button8 = new System.Windows.Forms.Button();
   this.button9 = new System.Windows.Forms.Button();
   this.button10 = new System.Windows.Forms.Button();
   this.crystalReportViewer1 = new CrystalDecisions.Windows.Forms.CrystalReportViewer();
   this.groupBox.SuspendLayout();
   ((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).BeginInit();
   ((System.ComponentModel.ISupportInitialize)(this.dataGrid3)).BeginInit();
   this.SuspendLayout();
   //
   // groupBox
   //
   this.groupBox.Controls.AddRange(new System.Windows.Forms.Control[] {
                       this.label3,
                       this.label2,
                       this.label1,
                       this.textBox3,
                       this.textBox2,
                       this.textBox1});
   this.groupBox.Location = new System.Drawing.Point(8, 40);
   this.groupBox.Name = "groupBox";
   this.groupBox.Size = new System.Drawing.Size(728, 56);
   this.groupBox.TabIndex = 0;
   this.groupBox.TabStop = false;
   //
   // label3
   //
   this.label3.Location = new System.Drawing.Point(480, 24);
   this.label3.Name = "label3";
   this.label3.TabIndex = 5;
   this.label3.Text = "状态";
   this.label3.TextAlign = System.Drawing.ContentAlignment.TopRight;
   //
   // label2
   //
   this.label2.Location = new System.Drawing.Point(8, 24);
   this.label2.Name = "label2";
   this.label2.TabIndex = 4;
   this.label2.Text = "公司编号";
   this.label2.TextAlign = System.Drawing.ContentAlignment.TopRight;
   //
   // label1
   //
   this.label1.Location = new System.Drawing.Point(240, 24);
   this.label1.Name = "label1";
   this.label1.TabIndex = 3;
   this.label1.Text = "公司名称";
   this.label1.TextAlign = System.Drawing.ContentAlignment.TopRight;
   //
   // textBox3
   //
   this.textBox3.Enabled = false;
   this.textBox3.Location = new System.Drawing.Point(592, 24);
   this.textBox3.Name = "textBox3";
   this.textBox3.TabIndex = 2;
   this.textBox3.Tag = "Status";
   this.textBox3.Text = "";
   //
   // textBox2
   //
   this.textBox2.Enabled = false;
   this.textBox2.Location = new System.Drawing.Point(352, 24);
   this.textBox2.Name = "textBox2";
   this.textBox2.TabIndex = 1;
   this.textBox2.Tag = "CompanyName";
   this.textBox2.Text = "";
   //
   // textBox1
   //
   this.textBox1.Enabled = false;
   this.textBox1.Location = new System.Drawing.Point(112, 24);
   this.textBox1.Name = "textBox1";
   this.textBox1.TabIndex = 0;
   this.textBox1.Tag = "SupplierID";
   this.textBox1.Text = "";
   //
   // button1
   //
   this.button1.Location = new System.Drawing.Point(8, 8);
   this.button1.Name = "button1";
   this.button1.Size = new System.Drawing.Size(48, 23);
   this.button1.TabIndex = 1;
   this.button1.Text = "最前";
   this.button1.Click += new System.EventHandler(this.button1_Click);
   //
   // button2
   //
   this.button2.Location = new System.Drawing.Point(64, 8);
   this.button2.Name = "button2";
   this.button2.Size = new System.Drawing.Size(48, 23);
   this.button2.TabIndex = 2;
   this.button2.Text = "向前";
   this.button2.Click += new System.EventHandler(this.button2_Click);
   //
   // button3
   //
   this.button3.Location = new System.Drawing.Point(120, 8);
   this.button3.Name = "button3";
   this.button3.Size = new System.Drawing.Size(48, 23);
   this.button3.TabIndex = 3;
   this.button3.Text = "向后";
   this.button3.Click += new System.EventHandler(this.button3_Click);
   //
   // button4
   //
   this.button4.Location = new System.Drawing.Point(176, 8);
   this.button4.Name = "button4";
   this.button4.Size = new System.Drawing.Size(48, 23);
   this.button4.TabIndex = 4;
   this.button4.Text = "最后";
   this.button4.Click += new System.EventHandler(this.button4_Click);
   //
   // dataGrid2
   //
   this.dataGrid2.DataMember = "";
   this.dataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText;
   this.dataGrid2.Location = new System.Drawing.Point(368, 104);
   this.dataGrid2.Name = "dataGrid2";
   this.dataGrid2.Size = new System.Drawing.Size(360, 96);
   this.dataGrid2.TabIndex = 6;
   //
   // dataGrid3
   //
   this.dataGrid3.DataMember = "";
   this.dataGrid3.HeaderForeColor = System.Drawing.SystemColors.ControlText;
   this.dataGrid3.Location = new System.Drawing.Point(8, 104);
   this.dataGrid3.Name = "dataGrid3";
   this.dataGrid3.Size = new System.Drawing.Size(352, 96);
   this.dataGrid3.TabIndex = 7;
   //
   // button5
   //
   this.button5.Location = new System.Drawing.Point(272, 8);
   this.button5.Name = "button5";
   this.button5.Size = new System.Drawing.Size(48, 23);
   this.button5.TabIndex = 8;
   this.button5.Text = "新增";
   this.button5.Click += new System.EventHandler(this.button5_Click);
   //
   // button6
   //
   this.button6.Location = new System.Drawing.Point(328, 8);
   this.button6.Name = "button6";
   this.button6.Size = new System.Drawing.Size(48, 23);
   this.button6.TabIndex = 9;
   this.button6.Text = "修改";
   this.button6.Click += new System.EventHandler(this.button6_Click);
   //
   // button7
   //
   this.button7.Location = new System.Drawing.Point(384, 8);
   this.button7.Name = "button7";
   this.button7.Size = new System.Drawing.Size(48, 23);
   this.button7.TabIndex = 10;
   this.button7.Text = "作废";
   this.button7.Click += new System.EventHandler(this.button7_Click);
   //
   // button8
   //
   this.button8.Enabled = false;
   this.button8.Location = new System.Drawing.Point(440, 8);
   this.button8.Name = "button8";
   this.button8.Size = new System.Drawing.Size(48, 23);
   this.button8.TabIndex = 11;
   this.button8.Text = "删除";
   this.button8.Click += new System.EventHandler(this.button8_Click);
   //
   // button9
   //
   this.button9.Location = new System.Drawing.Point(496, 8);
   this.button9.Name = "button9";
   this.button9.Size = new System.Drawing.Size(48, 23);
   this.button9.TabIndex = 12;
   this.button9.Text = "确定";
   this.button9.Click += new System.EventHandler(this.button9_Click);
   //
   // button10
   //
   this.button10.Location = new System.Drawing.Point(552, 8);
   this.button10.Name = "button10";
   this.button10.Size = new System.Drawing.Size(48, 23);
   this.button10.TabIndex = 13;
   this.button10.Text = "取消";
   this.button10.Click += new System.EventHandler(this.button10_Click);
   //
   // crystalReportViewer1
   //
   this.crystalReportViewer1.ActiveViewIndex = -1;
   this.crystalReportViewer1.Location = new System.Drawing.Point(8, 208);
   this.crystalReportViewer1.Name = "crystalReportViewer1";
   this.crystalReportViewer1.ReportSource = null;
   this.crystalReportViewer1.Size = new System.Drawing.Size(720, 296);
   this.crystalReportViewer1.TabIndex = 14;
   //
   // Form1
   //
   this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
   this.ClientSize = new System.Drawing.Size(736, 517);
   this.Controls.AddRange(new System.Windows.Forms.Control[] {
                    this.crystalReportViewer1,
                    this.button10,
                    this.button9,
                    this.button8,
                    this.button7,
                    this.button6,
                    this.button5,
                    this.dataGrid3,
                    this.dataGrid2,
                    this.button4,
                    this.button3,
                    this.button2,
                    this.button1,
                    this.groupBox});
   this.Name = "Form1";
   this.Text = "Form1";
   this.Load += new System.EventHandler(this.Form1_Load);
   this.groupBox.ResumeLayout(false);
   ((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();
   ((System.ComponentModel.ISupportInitialize)(this.dataGrid3)).EndInit();
   this.ResumeLayout(false);

  }
  #endregion

  /// <summary>
  /// 应用程序的主入口点。
  /// </summary>
  [STAThread]
  static void Main()
  {
   Application.Run(new Form1());
  }

 
  public  ArrayList Adapters
  {
   get
   {
    SqlConnection myCn = new SqlConnection(cString);
    ArrayList SqlDataAdapters=new ArrayList(TableNameTotal.Length);
    for(int i=0;i<TableNameTotal.Length;i++)
    {
     string strSQL="select * from "+TableNameTotal[i] + "  where status !=  'Discard' ";
     SqlDataAdapters.Insert(i,new SqlDataAdapter(strSQL,myCn));
    }

    return SqlDataAdapters;
   }
  }

  public  DataSet ExecuteTotalData()
  {
   SqlConnection myCn = new SqlConnection(cString);
   try
   {
    for(int i=0;i<TableNameTotal.Length;i++)
    {
     SqlDataAdapter adapter=(SqlDataAdapter)Adapters[i];
     adapter.Fill(TotalData,TableNameTotal[i]);
     adapter.Fill(ds1,TableNameTotal[i]);

    }
    return TotalData;
   }
   catch(System.Data.SqlClient.SqlException e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    myCn.Close();
   }
  }

  public bool UpdateDB(DataSet TotalDS)
  {
   SqlConnection myCn = new SqlConnection(cString);
   try
   {
    for(int i=0;i<TableNameTotal.Length;i++)
    {
     SqlDataAdapter adapter=(SqlDataAdapter)Adapters[i];
     SqlCommandBuilder cb=new SqlCommandBuilder(adapter);
     adapter.Update(TotalDS,TotalDS.Tables[i].TableName);
    }

    return true;
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message,"System");
    return false;
   }
   finally
   {
    myCn.Close();
   }
  }

  private void Form1_Load(object sender, System.EventArgs e)
  { 
   this.RefreshDataSource();
  }

  private void RefreshDataSource()
  {
   this.TotalData = new DataSet();
   TotalData = this.ExecuteTotalData();
   this.BindControl();
   this.BindDataGrid();

   this.bm = this.BindingContext[this.TotalData.Tables["Suppliers"]];
   this.PosRefreshButton();
   this.OpRefreshBtn("Default");
  }
  private void BindControl()
  {
   foreach(Control ctl in this.groupBox.Controls)
   {
    if(ctl.Tag != null)
    {
     ctl.DataBindings.Clear();
    }
   }

   foreach(Control ctl in this.groupBox.Controls)
   {
    if(ctl.Tag != null)
    {
     ctl.DataBindings.Add(new Binding("Text",TotalData.Tables["Suppliers"],ctl.Tag.ToString()));
    }
   }
  }

  private void BindDataGrid()
  {
   DataColumn parentColumn = TotalData.Tables["Suppliers"].Columns["SupplierID"];
   DataColumn childColumn = TotalData.Tables["Products"].Columns["SupplierID"];
   TotalData.Relations.Add("ParentToChild", parentColumn, childColumn);
   
   DataColumn pColumn = TotalData.Tables["Suppliers"].Columns["SupplierID"];
   DataColumn cColumn = TotalData.Tables["SP"].Columns["SupplierID"];
   TotalData.Relations.Add("ParentChild", pColumn, cColumn);

   this.dataGrid2.DataSource =(DataTable)TotalData.Tables["Suppliers"];
   this.dataGrid2.DataMember = "ParentToChild";

   this.dataGrid3.DataSource = (DataTable)TotalData.Tables["Suppliers"];
   this.dataGrid3.DataMember = "ParentChild";

   this.dataGrid2.AllowNavigation = false;
   this.dataGrid3.AllowNavigation = false;
  }
  private void button1_Click(object sender, System.EventArgs e)
  {
   this.bm.Position = 0;
   this.PosRefreshButton();
  }

  private void PosRefreshButton()
  {
   if(this.bm.Position == 0)
   {
    this.button1.Enabled = false;
    this.button2.Enabled = false;
    this.button3.Enabled = true;
    this.button4.Enabled = true;
   }
   else
   {
    if(this.bm.Position == this.TotalData.Tables["Suppliers"].Rows.Count - 1 )
    {
     this.button1.Enabled = true;
     this.button2.Enabled = true;
     this.button3.Enabled = false;
     this.button4.Enabled = false;
    }
    else
    {
     this.button1.Enabled = true;
     this.button2.Enabled = true;
     this.button3.Enabled = true;
     this.button4.Enabled = true;
    }
   }
  }

  private void button2_Click(object sender, System.EventArgs e)
  {
   this.bm.Position -=1;
   this.PosRefreshButton();
  }

  private void button3_Click(object sender, System.EventArgs e)
  {
   this.bm.Position +=1;
   this.PosRefreshButton();
  }

  private void button4_Click(object sender, System.EventArgs e)
  {
   this.bm.Position = this.TotalData.Tables["Suppliers"].Rows.Count - 1;
   this.PosRefreshButton();
  }

  private void button5_Click(object sender, System.EventArgs e)
  {
   this.bm.AddNew();
   this.textBox3.Text = "Normal";
   
   this.OpRefreshText("Add");

   this.OpRefreshBtn("Add");

   this.CtlPosButton(false);
  }

  private void OpRefreshText(string str)
  {
   switch(str)
   {
    case "Add":
     this.textBox1.Enabled = true;
     this.textBox2.Enabled = true;
     this.textBox3.Enabled = false;
     break;
    case "Modify":
     this.textBox1.Enabled = false;
     this.textBox2.Enabled = true;
     this.textBox3.Enabled = false;
     break;
    case "OK":
     this.textBox1.Enabled = false;
     this.textBox2.Enabled = false;
     this.textBox3.Enabled = false;
     break;
    case "Cancel":
     this.textBox1.Enabled = false;
     this.textBox2.Enabled = false;
     this.textBox3.Enabled = false;
     break;
    case "Discard":
     this.textBox1.Enabled = false;
     this.textBox2.Enabled = false;
     this.textBox3.Enabled = false;
     break;
    default:
     this.textBox1.Enabled = false;
     this.textBox2.Enabled = false;
     this.textBox3.Enabled = false;
     break;
   }

  }

  private void OpRefreshBtn(string str)
  {
   switch(str)
   {
    case "Add":
     this.button5.Enabled = false;
     this.button6.Enabled = false;
     this.button7.Enabled = false;
     this.button9.Enabled = true;
     this.button10.Enabled = true;
     break;
    case "Modify":
     this.button5.Enabled = false;
     this.button6.Enabled = false;
     this.button7.Enabled = false;
     this.button9.Enabled = true;
     this.button10.Enabled = true;
     break;
    case "OK":
     this.button5.Enabled = true;
     this.button6.Enabled = true;
     this.button7.Enabled = true;
     this.button9.Enabled = false;
     this.button10.Enabled = false;
     break;
    case "Cancel":
     this.button5.Enabled = true;
     this.button6.Enabled = true;
     this.button7.Enabled = true;
     this.button9.Enabled = false;
     this.button10.Enabled = false;
     break;
    case "Discard":
     this.button5.Enabled = true;
     this.button6.Enabled = true;
     this.button7.Enabled = true;
     this.button9.Enabled = false;
     this.button10.Enabled = false;
     break;
    default:
     this.button5.Enabled = true;
     this.button6.Enabled = true;
     this.button7.Enabled = true;
     this.button9.Enabled = false;
     this.button10.Enabled = false;
     break;
   }

  }

  private void button6_Click(object sender, System.EventArgs e)
  {
   this.OpRefreshText("Modify");
   this.OpRefreshBtn("Modify");
   this.CtlPosButton(false);
  }

  private void button10_Click(object sender, System.EventArgs e)
  {
   this.OpRefreshText("Cancel");
   this.OpRefreshBtn("Cancel");
   this.bm.CancelCurrentEdit();
   this.CtlPosButton(true);
   this.PosRefreshButton();
  }

  private void button7_Click(object sender, System.EventArgs e)
  {
   if ((MessageBox.Show("Discard the record","Longrist_IRS",MessageBoxButtons.YesNo,MessageBoxIcon.Question,MessageBoxDefaultButton.Button1) ==DialogResult.Yes))
   {
     this.OldPosition = this.bm.Position;
     this.OpRefreshText("Discard");    
     this.textBox3.Text = "Discard";
     this.DiscardDataGrid(this.TotalData);
     this.UpdateDB(this.TotalData);
     RefreshDataSource();
   } 
   
   this.OpRefreshBtn("Discard");  
   
   this.CtlPosButton(true);
   if(this.OldPosition -1 > 0)
   {
    this.bm.Position = this.OldPosition -1;
   }
   else
   {
    this.bm.Position = this.OldPosition;
   }
   this.PosRefreshButton();
   if(this.textBox1.Text =="")
   {
    this.button6.Enabled = false;
    this.button7.Enabled = false;
   }
  }

  private void button9_Click(object sender, System.EventArgs e)
  {
   this.bm.EndCurrentEdit();
   this.OldPosition = this.bm.Position;
   this.UpdateDB(this.TotalData);
   this.OpRefreshText("OK");
   this.OpRefreshBtn("OK");
   this.RefreshDataSource();
   this.CtlPosButton(true);
   this.bm.Position = this.OldPosition;
   this.PosRefreshButton();
  }

  private void DiscardDataGrid(DataSet TotalDS)
  {   
   foreach (DataTable dt in TotalDS.Tables)
   {
    DataRow[] drs = dt.Select("SupplierID = '" + this.textBox1.Text +"'");
    if(drs.Length > 0)
    {
     foreach(DataRow dr in drs)
     {
      dr["status"] = "Discard";
     }
    }
   }
  }

  private void button8_Click(object sender, System.EventArgs e)
  {
//   if ((MessageBox.Show("Del the record","Longrist_IRS",MessageBoxButtons.YesNo,MessageBoxIcon.Question,MessageBoxDefaultButton.Button1) ==DialogResult.Yes))
//   {
//    this.bm.RemoveAt(this.bm.Position);
//    this.DiscardDataGrid(this.TotalData);
//   }
  }

  private void CtlPosButton(bool Enable)
  {
   if(Enable)
   {
    this.button1.Enabled = true;
    this.button2.Enabled = true;
    this.button3.Enabled = true;
    this.button4.Enabled = true;
   }
   else
   {
    this.button1.Enabled = false;
    this.button2.Enabled = false;
    this.button3.Enabled = false;
    this.button4.Enabled = false;
   }
  }
 }
}