Multiple result sets

SQL code:

Set nocount on
Go
Alter procedure pro_insertstu
(
@ Stuid varchar (50 ),
@ Stuname varchar (50)
)
As
Begin
Insert into stuinfo values (@ stuid, @ stuname );
Select @ rowcount
Select scope_identity () -- output current scope (stuinfo) -- id of each table
Select @ identity -- output second (T2) -- table ID
Select @ A = A, @ from stuinfo
End;
Alter trigger tri_inonstud
On stuinfo after insert
As
Begin
Insert into T2 values ('22 ');
End;
Exec pro_insertstu '1', '3'

Create Table T2
(ID int identity (1, 1 ),
Username varchar (50)
)

C # code:

Using system;
Using system. Collections. Generic;
Using system. LINQ;
Using system. Web;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Data;
Using system. Data. sqlclient;
Public partial class test: system. Web. UI. Page
{
Protected override void onload (eventargs E)
{
Using (sqlconnection sqlcon = new sqlconnection ("Data Source =.; initial catalog = mytest; Integrated Security = true "))
{
Sqlcon. open ();
Sqlcommand sqlcmd = new sqlcommand ("pro_insertstu", sqlcon );
Sqlcmd. commandtype = commandtype. storedprocedure;
Sqlcommandbuilder. deriveparameters (sqlcmd );
Sqlcmd. Parameters. removeat (0 );
Sqlcmd. Parameters [0]. value = "software China ";
Sqlcmd. Parameters [1]. value = "cccccc ";
Sqldataadapter sdar = new sqldataadapter (sqlcmd );
Dataset DS = new dataset ();
Sdar. Fill (DS );
Response. Write (Ds. Tables [0]. Rows [0] [0]. tostring () + "<br/>"); // data of the first result set
Response. Write (Ds. Tables [1]. Rows [0] [0]. tostring () + "<br/>"); // data of the second result set
Response. Write (Ds. Tables [3]. Rows [0] [1]. tostring (); // data of the fourth result set
}
}
}

Commandbuilder. deriveparameters to fill in the parameters set of the command object
Using system;
Using system. Collections. Generic;
Using system. LINQ;
Using system. Web;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Data;
Using system. Data. sqlclient;

Public partial class test1: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{
Buildparams ("ABC", "DD ");
}
Private void buildparams (Params string [] Str)
{
Using (sqlconnection sqlcon = new sqlconnection ("Data Source =.; initial catalog = mytest; Integrated Security = true "))
{
Sqlcommand sqlcmd = sqlcon. createcommand ();
Sqlcmd. commandtext = "pro_insertstu ";
Sqlcmd. commandtype = commandtype. storedprocedure;
Sqlcon. open ();
Sqlcommandbuilder. deriveparameters (sqlcmd );
Sqlcmd. Parameters. removeat (0 );
Sqlparameter [] sqlpar = new sqlparameter [sqlcmd. Parameters. Count];
Sqlcmd. Parameters. copyto (sqlpar, 0 );
For (INT I = 0; I <sqlpar. length; I ++)
{
Sqlpar [I]. value = STR [I];
}
Sqlcmd. executenonquery ();
}
}
}
Stored Procedure
Create procedure pro_insertstu
(
@ Stuid varchar (50 ),
@ Stuname varchar (50)
)
As
Begin
Insert into stuinfo values (@ stuid, @ stuname );
End;


If (Sign = 4) // demonstrate how to use commandbuilder. deriveparameters to fill in the parameters set of the command object
{
Sqlcommand mycommand = new sqlcommand (sqlprocedurename, myconnection );
Mycommand. commandtype = commandtype. storedprocedure;
Sqldataadapter myadapter = new sqldataadapter (sqlprocedurename, myconnection );

Myconnection. open (); // If the connection is not enabled, the next sentence will not know where to fetch the required Stored Procedure
Sqlcommandbuilder. deriveparameters (mycommand );
Mycommand. Parameters ["@ start"]. value = 1;
Mycommand. Parameters ["@ end"]. value = 5;
Myadapter. selectcommand = mycommand;

Myadapter. Fill (mydataset );
Return mydataset;

}

Oraclecommandbuilder. deriveparameters bug

