I am a newb to OOP programming and I'm only just starting with C#. I've done
a TON of reading lately, and I'm currently in the process of modifying some
of the function provided by the "TimeTracker Start-up Kit" for my needs.
****** BEGINING OF TIME TRACKER SUMMARY *****
First, in the "TimeTracker Start-Up Kit" they appear to have tried to set it
up so that it can be expanded to any number of different datasource types
(SQL, Access, Oracle, etc). They have a class called "DataAccessHelper.cs"
that seems to be a wrapper class for the abstract class "DataAccess.cs",
which in turn is a base class for "SQLDataAccessLayer.cs".
DataAccessHelper.cs appears to check a value in the web.config to see what
DataAccessType the ConnectionString is for (SQL, Access, Oracle, etc) and
verifies that this DataAccessType is the same type as the object created by
DataAccess.cs. It then forwards control to DataAccess.cs.
DataAccess.cs checks to make sure there is a non-null connectionString
defined in the web.config, and defines connectionString. It then sets
contracts for several abstract methods that are overridden in
SQLDataAccess.cs.
SQLDataAccess.cs defines several constant variables that are used in it's
methods, and then overrides all the abstract methods defined in
DataAccess.cs. It also has several private methods that are used for
internal workings. It has private methods that take arguments from its other
public methods, checks to make sure the variables are good, and then submits
them to the SQL server.
****** END OF TIME TRACKER SUMMARY *****
****** BEGINNING OF "WHAT I'M TRYING TO DO" SUMMARY *****
I am farily confident that we will never use any data source other than an
SQL database (at least not for this application). But I still want to have a
DataAccessLayer to separate the functionality into more managable code.
I have created the following two classes for my data access layer.
DataAccess.cs is a non-abstract class that has one read-only property
(ConnectionString), and three "SQL Helper Methods" taken directly from the
"TimeTracker Start-Up Kit" SQLDataAccessLayer.cs class. I have modified
these methods to be protected static instead of just private. There are
three methods and they are defined below:
****BEGIN - 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;
}
****END - SQL HELPER METHODS***
I have another class, Contact.cs that inherits the "DataAccess.cs" class.
It currently has one constant variable (the name of a SQL stored procedure)
and one method. The class is defined as follow:
***BEGIN - CONTACT.CS CLASS***
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;
}
}
***END - CONTACT.CS CLASS***
****** END OF "WHAT I'M TRYING TO DO" SUMMARY *****
Through debugging, I have been able to determine that the returnValue
returned by my "GetContactFirstName" method in Contact.cs, is returning an
integer value of "0". However, when I run the store procedure directly in
SQL, it returns a string (like it's supposed to). After further
investigation, I see that the "ConnectionString" value in the
"ExecuteScalarCmd" method in the DataAccess.cs class IS defined properly, but
it never seems to get added to the "sqlCmd" object. I believe the problem
lies in the following section:
****
using (SqlConnection cn = new SqlConnection(ConnectionString))
{
sqlCmd.Connection = cn;
cn.Open();
sqlCmd.ExecuteScalar();
}
****
When using intellisense to view the values of the variables, I see that
"ConnectionString" is defined in the first line, but browsing through the
object sqlCmd at the next line, it still shows the "ConnectionString" to be
empty (not null, just empty).
Now, this obviously works just fine in the "TimeTracker Start-Up Kit", but
not in my class. I don't know if I've got the connectionString defined badly
in the web.config (all the online examples seem to leave out that actual
format of the connectionString, they just show where it's supposed to go in
the <add> tag), or if I've done something wrong with elsewhere. Can anyone
tell me what to do here?
Thanks for the taking the time to read this novel! =)
-Amanda
