連接字符串
    代碼如下:
    string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
    confige文件
    代碼如下:
    <connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>
    1. 只返回單一記錄集的存儲過程
    代碼如下:
    SqlConnection sqlconn = new SqlConnection(conn);
    SqlCommand cmd = new SqlCommand();
    // 設(shè)置sql連接
    cmd.Connection = sqlconn;
    // 如果執(zhí)行語句
    cmd.CommandText = "Categoriestest1";
    // 指定執(zhí)行語句為存儲過程
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter dp = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    // 填充dataset
    dp.Fill(ds);
    // 以下是顯示效果
    GridView1.DataSource = ds;
    GridView1.DataBind();
    存儲過程Categoriestest1
    代碼如下:
    CREATE PROCEDURE Categoriestest1
    AS
    select *
    from Categories
    GO
    2. 沒有輸入輸出的存儲過程
    c#代碼部分
    代碼如下:
    SqlConnection sqlconn = new SqlConnection(conn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = sqlconn;
    cmd.CommandText = "Categoriestest2";
    cmd.CommandType = CommandType.StoredProcedure;
    sqlconn.Open();
    // 執(zhí)行并顯示影響行數(shù)
    Label1.Text = cmd.ExecuteNonQuery().ToString();
    sqlconn.Close();
    存儲過程Categoriestest2
    復(fù)制代碼 代碼如下:
    CREATE PROCEDURE Categoriestest2 AS
    insert into dbo.Categories
    (CategoryName,[Description],[Picture])
    values ('test1','test1',null)
    GO
    3. 有返回值的存儲過程
    c#代碼部分
    代碼如下:
    SqlConnection sqlconn = new SqlConnection(conn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = sqlconn;
    cmd.CommandText = "Categoriestest3";
    cmd.CommandType = CommandType.StoredProcedure;
    // 創(chuàng)建參數(shù)
    IDataParameter[] parameters = {
    new SqlParameter("rval", SqlDbType.Int,4)
    };
    // 將參數(shù)類型設(shè)置為 返回值類型
    parameters[0].Direction = ParameterDirection.ReturnValue;
    // 添加參數(shù)
    cmd.Parameters.Add(parameters[0]);
    sqlconn.Open();
    // 執(zhí)行存儲過程并返回影響的行數(shù)
    Label1.Text = cmd.ExecuteNonQuery().ToString();
    sqlconn.Close();
    // 顯示影響的行數(shù)和返回值
    Label1.Text += "-" + parameters[0].Value.ToString() ;
    存儲過程Categoriestest3
    代碼如下:
    CREATE PROCEDURE Categoriestest3
    AS
    insert into dbo.Categories
    (CategoryName,[Description],[Picture])
    values ('test1','test1',null)
    return @@rowcount
    GO
    4. 有輸入?yún)?shù)和輸出參數(shù)的存儲過程
    c#代碼部分
    代碼如下:
    SqlConnection sqlconn = new SqlConnection(conn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = sqlconn;
    cmd.CommandText = "Categoriestest4";
    cmd.CommandType = CommandType.StoredProcedure;
    // 創(chuàng)建參數(shù)
    IDataParameter[] parameters = {
    new SqlParameter("@Id", SqlDbType.Int,4) ,
    new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
    };
    // 設(shè)置參數(shù)類型
    parameters[0].Direction = ParameterDirection.Output; // 設(shè)置為輸出參數(shù)
    parameters[1].Value = "testCategoryName";
    // 添加參數(shù)
    cmd.Parameters.Add(parameters[0]);
    cmd.Parameters.Add(parameters[1]);
    sqlconn.Open();
    // 執(zhí)行存儲過程并返回影響的行數(shù)
    Label1.Text = cmd.ExecuteNonQuery().ToString();
    sqlconn.Close();
    // 顯示影響的行數(shù)和輸出參數(shù)
    Label1.Text += "-" + parameters[0].Value.ToString() ;
    存儲過程Categoriestest4
    代碼如下:
    CREATE PROCEDURE Categoriestest4
    @id int output,
    @CategoryName nvarchar(15)
    AS
    insert into dbo.Categories
    (CategoryName,[Description],[Picture])
    values (@CategoryName,'test1',null)
    set @id = @@IDENTITY
    GO
    5. 同時具有返回值、輸入?yún)?shù)、輸出參數(shù)的存儲過程
    c#代碼部分
    代碼如下:
    SqlConnection sqlconn = new SqlConnection(conn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = sqlconn;
    cmd.CommandText = "Categoriestest5";
    cmd.CommandType = CommandType.StoredProcedure;
    // 創(chuàng)建參數(shù)
    IDataParameter[] parameters = {
    new SqlParameter("@Id", SqlDbType.Int,4) ,
    new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
    new SqlParameter("rval", SqlDbType.Int,4)
    };
    // 設(shè)置參數(shù)類型
    parameters[0].Direction = ParameterDirection.Output; // 設(shè)置為輸出參數(shù)
    parameters[1].Value = "testCategoryName"; // 給輸入?yún)?shù)賦值
    parameters[2].Direction = ParameterDirection.ReturnValue; // 設(shè)置為返回值
    // 添加參數(shù)
    cmd.Parameters.Add(parameters[0]);
    cmd.Parameters.Add(parameters[1]);
    cmd.Parameters.Add(parameters[2]);
    sqlconn.Open();
    // 執(zhí)行存儲過程并返回影響的行數(shù)
    Label1.Text = cmd.ExecuteNonQuery().ToString();
    sqlconn.Close();
    // 顯示影響的行數(shù)，輸出參數(shù)和返回值
    Label1.Text += "-" + parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();
    存儲過程Categoriestest5
    代碼如下:
    CREATE PROCEDURE Categoriestest5
    @id int output,
    @CategoryName nvarchar(15)
    AS
    insert into dbo.Categories
    (CategoryName,[Description],[Picture])
    values (@CategoryName,'test1',null)
    set @id = @@IDENTITY
    return @@rowcount
    GO
    6. 同時返回參數(shù)和記錄集的存儲過程
    c#代碼部分
    代碼如下:
    SqlConnection sqlconn = new SqlConnection(conn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = sqlconn;
    cmd.CommandText = "Categoriestest6";
    cmd.CommandType = CommandType.StoredProcedure;
    // 創(chuàng)建參數(shù)
    IDataParameter[] parameters = {
    new SqlParameter("@Id", SqlDbType.Int,4) ,
    new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
    new SqlParameter("rval", SqlDbType.Int,4) // 返回值
    };
    // 設(shè)置參數(shù)類型
    parameters[0].Direction = ParameterDirection.Output; // 設(shè)置為輸出參數(shù)
    parameters[1].Value = "testCategoryName"; // 給輸入?yún)?shù)賦值
    parameters[2].Direction = ParameterDirection.ReturnValue; // 設(shè)置為返回值
    // 添加參數(shù)
    cmd.Parameters.Add(parameters[0]);
    cmd.Parameters.Add(parameters[1]);
    cmd.Parameters.Add(parameters[2]);
    SqlDataAdapter dp = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    // 填充dataset
    dp.Fill(ds);
    // 顯示結(jié)果集
    GridView1.DataSource = ds.Tables[0];
    GridView1.DataBind();
    Label1.Text = "";
    // 顯示輸出參數(shù)和返回值
    Label1.Text += parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();
    存儲過程Categoriestest6
    代碼如下:
    CREATE PROCEDURE Categoriestest6
    @id int output,
    @CategoryName nvarchar(15)
    AS
    insert into dbo.Categories
    (CategoryName,[Description],[Picture])
    values (@CategoryName,'test1',null)
    set @id = @@IDENTITY
    select * from Categories
    return @@rowcount
    GO
    7. 返回多個記錄集的存儲過程
    c#代碼部分
    代碼如下:
    SqlConnection sqlconn = new SqlConnection(conn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = sqlconn;
    cmd.CommandText = "Categoriestest7";
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter dp = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    // 填充dataset
    dp.Fill(ds);
    // 顯示結(jié)果集1
    GridView1.DataSource = ds.Tables[0];
    GridView1.DataBind();
    // 顯示結(jié)果集2
    GridView2.DataSource = ds.Tables[1];
    GridView2.DataBind();
    存儲過程Categoriestest7
    代碼如下:
    CREATE PROCEDURE Categoriestest7
    AS
    select * from Categories
    select * from Categories
    GO