Get Stored Procedure returned data

Source: Internet
Author: User
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

 

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.