将值从sql返回到c# [英] Return a value from sql to c#
本文介绍了将值从sql返回到c#的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨朋友们,
我有一个商店程序,它将id从联系人返回到c#,但在c#中我无法接受它。
这是我的c#代码。
商店程序真的有效。
public static int ContactInsert( string name, string family, string address, string phoneNumber, string birthDate, string emailAddress, string faceBook)
{
尝试
{
使用(SqlConnection cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings [ DB]。ConnectionString))
{
SqlCommand cmm = new SqlCommand( Contact_Insert,cnn);
cmm.CommandType = CommandType.StoredProcedure;
cmm.Parameters.AddWithValue( @ Name,name);
cmm.Parameters.AddWithValue( @ Family,family);
cmm.Parameters.AddWithValue( @ Adderess,地址);
cmm.Parameters.AddWithValue( @ phoneNumber,phoneNumber);
cmm.Parameters.AddWithValue( @ BirthDate,Convert.ToDateTime(birthDate)。日期);
cmm.Parameters.AddWithValue( @ EmailAddress,emailAddress);
cmm.Parameters.AddWithValue( @ Facebook,faceBook);
cnn.Open();
var value = cmm.ExecuteScalar();
cnn.Close();
return Convert.ToInt32( value );
}
}
catch (例外)
{
掷跨度>;
}
}
和我的商店程序
USE [Mobile1]
GO
/ * *****对象:StoredProcedure [dbo]。[ Contact_Insert]脚本日期:9/23/2013 3:07:18 PM ****** /
SET ANSI_NULLS ON
GO
< span class =code-keyword> SET QUOTED_IDENTIFIER ON
GO
ALTER 程序 [dbo] 。[Contact_Insert]
@ Name nvarchar ( 50 ),
@ Family nvarchar ( 50 ),
@ Adderess nvarchar ( 100 ),
@ PhoneNumber char ( 10 ),
@ BirthDate date ,
@ EmailAddress nvarchar ( 100 ),
@ Facebook nvarchar ( 100 )
作为
声明 @ x int ;
开始
开始尝试
开始 Tran a
如果 存在(选择 id 来自联系其中名称= @ Name 和 Family = @ Family)
开始
设置 @ x =(选择 Id 从联系其中 Name = @ Name 和 Family = @ Family )
结束
其他
开始
插入 进入联系
(姓名,家庭,地址1,电话号码,出生日期) ,EmailAddress,脸书)
值
( @ Name , @ Family , @ Adderess , @ PhoneNumber , @ BirthDate , @ EmailAddress , @ Facebook )
set @ x =(选择 Id From 联系其中名称= @ Name 和 Family = @ Family )
结束
提交 Tran a;
结束尝试
开始 Catch
RollBack tran a;
结束 Catch
返回 @ X 跨度>;
结束
解决方案
您需要将参数声明为输出以返回值。
请参阅参考链接 http://stackoverflow.com/questions/14810037/get-return-value-from-stored-procedure-in-asp-net [ ^ ]
ExecuteScalar获取第一行的第一列。
在SP中更改此内容:
返回@x;
对此:
选择@x;
Hi Friends,
I have a store procedure which return id from contact to c#, but in c# i couldn't take it.
This my c# code.
Store procedure works truly.
public static int ContactInsert(string name, string family, string address, string phoneNumber, string birthDate, string emailAddress, string faceBook)
{
try
{
using (SqlConnection cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
{
SqlCommand cmm = new SqlCommand("Contact_Insert",cnn);
cmm.CommandType = CommandType.StoredProcedure;
cmm.Parameters.AddWithValue("@Name", name);
cmm.Parameters.AddWithValue("@Family", family);
cmm.Parameters.AddWithValue("@Adderess", address);
cmm.Parameters.AddWithValue("@phoneNumber", phoneNumber);
cmm.Parameters.AddWithValue("@BirthDate",Convert.ToDateTime( birthDate).Date);
cmm.Parameters.AddWithValue("@EmailAddress", emailAddress);
cmm.Parameters.AddWithValue("@Facebook", faceBook);
cnn.Open();
var value = cmm.ExecuteScalar();
cnn.Close();
return Convert.ToInt32(value);
}
}
catch (Exception)
{
throw;
}
}
and this my Store procedure
USE [Mobile1]
GO
/****** Object: StoredProcedure [dbo].[Contact_Insert] Script Date: 9/23/2013 3:07:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Contact_Insert]
@Name nvarchar(50),
@Family nvarchar(50),
@Adderess nvarchar(100),
@PhoneNumber char(10),
@BirthDate date,
@EmailAddress nvarchar(100),
@Facebook nvarchar(100)
As
Declare @x int;
Begin
Begin Try
Begin Tran a
If exists(Select id from Contact Where Name= @Name and Family =@Family)
Begin
set @x = (Select Id From Contact Where Name = @Name and Family = @Family)
End
else
Begin
Insert into Contact
(Name, Family, Address1, PhoneNumber, BirthDate, EmailAddress, Facebook)
Values
(@Name, @Family, @Adderess, @PhoneNumber, @BirthDate, @EmailAddress, @Facebook )
set @x = (Select Id From Contact Where Name= @Name and Family = @Family)
End
Commit Tran a;
End Try
Begin Catch
RollBack tran a;
End Catch
Return @x;
End
解决方案
You need to declare parameter as a output to return a value.
See the link for reference http://stackoverflow.com/questions/14810037/get-return-value-from-stored-procedure-in-asp-net[^]
The ExecuteScalar takes the firs column on the first row.
Change this in your SP:
Return @x;
TO this:
Select @x;
这篇关于将值从sql返回到c#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文