Solved

How to use sqlDataReader with Stored Procedure

Posted on 2011-03-04
3
753 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:silent_waters
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
>sqlParam.SqlDbType = SqlDbType.NVarChar;
you need to specify a Size also, otherwise it might presume size of 1, and hence truncate the value for the parameter

apart from that, you either don't have the good value for _alias there, or are connecting to another database than in your SQL Managaement Studio ...
0
 
LVL 9

Expert Comment

by:mayank_joshi
Comment Utility
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

0
 

Author Closing Comment

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now