存储过程在开发过程中,经常使用到,会给我们的开发带来很多的便利,存储过程建好后,在代码中该如何调用呢,下面的是一个简单的调用存储过程的示例:
1
2 3 4 5 6 7 8 9 |
CREATE PROC P_TEST
@Name VARCHAR ( 20 ) , @Rowcount INT OUTPUT AS BEGIN SELECT * FROM T_Customer WHERE NAME =@Name SET @Rowcount =@@ROWCOUNT END GO |
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 43 44 |
using
System.Data.SqlClient
;
...... private void Page_Load ( object sender, System . EventArgs e ) { // 在此处放置用户代码以初始化页面 String DBConnStr ; DataSet MyDataSet = new DataSet ( ) ; System.Data . SqlClient . SqlDataAdapter DataAdapter = new System.Data . SqlClient . SqlDataAdapter ( ) ; DBConnStr = System.Configuration . ConfigurationSettings . AppSettings [ "ConnectString" ] ; System.Data . SqlClient . SqlConnection myConnection = new System.Data . SqlClient . SqlConnection (DBConnStr ) ; if (myConnection . State !=ConnectionState . Open ) { myConnection . Open ( ) ; } System.Data . SqlClient . SqlCommand myCommand = new System.Data . SqlClient . SqlCommand ( "P_Test",myConnection ) ; myCommand . CommandType =CommandType . StoredProcedure ; //添加输入查询参数、赋予值 myCommand . Parameters . Add ( "@Name",SqlDbType . VarChar ) ; myCommand . Parameters [ "@Name" ] . Value = "A" ; //添加输出参数 myCommand . Parameters . Add ( "@Rowcount",SqlDbType . Int ) ; myCommand . Parameters [ "@Rowcount" ] . Direction =ParameterDirection . Output ; myCommand . ExecuteNonQuery ( ) ; DataAdapter . SelectCommand = myCommand ; if (MyDataSet != null ) { DataAdapter . Fill (MyDataSet, "table" ) ; } DataGrid1 . DataSource =MyDataSet ; DataGrid1 . DataBind ( ) ; //得到存储过程输出参数 Label1 . Text =myCommand . Parameters [ "@Rowcount" ] . Value . ToString ( ) ; if (myConnection . State == ConnectionState . Open ) { myConnection . Close ( ) ; } } |