正在从存储过程中检索多个值...请帮助 [英] retrieving multiple values from stored procedure...please help
问题描述
我正在使用asp.net 2010和sql express 2008进行开发.
我正在尝试检索以下内容:-
我有一个凭证表....我在其中存储了一个过程，我正在传递两个输入并期望三个输出.
输入:uid和密码
输出:booeanyesORno，Uname，Utype
另外，在单击按钮时，我编写了以下代码:
....做一个连接...
....设置存储的proc而不是选择查询...非常容易..
然后...
Hi, I am working on asp.net 2010 witgh sql express edition 2008.
I am trying to retrieve the following :-
I have a credentiuals table....i have a stored proc in whoich i am passing two inputs and expecting three outputs.
INputs: uid and password
output: booeanyesORno , Uname, Utype
Also, On the click of a button ,I wrote the following code:
....make a conection...
....setting a stored proc instead of a select query...very easy..
then...
cmd.parameters.addwithvalue("@uid", textbox1.text);
cmd.parameters.addwithvalue("@pwd", textbox2.text)
cmd.parameters.add("@booeanyesORno",sqldbtype.bit).direction=parameterdirection.output
cmd.parameters.add("@uname";,sqldbtype.varchar).direction=parameterdirection.output
cmd.parameters.add("@utype",sqldbtype.varchar).direction=parameterdirection.output
bool bb=(bool)cmd.executescalar();
===============================================
=================================================
create proc pp(@uid varchar(20),@pwd varchar(20),@booeanyesORno bit output, @uname varchar(20) output, @utype varchar(20) output
as
begin
select @booeanyesORno=count(*) from tablename where userid=@uid and password =@pwd
select @uname=username,usertype= @utype from tablename where userid=@uid and password =@pwd
end
---------------------------------------------
现在，此SP正常运行...问题出在编码的问题上....我的问题是我想在后面的代码中检索三个输出(以便在cmr的主页上可以看到uname和utype)...
-我不能使用nonquery和datareader，但是它不值..我尝试了标量方法...但是我不知道如何继续成功地获取隐藏在后面的值...请帮助！！！ br/>
其他任何输入(如果需要)..请询问...
-在这里，请不要过分区分大小写
请尽快帮助...有危险的工作...
[edit]添加的代码块-OriginalGriff [/edit]
---------------------------------------------
NOW, THIS SP is working all fine...bnut the issue is on the coding sode....my prob is that i want to retrieve the three outputs in the code behind(so that on home page the cmr could see the uname and utype)...
-i cant use nonquery and datareader but its not worth..i tried scalar method...but i dont know how to go ahead and retrieve the values whic hare succesfully in the coe-behind...Please help !!!!
Any other inputs(if you need) ..please ask...
- here, pls dont go by case-senstiveness
pLEASE HELP SOON...job in danger...
[edit]Code blocks added - OriginalGriff[/edit]
推荐答案
以下是显示我将如何执行的示例-输入和输出:
Here is an example showing how I would do it - input and output:
public static bool PostalAreaInsert( SqlConnection sqlConnection, ref Guid oid, ref DateTime? registeredTime, ref Guid? registeredBy, Guid? region, string name, DateTime? fromTime, DateTime? throughTime)
{
bool result = false;
SqlCommand sqlCommand = sqlConnection.CreateCommand( );
using( sqlCommand )
{
sqlCommand.CommandText = "PostalAreaInsert";
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter oidParameter =
sqlCommand.Parameters.Add(new SqlParameter("@Oid",
SqlDbType.UniqueIdentifier));
oidParameter.Direction = ParameterDirection.InputOutput;
oidParameter.Value = oid;
SqlParameter registeredTimeParameter =
sqlCommand.Parameters.Add(new SqlParameter
("@RegisteredTime", SqlDbType.DateTime));
registeredTimeParameter.Direction =
ParameterDirection.InputOutput;
if( registeredTime.HasValue )
{
registeredTimeParameter.Value =
registeredTime.Value;
}
else
{
registeredTimeParameter.Value = DBNull.Value;
}
SqlParameter registeredByParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@RegisteredBy",
SqlDbType.UniqueIdentifier));
registeredByParameter.Direction =
ParameterDirection.InputOutput;
if( ( registeredBy.HasValue )
&& ( registeredBy.Value != Guid.Empty ) )
{
registeredByParameter.Value = registeredBy.Value;
}
else
{
registeredByParameter.Value = DBNull.Value;
}
SqlParameter objectTypeParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@ObjectType", SqlDbType.Int));
objectTypeParameter.Direction =
ParameterDirection.InputOutput;
objectTypeParameter.Value = 2;
SqlParameter regionParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@Region",
SqlDbType.UniqueIdentifier));
if( ( region.HasValue )
&& ( region.Value != Guid.Empty ) )
{
regionParameter.Value = region.Value;
}
else
{
regionParameter.Value = DBNull.Value;
}
SqlParameter nameParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@Name",
SqlDbType.NVarChar, 255));
if(( name != null )
&& ( name.Length > 0 ))
{
nameParameter.Value = name;
}
else
{
nameParameter.Value = DBNull.Value;
}
SqlParameter fromTimeParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@FromTime",
SqlDbType.DateTime));
if( fromTime.HasValue )
{
fromTimeParameter.Value = fromTime.Value;
}
else
{
fromTimeParameter.Value = DBNull.Value;
}
SqlParameter throughTimeParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@ThroughTime",
SqlDbType.DateTime));
if( throughTime.HasValue )
{
throughTimeParameter.Value = throughTime.Value;
}
else
{
throughTimeParameter.Value = DBNull.Value;
}
int rowsAffected = sqlCommand.ExecuteNonQuery();
if(rowsAffected == 1)
{
Guid newOid = (Guid)oidParameter.Value;
DateTime regTime =
Convert.ToDateTime
(registeredTimeParameter.Value);
Guid regByOid = (Guid)registeredByParameter.Value;
oid = newOid;
registeredTime = regTime;
registeredBy = regByOid;
result = true;
}
}
return result;
}
存储过程:
The stored procedure:
CREATE PROCEDURE [PostalAreaInsert]
@Oid uniqueidentifier OUTPUT,
@RegisteredTime DateTime OUTPUT,
@RegisteredBy uniqueidentifier OUTPUT,
@ObjectType int OUTPUT,
@Region uniqueidentifier,
@Name nvarchar(255),
@FromTime DateTime,
@ThroughTime DateTime
AS
BEGIN
IF @Oid IS NULL
BEGIN
SET @Oid = NEWID()
END
IF @ObjectType IS NULL
BEGIN
SET @ObjectType = 2
END
IF @RegisteredBy IS NULL
BEGIN
SET @RegisteredBy = (SELECT Oid From SecurityLogin WHERE [Identity] = SYSTEM_USER)
END
IF @RegisteredTime IS NULL
BEGIN
SET @RegisteredTime = SYSDATETIME()
END
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
SAVE TRANSACTION SavePoint2;
ELSE
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO Element(Oid,OptimisticLockField,RegisteredBy,RegisteredTime,ObjectType)
VALUES(@Oid,0,@RegisteredBy,@RegisteredTime,@ObjectType);
INSERT INTO [PostalArea](Oid, [Region], [Name], [FromTime], [ThroughTime])
VALUES(@Oid, @Region, @Name, @FromTime, @ThroughTime);
IF @TranCounter = 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF @TranCounter = 0
ROLLBACK TRANSACTION;
ELSE
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION SavePoint2;
RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState);
END CATCH
END
GO
问候
Espen Harlinn
Regards
Espen Harlinn
为什么不看看本文的使用OUTPUT变量"部分
http://www.sqlteam.com/article/stored-procedures-returning-data [ ^ ]
例如
Why not have a look at the ''Using OUTPUT variables'' part of this article
http://www.sqlteam.com/article/stored-procedures-returning-data[^]
e.g.
// Your code should get the connection string from web.config
string connectionString =
@"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("dbo.GetCountByLastName"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));
SqlParameter countParameter = new SqlParameter("@LastNameCount", 0);
countParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(countParameter);
conn.Open();
cmd.Connection = conn;
cmd.ExecuteNonQuery();
int count = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
Response.Write("<p>Count: " + count.ToString());
conn.Close();
}
}
调用ExecuteNonquery之后，只需检索您需要的每个参数
一个简单的谷歌搜索从存储过程中获取输出参数"，这是最热门的.如果您想确保自己的工作安全，我认为学习Google应该是您的第一目标
After a call to ExecuteNonquery, just retrieve each of the parameters you need
A simple google for ''get output parameters from stored procedure'' and this was the top hit. If you want to keep your job safe, I think learning how to google should be your number 1 goal
u可以创建这样的
如果您从一个商店生产者那里获得乘数数据，则您的查询是错误的
您无法执行定标器推荐命令，因为获取的数据不正确或错误
:omg::omg::omg::omg:
u can create a like this
Your query is wrong in case of u get a multiplier data from one store producer
you cant not execute the scaler commend cos u get data not true or false
:omg: :omg: :omg: :omg:
这篇关于正在从存储过程中检索多个值...请帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
