ALTER PROCEDURE Account_CreateUser
@Name nvarchar(50),
@Email nvarchar(50),
@Password binary(50),
@PromptQuestion nvarchar(50),
@PromptAnswer nvarchar(50),
@Sex bit,
@HomePage nvarchar(250),
@Country nvarchar(50),
@Phone nvarchar(50),
@Birthday DateTime,
@Address nvarchar(250),
@Job nvarchar(50),
@ZipCode nvarchar(20),
@UserID int outputAS
INSERT INTO Users(Name,Email,Password,PromptQuestion,PromptAnswer,Sex,HomePage,Country,Phone,Birthday,Address,Job,ZipCode)
Values(@Name,@Email,@Password,@PromptQuestion,@PromptAnswer,@Sex,@HomePage,@Country,@Phone,@Birthday,@Address,@Job,@ZipCode)
SET @UserID = @@IDENTITY
RETURN 1
@Name nvarchar(50),
@Email nvarchar(50),
@Password binary(50),
@PromptQuestion nvarchar(50),
@PromptAnswer nvarchar(50),
@Sex bit,
@HomePage nvarchar(250),
@Country nvarchar(50),
@Phone nvarchar(50),
@Birthday DateTime,
@Address nvarchar(250),
@Job nvarchar(50),
@ZipCode nvarchar(20),
@UserID int outputAS
INSERT INTO Users(Name,Email,Password,PromptQuestion,PromptAnswer,Sex,HomePage,Country,Phone,Birthday,Address,Job,ZipCode)
Values(@Name,@Email,@Password,@PromptQuestion,@PromptAnswer,@Sex,@HomePage,@Country,@Phone,@Birthday,@Address,@Job,@ZipCode)
SET @UserID = @@IDENTITY
RETURN 1
int sex,string homePage,string country,string phone,DateTime birthday,string address,string job,string zipCode)
{
int rowsAffected;
SqlParameter[] parameters = {
new SqlParameter("@Name",SqlDbType.NVarChar,50),
new SqlParameter("@Passwrod",SqlDbType.Binary,50),
new SqlParameter("@Email",SqlDbType.NVarChar,50),
new SqlParameter("@PromptQuestion",SqlDbType.NVarChar,50),
new SqlParameter("@PromptAnswer",SqlDbType.NVarChar,50),
new SqlParameter("@Sex",SqlDbType.Bit,1),
new SqlParameter("@HomePage",SqlDbType.NVarChar,250),
new SqlParameter("@Country",SqlDbType.NVarChar,50),
new SqlParameter("@Phone",SqlDbType.NVarChar,50),
new SqlParameter("@Birthday",SqlDbType.DateTime,4),
new SqlParameter("@Address",SqlDbType.NVarChar,50),
new SqlParameter("@Job",SqlDbType.NVarChar,50),
new SqlParameter("@ZipCode",SqlDbType.NVarChar,20),
new SqlParameter("@UserID",SqlDbType.Int,4)
};
parameters[0].Value = name;
parameters[1].Value = password;
parameters[2].Value = email;
parameters[3].Value = promptQuestion;
parameters[4].Value = promptAnswer;
parameters[5].Value = true;
parameters[6].Value = homePage;
parameters[7].Value = country;
parameters[8].Value = phone;
parameters[9].Value = birthday;
parameters[10].Value = address;
parameters[11].Value = job;
parameters[12].Value = zipCode;
parameters[13].Direction = ParameterDirection.Output;
try
{
RunProcedure("Account_CreateUser",parameters,out rowsAffected);
}
catch(SqlException e)
{
if( e.Number == 2601)//当此用户已经存在时(因为表的Name与Email都有约束)
{
return (int)CSharp.Name.WebModules.Account.ProcResultCodes.AccountAlreadyExist ;//返回-100 }
else
{
throw new AppException("执行Account_CreateUser存储过程时发生一个未知的异常");
}
} //若运行至此,表示创建成功,返回创建的UserID
return (int)parameters[13].Value; }