silent_waters
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!
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();
}
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Angelll, yet again you've solved a problem in seconds that has been bugging me for hours! Thank you very much indeed.
Open in new window