How can I convert this to a generic method?

Question

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).

1. How can I make it better?
2. 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<int>("ACUSTN"),
                        Pin = i.Field<int>("AWEBPN"),
                        Name = i.Field<string>("ANAME").Trim(),
                        Address1 = i.Field<string>("AADD1").Trim(),
                        Address2 = i.Field<string>("AADD1").Trim(),
                        City = i.Field<string>("ACITY").Trim(),
                        State = i.Field<string>("ASTATE").Trim(),
                        Zip5 = i.Field<string>("AZIP5").Trim(),
                        Zip4 = i.Field<string>("AZIP4").Trim(),
                        LocationNumber = i.Field<int>("ALOCAT"),
                        ServiceAddress = i.Field<string>("SERVICEADDR").Trim(),
                        BalanceDue = i.Field<decimal>("BALANCE"),
                        Email = i.Field<string>("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?

Answer 1

You could receive a lambda that adds parameters.

public static DataTable GetData(string connString, string sqlStatement, Action<iDB2ParameterCollection> 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 => {
        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<T> is the same as Func<T, bool> but a bit more semantically significant.

Answer 2

Mike, You can also create an Entity Model from your SQL database and query to that. Entity Framework 4.1 uses Lambda's effectively to create any type of query or even update your db. You can use Eager Loading to request related data.

Julie Lerman's Entity Framework book is excellent with lots of code samples.