DOTNET 19:49:16 read 17 comments 0 font size: large and small subscriptions
Problem:
 
Today, we found that the parameter set found by oraclecommandbuilder. deriveparameters does not include the returned values of stored procedures. We know that sqlcommandbuilder. deriveparameters is included, and the first parameter set is.
 
Environment:
 
Client: Oracle 10g, database Oracle 9.2, Windows server2003
 
Solution:
 
1. deriveparameters under oralce does not support returning return_value
2. The oraclecommandbuilder. deriveparameters provided by Microsoft has a bug. For details, refer to the attached code. (For oracle. Net class libraries provided by oralce, please download them from the official Oracle website and reference them to the project)
 
Appendix:
 
1.. Net code
 

Protected system. Web. UI. htmlcontrols. htmlinputtext lblsql;

Protected system. Web. UI. htmlcontrols. htmlinputtextlbloracle;
Protected system. Web. UI. htmlcontrols. htmlinputtextlblora;

// Obtain the SQL server stored procedure parameters (northwind)

Sqlconnection = new sqlconnection ("datasource =.; initial catalog = northwind; userid = sa; Password = caini ");

Sqlcommandsqlcmd = newsqlcommand ("custorderhist", sqlconnection );

Sqlcmd. commandtype = commandtype. storedprocedure;

Sqlconnection. open ();

Sqlcommandbuilder. deriveparameters (sqlcmd );

Sqlconnection. Close ();

Foreach (sqlparameter sqlparam in sqlcmd. Parameters)

{

Lblsql. value = lblsql. Value + "," + sqlparam. parametername;

}

// Return value: lblsql. value = @ return_value, @ customerid

// Obtain the Oracle stored procedure parameters (ctxsys. ctx_ddl.sync_index)

// Use the. NET class library provided by Microsoft.

// Use SYS. subptxt for testing.

String spname = "SYS. subptxt ";

System. Data. oracleclient. oracleconnection oraconnection = Newsystem. Data. oracleclient. oracleconnection ("userid = file_manage; Data Source = ADIM; Password = caini ");

System. Data. oracleclient. oraclecommand mongomd = Newsystem. Data. oracleclient. oraclecommand (spname, oraconnection );

Using MD. commandtype = commandtype. storedprocedure;

Oraconnection. open ();

System. Data. oracleclient. oraclecommandbuilder. deriveparameters (mongomd );

Oraconnection. Close ();

Foreach (system. Data. oracleclient. oracleparameter oraparamin1_md. Parameters)

{

Lblora. value = lblora. Value + "," + oraparam. parametername;

}

// Return value: lblora. value = Name, name, subname, subname, USR, USR, dbname, txt, dbowner, txt, and status (incorrect answer)

// Use the. NET class library provided by Oracle.

Oracle. dataaccess. Client. oracleconnection = neworacle. dataaccess. Client. oracleconnection ("User ID = file_manage; datasource = ADIM; Password = caini ");

Oracle. dataaccess. Client. oraclecommand oraclecmd = neworacle. dataaccess. Client. oraclecommand (spname, oracleconnection );

Oraclecmd. commandtype = commandtype. storedprocedure;

Oracleconnection. open ();

Oracle. dataaccess. Client. oraclecommandbuilder. deriveparameters (oraclecmd );

Oracleconnection. Close ();

Foreach (Oracle. dataaccess. Client. oracleparameter oraparaminoraclecmd. Parameters)

{

Lbloracle. value = lbloracle. Value + "," + oraparam. parametername;
}
// Return value: lbloracle. value = Name, subname, USR, txt (correct answer)
2. Stored Procedure Code

-- SQL Server storedprocedure
Create procedurecustorderhist @ customerid nchar (5)
As
Select productname, total = sum (Quantity)
From products P, [Order Details] OD, orders o, customers C
Where C. customerid = @ customerid and
C. customerid = O. customerid and
O. orderid = OD. orderid and
OD. productid = P. productid
Group byproductname

-- Oracle storedprocedure
Create or replace proceduresys. subptxt (
Name varchar2,
Subname varchar2,
USR varchar2,
TXT in out varchar2)
Is
Begin
Subptxt2 (name, subname, USR, null, null, txt );
End;