C# Ado.net实现读取SQLServer数据库存储过程列表及参数信息示例
本文实例讲述了C#Ado.net读取SQLServer数据库存储过程列表及参数信息的方法。分享给大家供大家参考,具体如下:
得到数据库存储过程列表:
select*fromdbo.sysobjectswhereOBJECTPROPERTY(id,N'IsProcedure')=1orderbyname
得到某个存储过程的参数信息:(SQL方法)
select*fromsyscolumnswhereIDin (SELECTidFROMsysobjectsasa WHEREOBJECTPROPERTY(id,N'IsProcedure')=1 andid=object_id(N'[dbo].[mystoredprocedurename]'))
得到某个存储过程的参数信息:(Ado.net方法)
SqlCommandBuilder.DeriveParameters(mysqlcommand);
得到数据库所有表:
select*fromdbo.sysobjectswhereOBJECTPROPERTY(id,N'IsUserTable')=1orderbyname
得到某个表中的字段信息:
selectc.nameasColumnName,c.colorderasColumnOrder,c.xtypeasDataType,typ.nameasDataTypeName,c.Length,c.isnullablefromdbo.syscolumnscinnerjoindbo.sysobjectst onc.id=t.id innerjoindbo.systypestypontyp.xtype=c.xtype whereOBJECTPROPERTY(t.id,N'IsUserTable')=1 andt.name='mytable'orderbyc.colorder;
C#Ado.net代码示例:
1.得到数据库存储过程列表:
usingSystem.Data.SqlClient; privatevoidGetStoredProceduresList() { stringsql="select*fromdbo.sysobjectswhereOBJECTPROPERTY(id,N'IsProcedure')=1orderbyname"; stringconnStr=@"DataSource=(local);InitialCatalog=mydatabase;IntegratedSecurity=True;ConnectionTimeout=1;"; SqlConnectionconn=newSqlConnection(connStr); SqlCommandcmd=newSqlCommand(sql,conn); cmd.CommandType=CommandType.Text; try { conn.Open(); using(SqlDataReaderMyReader=cmd.ExecuteReader()) { while(MyReader.Read()) { //Getstoredprocedurename this.listBox1.Items.Add(MyReader[0].ToString()); } } } finally { conn.Close(); } }
2.得到某个存储过程的参数信息:(Ado.net方法)
usingSystem.Data.SqlClient; privatevoidGetArguments() { stringconnStr=@"DataSource=(local);InitialCatalog=mydatabase;IntegratedSecurity=True;ConnectionTimeout=1;"; SqlConnectionconn=newSqlConnection(connStr); SqlCommandcmd=newSqlCommand(); cmd.Connection=conn; cmd.CommandText="mystoredprocedurename"; cmd.CommandType=CommandType.StoredProcedure; try { conn.Open(); SqlCommandBuilder.DeriveParameters(cmd); foreach(SqlParametervarincmd.Parameters) { if(cmd.Parameters.IndexOf(var)==0)continue;//Skipreturnvalue MessageBox.Show((String.Format("Param:{0}{1}Type:{2}{1}Direction:{3}", var.ParameterName, Environment.NewLine, var.SqlDbType.ToString(), var.Direction.ToString()))); } } finally { conn.Close(); } }
3.列出所有数据库:
usingSystem; usingSystem.Windows.Forms; usingSystem.Collections.Generic; usingSystem.Text; usingSystem.Data; usingSystem.Data.SqlClient; privatestaticstringconnString= "PersistSecurityInfo=True;timeout=5;DataSource=192.168.1.8;UserID=sa;Password=password"; //////列出所有数据库 /// ///publicstring[]GetDatabases() { returnGetList("SELECTnameFROMsysdatabasesorderbynameasc"); } privatestring[]GetList(stringsql) { if(String.IsNullOrEmpty(connString))returnnull; stringconnStr=connString; SqlConnectionconn=newSqlConnection(connStr); SqlCommandcmd=newSqlCommand(sql,conn); cmd.CommandType=CommandType.Text; try { conn.Open(); List ret=newList (); using(SqlDataReaderMyReader=cmd.ExecuteReader()) { while(MyReader.Read()) { ret.Add(MyReader[0].ToString()); } } if(ret.Count>0)returnret.ToArray(); returnnull; } finally { conn.Close(); } }
4.得到Table表格列表:
privatestaticstringconnString= "PersistSecurityInfo=True;timeout=5;DataSource=192.168.1.8;InitialCatalog=myDb;UserID=sa;Password=password"; /*selectnamefromsysobjectswherextype='u'--- C=CHECK约束 D=默认值或DEFAULT约束 F=FOREIGNKEY约束 L=日志 FN=标量函数 IF=内嵌表函数 P=存储过程 PK=PRIMARYKEY约束(类型是K) RF=复制筛选存储过程 S=系统表 TF=表函数 TR=触发器 U=用户表 UQ=UNIQUE约束(类型是K) V=视图 X=扩展存储过程 */ publicstring[]GetTableList() { returnGetList("SELECTnameFROMsysobjectsWHERExtype='U'ANDname<>'dtproperties'orderbynameasc"); }
5.得到View视图列表:
publicstring[]GetViewList() { returnGetList("SELECTnameFROMsysobjectsWHERExtype='V'ANDname<>'dtproperties'orderbynameasc"); }
6.得到Function函数列表:
publicstring[]GetFunctionList() { returnGetList("SELECTnameFROMsysobjectsWHERExtype='FN'ANDname<>'dtproperties'orderbynameasc"); }
7.得到存储过程列表:
publicstring[]GetStoredProceduresList() { returnGetList("select*fromdbo.sysobjectswhereOBJECTPROPERTY(id,N'IsProcedure')=1orderbynameasc"); }
8.得到table的索引Index信息:
publicTreeNode[]GetTableIndex(stringtableName) { if(String.IsNullOrEmpty(connString))returnnull; Listnodes=newList (); stringconnStr=connString; SqlConnectionconn=newSqlConnection(connStr); SqlCommandcmd=newSqlCommand(String.Format("execsp_helpindex{0}",tableName),conn); cmd.CommandType=CommandType.Text; try { conn.Open(); using(SqlDataReaderMyReader=cmd.ExecuteReader()) { while(MyReader.Read()) { TreeNodenode=newTreeNode(MyReader[0].ToString(),2,2);/*Indexname*/ node.ToolTipText=String.Format("{0}{1}{2}",MyReader[2].ToString()/*indexkeys*/,Environment.NewLine, MyReader[1].ToString()/*Description*/); nodes.Add(node); } } } finally { conn.Close(); } if(nodes.Count>0)returnnodes.ToArray(); returnnull; }
9.得到Table,View,Function,存储过程的参数,Field信息:
publicstring[]GetTableFields(stringtableName) { returnGetList(String.Format("selectnamefromsyscolumnswhereid=object_id('{0}')",tableName)); }
10.得到Table各个Field的详细定义:
publicTreeNode[]GetTableFieldsDefinition(stringTableName) { if(String.IsNullOrEmpty(connString))returnnull; stringconnStr=connString; Listnodes=newList (); SqlConnectionconn=newSqlConnection(connStr); SqlCommandcmd=newSqlCommand(String.Format("selecta.name,b.name,a.length,a.isnullablefromsyscolumnsa,systypesb,sysobjectsdwherea.xtype=b.xusertypeanda.id=d.idandd.xtype='U'anda.id=object_id('{0}')", TableName),conn); cmd.CommandType=CommandType.Text; try { conn.Open(); using(SqlDataReaderMyReader=cmd.ExecuteReader()) { while(MyReader.Read()) { TreeNodenode=newTreeNode(MyReader[0].ToString(),2,2); node.ToolTipText=String.Format("Type:{0}{1}Length:{2}{1}Nullable:{3}",MyReader[1].ToString()/*type*/,Environment.NewLine, MyReader[2].ToString()/*length*/,Convert.ToBoolean(MyReader[3])); nodes.Add(node); } } if(nodes.Count>0)returnnodes.ToArray(); returnnull; } finally { conn.Close(); } }
11.得到存储过程内容:
类似“8.得到table的索引Index信息”,SQL语句为:EXECSp_HelpText'存储过程名'
12.得到视图View定义:
类似“8.得到table的索引Index信息”,SQL语句为:EXECSp_HelpText'视图名'
(以上代码可用于代码生成器,列出数据库的所有信息)
更多关于C#相关内容感兴趣的读者可查看本站专题:《C#常见数据库操作技巧汇总》、《C#常见控件用法教程》、《C#窗体操作技巧汇总》、《C#数据结构与算法教程》、《C#面向对象程序设计入门教程》及《C#程序设计之线程使用技巧总结》
希望本文所述对大家C#程序设计有所帮助。