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