Using a Dedicated Class & Constructor to initialize Stored Procedure for SQL Transaction

Question

as i thought it will be sutible as a Quetion for SO...

i started to describe the issue of my Problem, though as i completed Editing the post , i could see that it will better fit here, In CR.

so to sumarraize it, 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 databse transactions .

a side note

to assist you with your answer, I've pasted the folowing (just for Reference)

a Usage-Code : GetTestOfTablTime() returns a DataTable

class : SQLDBInteraction this 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)

this class is holding each SP _(requierd) parameters

_{HTSPs class contains another sub Class, for all SPs Names, it only has const strings 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();            
    }
}

so the issue is: how do I initialize the constractor ?

what is the Proper way to have your simple customised StoredProcedure "MetaData"

Iv'e 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 / constuctor

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 parmeters 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

that way as it is already used for most of the requierd steps to interact with the Database,

so this is how i will drop some unnecessery usage of extra variables

while in same time i wanted to assign SqlCmd ( parmsTime.SqlCmd.Parameters.Add(......))

with the Struct - SPTime Real SqlParameters instead of using the strings that reperesnts their name ..

E.g parameter.name - (SPTime.ParNameMonth, someValue)

final stage- sql trasaction

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

Answer 1

Maybe you are trying to do something like this

public interface DbProc
{
    public SqlCommand GetCommand();
}

public class MyProc : DbProc
{
    private SqlCommand _cmd;

    public const string COMMAND_TEXT = "spc_MyProc";
    public const string PARAM1 = "@Param1";
    public const string PARAM2 = "@Param2";        

    public MyProc(int param1, string param2)
    {
        _cmd = new SqlCommand(COMMAND_TEXT);
        _cmd.CommandType = CommandType.StoredProcedure;
        _cmd.Parameters.AddWithValue(PARAM1, param1);
        _cmd.Parameters.AddWithValue(PARAM2, param2);
    }

    public SqlCommand GetCommand()
    {
        return _cmd;
    }
}

public class Db
{
    public DataTable ExecuteProc(DbProc proc)
    {
        SqlCommand cmd = proc.GetCommand();
        ConfigureCommand(cmd);
        // execute the command into a data table
        return result;
    }

    // Set the common settings for all commands
    private void ConfigureCommand(SqlCommand cmd)
    {
        cmd.CommandTimeout = 10000;            
    }
}

Answer 2

Just to give you idea how your code would look like if you use Entity Framework:

using (var db = new HpDatabaseContext())
{
    return db.GetTimesWithCustomerNames(userId, month, year, reasonId).ToArray();
}

Answer 3

this is aventually what i am using (atleast For now)

...as there will ,i guess.. always be, further developments.

i will mark this as the answer soon , though you are still very welcome to post comments, or better yet, your improved version .

anyways this is an example for how a stored procedure is stored in my "DbSchema" namespace

    public static class RobSelect_Update_tblTC_CPA_ByDDL_paramNames
    {
        static SqlParameter aDepartment, bUid, cMonth, dYear, eUpdateFirst;

        public static List<SqlParameter> SqlParlstCPA(string SelectedDepartmentID, string SelectedUserID, string SelectedMonth, string selectedYear, string AbitValuIfUpdateFirst)
        {
            aDepartment = new SqlParameter( a_UsrDepartment, SelectedDepartmentID);
            bUid = new SqlParameter(b_UsrID, SelectedUserID);
            cMonth = new SqlParameter(c_Month, SelectedMonth);
            dYear = new SqlParameter(d_Year, selectedYear);
            eUpdateFirst = new SqlParameter(e_Updateit_bit, AbitValuIfUpdateFirst);


            List<SqlParameter> RetSqlParLst  = new List<SqlParameter>();
            RetSqlParLst.Add(aDepartment);
            RetSqlParLst.Add(bUid);
            RetSqlParLst.Add(cMonth);
            RetSqlParLst.Add(dYear);
            RetSqlParLst.Add(eUpdateFirst);

            return RetSqlParLst;
        }
        static readonly string a_UsrDepartment = "@Department",
                     b_UsrID = "@Uid",
                     c_Month = "@month",
                     d_Year = "@year",
                     e_Updateit_bit = "@updateItFirst";
    }