C# Ado.net实现读取SQLServer数据库存储过程列表及参数信息示例

本文实例讲述了c# ado.net读取sqlserver数据库存储过程列表及参数信息的方法。分享给大家供大家参考，具体如下：

得到数据库存储过程列表：

select * from dbo.sysobjects where objectproperty(id, n'isprocedure') = 1 order by name

得到某个存储过程的参数信息：(sql方法)

select * from syscolumns where id in
 (select id from sysobjects as a
  where objectproperty(id, n'isprocedure') = 1
  and id = object_id(n'[dbo].[mystoredprocedurename]'))

得到某个存储过程的参数信息：(ado.net方法)

sqlcommandbuilder.deriveparameters(mysqlcommand);

得到数据库所有表：

select * from dbo.sysobjects where objectproperty(id, n'isusertable') = 1 order by name

得到某个表中的字段信息：

select c.name as columnname, c.colorder as columnorder, c.xtype as datatype, typ.name as datatypename, c.length, c.isnullable from dbo.syscolumns c inner join dbo.sysobjects t
on c.id = t.id
inner join dbo.systypes typ on typ.xtype = c.xtype
where objectproperty(t.id, n'isusertable') = 1
and t.name='mytable' order by c.colorder;

c# ado.net代码示例：

1. 得到数据库存储过程列表：

using system.data.sqlclient;
private void getstoredprocedureslist()
{
  string sql = "select * from dbo.sysobjects where objectproperty(id, n'isprocedure') = 1 order by name";
  string connstr = @"data source=(local);initial catalog=mydatabase; integrated security=true; connection timeout=1;";
  sqlconnection conn = new sqlconnection(connstr);
  sqlcommand cmd = new sqlcommand(sql, conn);
  cmd.commandtype = commandtype.text;
  try
  {
    conn.open();
    using (sqldatareader myreader = cmd.executereader())
    {
      while (myreader.read())
      {
        //get stored procedure name
        this.listbox1.items.add(myreader[0].tostring());
      }
    }
  }
  finally
  {
    conn.close();
  }
}

2. 得到某个存储过程的参数信息：(ado.net方法)

