我在SQL中建了一个存储过程叫AddCustomer
有若干的参数,最后一个是输入参数,请问在C#中的程序改怎么个写法得那个输出参数
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
SqlCommand cmd = new SqlCommand("AddCustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = ci.CName;
cmd.Parameters.Add("@Sex", SqlDbType.NVarChar, 1).Value = ci.CSex;
cmd.Parameters.Add("@IDType", SqlDbType.NVarChar, 50).Value = pi.PType;
cmd.Parameters.Add("@IDNumber", SqlDbType.NVarChar, 50).Value = pi.PNumber;
cmd.Parameters.Add("@Desposit", SqlDbType.Money).Value = ci.CDeposit;
cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 50).Value = ci.CPhone;
cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 50).Value = ci.CAddress;
cmd.Parameters.Add("@Desource", SqlDbType.NVarChar, 50).Value = ci.CDesource;
cmd.Parameters.Add("@Re", SqlDbType.NVarChar, 255).Value = ci.CRe;
int result = cmd.ExecuteNonQuery();
con.Close();
}
有若干的参数,最后一个是输入参数,请问在C#中的程序改怎么个写法得那个输出参数
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
SqlCommand cmd = new SqlCommand("AddCustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = ci.CName;
cmd.Parameters.Add("@Sex", SqlDbType.NVarChar, 1).Value = ci.CSex;
cmd.Parameters.Add("@IDType", SqlDbType.NVarChar, 50).Value = pi.PType;
cmd.Parameters.Add("@IDNumber", SqlDbType.NVarChar, 50).Value = pi.PNumber;
cmd.Parameters.Add("@Desposit", SqlDbType.Money).Value = ci.CDeposit;
cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 50).Value = ci.CPhone;
cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 50).Value = ci.CAddress;
cmd.Parameters.Add("@Desource", SqlDbType.NVarChar, 50).Value = ci.CDesource;
cmd.Parameters.Add("@Re", SqlDbType.NVarChar, 255).Value = ci.CRe;
int result = cmd.ExecuteNonQuery();
con.Close();
}
CREATE PROCEDURE [dbo].[AddCategory]
@CategoryId int output,
@Name nvarchar(32),
@Type int,
@ParentId int,
@Path nvarchar(256),
@Level int,
@Order int,
@Description nvarchar(512),
@Attributes int
AS
BEGIN
SET NOCOUNT ON; INSERT INTO [dbo].[Category]
(Name,
[Type] ,
ParentId ,
[Path] ,
[Level] ,
[Order] ,
[Description] ,
Attributes,
CreatedDate,
ModifiedDate)
VALUES
(@Name,
@Type ,
@ParentId ,
@Path ,
@Level ,
@Order ,
@Description ,
@Attributes,
GETDATE(),
GETDATE())
SELECT @CategoryId = SCOPE_IDENTITY()
END
//调用存储过程,返回输出参数。
public int AddCategory(string name, int type, int parentId, string path, int level, int order, string description, int attributes)
{
SqlConnection conn = new SqlConnection( sqlDataConnString );
SqlCommand comm = new SqlCommand("AddCategory", conn);
comm.CommandType = CommandType.StoredProcedure; SqlParameter param = comm.Parameters.Add(new SqlParameter("@CategoryId", SqlDbType.Int));
comm.Parameters[0].Direction = ParameterDirection.Output; comm.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 32));
comm.Parameters[1].Value = name; comm.Parameters.Add(new SqlParameter("@Type", SqlDbType.Int));
comm.Parameters[2].Value = type; comm.Parameters.Add(new SqlParameter("@ParentId", SqlDbType.Int));
if (parentId == 0)
comm.Parameters[3].Value = DBNull.Value;
else
comm.Parameters[3].Value = parentId; comm.Parameters.Add(new SqlParameter("@Path", SqlDbType.NVarChar, 256));
if (String.IsNullOrEmpty(path))
comm.Parameters[4].Value = DBNull.Value;
else
comm.Parameters[4].Value = path; comm.Parameters.Add(new SqlParameter("@Level", SqlDbType.Int));
comm.Parameters[5].Value = level; comm.Parameters.Add(new SqlParameter("@Order", SqlDbType.Int));
comm.Parameters[6].Value = order; comm.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar,512));
if (String.IsNullOrEmpty(description))
comm.Parameters[7].Value = DBNull.Value;
else
comm.Parameters[7].Value = description;
comm.Parameters.Add(new SqlParameter("@Attributes", SqlDbType.Int));
comm.Parameters[8].Value = attributes;
try
{
conn.Open();
comm.ExecuteNonQuery();
return (int)param.Value;
}
//catch (Exception ex)
//{
// throw new Exception("", ex);
//}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
用程序模板自动生成去~~
conn.Open();
SqlCommand MyCommand = new SqlCommand("MYSQL", conn);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
MyCommand.Parameters["@a"].Value = 20;
MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int));
MyCommand.Parameters["@b"].Direction = ParameterDirection.Output;
MyCommand.ExecuteNonQuery();
Response.Write(MyCommand.Parameters["@b"].Value.ToString());
这个输出参数不需要赋值。
string safdsf=Command.Parameters["@sdfsf"].Value;
safdsf就是你要输出参数的值。
sqlpar.Direction = ParameterDirection.Output;
com.ExecuteNonQuery();