ExecuteNonQuery语句的返回值是多少?

What is the return value of ExecuteNonQuery statement?
2020-10-17
  •  译文(汉语)
  •  原文(英语)

我已经编写了一个程序来在Visual Studio 10中使用3层体系结构验证用户名和密码.在DAL中,ExecuteNonQuery语句返回"-1".但是我希望它在用户名和密码正确的情况下返回"1",在不正确的情况下返回"0".

DAL的代码片段:

public class LoginDataAccess
{
    SqlConnection con;
    string constr = ConfigurationManager.ConnectionStrings["localhostakash"].ToString();

    public int LoginData(LoginEntity elOj)
    {
            try
            {
                con = new SqlConnection(constr);
                int result;

                if(ConnectionState.Closed==con.State)
                    con.Open();

                SqlCommand cmd = new SqlCommand("uspuserlogin", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Username", elOj.Username);
                cmd.Parameters.AddWithValue("@Password", elOj.Password);

                result = Convert.ToInt32(cmd.ExecuteNonQuery());

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
    }
}

BLL的代码段:

public class LoginLogic
{
        LoginDataAccess lda = new LoginDataAccess();

        public int userValidate(LoginEntity le)
        {
            int result = 0;

            try
            {
                result = Convert.ToInt32(lda.LoginData(le));
            }
            catch (Exception ex)
            {
                //response.write(ex.Message);
            }

            return result;
        }
}

按钮功能的代码段:

protected void Button1_Click(object sender, EventArgs e)
{
            LoginLogic ll = new LoginLogic();
            LoginEntity le = new LoginEntity();

            int v;

            le.Username = TextBox1.Text;
            le.Password = TextBox2.Text;

            v = Convert.ToInt32(ll.userValidate(le));

            if (v == 1)
            {
                Label1.Text = "LOGGED IN SUCCESSFULLY!";
            }
            else
            {
                Label1.Text = "TRY AGAIN...";
            }
}
速聊1:
您是否尝试过阅读文档?
速聊2:
由于我是新手,所以我没有足够的经验.因此,在这种情况下,我需要您的干预并提供适当的代码段.
解决过程1

这里是文档:

对于UPDATE,INSERT和DELETE语句,返回值是该命令影响的行数.当要插入或更新的表上存在触发器时,返回值包括受插入或更新操作影响的行数以及受一个或多个触发器影响的行数.对于所有其他类型的语句,返回值为-1.如果发生回滚,则返回值也为-1.

在此处阅读更多信息:https : //msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

您正在将存储过程称为"uspuserlogin".这就是为什么ExecuteNonQuery返回-1.

如果需要了解操作结果,可以将值作为行返回.

ALTER PROCEDURE [dbo].[uspuserlogin]
    @username nvarchar(255),
    @password nvarchar(255)
AS
BEGIN
    SELECT COUNT(*) AS Found 
    FROM [Users]
    WHERE [Username] = @username AND [Password] = @password
END

在代码中:

var obj = cmd.ExecuteScalar();
return (int)obj;

// Somewhere in code
if (loginDataAccess.LoginData(loginEntity) == 1)
    // Authorize

当然,您可以bool方便地将其转换为:

public bool LoginData(LoginEntity elOj)
{
    try
    {
        con = new SqlConnection(constr);
        int result;

        if(ConnectionState.Closed==con.State)
            con.Open();

        SqlCommand cmd = new SqlCommand("uspuserlogin", con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Username", elOj.Username);
        cmd.Parameters.AddWithValue("@Password", elOj.Password);

        var obj = cmd.ExecuteScalar();
        return ((int)obj > 0);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
    }
}
速聊1:
感谢您的考虑.这对我来说是非常有用的.我如何替换该声明.
速聊2:
我不会使用ExecuteReader,如果您返回的只是一行使用一列ExecuteScalar,那就是它的设计目的.
速聊3:
是的,你是对的.只是个坏习惯.更改了代码.
速聊4:
@ Yeldar Kurmangaliyev我已经尝试过您的代码.但这显示了一个错误.它是:"并非所有代码路径都返回值"
速聊5:
您是否尝试过更新的代码?根据Scott的建议,我对其进行了更改,以便使用ExecuteScalar.

I have written a program to verify username and password using 3 tier architecture in Visual Studio 10. In the DAL, ExecuteNonQuery statement returns '-1'. But I want it to return '1' if username and password are correct or '0'if not correct.

Code snipped for DAL:

public class LoginDataAccess
{
    SqlConnection con;
    string constr = ConfigurationManager.ConnectionStrings["localhostakash"].ToString();

    public int LoginData(LoginEntity elOj)
    {
            try
            {
                con = new SqlConnection(constr);
                int result;

                if(ConnectionState.Closed==con.State)
                    con.Open();

                SqlCommand cmd = new SqlCommand("uspuserlogin", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Username", elOj.Username);
                cmd.Parameters.AddWithValue("@Password", elOj.Password);

                result = Convert.ToInt32(cmd.ExecuteNonQuery());

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
    }
}

Code snippet for BLL:

public class LoginLogic
{
        LoginDataAccess lda = new LoginDataAccess();

        public int userValidate(LoginEntity le)
        {
            int result = 0;

            try
            {
                result = Convert.ToInt32(lda.LoginData(le));
            }
            catch (Exception ex)
            {
                //response.write(ex.Message);
            }

            return result;
        }
}

Code snippet for button function:

protected void Button1_Click(object sender, EventArgs e)
{
            LoginLogic ll = new LoginLogic();
            LoginEntity le = new LoginEntity();

            int v;

            le.Username = TextBox1.Text;
            le.Password = TextBox2.Text;

            v = Convert.ToInt32(ll.userValidate(le));

            if (v == 1)
            {
                Label1.Text = "LOGGED IN SUCCESSFULLY!";
            }
            else
            {
                Label1.Text = "TRY AGAIN...";
            }
}
Talk1:
Have you tried reading the documentation?
Talk2:
As I'm a beginner I don't have enough experience on it.So I need your intervention in this context with appropriate code snippets.
Solutions1

Here is the documentation:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Read more here: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

You are calling a stored procedure "uspuserlogin". That's why ExecuteNonQuery returns -1.

You can return value as row if you need to know result of operation.

ALTER PROCEDURE [dbo].[uspuserlogin]
    @username nvarchar(255),
    @password nvarchar(255)
AS
BEGIN
    SELECT COUNT(*) AS Found 
    FROM [Users]
    WHERE [Username] = @username AND [Password] = @password
END

In code:

var obj = cmd.ExecuteScalar();
return (int)obj;

// Somewhere in code
if (loginDataAccess.LoginData(loginEntity) == 1)
    // Authorize

Of course, you can transform it to bool for your convenience:

public bool LoginData(LoginEntity elOj)
{
    try
    {
        con = new SqlConnection(constr);
        int result;

        if(ConnectionState.Closed==con.State)
            con.Open();

        SqlCommand cmd = new SqlCommand("uspuserlogin", con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Username", elOj.Username);
        cmd.Parameters.AddWithValue("@Password", elOj.Password);

        var obj = cmd.ExecuteScalar();
        return ((int)obj > 0);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
    }
}
Talk1:
Thanks for your consideration. It was very useful for me. How can i replace that statement.
Talk2:
I would not use ExecuteReader, if all that you are returning is one row with one column use ExecuteScalar, that is what it is designed for.
Talk3:
Yes, you are right. Just a bad habit. Changed the code.
Talk4:
@ Yeldar Kurmangaliyev I have tried your code. But it shows an error. It is: "not all code path return a value"
Talk5:
Have you tried the updated code? By the advice of Scott, I have changed it so that uses ExecuteScalar.
转载于:https://stackoverflow.com/questions/30859145/what-is-the-return-value-of-executenonquery-statement

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入