How can I convert this to a generic method? I have this code I used to to pull data from our IBM i into .NET into an object so it is easy to work with (avoiding DataTables).


How can I make it better?
How can I convert all/most of this into a generic reusable method?


Here is an example of a method I have written:

public Customer GetUtilityBillCustomer(int id)
{
    #region SQL Statement
    StringBuilder sb = new StringBuilder();
    sb.Append("SELECT ACUSTN, AWEBPN, ANAME, AADD1, AADD2, ACITY, ASTATE, AZIP5, AZIP4, AEMADR, ALOCAT, ");
    sb.Append("(TRIM(WTMETER.MADRSN) || ' ' || TRIM(WTMETER.MADRSA) || ' ' || TRIM(WTMETER.MADRSX)) AS SERVICEADDR, ");
    sb.Append("(AARR1 + AARR2 + AARR3) AS BALANCE ");
    sb.Append("FROM WTCUST, WTMETER ");
    sb.Append("WHERE ALOCAT = MLOCAT AND ACUSTN = @custnumber1 ");
    sb.Append("UNION ");
    sb.Append("SELECT FCUSTN, FWEBPN, FNAME, FADD1, FADD2, FCITY, FSTATE, FZIP5, FZIP4, FEMADR, FLOCAT, ");
    sb.Append("(TRIM(WTMETER.MADRSN) || ' ' || TRIM(WTMETER.MADRSA) || ' ' || TRIM(WTMETER.MADRSX)) AS SERVICEADDR, WTFINAL.FAMTDU AS BALANCE ");
    sb.Append("FROM WTFINAL, WTMETER ");
    sb.Append("WHERE FLOCAT = MLOCAT AND FCUSTN = @custnumber2");
    #endregion

    DataTable dt = new DataTable();
    using (iDB2Connection conn = new iDB2Connection(_connString))
    {
        using (iDB2Command cmd = new iDB2Command(sb.ToString(), conn))
        {
            conn.Open();
            cmd.Parameters.Add("@custnumber1", iDB2DbType.iDB2Decimal).Value = id;
            cmd.Parameters.Add("@custnumber2", iDB2DbType.iDB2Decimal).Value = id;
            using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
            conn.Close();
        }
    }

    #region Fill object from DataTable
    var customer = (from i in dt.AsEnumerable()
                    select new Customer
                    {
                        Id = i.Field&lt;int&gt;("ACUSTN"),
                        Pin = i.Field&lt;int&gt;("AWEBPN"),
                        Name = i.Field&lt;string&gt;("ANAME").Trim(),
                        Address1 = i.Field&lt;string&gt;("AADD1").Trim(),
                        Address2 = i.Field&lt;string&gt;("AADD1").Trim(),
                        City = i.Field&lt;string&gt;("ACITY").Trim(),
                        State = i.Field&lt;string&gt;("ASTATE").Trim(),
                        Zip5 = i.Field&lt;string&gt;("AZIP5").Trim(),
                        Zip4 = i.Field&lt;string&gt;("AZIP4").Trim(),
                        LocationNumber = i.Field&lt;int&gt;("ALOCAT"),
                        ServiceAddress = i.Field&lt;string&gt;("SERVICEADDR").Trim(),
                        BalanceDue = i.Field&lt;decimal&gt;("BALANCE"),
                        Email = i.Field&lt;string&gt;("AEMADR")
                    }).SingleOrDefault();
    #endregion

    return customer;
}


I am thinking along the lines of:

public static DataTable GetData(string connString, string sqlStatement)
{
    DataTable dt = new DataTable();

    using (iDB2Connection conn = new iDB2Connection(connString))
    {
        using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
        {
            conn.Open();
            cmd.Parameters.Add("@custnumber1", iDB2DbType.iDB2Decimal).Value = id;
            cmd.Parameters.Add("@custnumber2", iDB2DbType.iDB2Decimal).Value = id;
            using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
            conn.Close();
         }
    }

    return dt;
}


But how do I pass in an unknown list of parameters and parameter types? Would I use an array of objects? Other thoughts?
You could receive a lambda that adds parameters.

public static DataTable GetData(string connString, string sqlStatement, Action&lt;iDB2ParameterCollection&gt; addParameters)
{
    DataTable dt = new DataTable();

    using (iDB2Connection conn = new iDB2Connection(connString))
    {
        using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
        {
            conn.Open();
            if(addParameters != null) {
                addParameters(cmd.Parameters);
            }
            using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
            conn.Close();
         }
    }

    return dt;
}

//...
GetData(connStr, sqlStatement, parameters =&gt; {
        parameters.Add("@custnumber1", iDB2DbType.iDB2Decimal).Value = id;
        parameters.Add("@custnumber2", iDB2DbType.iDB2Decimal).Value = id;
    });




Edit: I suggest reading about the different delegates: Action; Func; Predicate.
https://stackoverflow.com/questions/566860/delegates-predicate-action-func

In a nutshell:


Action does something;
Func is the same as an Action, but with a return value;
Predicate&lt;T&gt; is the same as Func&lt;T, bool&gt; but a bit more semantically significant.