Public sqlconnection conn = new sqlconnection ("...");
Public sqlcommand COM = new sqlcommand ();
Public sqldataadapter adpt = new sqldataadapter ();
Public dataset set = new dataset ();
Public datatable dtb = new datatable ();
// Insert, update, and delect statements
Public int pro0 ()
{
Com. Connection = conn;
Com. commandtype = commandtype. text;
Com. commandtext = "insert into t_zichan values (ID )";
Int I = 0;
Try
{
Com. Connection. open ();
I = com. executenonquery ();
}
Catch (exception ex)
{
}
Com. Connection. Close ();
// Insert, update, and delect execute executenonquery (). If the number of affected rows is returned,> 0 indicates that the query is successful.
// The number of rows affected is the change of data. The SELECT statement uses executenonquery () to return-1.
Return I;
}
// SELECT statement
Public datatable pro1 ()
{
Com. Connection = conn;
Com. commandtype = commandtype. text;
Com. commandtext = "select * From t_zichan ";
// Write 1
Com. Connection. open ();
Sqldatareader reader = com. executereader ();
Dtb. Load (Reader );
Com. Connection. Close ();
/* Statement 2 Use sqldataadapter
Com. commandtext = "select * From t_zichan; select * From t_user ;";
// Execute multi-sentence query at a time
Adpt. selectcommand = com;
Adpt. selectcommand. Connection. open ();
Adpt. Fill (SET );
// A table in the result set can also be directly imported into the adpt. Fill (datatable) table to import multiple results into adpt. Fill (Dataset)
Dtb = set. Tables [0];
Adpt. selectcommand. Connection. Close ();
*/
Return dtb;
}
// The returned result set public dataset pro2 () {COM. connection = conn; COM. commandtype = commandtype. storedprocedure; COM. commandtext = "BBB"; adpt. selectcommand = com; adpt. selectcommand. connection. open (); adpt. fill (SET); adpt. selectcommand. connection. close (); return set;} // Number of affected rows returned
Public int pro3 () {COM. connection = conn; COM. commandtype = commandtype. storedprocedure; COM. commandtext = "BBB"; COM. connection. open (); int I; I = com. executenonquery (); COM. connection. close (); return I;} // a row or column of the returned result set is applicable to the query result and one column, for example, count (*), query the total number of rows on the page.
Public int pro4 () {COM. connection = conn; COM. commandtype = commandtype. storedprocedure; COM. commandtext = "CCC"; COM. connection. open (); int I; I = (INT) COM. executescalar (); COM. connection. close (); return I;} // return several results of the stored procedure. The return parameter affects the number of rows.
Public String pro5 () {com. Connection = conn; com. commandtype = commandtype. storedprocedure; com. commandtext = "DDD ";
Com. parameters. add (New sqlparameter ("@ A", sqldbtype. INT); COM. parameters ["@ A"]. value = 21; // declare @ A int COM. parameters. add (New sqlparameter ("@ B", sqldbtype. varchar, 20); COM. parameters ["@ B"]. direction = parameterdirection. output; // declare @ B varchar (20) COM. parameters. add (New sqlparameter ("@ return", sqldbtype. INT); COM. parameters ["@ return"]. direction = parameterdirection. returnvalue; // declare @ return int I = 0; try {COM. connection. open (); I = com. executenonquery (); // asynchronous execution I = com. endexecutenonquery (COM. beginexecutenonquery (); // exec @ return = DDD @ A, @ B output dtb. load (COM. executereader (); // dataset is also supported. load (Reader) Load sqldatareader} catch (exception ex) {Throw ex;} finally {If (COM. connection. state = connectionstate. open) COM. connection. close ();} string S = ""; S + = "Return:" + COM. parameters ["@ return"]. value. tostring () + ","; S + = "output:" + COM. parameters ["@ B"]. value. tostring () + ","; S + = "result set: 1/" + dtb. rows [0] [0]. tostring () + "2/" + dtb. rows [0] [1]. tostring () + ","; S + = "affected rows:" + I + ","; return s ;}
Stored Procedure
Create procedure [DBO]. [BBB] as select * From t_zichan select * From t_usersgocreate procedure [DBO]. [CCC] as select 5 gocreate procedure [DBO]. [DDD] @ A int, @ B varchar (20) outputas select 'yiele', 78 set @ B = 'output' exec pro_delete_zichan @ A -- generate affected rows return @ A + 1go