C#中調用預存程序，通過DataGridView顯示

（轉）

 

1. 通過文字框textbox.Text輸入 實際要輸入的參數值，點擊Button1，執行預存程序後把檢索的資料用datagridview1顯示出來；

 

private void button1_Click(object sender, EventArgs e)
        {

 

                    //資料庫連接字串
                    string connString = " =DataSource=.;Initial Catalog=user_table;Persist Security Info=True;User      ID=sa;Password=123";
                    SqlConnection conn = new SqlConnection(connString);
                    string proc_name = "proc_transfer";           //預存程序名
                    SqlCommand comm = new SqlCommand(proc_name, conn);
                    //把SqlCommand執行類型改為預存程序方式，預設為Text
                    comm.CommandType = CommandType.StoredProcedure;

                  /*預存程序proc_transfer的3個參數
                    string cardID1 = Form_password.cardID;
                    string cardID2 = txtBx_cardID.Text;
                    string transMoney = txtbox_transMoney.Text;
                    */
                    /*第一個參數@cardID_1 */ // SqlDbType.Char為資料庫下表“UserTable”對應欄位參數實際的資料類型
                    SqlParameter sp = comm.Parameters.Add("@cardID_1", SqlDbType.Char, 20);//
                    sp.Value = Form_password.cardID;  //參數值
                    sp.Direction = ParameterDirection.Input;  //指示此參數為輸入參數

                    sp = comm.Parameters.Add("@cardID_2", SqlDbType.Char, 20);
                    sp.Value = txtBx_cardID.Text;
                    sp.Direction = ParameterDirection.Input;

                    sp = comm.Parameters.Add("@transMoney", SqlDbType.Money);
                    sp.Value = txtbox_transMoney.Text;
                    sp.Direction = ParameterDirection.Input;

                    SqlDataAdapter da = new SqlDataAdapter(comm);
                    DataSet ds = new DataSet();
                    da.Fill(ds,"UserTable");

                    //    將返回的資料和DataGrid綁定顯示 
                 dataGridView1.DataSource = ds.Tables[0].DefaultView;       

}

 

===============================================================================

（轉）

在.NET(C#)中調用預存程序並通過DataGridView顯示2008-07-12 09:35

1.無參數預存程序 //串連資料庫              SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=5366845;database=testfile");              SqlCommand selectCMD = new SqlCommand("test", conn);              selectCMD.CommandType = CommandType.StoredProcedure;              //    建立DataAdapter對象填充資料                 DataSet myDS = new DataSet();              SqlDataAdapter adapter = new SqlDataAdapter(selectCMD);              myDS.Clear();              adapter.Fill(myDS, "TestTable");              //    將返回的資料和DataGrid綁定顯示              myDataGrid.DataSource = myDS.Tables[0].DefaultView;              myDataGrid.Refresh();              myDS = null;                        //關閉串連              adapter = null;              selectCMD.Dispose();              conn.Dispose(); 2.帶參數的預存程序 //串連資料庫             SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=5366845;database=testfile");                                   SqlCommand selectCMD = new SqlCommand("get_test", conn);                  selectCMD.CommandType = CommandType.StoredProcedure;                  //取得參數值 lat ,lon為定義的string 型變數.                  lat = (String) dgv.Rows[e.RowIndex].Cells[0].Value;                  lon = (String) dgv.Rows[e.RowIndex].Cells[1].Value;                  //添參數                  SqlParameter strlac = new SqlParameter("@lat", SqlDbType.VarChar);                  selectCMD.Parameters.Add(strlac);                  strlac.Value = lat;                  SqlParameter strlon = new SqlParameter("@lon", SqlDbType.VarChar);                  strlon.Value = lon;                  selectCMD.Parameters.Add(strlon);                    //    建立DataAdapter對象填充資料                     DataSet myDS = new DataSet();                  SqlDataAdapter adapter = new SqlDataAdapter(selectCMD);                  myDS.Clear();                  adapter.Fill(myDS, "TestTable1");                  //    將返回的資料和DataGrid綁定顯示                  myDataGridDetail.DataSource = myDS.Tables[0].DefaultView;

=================================================================================