C# Abstracting methods

I have a set of methods, that take in dates and a bool. These methods then use some SQL then pass it on to another method and return a list. Each method is pretty much the same with the exception of the SQL and the List returned. Now I know there is a better way do these methods but I'm not sure how.

Do开发者_开发技巧es anyone have any ideas as to how I can abstract this or use a better design as the methods are almost identical?

Here is the code:

private List<ENT_Message> GetMessageData(DateTime? startDate, DateTime? endDate, bool IsSelectAll)
{
    List<ENT_Message> ret = new List<ENT_Message>();
    string sql = "";

    if (IsSelectAll)
    {
        sql = "select * from tbl_message";
    }
    else
    {
        sql = " Select * from tbl_message where created_Date between @start_Date and @end_date";
    }

    return Converter.SerializeToMessageList(this.GetData(startDate, endDate, IsSelectAll, sql));                   
}

private List<ENT_SensorData> GetSensorData(DateTime? startDate, DateTime? endDate, bool IsSelectAll)
{
    List<ENT_SensorData> ret = new List<ENT_SensorData>();
    string sql = "";

    if (IsSelectAll)
    {
        sql = "select * from tbl_sensor_data";
    }
    else
    {
        sql = "select * from tbl_sensor_data where Timestamp between @start_date and @end_Date";
    }

    return Converter.SerializeToSensorDataList(this.GetData(startDate, endDate, IsSelectAll, sql)); 
}

private List<ENT_SensorDataEvent> GetSensorDataEvents(DateTime? startDate, DateTime? endDate, bool IsSelectAll)
{
    List<ENT_SensorDataEvent> ret = new List<ENT_SensorDataEvent>();
    string sql = "";

    if (IsSelectAll)
    {
        sql = "select * from tbl_sensor_data_event";
    }
    else
    {
        sql = "select * from tbl_sensor_data_event where start_time between @start_date and @end_Date";
    }

    return Converter.SerializeToSensorEventDataList(this.GetData(startDate, endDate, IsSelectAll, sql)); 
}

private List<ENT_SensorDataState> GetSensorDataState(DateTime? startDate, DateTime? endDate, bool IsSelectAll)
{
    List<ENT_SensorDataState> ret = new List<ENT_SensorDataState>();
    string sql = "";

    if (IsSelectAll)
    {
        sql = "select * from tbl_sensor_data_state";
    }
    else
    {
        sql = "select * from tbl_sensor_data_state where start_time between @start_date and @end_Date";
    }

    return Converter.SerializeToSensorDateStateList(this.GetData(startDate, endDate, IsSelectAll, sql)); 
}

private List<ENT_WorkOrder> GetWorkOrders(DateTime? startDate, DateTime? endDate, bool IsSelectAll)
{
    List<ENT_WorkOrder> ret = new List<ENT_WorkOrder>();
    string sql = "";

    if (IsSelectAll)
    {
        sql = "select * from tbl_workorder";
    }
    else
    {
        sql = "select * from tbl_sensor_data_state where completed_date between @start_date and @end_Date";
    }

    return Converter.SerializeToWorkOrderList(this.GetData(startDate, endDate, IsSelectAll, sql));   
}

You could shorten your functions considerably. For example, instead of

private List<ENT_SensorDataState> GetSensorDataState(DateTime? startDate, DateTime? endDate, bool IsSelectAll)
{
    List<ENT_SensorDataState> ret = new List<ENT_SensorDataState>();
    string sql = "";

    if (IsSelectAll)
    {
        sql = "select * from tbl_sensor_data_state";
    }
    else
    {
        sql = "select * from tbl_sensor_data_state where start_time between @start_date and @end_Date";
    }

    return Converter.SerializeToSensorDateStateList(this.GetData(startDate, endDate, IsSelectAll, sql));
}

you could simply write

private List<ENT_SensorDataState> GetSensorDataState(DateTime? startDate, DateTime? endDate, bool IsSelectAll)
{
    string sql = "select * from tbl_sensor_data_state"
                 + (IsSelectAll ? "" : "where start_time between @start_date and @end_Date");

    return Converter.SerializeToSensorDateStateList(this.GetData(startDate, endDate, IsSelectAll, sql));
}

That basically reduces your function to the stuff that really matters: The SQL and the name of the serialization function.

It looks like you have an opportunity to make a generic method. I apologize in advance if I have syntax errors.

private List<T> GetData<T>(DateTime? startDate, DateTime? endDate, bool IsSelectAll) where T : ENT_Data
{
    List<T> ret = new List<T>();
    string sql = "";

    if (IsSelectAll)
    {
        sql = "select * from " + T.table;
    }
    else
    {
        sql = " Select * from " + T.table + " where created_Date between @start_Date and @end_date";
    }

    return Converter.Serialize<T>(this.GetData(startDate, endDate, IsSelectAll, sql));    
}

Notice a few things I did:

1. The generic method has a constraint that T is a subclass of ENT_Data, a type I just made up.
2. I gave ENT_Data types a table field, for use in your query.
3. The Converter.Serialize methods have also been made generic in a similar manner.

If I missed anything important just let me know.

private List<T> GetData<T>(DateTime? startDate, DateTime? endDate, bool IsSelectAll)
        {
            List<T> ret = new List<T>();

            string tableName = "";//Select the table name based on T here
            string sql = "";

            if (IsSelectAll)
            {
                sql = "select * from tbl_sensor_data";
            }
            else
            {
                sql = "select * from tbl_sensor_data where Timestamp between @start_date and @end_Date";
            }
            //Build your list using the appropriate converter based on T here        
            return Converter.SerializeToSensorDataList(this.GetData(startDate, endDate, IsSelectAll, sql)); 
        }

From that point on, you call

GetData<ENT_whatever>() 

only.

You can create a class SqlRetriever, and then subclass each one with the specific SQL message to send.

class SqlRetriever {
    public abstract void SendSqlCmd();
    public ArrayList List {
        get; set;
    }
}

class SqlRetrieverXXX
    public const string SqlCmd = " ... ";
    public override void SendSqlCmd()
    {
       /* ... */
    }
}

Another possibility, involving less classses, with a worse design is the following one:

class SqlRetriever {
        public const string SQLCmd1 = "...";
        public const string SQLCmd2 = "...";

        public void SendSqlCmd(string sqlcmd)
        {
             /*Send the SQL Cmd and store the result in the list */
        }

        public ArrayList List {
            get; set;
        }

        public static ArrayList DoSqlCmd(string sqlCmd)
        {
             var obj = new SqlRetriever();

             obj.SendSqlCmd( sqlCmd );

             return obj.List;
        }
}

If you store your sql statements and a delegate method in a map where the key is some type T, then make your method generic (method takes type T and returns List of T), you can do a map lookup to get the correct sql statements and delegate method.

You could have one base class all your ENT_* classes inherit from. Then make one method List<ENT_Base>GetData(yourParams). Create a factory method which gets you a list of entities depending on the type of ENT_Base. Your GetData() calls that and returns it. You will not get around a switch-case or if-then to figure out which kind of entities you have to return.