C#讀取SqlServer預存程序及參數《轉》

讀取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();

    }

              

}