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

原创 2004年10月28日 17:12:00

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;
  }

 
 
 
 }
}

数据库设计(表与表之间的3种关系)

表与表之间一般存在三种关系,即一对一,一对多,多对多关系。 下面分别就三种关系讲解数据库相关设计的思路和思考过程;(1)一对一关系 例如,下面的一张表,保存了人的相关信息,有男有女,要求查处所有的...
  • lengjinghk
  • lengjinghk
  • 2016年08月07日 02:31
  • 46737

数据库中的数据表之间的关系

主键:能够唯一表示数据表中的每个记录的字段或者字段的组合就称为主键。一个主键是唯一识别一个表的每一行记录,但这只是其作用的一疗分,主键的主要作用是将记录和存放在其他表中的数据进行关联,在这一点上,主键...
  • normal_cai
  • normal_cai
  • 2012年12月14日 16:56
  • 10306

怎么去分析数据库表的模型(数据模型)

订单商品数据模型 数据模型分析思路1、 弄清每张表记录的数据内容 分模块对每张表记录的内容进行熟悉,相当 于你学习系统 需求(功能)的过程。 2、每张表重要的字段设置 非空字段...
  • jimgreatly
  • jimgreatly
  • 2017年05月01日 20:33
  • 363

powerdesiginer 删除表与表的之间关系线的描述字

tools->display preferences ->content->refernce->center->none
  • jackyxwr
  • jackyxwr
  • 2016年07月07日 10:32
  • 329

如何创建数据库表之间的关联关系

                                                    一对一关联   1.一对一的单向主键关联: 例如两张表:Husband和wife...
  • gpf1320253667
  • gpf1320253667
  • 2015年10月24日 16:04
  • 1054

数据库表与表之间的连接的几种方式

1.内连接    inner join 左右表中都出现 2.左连接   left join 左表为准 3.右连接   right  join 右表为准  4. 完全连接   linq的...
  • wang395442165
  • wang395442165
  • 2015年03月24日 15:05
  • 384

linux VFS 之六:进程与文件系统的关联

struct task_struct { 。。。。。。。。
  • Fybon
  • Fybon
  • 2014年04月23日 17:23
  • 1165

PowerDesigner ER图 实体关联关系

一,四种基本的联系       1.一对一(ONE TO ONE)联系、      2. 一对多(ONE TO MANY)联系、      3.多对一(MANY TO ONE)联系      ...
  • qq_27435059
  • qq_27435059
  • 2016年09月08日 16:53
  • 2334

谈谈类之间的关联关系与依赖关系

原文:http://www.cnblogs.com/iyangyuan/archive/2013/06/16/3138463.html   对于很多刚刚接触UML的童鞋,可能会对类之...
  • van38686061
  • van38686061
  • 2016年08月03日 10:28
  • 1814

数据库实体之间的关联关系:一对一、一对多、多对多

数据库实体间有三种关联关系:一对一,一对多,多对多。一对一实例:一个学生只有个身份证编号。 一对多实例:一个班级有多个学生。 多对多实例:多对多就是双向一对多,一个学生可以选择多门课,一门课也有多名学...
  • u012234452
  • u012234452
  • 2017年10月23日 23:30
  • 223
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:通过程序得到数据库表之间的关联关系
举报原因:
原因补充:

(最多只允许输入30个字)