Link to home
Start Free TrialLog in
Avatar of silent_waters
silent_watersFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to use sqlDataReader with Stored Procedure

I'm trying to select a single row from a single table in my MSSQL database using a datareader for speed and a stored procedure for abstraction.
Please see the SP and C# code atached.
If I just insert the SQL into the connection string (and remove the connection type line) it works perfectly. If i run the SP in Management Studio it works perfectly. If I run it as it is below I get an empty reader.
So I must be doing something wrong in passing the parameters back and forth, and I'm hoping it is something obvious!
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["IntranetConnectionString"].ConnectionString);
            SqlCommand sqlCmd = new SqlCommand("usp_select_user_by_alias", sqlConn);
            sqlCmd.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlParam = new SqlParameter();
            sqlParam.ParameterName = "@alias_in";
            sqlParam.SqlDbType = SqlDbType.NVarChar;
            sqlParam.Direction = ParameterDirection.Input;
            sqlParam.Value = _alias;
            sqlCmd.Parameters.Add(sqlParam);

            SqlDataReader sqlReader;

            try
            {
                sqlConn.Open();
                sqlReader = sqlCmd.ExecuteReader();
                sqlReader.Read();
                _guid = ((Guid)sqlReader["user_id"]).ToString();
                _alias = (string)sqlReader["alias"];
                _displayName = (string)sqlReader["displayname"];
                _firstName = (string)sqlReader["firstname"];
                _surname = (string)sqlReader["surname"];
                _title = (string)sqlReader["title"];
                _phone = (string)sqlReader["phone"];
                _fax = (string)sqlReader["fax"];
                _mobile = (string)sqlReader["mobile"];
                _dmsId = (string)sqlReader["dms_id"];
                _ext = (string)sqlReader["extension"];
                _addr = (string)sqlReader["street_address"];
                _town = (string)sqlReader["town"];
                _county = (string)sqlReader["county"];
                _postcode = (string)sqlReader["postcode"];
                _email = (string)sqlReader["email"];
                _office = (string)sqlReader["office"];
                _active = (bool)sqlReader["active"];
            }
            catch (Exception ex)
            {
                throw new Exception("Failed to fill IntranetUser from Database. " + ex.Message);
            }
            finally
            {
                sqlConn.Close();
            }

Open in new window

USE [intranetdb]
GO
/****** Object:  StoredProcedure [dbo].[usp_select_user_by_alias]    Script Date: 03/03/2011 16:26:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	xxxxxxxxxxxx
-- Create date:   xxxxxxxxxxxx
-- Description:	xxxxxxxxxxxxxxxx
-- =============================================
ALTER PROCEDURE [dbo].[usp_select_user_by_alias] 
	(
	@alias_in NVarChar(100)
    )
AS
BEGIN
	SET NOCOUNT ON;

	SELECT user_id, alias, displayname, firstname, surname, title, phone, fax, mobile, dms_id, extension, street_address, town, county, postcode, email, office, active 
		FROM users 
		WHERE alias = @alias_in;
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try using while  (sqlReader.Read())


SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["IntranetConnectionString"].ConnectionString);
            SqlCommand sqlCmd = new SqlCommand("usp_select_user_by_alias", sqlConn);
            sqlCmd.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlParam = new SqlParameter();
            sqlParam.ParameterName = "@alias_in";
            sqlParam.SqlDbType = SqlDbType.NVarChar;
            sqlParam.Direction = ParameterDirection.Input;
            sqlParam.Value = _alias;
            sqlCmd.Parameters.Add(sqlParam);

            SqlDataReader sqlReader;

            try
            {
                sqlConn.Open();
                sqlReader = sqlCmd.ExecuteReader();
               while  (sqlReader.Read())
{
                _guid = ((Guid)sqlReader["user_id"]).ToString();
                _alias = (string)sqlReader["alias"];
                _displayName = (string)sqlReader["displayname"];
                _firstName = (string)sqlReader["firstname"];
                _surname = (string)sqlReader["surname"];
                _title = (string)sqlReader["title"];
                _phone = (string)sqlReader["phone"];
                _fax = (string)sqlReader["fax"];
                _mobile = (string)sqlReader["mobile"];
                _dmsId = (string)sqlReader["dms_id"];
                _ext = (string)sqlReader["extension"];
                _addr = (string)sqlReader["street_address"];
                _town = (string)sqlReader["town"];
                _county = (string)sqlReader["county"];
                _postcode = (string)sqlReader["postcode"];
                _email = (string)sqlReader["email"];
                _office = (string)sqlReader["office"];
                _active = (bool)sqlReader["active"];
}
            }
            catch (Exception ex)
            {
                throw new Exception("Failed to fill IntranetUser from Database. " + ex.Message);
            }
            finally
            {
                sqlConn.Close();
            }

Open in new window

Avatar of silent_waters

ASKER

Angelll, yet again you've solved a problem in seconds that has been bugging me for hours! Thank you very much indeed.