The association relationship between database tables is obtained by program

Source: Internet
Author: User
Tags foreach bool empty table name tostring
Program | process | data | database using System;
Using System.Drawing;
Using System.Collections;
Using System.ComponentModel;
Using System.Windows.Forms;
Using System.Data;
Using System.Data. SqlClient;

Namespace database Management Automation
{
<summary>
Summary description of the 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>
The required designer variable.
</summary>
Private System.ComponentModel.Container components = null;

Public Form1 ()
{
//
Required for Windows Forms Designer support
//
InitializeComponent ();

//
TODO: Add any constructor code after the InitializeComponent call
//
}

<summary>
Clean up all resources that are in use.
</summary>
protected override void Dispose (bool disposing)
{
if (disposing)
{
if (Components!= null)
{
Components. Dispose ();
}
}
Base. Dispose (disposing);
}

Code generated #region the Windows forms Designer
<summary>
Designer supports required methods-do not use the Code editor to modify
The contents of this method.
</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 = "column name";
//
ColumnHeader2
//
This.columnHeader2.Text = "Data type";
This.columnHeader2.Width = 48;
//
ColumnHeader3
//
This.columnHeader3.Text = "column width";
This.columnHeader3.Width = 36;
//
ColumnHeader6
//
This.columnHeader6.Text = "ColID";
//
ColumnHeader4
//
This.columnHeader4.Text = "can be empty";
//
ColumnHeader5
//
This.columnHeader5.Text = "Whether primary key";
//
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 = "Query";
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 = "Association table (as parent table)";
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 ;p ers "+
"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 = "column name";
//
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 = "table name";
//
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 = "View the corresponding field";
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>
The main entry point for the application.
</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 ();//Column name
String coltype=dr["type"]. ToString ();//sqltype
int Len=convert.toint32 (dr["Length"). ToString ());//Column width
BOOL bnull=dr["IsNullable"]. ToString () = = "1"? true:false;//can be empty
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;
}




}
}




Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.