C# 調用SQLServer的預存程序

目的：

在C#中輸入條件導入SQLServr的預存程序
再取得預存程序的結果輸出至C#程式中

使用者輸入範圍，取得該範圍內總和、平均、及個數

按查詢全表，會取得全部的內容

取得全表後，可以進行金額由大到小的排序

輸入"肉"，取得品名中有"肉"的產品

說明：

先說為什麼要用StoredProcedure?

StoredProcedure的方法，也可以在程式中寫出

主要目的為了降低藕合，加快執行速度

當需要某些修改時，只要去修訂StoredProcedure就好了

不需要再修訂程式

比喻就是：

到麥當勞時，單點一個大麥克、一個中薯、一個可樂

還是直接說我要「1號餐」，那個比較快?

用程式去撈出完整且單純的資料後，再用力撰寫方法

還是條件給資料庫，直接說要這樣的結果，那個比較快?

相信應該很容易分辦。

程式碼：

先開啟SQLServer，選取「練練」資料庫後新增查詢：

這很重要，沒選練練直接查詢，會把預存程序放在master資料庫

CREATE PROCEDURE 巨巨產品 @price1 MONEY,@price2 MONEY
 ,@sum MONEY OUTPUT,@avg MONEY OUTPUT
AS
  DECLARE @count INT

  SELECT @sum=SUM(價錢),@avg=AVG(價錢)
  FROM 巨巨
  WHERE 價錢 BETWEEN @price1 AND @price2

  SELECT 產品編號,品名,價錢
  FROM 巨巨
  WHERE 價錢 BETWEEN @price1 AND @price2

  SET @count=@@ROWCOUNT
  RETURN @count
GO

主程式碼：

public partial class Form1 : Form
{
    private DataView vv;
    //用DataView來做展示的Source
    //取得資料庫的資料後
    //盡量不要用資料庫進行
    //排序或篩選等行為
    //減輕資料庫的負擔
        
    public Form1()
    {
        InitializeComponent();
    }
    //--------------------------------------------------------------------------------------
    private void button1_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("server=localhost;database=練練;UID=SQLAdmin;PWD=1234;");
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "巨巨產品";
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        //指定要做StoredProcedure型態
        //換句話說，我要用"巨巨產品"這個StoredProcedure

        cmd.Parameters.Add("@price1",SqlDbType.Money).Value = textBox1.Text;
        cmd.Parameters.Add("@price2", SqlDbType.Money).Value = textBox2.Text;
        cmd.Parameters.Add("@sum", SqlDbType.Money);
        cmd.Parameters.Add("@avg", SqlDbType.Money);
        cmd.Parameters.Add("@count", SqlDbType.Int);
        /*說明：
            * 1.預存程序"巨巨產品"中有 @price1，@price2，@sum，@avg，@count這5個參數
            * 2.@price1，@price2 的值，是由程式中使用者輸入後取得
            * 3.@sum，@avg，@count 的值，是由預存程序計算後，回傳的值
            */

        cmd.Parameters["@sum"].Direction = ParameterDirection.Output;
        cmd.Parameters["@avg"].Direction = ParameterDirection.Output;
        cmd.Parameters["@count"].Direction = ParameterDirection.ReturnValue;
        /*說明：
            * 1.設定參數 @sum，@avg，這2個的「方向」為輸出
            * 2.設定參數 @count 的「方向」為單純取值
            */

        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(reader);
        dt.DefaultView.Sort = "價錢 ASC,產品編號 ASC";
        //設定預設排序為價錢由小到大，若價錢相同產品編號由小到大

        double aa = Convert.ToDouble(cmd.Parameters["@sum"].Value);
        double bb = Convert.ToDouble(cmd.Parameters["@avg"].Value);
        int count = (int)cmd.Parameters["@count"].Value;
        //取值

        reader.Close();
        cmd.Dispose();
        conn.Close();

        label5.Text = aa.ToString();
        label6.Text = bb.ToString();
        label7.Text = count.ToString();
        dataGridView1.DataSource = dt;
        //秀出到程式

    }
    //--------------------------------------------------------------------------------------
    private void button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("server=localhost;database=練練;UID=SQLAdmin;PWD=1234;");
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SELECT * FROM 巨巨";
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;

        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(reader);
        reader.Close();
        cmd.Dispose();
        con.Close();

        //利用DataTable產生DataView物件
        vv = dt.DefaultView;
        dataGridView1.DataSource = vv;

    }
    //--------------------------------------------------------------------------------------
    private void button3_Click(object sender, EventArgs e)
    {
        //必須先做button2_Click後取得全表，如此DataView vv才會有東西
        vv.Sort = "價錢 DESC,品名 ASC";

    }
    //--------------------------------------------------------------------------------------
    private void button4_Click(object sender, EventArgs e)
    {
        //必須先做button2_Click後取得全表

        vv.RowFilter = "品名 LIKE '%"+textBox3.Text+"%'";

        /*RowFilter就像資料庫中的WHERE，也就是條件：
            * 
            * vv.RowFilter = "價錢 >=10 AND 價錢<=20"; 
            * //取得價錢在10~20之間的產品
            * 
            * vv.RowFilter = "品名='" + textBox3.Text + "'"; 
            * //取得品名完全相同的產品
            */         
    }
}