C# Ado.net實(shí)現(xiàn)讀取SQLServer數(shù)據(jù)庫存儲(chǔ)過程列表及參數(shù)信息示例

更新時(shí)間：2019年02月12日 11:12:07 作者：linFen

這篇文章主要介紹了C# Ado.net實(shí)現(xiàn)讀取SQLServer數(shù)據(jù)庫存儲(chǔ)過程列表及參數(shù)信息,結(jié)合實(shí)例形式總結(jié)分析了C#針對(duì)SQLServer數(shù)據(jù)庫存儲(chǔ)過程及參數(shù)信息的各種常見操作技巧,需要的朋友可以參考下

本文實(shí)例講述了C# Ado.net讀取SQLServer數(shù)據(jù)庫存儲(chǔ)過程列表及參數(shù)信息的方法。分享給大家供大家參考，具體如下：

得到數(shù)據(jù)庫存儲(chǔ)過程列表：

select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name

得到某個(gè)存儲(chǔ)過程的參數(shù)信息：(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]'))

得到某個(gè)存儲(chǔ)過程的參數(shù)信息：(Ado.net方法)

SqlCommandBuilder.DeriveParameters(mysqlcommand);

得到數(shù)據(jù)庫所有表：

select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name

得到某個(gè)表中的字段信息：

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. 得到數(shù)據(jù)庫存儲(chǔ)過程列表：

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. 得到某個(gè)存儲(chǔ)過程的參數(shù)信息：(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. 列出所有數(shù)據(jù)庫：

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>
/// 列出所有數(shù)據(jù)庫
/// </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 = 默認(rèn)值或 DEFAULT 約束
F = FOREIGN KEY 約束
L = 日志
FN = 標(biāo)量函數(shù)
IF = 內(nèi)嵌表函數(shù)
P = 存儲(chǔ)過程
PK = PRIMARY KEY 約束（類型是 K）
RF = 復(fù)制篩選存儲(chǔ)過程
S = 系統(tǒng)表
TF = 表函數(shù)
TR = 觸發(fā)器
U = 用戶表
UQ = UNIQUE 約束（類型是 K）
V = 視圖
X = 擴(kuò)展存儲(chǔ)過程
*/
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函數(shù)列表：

public string[] GetFunctionList()
{
  return GetList("SELECT name FROM sysobjects WHERE xtype='FN' AND name  <>  'dtproperties' order by name asc");
}

7. 得到存儲(chǔ)過程列表：

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，F(xiàn)unction，存儲(chǔ)過程的參數(shù)，F(xiàn)ield信息：

public string[] GetTableFields(string tableName)
{
  return GetList(String.Format("select name from syscolumns where id =object_id('{0}')", tableName));
}

10. 得到Table各個(gè)Field的詳細(xì)定義：

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. 得到存儲(chǔ)過程內(nèi)容：

類似“8. 得到table的索引Index信息”，SQL語句為：EXEC Sp_HelpText '存儲(chǔ)過程名'

12. 得到視圖View定義：

類似“8. 得到table的索引Index信息”，SQL語句為：EXEC Sp_HelpText '視圖名'

(以上代碼可用于代碼生成器，列出數(shù)據(jù)庫的所有信息)

更多關(guān)于C#相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《C#常見數(shù)據(jù)庫操作技巧匯總》、《C#常見控件用法教程》、《C#窗體操作技巧匯總》、《C#數(shù)據(jù)結(jié)構(gòu)與算法教程》、《C#面向?qū)ο蟪绦蛟O(shè)計(jì)入門教程》及《C#程序設(shè)計(jì)之線程使用技巧總結(jié)》

希望本文所述對(duì)大家C#程序設(shè)計(jì)有所幫助。

您可能感興趣的文章: