sql 代码：
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()--输出当前作用域（StuInfo）——个表的id
select @@IDENTITY --输出第二（t2）——个表的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# 代码：
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 = "软件中国";
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/>");//第一个结果集的数据
Response.Write(ds.Tables[1].Rows[0][0].ToString() + "<br/>");//第二个结果集的数据
Response.Write(ds.Tables[3].Rows[0][1].ToString());//第四个结果集的数据
}
}
}
CommandBuilder.DeriveParameters 来填充 Command 对象的 Parameters 集合
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();
}
}
}
存储过程
create procedure pro_insertStu
(
@stuid varchar(50),
@stuname varchar(50)
)
as
begin
insert into StuInfo values (@stuid,@stuname);
end;
if(sign==4) //演示如何使用 CommandBuilder.DeriveParameters 来填充 Command 对象的 Parameters 集合
{
SqlCommand myCommand=new SqlCommand(sqlProcedureName,myConnection);
myCommand.CommandType=CommandType.StoredProcedure;
SqlDataAdapter myAdapter=new SqlDataAdapter(sqlProcedureName,myConnection);
myConnection.Open(); //如果不打开连接，下一句将不知道从哪里去取所需的存储过程
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 2006-12-14 19:49:16 阅读17 评论0 字号：大中小 订阅
问题：
今天发现，OracleCommandBuilder.DeriveParameters找到的参数集合竟然不包括存储过程的返回值。而我们知道SqlCommandBuilder.DeriveParameters是包含的，在参数集的第一个就是。
环境：
客户端：Oracle 10g，数据库Oracle 9.2，Windows Server2003
解决：
1、Oralce下的DeriveParameters不支持返回Return_Value
2、微软提供的OracleCommandBuilder.DeriveParameters存在Bug。具体看所附代码。(Oralce自己提供的访问Oracle的.Net类库请到Oracle官方网站下载，并引用到项目中)
附：
1、.Net代码
protected System.Web.UI.HtmlControls.HtmlInputText lblSql;
protected System.Web.UI.HtmlControls.HtmlInputTextlblOracle;
protected System.Web.UI.HtmlControls.HtmlInputTextlblOra;
//获取SQL SERVER存储过程参数(Northwind)
SqlConnection 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;
}
//返回: lblSql.Value =@RETURN_VALUE,@CustomerID
//获取Oracle存储过程参数(ctxsys.ctx_ddl.sync_index)
//使用Microsoft提供的.Net类库。
//使用sys.subptxt进行测试，有惊喜
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 oraCmd =newSystem.Data.OracleClient.OracleCommand(spName,oraConnection);
oraCmd.CommandType =CommandType.StoredProcedure;
oraConnection.Open();
System.Data.OracleClient.OracleCommandBuilder.DeriveParameters(oraCmd);
oraConnection.Close();
foreach(System.Data.OracleClient.OracleParameter oraParaminoraCmd.Parameters)
{
lblOra.Value = lblOra.Value + "," +oraParam.ParameterName;
}
//返回: lblOra.Value = NAME , NAME , SUBNAME, SUBNAME , USR , USR , DBNAME , TXT , DBOWNER , TXT ,STATUS（错误答案）
//使用Oracle提供的.Net类库。
Oracle.DataAccess.Client.OracleConnection 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;
}
// 返回:lblOracle.Value=NAME,SUBNAME,USR,TXT(正确答案)
2、存储过程代码
--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;