I will try to be as concise as I can:
The goal: trying to universalize a specific section of a project, that is dealing with the SQL database transactions.
To assist you with your answer, I've pasted the following (just for reference):
a usage code:
GetTestOfTablTime()
returns aDataTable
class:
SQLDBInteraction
is another class - responsible for the final(SQL transaction) stage
In this code below, I am constructing what I call: "Stored Procedure's Meta Data". That class is the one that holds all of the SQL Db SPs.
HTSPs
(HT is the company's aliases) is holding each SP
required) parameters. HTSPs
class contains another sub Class. For all SP
s Names, it only has const string
s for each SP
name.
public sealed class HTSPs
{
//so for example this is one of the members of this class - a stored procedure
//its mission: get evnents with specified id OF specified userId in spec' month, year..
public sealed class GetTimesWithCustomerNames
{
//if I DO need Constructor for its parameters how do I properly format the constructor?
public GetTimesWithCustomerNames()
{
Userid.ParameterName = ParNameUserid;
Month.ParameterName = ParNameMonth;
Year.ParameterName = ParNameYear;
EventId.ParameterName = ParNameReasonid;
}
const string ParNameUserId = "@userId",
ParNameMonth = "@month",
ParNameYear = "@year",
ParNameEventId = "@eventId";
public static SqlParameter Userid = new SqlParameter();
public static SqlParameter Month = new SqlParameter();
public static SqlParameter Year = new SqlParameter();
public static SqlParameter EventId = new SqlParameter();
}
}
The issue is: how do I initialize the contractor? What is the proper way to have your simple customized StoredProcedure
"MetaData"? I've currently completed the implementation of the method below (apart from that issue).
USAGE
This is a method that returns DataTable
while using the HTSPs
class / constructor.
using SPTime = HT_DBSchema.HTSPs.GetTimesWithCustomerNames;
private DataTable GetTestOfTablTime()
{
SQLDBInteraction.DataContainer DC_Time = new SQLDBInteraction.DataContainer();
SQLDBInteraction.SqlParamList parmsTime = new SQLDBInteraction.SqlParamList();
Dictionary<SqlParameter, int> SqlCmdParDict = new Dictionary<SqlParameter, int>();
parmsTime.SqlCmd.CommandType = CommandType.StoredProcedure;
parmsTime.SqlCmd.CommandText = AppDb.MetaSqlSProc.Time.Name;
parmsTime.SP_Name = AppDb.MetaSqlSProc.Time.Name;
parmsTime.TableName = AppDb.MetaSqlTable.Time.Name;
//While folowing implementation Does Work I comented it out to try using the SP Struct
//ParmsTTime.SP_Params.Add(new SqlParameter(SPTime.ParNameMonth, 9));
//ParmsTTime.SP_Params.Add(new SqlParameter(SPTime.ParNameReasonid, 1));
//ParmsTTime.SP_Params.Add(new SqlParameter(SPTime.ParNameYear, 2012));
//ParmsTTime.SP_Params.Add(new SqlParameter(SPTime.ParNameUserid, 3571));
//here's where I'm currently stuck, in section below. trying to assign values for the SqlCommand
parmsTime.SqlCmd.Parameters.AddWithValue(SPTime.ParNameMonth, 9);
parmsTime.SqlCmd.Parameters.AddWithValue(SPTime.ParNameYear, 2012);
parmsTime.SqlCmd.Parameters.AddWithValue(SPTime.ParNameReasonid, 1);
SPTime.Userid.Direction = ParameterDirection.Input;
SPTime.Userid.SqlValue = 3571;
return DC_Time.LocalTbl_V3(ParmsTime);
}
UPDATE
The last lines of the code above is trying to implement the parameters assignment, thus it will no longer be required to use:
SQLDBInteraction.SqlParamList.SP_Params
(which is List<SqlParameter>
).
And instead, I would really like to be able to use SQLDBInteraction.SqlParamList.SqlCmd.Parameters
as it is already used for most of the required steps to interact with the database.
This is how I will drop some unnecessary usage of extra variables while at the same time I wanted to assign SqlCmd (parmsTime.SqlCmd.Parameters.Add(......)
) with the struct - SPTime
Real SqlParameters
instead of using the strings that represents their name (e.g. parameter.name
- (SPTime.ParNameMonth, someValue
)).
Final stage- SQL transaction
The SQLDBInteraction
class that does the transaction:
public class SQLDBInteraction
{
public class SqlParamList
{
public SqlCommand SqlCmd = new SqlCommand();
public List<SqlParameter> SP_Params = new List<SqlParameter>();
public String SP_Name;
public string TableName;
public string SelectCommand;
///public SqlCommandType SelectedCmdType;
}
public class DataContainer
{
public DataTable LocalTbl_V3(SqlParamList Params)
{
SqlConnection sqlConnection;
DataTable Usrs = new DataTable(Params.TableName);
SqlDataAdapter sqlDataAdapter;
using (sqlConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["HTConn"].ConnectionString))
{
sqlConnection.Open();
using (Params.SqlCmd.Connection = sqlConnection)
{
using (sqlDataAdapter = new SqlDataAdapter(Params.SqlCmd.CommandText, sqlConnection))
{
if (sqlDataAdapter.SelectCommand.Parameters.Count > 0 == false)
{
sqlDataAdapter.SelectCommand = Params.SqlCmd;
sqlDataAdapter.Fill(Usrs);
}
}
}
}
return Usrs;
}
I will really appreciate it if someone will find what am I doing wrong with the part of the stored procedure's parameters assigned to the SQL command.
DataTables
rather than typed entities? Is there a strong reason to use stored procedures? Your code is absolutely not thread-safe and has too much of unnecessary classes involved in the simplest operations like reading the data from DB. \$\endgroup\$ – almaz Dec 9 '12 at 18:14select * From Table
??, it has half a page or even almost a full page of code that makes a report of accouts \$\endgroup\$ – LoneXcoder Dec 9 '12 at 18:19SqlConnection
s ` andSqlDataAdapter
s andDataTable
s, you get a nice, strongly-typed collection of objects. \$\endgroup\$ – svick Dec 9 '12 at 18:47