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

2020-01-24 00:10:23

字体：大中小

来源：转载

供稿：网友

本文实例讲述了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 ton c.id = t.idinner join dbo.systypes typ on typ.xtype = c.xtypewhere OBJECTPROPERTY(t.id, N'IsUserTable') = 1and 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#程序设计有所帮助。

上一篇：C#学习教程之Socket的简单使用

下一篇：C#实现推送钉钉消息的方法示例