Failed to get data from stored procedure
I am trying to use a stored procedure from C # code, but I always get the result == -1. I don't know where I went wrong. I searched a lot but couldn't find a solution. Please take a look at my code snippet and tell me what I am doing wrong.
Thanks in advance.
C # code:
using (SqlConnection connection = new SqlConnection(getConnectionString()))
using (SqlCommand command = new SqlCommand())
{
Int32 rowsAffected;
command.CommandText = "SP_LOGIN_GETUSERBYNAME";
command.CommandType = CommandType.StoredProcedure;
// command.Parameters.Add(new SqlParameter("@Email", userObj.email));
// command.Parameters.Add("@Email", SqlDbType.VarChar).Value = userObj.email.Trim();
command.Parameters.AddWithValue("@Email", userObj.email.ToString());
command.Connection = connection;
connection.Open();
rowsAffected = command.ExecuteNonQuery();
connection.Close();
return rowsAffected;
}
Connection string:
return "Data Source=MUNEEB-PC;Initial Catalog=HRPayRoll;User ID=sa; Password=sa";
Stored procedure code:
CREATE PROCEDURE SP_LOGIN_GETUSERBYNAME
@Email varchar(50)
AS
SELECT *
FROM [User]
WHERE Email = @Email
GO
source to share
From ExecuteNonQuery
doc;
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on an inserted or updated table, the return value includes the number of rows affected by the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of operators, the return value is -1
Since your command is SELECT, it's too normal to get -1 as a return value.
If you want to achieve your results, you can use ExecuteReader
.
var reader = command.ExecuteReader();
while (reader.Read())
{
// This will iterate your results line by line and
// You can get columns with zero-based values like reader[0], reader[1] or
// can use GetXXX methods of it like GetString(0) or GetInt32(1) etc.
}
source to share