如何在会话C#中使用存储过程SCOPE_IDENTITY()输出 [英] How to use Stored Procedure SCOPE_IDENTITY() output in Session C#

问题描述

我正在尝试使用Insert存储过程中的SCOPE_IDENTITY并将其传递给Update语句.我一直在努力使其工作近2天，任何建议将不胜感激.这是我的代码:


插入存储过程代码:

I''m trying to use the SCOPE_IDENTITY from an Insert stored procedure and pass it for use in an Update statement as well. I''ve been trying to get this to work for almost 2 days now, any suggestions would be greatly appreciated. Here is my code:


Insert Stored Procedure Code:

ALTER PROCEDURE [dbo].InsertUser
(
	@UserID int OUTPUT,
	@FirstName nvarchar(50),
	@LastName nvarchar(50),
	@Username nvarchar(50),
	@Password nvarchar(50),
	@SecurityQuestionOption nvarchar(50),
	@SecurityAnswer nvarchar(50),
	@Address nvarchar(50),
	@City nvarchar(50),
	@State nvarchar(50),
	@ZipCode nvarchar(50),
	@PhoneNumber nvarchar(50),
	@EMail nvarchar(50),
	@LastLogin datetime,
	@IsActive bit,
	@IsAdmin bit
)
AS
	SET NOCOUNT ON;
INSERT INTO [Users] ([FirstName], [LastName], [Username], [Password], [SecurityQuestionOption], [SecurityAnswer], [Address], [City], [State], [ZipCode], [PhoneNumber], [EMail], [LastLogin], [IsActive], [IsAdmin]) VALUES (@FirstName, @LastName, @Username, @Password, @SecurityQuestionOption, @SecurityAnswer, @Address, @City, @State, @ZipCode, @PhoneNumber, @EMail, @LastLogin, @IsActive, @IsAdmin);
	
SELECT @UserID = SCOPE_IDENTITY();

这是用于插入的Register.aspx.cs代码:

Here''s the Register.aspx.cs code for inserting:

protected void ButtonRegisterUser_Click(object sender, EventArgs e)
    {
        try
        {
            //Inserts user into database if student is selected, password is encrypted
            SqlCommand cmd = new SqlCommand("InsertUser", cn);
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = TextBoxFName.Text;
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = TextBoxLName.Text;
            cmd.Parameters.Add("@Username", SqlDbType.NVarChar).Value = TextBoxUsername.Text;
            cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = FormsAuthentication.HashPasswordForStoringInConfigFile(TextBoxPassword.Text, "SHA1");
            cmd.Parameters.Add("@SecurityQuestionOption", SqlDbType.NVarChar).Value = DropDownListSecurityQuestion.SelectedValue;
            cmd.Parameters.Add("@SecurityAnswer", SqlDbType.NVarChar).Value = TextBoxSecurityAnswer.Text;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = TextBoxAddress.Text;
            cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = TextBoxCity.Text;
            cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = DropDownListState.SelectedValue;
            cmd.Parameters.Add("@ZipCode", SqlDbType.NVarChar).Value = TextBoxZipCode.Text;
            cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = TextBoxPhoneNumber.Text;
            cmd.Parameters.Add("@EMail", SqlDbType.NVarChar).Value = TextBoxEmailAddress.Text;
            cmd.Parameters.Add("@LastLogin", SqlDbType.DateTime).Value = DateTime.Now.ToString();
            cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = "True";
            cmd.Parameters.Add("@IsAdmin",SqlDbType.Bit).Value = "False";
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            cmd.ExecuteNonQuery();
            int ID = Convert.ToInt32(cmd.Parameters["@UserId"].Value);
            //UserID that will be passed into session to be checked throughout program
            Session["UserId"] = ID;
            cn.Close();

            //username that will be passed onto confirm registration
            Session["uname"] = TextBoxUsername.Text;

            //Information session to be passed to the confirm registration
            Session["fname"] = TextBoxFName.Text;
            Session["lname"] = TextBoxLName.Text;
            Session["address"] = TextBoxAddress.Text;
            Session["city"] = TextBoxCity.Text;
            Session["state"] = DropDownListState.SelectedValue;
            Session["zip"] = TextBoxZipCode.Text;
            Session["phone"] = TextBoxPhoneNumber.Text;
            Session["email"] = TextBoxEmailAddress.Text;
            Response.Redirect("~/ConfirmUserRegistration.aspx");
        }
        catch (Exception ex)
        {
            LabelMessage.Text = ex.Message.ToString();
        }
    }

这是更新存储过程代码:

Here''s the Update Stored Procedure code:

ALTER PROCEDURE [dbo].UpdateUser
(
	@FirstName nvarchar(50),
	@LastName nvarchar(50),
	@Address nvarchar(50),
	@City nvarchar(50),
	@State nvarchar(50),
	@ZipCode nvarchar(50),
	@PhoneNumber nvarchar(50),
	@EMail nvarchar(50),
	@Original_UsersID int,
	@UsersID int
)
AS
	SET NOCOUNT OFF;
UPDATE [Users] SET [FirstName] = @FirstName, [LastName] = @LastName, [Address] = @Address, [City] = @City, [State] = @State, [ZipCode] = @ZipCode, [PhoneNumber] = @PhoneNumber, [EMail] = @EMail WHERE (([UsersID] = @Original_UsersID));
	
