none
Don't Repeat Yourself (DRY) question RRS feed

  • Question

  • Hello,

    I have several WCF operations.  All operations return different data types and take different parameters.  A peer is saying that I have violated the DRY principal.  He says that my SQL calls should be combined into one method.  Even further, he wants me to put that one method in a new class. 

    Specifically, he says he's tired of seeing the conn=new SqlConnection and cmd= new SqlCommand(query) in every operation. 

    Combining the SQL commands into a new class doesn't make much sense to me and I think would create a mess.  In my opinion, the current operations are clean and easy to understand.

    I'd be very grateful for other opinions.

    public List<DateType1> GetDataType1(string account, AccountType_Enums accountType, DateTime begDate, DateTime endDate)
    {
       SqlConnection conn= null;
       SqlCommand cmd= null;
      
       List<DateType1> list= null;

       try
       {
          list= new List<DateType1>();

          string query= "Select Balance, DateAdded, Sequence from tTable1 where AcctNumber=@account and AcctType=@acctType and DateOccurred>=@begDate and DateOccurred<=@endDate";
          conn= new SqlConnection(...);
          cmd= new SqlCommand(query,conn);
          cmd.Parameters.AddWithValue("@account",account);
          if( accountType==AccountType_Enums.DDA)
             cmd.Parameters.AddWithValue("@acctType", "DD");
          else
             cmd.Parameters.AddWithValue("@acctType", SV");
          cmd.Parameters.AddWithValue("@begDate", begDate);
          cmd.Parameters.AddWithValue("@endDate", endDate);
          conn.Open();
          ......
         
       }
     }

    public List<DateType2> GetDateType2(int mobileID, string account, DateTime begDate, DateTime endDate)
    {
       SqlConnection conn= null;
       SqlCommand cmd= null;
      
       List<DateType2> list= null;

       try
       {
          list= new List<DateType2>();

          string query= "Select TransactionType,DatePosted,Amount from tTable2 where MobileID=@mobileID and Account=@account and DateOccurred>=@begDate and DateOccurred<=@endDate";
          conn= new SqlConnection(...);
          cmd= new SqlCommand(query,conn);
          cmd.Parameters.AddWithValue("@mobileID",mobileID);
          cmd.Parameters.AddWithValue("@account",account);
          cmd.Parameters.AddWithValue("@begDate", begDate);
          cmd.Parameters.AddWithValue("@endDate", endDate);
          conn.Open();
          ......
         
       }
    }

    Thank you very much!




    • Edited by Hiline1961 Friday, September 19, 2014 10:31 PM
    Friday, September 19, 2014 10:27 PM

Answers

  • The thing is that you should create a class which creates the SqlConnection and SqlCommand objects and connects to the database and then use this one in all service methods. Something like this:

    public class MyDal
    {
     public static DataSet GetData(string query, Dictionary<string, object> params)
     {
      ...connect the the db and return a DataSet
     }
    }

     

     

    public List<DateType2> GetDateType2(int mobileID, string account, DateTime begDate, DateTime endDate)
    {
    string query= "Select TransactionType,DatePosted,Amount from tTable2 where MobileID=@mobileID and  Account=@account and DateOccurred>=@begDate and DateOccurred<=@endDate";
    
    Dictionary<string, object> params = new Dictionary<string, object>();
    params.Add("@mobileID",mobileID);
    
    MyDal.GetData(query, params);
    
    }
    

    • Marked as answer by Hiline1961 Monday, September 22, 2014 4:52 PM
    Monday, September 22, 2014 3:18 PM

All replies

  • So what is your question in here?

    For it you should post to asp.net forum
    http://forums.asp.net/

    Monday, September 22, 2014 5:56 AM
  • Not sure why I would put it in the ASP.NET forum since the two operations are WCF operations.  Maybe I should have put it under general .NET questions.

    But, my question was that if I have multiple SQL operations does it make sense to try and consolidate all the calls into one method?  I think that takes the DRY concept to far.

    In my mind, it's clearer to keep them in separate operations.

    I was just seeking other opinions.

    Thanks,

    Monday, September 22, 2014 3:10 PM
  • The thing is that you should create a class which creates the SqlConnection and SqlCommand objects and connects to the database and then use this one in all service methods. Something like this:

    public class MyDal
    {
     public static DataSet GetData(string query, Dictionary<string, object> params)
     {
      ...connect the the db and return a DataSet
     }
    }

     

     

    public List<DateType2> GetDateType2(int mobileID, string account, DateTime begDate, DateTime endDate)
    {
    string query= "Select TransactionType,DatePosted,Amount from tTable2 where MobileID=@mobileID and  Account=@account and DateOccurred>=@begDate and DateOccurred<=@endDate";
    
    Dictionary<string, object> params = new Dictionary<string, object>();
    params.Add("@mobileID",mobileID);
    
    MyDal.GetData(query, params);
    
    }
    

    • Marked as answer by Hiline1961 Monday, September 22, 2014 4:52 PM
    Monday, September 22, 2014 3:18 PM
  • Thank you, Magnus.  That's very interesting and you've given me much to think about.  Not sure I'm comfortable using a static method in the DAL but everything else is definitely something I'll be discussing with my peer.

    Nice job and thanks again!

    Monday, September 22, 2014 4:52 PM