通过程序得到数据库表之间的关联关系

类别:.NET开发 点击:0 评论:0 推荐:

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

namespace 数据库管理自动化
{
 /// <summary>
 /// Form1 的摘要说明。
 /// </summary>
 public class Form1 : System.Windows.Forms.Form
 {
  private System.Windows.Forms.ComboBox tabList;
  private System.Windows.Forms.ListView tabPty;
  private System.Windows.Forms.ColumnHeader columnHeader1;
  private System.Windows.Forms.ColumnHeader columnHeader2;
  private System.Windows.Forms.ColumnHeader columnHeader3;
  private System.Windows.Forms.ColumnHeader columnHeader4;
  private System.Windows.Forms.ColumnHeader columnHeader5;
  private System.Windows.Forms.ColumnHeader columnHeader6;
  private System.Windows.Forms.Panel panel1;
  private System.Windows.Forms.TextBox idstr;
  private System.Windows.Forms.Button qry;
  private System.Windows.Forms.Button button1;
  private System.Data.SqlClient.SqlConnection sqlConnection1;
  private System.Windows.Forms.ListView listView1;
  private System.Windows.Forms.ColumnHeader columnHeader7;
  private System.Windows.Forms.ColumnHeader columnHeader8;
  private System.Windows.Forms.ColumnHeader ColID;
  private System.Windows.Forms.ListView listView2;
  private System.Windows.Forms.ColumnHeader columnHeader9;
  private System.Windows.Forms.ColumnHeader columnHeader10;
  private System.Windows.Forms.ColumnHeader columnHeader11;
  private System.Windows.Forms.Button button2;
  /// <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 窗体设计器生成的代码
  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.tabList = new System.Windows.Forms.ComboBox();
   this.tabPty = new System.Windows.Forms.ListView();
   this.columnHeader1 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader2 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader3 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader6 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader4 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader5 = new System.Windows.Forms.ColumnHeader();
   this.panel1 = new System.Windows.Forms.Panel();
   this.idstr = new System.Windows.Forms.TextBox();
   this.qry = new System.Windows.Forms.Button();
   this.button1 = new System.Windows.Forms.Button();
   this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
   this.listView1 = new System.Windows.Forms.ListView();
   this.columnHeader7 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader8 = new System.Windows.Forms.ColumnHeader();
   this.ColID = new System.Windows.Forms.ColumnHeader();
   this.listView2 = new System.Windows.Forms.ListView();
   this.columnHeader9 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader10 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader11 = new System.Windows.Forms.ColumnHeader();
   this.button2 = new System.Windows.Forms.Button();
   this.panel1.SuspendLayout();
   this.SuspendLayout();
   //
   // tabList
   //
   this.tabList.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
    | System.Windows.Forms.AnchorStyles.Right)));
   this.tabList.Location = new System.Drawing.Point(8, 8);
   this.tabList.Name = "tabList";
   this.tabList.Size = new System.Drawing.Size(512, 20);
   this.tabList.TabIndex = 0;
   this.tabList.SelectedIndexChanged += new System.EventHandler(this.tabList_SelectedValueChanged);
   //
   // tabPty
   //
   this.tabPty.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
    | System.Windows.Forms.AnchorStyles.Left)
    | System.Windows.Forms.AnchorStyles.Right)));
   this.tabPty.Columns.AddRange(new System.Windows.Forms.ColumnHeader[] {
                      this.columnHeader1,
                      this.columnHeader2,
                      this.columnHeader3,
                      this.columnHeader6,
                      this.columnHeader4,
                      this.columnHeader5});
   this.tabPty.GridLines = true;
   this.tabPty.Location = new System.Drawing.Point(8, 32);
   this.tabPty.Name = "tabPty";
   this.tabPty.Size = new System.Drawing.Size(176, 208);
   this.tabPty.TabIndex = 1;
   this.tabPty.View = System.Windows.Forms.View.Details;
   //
   // columnHeader1
   //
   this.columnHeader1.Text = "列名";
   //
   // columnHeader2
   //
   this.columnHeader2.Text = "数据类型";
   this.columnHeader2.Width = 48;
   //
   // columnHeader3
   //
   this.columnHeader3.Text = "列宽";
   this.columnHeader3.Width = 36;
   //
   // columnHeader6
   //
   this.columnHeader6.Text = "ColID";
   //
   // columnHeader4
   //
   this.columnHeader4.Text = "可以为空";
   //
   // columnHeader5
   //
   this.columnHeader5.Text = "是否主键";
   //
   // panel1
   //
   this.panel1.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)
    | System.Windows.Forms.AnchorStyles.Right)));
   this.panel1.Controls.Add(this.button2);
   this.panel1.Controls.Add(this.button1);
   this.panel1.Controls.Add(this.qry);
   this.panel1.Controls.Add(this.idstr);
   this.panel1.Location = new System.Drawing.Point(8, 248);
   this.panel1.Name = "panel1";
   this.panel1.Size = new System.Drawing.Size(512, 80);
   this.panel1.TabIndex = 2;
   //
   // idstr
   //
   this.idstr.Location = new System.Drawing.Point(16, 8);
   this.idstr.Name = "idstr";
   this.idstr.ReadOnly = true;
   this.idstr.Size = new System.Drawing.Size(128, 21);
   this.idstr.TabIndex = 0;
   this.idstr.Text = "textBox1";
   //
   // qry
   //
   this.qry.Location = new System.Drawing.Point(152, 8);
   this.qry.Name = "qry";
   this.qry.Size = new System.Drawing.Size(72, 24);
   this.qry.TabIndex = 1;
   this.qry.Text = "查询";
   this.qry.Click += new System.EventHandler(this.qry_Click);
   //
   // button1
   //
   this.button1.Location = new System.Drawing.Point(16, 48);
   this.button1.Name = "button1";
   this.button1.Size = new System.Drawing.Size(208, 24);
   this.button1.TabIndex = 2;
   this.button1.Text = "关联表(作为父表)";
   this.button1.Click += new System.EventHandler(this.button1_Click);
   //
   // sqlConnection1
   //
   this.sqlConnection1.ConnectionString = "workstation id=DHZ;packet size=4096;integrated security=SSPI;data source=DHZ;pers" +
    "ist security info=False;initial catalog=SXQGZA";
   //
   // listView1
   //
   this.listView1.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
   this.listView1.Columns.AddRange(new System.Windows.Forms.ColumnHeader[] {
                      this.columnHeader7,
                      this.columnHeader8,
                      this.ColID});
   this.listView1.Location = new System.Drawing.Point(208, 32);
   this.listView1.Name = "listView1";
   this.listView1.Size = new System.Drawing.Size(312, 184);
   this.listView1.TabIndex = 3;
   this.listView1.View = System.Windows.Forms.View.Details;
   //
   // columnHeader7
   //
   this.columnHeader7.Text = "ColID";
   //
   // columnHeader8
   //
   this.columnHeader8.Text = "列名";
   //
   // ColID
   //
   this.ColID.Text = "ColID2";
   //
   // listView2
   //
   this.listView2.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
    | System.Windows.Forms.AnchorStyles.Right)));
   this.listView2.Columns.AddRange(new System.Windows.Forms.ColumnHeader[] {
                      this.columnHeader9,
                      this.columnHeader10,
                      this.columnHeader11});
   this.listView2.Location = new System.Drawing.Point(208, 216);
   this.listView2.Name = "listView2";
   this.listView2.Size = new System.Drawing.Size(312, 24);
   this.listView2.TabIndex = 4;
   this.listView2.View = System.Windows.Forms.View.Details;
   //
   // columnHeader9
   //
   this.columnHeader9.Text = "ColID";
   //
   // columnHeader10
   //
   this.columnHeader10.Text = "表名";
   //
   // columnHeader11
   //
   this.columnHeader11.Text = "ColID2";
   //
   // button2
   //
   this.button2.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
    | System.Windows.Forms.AnchorStyles.Left)
    | System.Windows.Forms.AnchorStyles.Right)));
   this.button2.Location = new System.Drawing.Point(264, 8);
   this.button2.Name = "button2";
   this.button2.Size = new System.Drawing.Size(240, 64);
   this.button2.TabIndex = 3;
   this.button2.Text = "查看对应的字段";
   this.button2.Click += new System.EventHandler(this.button2_Click);
   //
   // Form1
   //
   this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
   this.ClientSize = new System.Drawing.Size(528, 333);
   this.Controls.Add(this.listView1);
   this.Controls.Add(this.panel1);
   this.Controls.Add(this.tabPty);
   this.Controls.Add(this.tabList);
   this.Controls.Add(this.listView2);
   this.Name = "Form1";
   this.Text = "Form1";
   this.Load += new System.EventHandler(this.Form1_Load);
   this.panel1.ResumeLayout(false);
   this.ResumeLayout(false);

  }
  #endregion

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

  private void Form1_Load(object sender, System.EventArgs e)
  {
   SqlCommand cmd=new  SqlCommand("select id,name from sysobjects where xtype='u'",this.sqlConnection1 );
  
   SqlDataAdapter da=new SqlDataAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet("Mgmt");
   da.Fill(ds,"TabID");
   this.tabList.DataSource=ds;
   this.tabList.ValueMember="TabID.id";
   this.tabList.DisplayMember="TabID.name";
   
  }

  private void tabList_SelectedValueChanged(object sender, System.EventArgs e)
  {
   this.idstr.Text=this.tabList.SelectedValue.ToString ();
   
  

  }

  private void qry_Click(object sender, System.EventArgs e)
  {
   string spc=this.idstr.Text ;
   string cmdstr="select a.name ,b.name as type ,a.length,a.isnullable,a.colid,a.status";
   cmdstr+="  from syscolumns a ,systypes b ";
   cmdstr+="  where a.id=@id  and b.xusertype=a.xtype";
   SqlCommand cmd=new SqlCommand(cmdstr,this.sqlConnection1 );
   cmd.Parameters.Add("@id",spc);
   this.tabPty.Items.Clear ();

   this.sqlConnection1.Close ();
   this.sqlConnection1.Open ();
   SqlDataReader dr=cmd.ExecuteReader();
   while(dr.Read ())
   {
    string colna=dr["name"].ToString ();//列名
    string colType=dr["type"].ToString ();//sqltype
    int    len=Convert.ToInt32(dr["length"].ToString ());//列宽
    bool   bNull=dr["isnullable"].ToString ()=="1"?true:false;//可以为空
    int    colid=Convert.ToInt32(dr["colid"].ToString ());
    bool   bPk=dr["status"].ToString ()=="128"?true:false;
    string[] lVi=new string[]{
            colna,colType,len.ToString (),colid.ToString (),bNull.ToString (),bPk.ToString ()
           };
    this.tabPty.Items.Add(new ListViewItem(lVi));
   }
   dr.Close ();
   this.sqlConnection1.Close ();
  
  }

  private void button1_Click(object sender, System.EventArgs e)
  {
   this.listView1.Items.Clear ();
   this.listView2 .Items.Clear ();
   string str="select r.fkey1 as TabColID,o.name as refTabName ,r.rkeyindid  as refTabColID";
   str+=" from sysreferences r,sysobjects o";
            str+=" where rkeyid=@id and o.id=r.fkeyid ";
   SqlCommand cmd=new SqlCommand();
   cmd.Connection=this.sqlConnection1 ;
   cmd.CommandText=str;
   cmd.Parameters.Add("@id",this.idstr.Text );
   cmd.Connection.Open ();
   SqlDataReader dr=cmd.ExecuteReader ();
   while(dr.Read ())
   {
    string c1=dr[0].ToString ();
    string c2=dr[1].ToString ();
    string c3=dr[2].ToString ();
    ListViewItem lvi=new ListViewItem(new string[]{c1,c2,c3});
    this.listView1.Items.Add(lvi);
   }
   dr.Close ();
   cmd.Connection.Close ();
            str=" select r.fkey1 as TobColID,o.name as refTabName,r.rkeyindid as refTabColID ";
            str+=" from sysreferences r,sysobjects o";
            str+=" where fkeyid=@id and o.id=r.rkeyid ";
   cmd.CommandText=str;
   cmd.Connection.Open ();
   dr=cmd.ExecuteReader ();
   while(dr.Read ())
   {
    string c1=dr[0].ToString ();
    string c2=dr[1].ToString ();
    string c3=dr[2].ToString ();
    ListViewItem lvi=new ListViewItem(new string[]{c1,c2,c3});
    this.listView2.Items.Add(lvi);
   }
   dr.Close ();
   cmd.Connection.Close ();
 
  
  }

  private void button2_Click(object sender, System.EventArgs e)
  {
   int kc=0;
   if(this.listView1 .Items.Count >0)
   {
    kc=this.listView1.Items.Count ;
    for(int i=0;i<kc;i++)
    {
     ListViewItem lvi=this.listView1.Items[i];
     string colid=lvi.SubItems[0].Text ;
     string tabName=lvi.SubItems[1].Text ;
     string  colid2=lvi.SubItems[2].Text ;
     lvi.SubItems[0].Text =this.getColName(tabName,colid);
     foreach(ListViewItem tc in this.tabPty.Items )
     {
      if(tc.SubItems[3].Text ==colid2)
      {
       lvi.SubItems[2].Text=tc.SubItems[0].Text ;
      }
     }

    }
   }
   if(this.listView2 .Items.Count >0)
   {
    kc=this.listView2.Items.Count ;
    for(int i=0;i<kc;i++)
    {
     ListViewItem lvi=this.listView2.Items[i];
     string colid=lvi.SubItems[0].Text ;
     string tabName=lvi.SubItems[1].Text ;
     string  colid2=lvi.SubItems[2].Text ;
     lvi.SubItems[2].Text =this.getColName(tabName,colid2);
     foreach(ListViewItem tc in this.tabPty.Items )
     {
      if(tc.SubItems[3].Text ==colid)
      {
       lvi.SubItems[0].Text=tc.SubItems[0].Text ;
      }
     }

    }
   }
  }
  private string  getColName(string tabName,string colid)
  {
   string cmdstr="select  name from syscolumns ";
   cmdstr+="where id=(select id from sysobjects where xtype='u' and name=@tab) and colid=@colid";
   SqlCommand cmd=new SqlCommand(cmdstr,this.sqlConnection1 );
   cmd.Parameters.Add("@tab",tabName);
   cmd.Parameters.Add("@colid",colid);
   cmdstr="";
   cmd.Connection.Close ();
   cmd.Connection.Open ();
   SqlDataReader dr=cmd.ExecuteReader ();
   string str="";
   if(dr.Read ())
   {
     str=dr[0].ToString ();
   }
   dr.Close ();
   cmd.Connection.Close ();
   return str;
  }

 
 
 
 }
}

本文地址:http://com.8s8s.com/it/it43203.htm