从C#代码调用存储过程
我有一个存储过程它检查输入的电子邮件(&移动)是否已经存在于数据库中。如果它存在则返回True,否则返回False。如果返回False(即电子邮件&移动是唯一的),另一个存储过程将把用户详细信息插入数据库并注册他。所有这一切都是在单击按钮完成。
I have a stored procedure that checks if the Email (& Mobile) entered already exists in the database. It returns True if it does (exist), False otherwise. If False is returned (i.e. email & mobile are unique), another stored procedure will insert the user details into the database and register him. All this is done on a single button click.
需要使用此代码帮助:
编辑2:
protected void btnRegister_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
con.Open();
SqlCommand Cmd = new SqlCommand("usp_CheckEmailMobile", con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "Registration";
Cmd.Parameters.AddWithValue("@Name", txtName.Text);
Cmd.Parameters.AddWithValue("@Email", txtEmailAddress.Text);
Cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
Cmd.Parameters.AddWithValue("@CountryCode", ddlCountryCode.Text);
Cmd.Parameters.AddWithValue("@Mobile", txtMobileNumber.Text);
//Cmd.Parameters.Add("@Result", DbType.Boolean);
SqlParameter sqlParam = new SqlParameter("@Result", DbType.Boolean);
//sqlParam.ParameterName = "@Result";
//sqlParam.DbType = DbType.Boolean;
sqlParam.Direction = ParameterDirection.Output;
Cmd.Parameters.Add(sqlParam);
Cmd.ExecuteNonQuery();
con.Close();
Response.Write(Cmd.Parameters["@Result"].Value);
}
问题:如何使其工作?如何使用最少的资源工作?我复制代码?我想以最有效/逻辑/正确的方式做这个。
Question: how do I make it work? How do I make it work with minimum resources? Am I duplicating code? I wanna do this in the most efficient/logical/correct way.
编辑:
ALTER PROCEDURE [dbo].[usp_CheckEmailMobile]
( @Name VARCHAR(50),
@Email NVARCHAR(50),
@Password NVARCHAR(50),
@CountryCode INT,
@Mobile VARCHAR(50),
@Result BIT OUTPUT)
AS
BEGIN
IF EXISTS (SELECT COUNT (*) FROM AUser WHERE [Email] = @Email AND [Mobile] = @Mobile)
Begin
Set @Result=0; --Email &/or Mobile does not exist in database
End
ELSE
Begin
--Insert the record & register the user
INSERT INTO [AUser] ([Name], [Email], [Password], [CountryCode], [Mobile]) VALUES (@Name, @Email, @Password, @CountryCode, @Mobile)
Set @Result=1;
End
END
您可以通过 usp_CheckEmailMobile
存储过程执行操作:
You can perform the operation via your usp_CheckEmailMobile
stored procedure:
在存储过程中执行检查这个:
Inside your stored procedure do a check like this:
-- Check that record doesn't exist
IF NOT EXISTS (SELECT TOP 1 FROM AUser WHERE Email=@Email and Mobile=@Mobile)
-- Insert record
INSERT INTO [AUser] ([Name], [Email], [Password], [CountryCode], [Mobile])
VALUES (@Name, @Email, @Password, @CountryCode, @Mobile)
如果不存在则为记录。您只需要将其他参数添加到此存储过程。
This will insert the record if it doesn't exist. You will just need to add the other parameters on to this stored procedure.
您可以使用True / False值,说注册成功取决于返回的值。
You can use the True / False value and say 'Registration Successful' or 'User already exists' depending on the value returned.