本文实例讲述了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数据库存储过程列表及参数信息示例的详细内容...