Kinda new and having trouble with SQL stored procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a bit of a newb to .NET and C#, and I've been trying to make a data
access class that will act as a buffer between my other classes and my SQL
database. However, the data it's returning is wrong and I'm confused as to
why.

It's a very simple class, shown here:

*************
namespace ACD.CMS.DataAccessLayer
{
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
#region PROPERTIES
private static string ConnectionString
{
get
{
if (ConfigurationManager.ConnectionStrings["support7_SQL"]
== null)
throw (new NullReferenceException("ConnectionString
configuration is missing from your web.config. It should contain
<connectionStrings> <add name=\"support7_SQL\" connectionString=\"Data
Source=xena;Initial Catalog=support7;Persist Security Info=True;User
ID=someusernamehere;Password=sompasswordhere\"
providerName=\"System.Data.SqlClient\"/></connectionStrings>"));

string connectionString =
ConfigurationManager.ConnectionStrings["support7_SQL"].ConnectionString;

if (String.IsNullOrEmpty(connectionString))
throw (new NullReferenceException("ConnectionString
configuration is missing from your web.config. It should contain
<connectionStrings> <add name=\"support7_SQL\" connectionString=\"Data
Source=xena;Initial Catalog=support7;Persist Security Info=True;User
ID=someusernamehere;Password=somepasswordhere\"
providerName=\"System.Data.SqlClient\"/></connectionStrings>"));
else
return (connectionString);
}
}
#endregion
#region SQL HELPER METHODS
protected static void AddParamToSQLCmd(SqlCommand sqlCmd,
string paramId,
SqlDbType sqlType,
int paramSize,
ParameterDirection paramDirection,
object paramvalue)
{

if (sqlCmd == null)
throw (new ArgumentNullException("sqlCmd"));
if (paramId == string.Empty)
throw (new ArgumentOutOfRangeException("paramId"));

SqlParameter newSqlParam = new SqlParameter();
newSqlParam.ParameterName = paramId;
newSqlParam.SqlDbType = sqlType;
newSqlParam.Direction = paramDirection;

if (paramSize > 0)
newSqlParam.Size = paramSize;

if (paramvalue != null)
newSqlParam.Value = paramvalue;

sqlCmd.Parameters.Add(newSqlParam);
}

protected static void ExecuteScalarCmd(SqlCommand sqlCmd)
{
if (ConnectionString == string.Empty)
throw (new ArgumentOutOfRangeException("ConnectionString"));

if (sqlCmd == null)
throw (new ArgumentNullException("sqlCmd"));

using (SqlConnection cn = new SqlConnection(ConnectionString))
{
sqlCmd.Connection = cn;
cn.Open();
sqlCmd.ExecuteScalar();

}
}

protected static void SetCommandType(SqlCommand sqlCmd, CommandType
cmdType, string cmdText)
{
sqlCmd.CommandType = cmdType;
sqlCmd.CommandText = cmdText;
}
#endregion
}
}
*************

I have another very simple class called Contact.cs that has a single method
that calls the DataAcess.cs class above. Contact.cs is shown below:

************
namespace ACD.CMS.DataAccessLayer
{
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using ACD.CMS.DataAccessLayer;
using System.Data.SqlClient;


/// <summary>
/// This class serves as the data access layer for the
ACD.CMS.BusinessLogicLayer.Contact class.
/// </summary>
public class Contact : DataAccess
{
private const string SP_CONTACT_GetContactFirstName =
"SP_CONTACT_GetContactFirstName";

public static string GetContactFirstName(uint contactId)
{
SqlCommand sqlCmd = new SqlCommand();

AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.NVarChar, 0,
ParameterDirection.ReturnValue, null);
AddParamToSQLCmd(sqlCmd, "@contactId", SqlDbType.Int, 0,
ParameterDirection.Input, contactId);

SetCommandType(sqlCmd, CommandType.StoredProcedure,
SP_CONTACT_GetContactFirstName);
/*string blah = */ExecuteScalarCmd(sqlCmd);

string returnValue =
(string)sqlCmd.Parameters["@ReturnValue"].Value;

return returnValue;
}
}
}
************

The problem I am having is that the DataAccess method "ExecuteScalarCmd"
ALWAYS RETURNS ZERO. However, when I run the stored procedure from Query
Analyzer, it works just fine. I can see by using intellisense in debug mode
that it has the correct value for the "contactId" parameter, but the
"@ReturnValue" parameter is always 0.

The stored procedure itself is incredibly simple (shown below):

***********
Create procedure SP_CONTACT_GetContactFirstName @contactId int
as

SELECT First_Name from Contacts where contactID = @contactId
***********

I assume I've got some kind of syntax wrong? I've been struggling with this
for two days now.. can anyone give me any insight? It would be greatly
appreciated. =)

-Amanda
 
Hi Amanda,

The ExecuteScalar function of a SqlCommand object returns the value in
the first column of the first row in the result set returned by SQL
Server. Return value parameters (whose direction is set to
ParameterDirection.ReturnValue) alwyas return an integer value that
indicate the return code of an SQL Statement.

In the case of your stored proc the @ReturnValue parameter would
contain 0 because your stored proc never sets any return value using
the RETURN statement. While the actual result is being returned as an
object from the SqlCommand.ExecuteScalar function.

The first change is in your DataAccess class. The current
implementation of ExecuteScalar is defined as a void function (no
return value). It needs to change to return an object back:

protected static object ExecuteScalarCmd(SqlCommand sqlCmd)
{
if (ConnectionString == string.Empty)
throw (new
ArgumentOutOfRangeException("ConnectionString"));


if (sqlCmd == null)
throw (new ArgumentNullException("sqlCmd"));


using (SqlConnection cn = new
SqlConnection(ConnectionString))
{
sqlCmd.Connection = cn;
cn.Open();
return sqlCmd.ExecuteScalar();
}
}


Second is when you call the ExecuteScalar function, the returned object
should contain the first name of the contact:

//Code to setup the sql command object comes here...
object result = ExecuteScalarCmd (...);
Console.WriteLine (result.ToString());

Hope this helps...
NuTcAsE
 
Oh THANK YOU! =)

It worked.. I'm am incredibly relieved. Thank you so much for taking the
time to answer and to explain why it was wrong. I had no idea that the
ReturnValue was only returning a return code from the SQL statement. I had
seen an earlier example that was doing it this way, so I thought it would
work for me. Thank you clearing that up. =)

I had to do a little extra with a .ToString() a few times, but I think
that's normal? Anyway, I hope so.

Thank you again for taking the time to answer, I appreciate it!

-Amanda
 
Back
Top