using system.data.sqlclient;
private void getarguments()
{
  string connstr = @"data source=(local);initial catalog=mydatabase; integrated security=true; connection timeout=1;";
  sqlconnection conn = new sqlconnection(connstr);
  sqlcommand cmd = new sqlcommand();
  cmd.connection = conn;
  cmd.commandtext = "mystoredprocedurename";
  cmd.commandtype = commandtype.storedprocedure;
  try
  {
    conn.open();
    sqlcommandbuilder.deriveparameters(cmd);
    foreach (sqlparameter var in cmd.parameters)
    {
      if (cmd.parameters.indexof(var) == 0) continue;//skip return value
      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. 列出所有数据库：

using system;
using system.windows.forms;
using system.collections.generic;
using system.text;
using system.data;
using system.data.sqlclient;
private static string connstring =
      "persist security info=true;timeout=5;data source=192.168.1.8;user id=sa;password=password";
/// <summary>
/// 列出所有数据库
/// </summary>
/// <returns></returns>
public string[] getdatabases()
{
  return getlist("select name from sysdatabases order by name asc");
}
private string[] getlist(string sql)
{
  if (string.isnullorempty(connstring)) return null;
  string connstr = connstring;
  sqlconnection conn = new sqlconnection(connstr);
  sqlcommand cmd = new sqlcommand(sql, conn);
  cmd.commandtype = commandtype.text;
  try
  {
    conn.open();
    list<string> ret = new list<string>();
    using (sqldatareader myreader = cmd.executereader())
    {
      while (myreader.read())
      {
        ret.add(myreader[0].tostring());
      }
    }
    if (ret.count > 0) return ret.toarray();
    return null;
  }
  finally
  {
    conn.close();
  }
}

4. 得到table表格列表：

private static string connstring =
 "persist security info=true;timeout=5;data source=192.168.1.8;initial catalog=mydb;user id=sa;password=password";
/* select name from sysobjects where xtype='u' ---
c = check 约束
d = 默认值或 default 约束
f = foreign key 约束
l = 日志
fn = 标量函数
if = 内嵌表函数
p = 存储过程
pk = primary key 约束（类型是 k）
rf = 复制筛选存储过程
s = 系统表
tf = 表函数
tr = 触发器
u = 用户表
uq = unique 约束（类型是 k）
v = 视图
x = 扩展存储过程
*/
public string[] gettablelist()
{
  return getlist("select name from sysobjects where xtype='u' and name  <>  'dtproperties' order by name asc");
}

5. 得到view视图列表：

public string[] getviewlist()
{
   return getlist("select name from sysobjects where xtype='v' and name  <>  'dtproperties' order by name asc");
}

6. 得到function函数列表：

public string[] getfunctionlist()
{
  return getlist("select name from sysobjects where xtype='fn' and name  <>  'dtproperties' order by name asc");
}

7. 得到存储过程列表：

public string[] getstoredprocedureslist()
{
  return getlist("select * from dbo.sysobjects where objectproperty(id, n'isprocedure') = 1 order by name asc");
}

8. 得到table的索引index信息：

public treenode[] gettableindex(string tablename)
{
  if (string.isnullorempty(connstring)) return null;
  list<treenode> nodes = new list<treenode>();
  string connstr = connstring;
  sqlconnection conn = new sqlconnection(connstr);
  sqlcommand cmd = new sqlcommand(string.format("exec sp_helpindex {0}", tablename), conn);
  cmd.commandtype = commandtype.text;
  try
  {
    conn.open();
    using (sqldatareader myreader = cmd.executereader())
    {
      while (myreader.read())
      {
        treenode node = new treenode(myreader[0].tostring(), 2, 2);/*index name*/
        node.tooltiptext = string.format("{0}{1}{2}", myreader[2].tostring()/*index keys*/, environment.newline,
          myreader[1].tostring()/*description*/);
        nodes.add(node);
      }
    }
  }
  finally
  {
    conn.close();
  }
  if(nodes.count>0) return nodes.toarray ();
  return null;
}

9. 得到table，view，function，存储过程的参数，field信息：

public string[] gettablefields(string tablename)
{
  return getlist(string.format("select name from syscolumns where id =object_id('{0}')", tablename));
}

10. 得到table各个field的详细定义：

public treenode[] gettablefieldsdefinition(string tablename)
{
  if (string.isnullorempty(connstring)) return null;
  string connstr = connstring;
  list<treenode> nodes = new list<treenode>();
  sqlconnection conn = new sqlconnection(connstr);
  sqlcommand cmd = new sqlcommand(string.format("select a.name,b.name,a.length,a.isnullable from syscolumns a,systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.xtype='u' and a.id =object_id('{0}')",
         tablename), conn);
  cmd.commandtype = commandtype.text;
  try
  {
    conn.open();
    using (sqldatareader myreader = cmd.executereader())
    {
      while (myreader.read())
      {
        treenode node = new treenode(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) return nodes.toarray();
    return null;
  }
  finally
  {
    conn.close();
  }
}

11. 得到存储过程内容：

类似“8. 得到table的索引index信息”，sql语句为：exec sp_helptext '存储过程名'

12. 得到视图view定义：

类似“8. 得到table的索引index信息”，sql语句为：exec sp_helptext '视图名'

(以上代码可用于代码生成器，列出数据库的所有信息)

更多关于c#相关内容感兴趣的读者可查看本站专题：《c#常见数据库操作技巧汇总》、《c#常见控件用法教程》、《c#窗体操作技巧汇总》、《c#数据结构与算法教程》、《c#面向对象程序设计入门教程》及《c#程序设计之线程使用技巧总结》

希望本文所述对大家c#程序设计有所帮助。