从SQL过程返回字符串到C#

zcum 发布于 2019-03-09 procedure 最后更新 2019-03-09 14:37 3 浏览

我有这样的SQL创建过程查询

    USE KOST
    GO
    --Cabang means Branch in Bahasa.
CREATE PROCEDURE login_pro
        --received parameter
        @NoIDStaff VARCHAR(5),
        @Password VARCHAR (50)
AS
--sent parameter
    DECLARE @status INT
    DECLARE @IDCabang VARCHAR(5)
--owner login
    IF EXISTS(SELECT IDCabang, [Password]
                FROM MsStaff 
                WHERE @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('B%'))
            SET @status=1
--staff login
    ELSE IF EXISTS(SELECT IDCabang, [Password]
                FROM MsStaff 
                WHERE @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('A%'))
            BEGIN
            SELECT @IDCabang=IDCabang FROM MsStaff
            SET @status=2
            SET @IDCabang = (select IDCabang FROM Msstaff where @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('A%'))
            END
--neither of them login
    ELSE
            set @status=0
select @status, @IDCabang
    GO
查询返回状态值,但不返回IDCabang。 我应该怎么做才能返回IDCabang的价值? 这是我的C#代码:
 private void submit_Click(object sender, EventArgs e)
    {
        NoIDStaff = new SqlParameter();
        Password = new SqlParameter();
        SqlConnection con = new SqlConnection(strCon);
com = new SqlCommand();
        com.Connection = con;
        con.Open();
com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "login_pro";
NoIDStaff.SqlDbType = SqlDbType.VarChar;
        NoIDStaff.Size = 50;
        NoIDStaff.ParameterName = "@NoIDStaff";
        NoIDStaff.Value = username.Text.ToString();
        NoIDStaff.Direction = ParameterDirection.Input;
Password.SqlDbType = SqlDbType.VarChar;
        Password.Size = 50;
        Password.ParameterName = "@Password";
        Password.Value = password.Text.ToString();
        Password.Direction = ParameterDirection.Input;
com.Parameters.Add(NoIDStaff);
        com.Parameters.Add(Password);
int status;
        string IDCabang;
        status = Convert.ToInt16(com.ExecuteScalar());
        IDCabang = Convert.ToString(com.ExecuteScalar());
//owner login
        if (status == 1)
        {
            this.Hide();
            frm = new Form2();
            frm.Show();
        }
//staff login
        else if (status == 2)
        {
            this.Hide();
            frm4 = new Form4(IDCabang);
            frm4.Show();
        }
else if (username.Text.Equals(""))
        {
            MessageBox.Show("Username Must be Filled!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
else if (password.Text.Equals(""))
        {
            MessageBox.Show("Password Must be Filled!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
else
        {
            MessageBox.Show("Invalid Username or Password", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
con.Close();
    }
我想以字符串的形式接收IDCabang,所以我可以将它发送给Form4。但看起来,IDCabang没有返回任何价值。
已邀请:

uqui

赞同来自:

您正在使用ExecuteScalar,它只会查看返回集的第一行和第一列:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx 你真的想要使用ExecuteReader或类似的东西。另外,我会在您的选择中为变量添加别名,以便您在查找时更容易引用它们(即SELECT @status as Status, @IDCabang as IDCabang)。

SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
  status = reader["Status"] == DBNull.Value ? 0 : (int)reader["Status"];
  IDCabang = reader["IDCabang"] == DBNull.Value ? null : (string)reader["IDCabang"];
}
编辑:参考Aaron Bertrand的评论,您还可以在存储过程中设置输出参数并读取。有关如何执行此操作的更多详细信息:Get output parameter value in ADO.NET

yquo

赞同来自:

你尝试过这样的:

    USE KOST
    GO
    --Cabang means Branch in Bahasa.
CREATE PROCEDURE login_pro(@IDCabang VARCHAR(5))
        --received parameter
        @NoIDStaff VARCHAR(5),
        @Password VARCHAR (50)
AS
--sent parameter
    DECLARE @status INT
--owner login
    IF EXISTS(SELECT IDCabang, [Password]
                FROM MsStaff 
                WHERE @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('B%'))
            SET @status=1
--staff login
    ELSE IF EXISTS(SELECT IDCabang, [Password]
                FROM MsStaff 
                WHERE @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('A%'))
            BEGIN
            SELECT @IDCabang=IDCabang FROM MsStaff
            SET @status=2
            SET @IDCabang = (select IDCabang FROM Msstaff where @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('A%'))
            END
--neither of them login
    ELSE
            set @status=0
select @status, @IDCabang
    GO
另请参阅THIS

edolor

赞同来自:

尝试这个

SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
  status = reader["Status"] == DBNull.Value ? 0 : (int)reader["Status"];
  IDCabang = reader["IDCabang"] == DBNull.Value ? null : (string)reader["IDCabang"];
}