本文實例講述了C# Ado.net讀取SQLServer數據庫存儲過程列表及參數信息的方法。分享給大家供大家參考，具體如下：
得到數據庫存儲過程列表：
|
1
|
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name |
得到某個存儲過程的參數信息：(SQL方法)
|
1
2
3
4
|
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方法)
|
1
|
SqlCommandBuilder.DeriveParameters(mysqlcommand); |
得到數據庫所有表：
|
1
|
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name |
得到某個表中的字段信息：
|
1
2
3
4
5
|
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. 得到數據庫存儲過程列表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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方法)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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. 列出所有數據庫：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
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表格列表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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視圖列表：
|
1
2
3
4
|
public string[] GetViewList(){ return GetList("SELECT name FROM sysobjects WHERE xtype='V' AND name <> 'dtproperties' order by name asc");} |
6. 得到Function函數列表：
|
1
2
3
4
|
public string[] GetFunctionList(){ return GetList("SELECT name FROM sysobjects WHERE xtype='FN' AND name <> 'dtproperties' order by name asc");} |
7. 得到存儲過程列表：
|
1
2
3
4
|
public string[] GetStoredProceduresList(){ return GetList("select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name asc");} |
8. 得到table的索引Index信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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信息：
|
1
2
3
4
|
public string[] GetTableFields(string tableName){ return GetList(String.Format("select name from syscolumns where id =object_id('{0}')", tableName));} |
10. 得到Table各個Field的詳細定義：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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