SELECT UsersID, FirstName, LastName, Username, Password, SecurityQuestionOption, SecurityAnswer, Address, City, State, ZipCode, PhoneNumber, EMail, LastLogin, IsActive, IsAdmin FROM Users WHERE (UsersID = @UsersID)

最后是插入代码UpdateRegister.aspx.xs:

And finally here''s the code for the insert, UpdateRegister.aspx.xs:

protected void ButtonUpdate_Click(object sender, EventArgs e)
    {
            //Updates contact information
            cmd = new SqlCommand("UpdateUser", cn);
            cmd.Parameters.Add("@UsersID", SqlDbType.Int).Value = Session["UserId"];
            cmd.Parameters.Add("@Original_UsersID", SqlDbType.Int).Value = Session["UserId"];
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = TextBoxFName.Text;
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = TextBoxLName.Text;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = TextBoxAddress.Text;
            cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = TextBoxCity.Text;
            cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = DropDownListState.SelectedValue;
            cmd.Parameters.Add("@ZipCode", SqlDbType.NVarChar).Value = TextBoxZipCode.Text;
            cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = TextBoxPhoneNumber.Text;
            cmd.Parameters.Add("@EMail", SqlDbType.VarChar).Value = TextBoxEmailAddress.Text;
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
            Response.Redirect("~/Login.aspx");
    }

推荐答案

由于您无法使其正常工作，我花了一些时间处理您的代码，而我只需要使用Marcus Kramer提出的建议，它起作用了，并且我在int ID

Since you were not able to get it working i spent some time with your code and i just had to use what Marcus Kramer suggested SET @UserID = SCOPE_IDENTITY(), it worked and i got the autogenerated UserID inside int ID

protected void btnSave_Click(object sender, EventArgs e)
       {
           SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["TEST"].ToString());
           SqlCommand cmd = new SqlCommand("InsertUser", cn);
           cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
           cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = "FirstName";
           cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = "LastName";
           cmd.Parameters.Add("@Username", SqlDbType.NVarChar).Value = "Username";
           cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = "Password";
           cmd.Parameters.Add("@SecurityQuestionOption", SqlDbType.NVarChar).Value = "SecurityQuestionOption";
           cmd.Parameters.Add("@SecurityAnswer", SqlDbType.NVarChar).Value = "SecurityAnswer";
           cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value ="Address";
           cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = "City";
           cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = "State";
           cmd.Parameters.Add("@ZipCode", SqlDbType.NVarChar).Value = "ZipCode";
           cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = "PhoneNumber";
           cmd.Parameters.Add("@EMail", SqlDbType.NVarChar).Value = "EMail";
           cmd.Parameters.Add("@LastLogin", SqlDbType.DateTime).Value = DateTime.Now;
           cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = "True";
           cmd.Parameters.Add("@IsAdmin", SqlDbType.Bit).Value = "False";
           cmd.CommandType = CommandType.StoredProcedure;
           cn.Open();
           cmd.ExecuteNonQuery();
           int ID = Convert.ToInt32(cmd.Parameters["@UserId"].Value);

           cn.Close();
       }

这是我使用的存储过程

ALTER PROCEDURE [dbo].InsertUser
(
	@UserID int OUTPUT,
	@FirstName nvarchar(50),
	@LastName nvarchar(50),
	@Username nvarchar(50),
	@Password nvarchar(50),
	@SecurityQuestionOption nvarchar(50),
	@SecurityAnswer nvarchar(50),
	@Address nvarchar(50),
	@City nvarchar(50),
	@State nvarchar(50),
	@ZipCode nvarchar(50),
	@PhoneNumber nvarchar(50),
	@EMail nvarchar(50),
	@LastLogin datetime,
	@IsActive bit,
	@IsAdmin bit
)
AS
	SET NOCOUNT ON;
INSERT INTO [Users] ([FirstName], [LastName], [Username], [Password], [SecurityQuestionOption], [SecurityAnswer], [Address], [City], [State], [ZipCode], [PhoneNumber], [EMail], [LastLogin], [IsActive], [IsAdmin]) VALUES (@FirstName, @LastName, @Username, @Password, @SecurityQuestionOption, @SecurityAnswer, @Address, @City, @State, @ZipCode, @PhoneNumber, @EMail, @LastLogin, @IsActive, @IsAdmin);
	
SET @UserID = SCOPE_IDENTITY();

，这是我使用的表

CREATE Table Users(
   UserID int IDENTITY(1,1) ,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   Username nvarchar(50),
   [Password] nvarchar(50),
   SecurityQuestionOption nvarchar(50),
   SecurityAnswer nvarchar(50),
   [Address] nvarchar(50),
   City nvarchar(50),
   [State] nvarchar(50),
   ZipCode nvarchar(50),
   PhoneNumber nvarchar(50),
   EMail nvarchar(50),
   LastLogin datetime,
   IsActive bit,
   IsAdmin bit
)

我以前的建议是不需要的，即使不使用ExecuteScalar()，我也能接收回输出参数.

希望这会有所帮助.

What i suggested previously wasnt needed and I am able to receive back the output parameter even without using ExecuteScalar().

Hope this helps.

这篇关于如何在会话C#中使用存储过程SCOPE_IDENTITY()输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！