本例为插入一条用户数据并把新数据的编号返回
*注意:据说返回参数必须是sqlcommand 的第一个参数
存储过程为:
CREATE PROCEDURE InsertUser
(
@user_id nvarchar(16),
@password nvarchar(20),
@user_name nvarchar(30),
@sex nvarchar(10),
@city nvarchar(50),
@address nvarchar(255),
@tel nvarchar(20),
@mobilephone nvarchar(20),
@email nvarchar(100),
@postcode nvarchar(20),
@register_date datetime,
@member_type nvarchar(20)
)
as
Insert into usermanage([user_id],password,[user_name],sex,city,address,tel,mobilephone,email,postcode,register_date,member_type)
values(@user_id,@password,@user_name,@sex,@city,@address,@tel,@mobilephone,@email,@postcode,@register_date,@member_type
)
declare @returnValue int
select @returnValue=@@IDENTITY
return @returnValue
GO
简单表示为
create pro 名称
(
@u_id int
.........
)
as
insert into() values()
declare @returnValue int
select @returnValue=@@identity
return @returnValue
存储过程完毕
------------------------------------------------------------------------------------------------------------
下边是c#代码
public int AddUserSimpleInfo(string user_id, string password, string user_name, string sex, string city, string address, string tel, string mobilephone, string email, string postcode)
{
using (SqlConnection conn = new SqlConnection(new NewData().ConnString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "InsertUser";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter returnValue = new SqlParameter("@returnValue", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnValue);
cmd.Parameters.Add("@user_id", SqlDbType.NVarChar).Value = user_id;
cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;
cmd.Parameters.Add("@user_name", SqlDbType.NVarChar).Value = user_name;
cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = sex;
cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = address;
cmd.Parameters.Add("@tel", SqlDbType.NVarChar).Value = tel;
cmd.Parameters.Add("@mobilephone", SqlDbType.NVarChar).Value = mobilephone;
cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
cmd.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = postcode;
cmd.Parameters.Add("@register_date", SqlDbType.DateTime).Value = DateTime.Today;
cmd.Parameters.Add("@member_type", SqlDbType.NVarChar).Value = "普通会员";
try
{
cmd.ExecuteNonQuery();
return Convert.ToInt32(returnValue.Value.ToString());
}
catch (SqlException ex)
{
return 0;
}
finally
{
conn.Close();
}
}
}