通過程式得到資料庫表之間的關聯關係

來源:互聯網
上載者:User
程式|過程|資料|資料庫 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;
}




}
}




相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。