好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

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#程序设计有所帮助。

原文链接:http://www.cnblogs.com/luluping/archive/2009/07/24/1530528.html

dy("nrwz");

查看更多关于C# Ado.net实现读取SQLServer数据库存储过程列表及参数信息示例的详细内容...

  阅读:41次