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 [email protected] 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 [email protected] 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 [email protected] 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 [email protected]) and [email protected]";
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;
}
}
}
