Assign value from stored procedure to a variable

Question

I'm using stored procedure to get the 'password' value from the database. and i need to assign this value to a variable. I'm using asp.net- mvc and Ado.net. Here is my stored procedure.

CREATE PROCEDURE [dbo].[getPassword]
(
@Email VARCHAR(100)
)
AS
BEGIN
SELECT Password FROM dbo.Staff_Login WHERE Email=@Email
END

Here is my repository class.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Job_Recuitment_System.Repository
{
    public class LoginRepository
    {
        private SqlConnection con;
        //To handle sql connection
        private void connection() {
            string constr = ConfigurationManager.ConnectionStrings["mycon"].ToString();
            con = new SqlConnection(constr);
        }

        //to get the password
        public List<StaffLogin> getPassword(StaffLogin obj) {
            connection();
            List<StaffLogin> pword = new List<StaffLogin>();
            SqlCommand com = new SqlCommand("getPassword", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Email",obj.Email);
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            con.Open();
            da.Fill(dt);
            con.Close();

            //Bind StaffLogin 
            pword = (from DataRow dr in dt.Rows

                       select new StaffLogin()
                       {
                           Password = Convert.ToString(dr["Password"]),
                       }).ToList();


            return pword;

        }

    }
}

I have use a list. But i need to assign value to a varible. because i only i need is one value (password).

score 2 · Answer 1 · edited May 23 '17 at 12:00

You don't need an SqlDataAdapter but you just use the SqlCommand.ExecuteScalar

        connection();
        SqlCommand com = new SqlCommand("getPassword", con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Email",SqlDbType.NVarChar, 100). Value = obj.Email;
        con.Open();
        var result = com.ExecuteScalar();            
        if(result != null)
             MessageBox.Show("Password = " + result.ToString();
        con.Close();

ExecuteScalar returns the first column of the first row retrieved by the command and your query fits nicely this condition. However it is important to consider that ExecuteScalar could return NULL if the query doesn't produce any result so always test the result against a null value before using it.

On another matter I really suggest you to avoid stored procedures for these simple menial tasks unless there is a good reason to use them. Using the Add instead of AddWithValue and specifying the exact size of the parameter gives to the Sql optimizer enough hints to create an optimized query.

Finally, remember that storing/returning passwords in clear text is considered a very big security risk. Try to use a safer method as explained in this question: Best way to store passwords in a database

sujith karivelil · Answer 2 · 2016-08-23T07:19:20.277

In this case you are retrieving the value of a single column, You can use ExecuteScalar() in such situations. Then the command execution will be like the following:

string passwordStr= (string)com.ExecuteScalar();

So the signature of the getPassword method will also be changed, its return type will become string instead for List<StaffLogin>; the new signature will be:

public string getPassword(StaffLogin obj)
 {
     connection();
     string passwordStr = String.Empty;
     using (SqlCommand com = new SqlCommand("getPassword", con))
     {
         com.CommandType = CommandType.StoredProcedure;
         com.Parameters.AddWithValue("@Email", obj.Email);
         passwordStr = (string)com.ExecuteScalar();
     }
     return passwordStr;
 }

Assign value from stored procedure to a variable

2 Answers2