3
\$\begingroup\$

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?

\$\endgroup\$
3
\$\begingroup\$

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.
\$\endgroup\$
  • \$\begingroup\$ I was wondering how the lamda stuff works. I'll see how that works. \$\endgroup\$ – Mike Wills Dec 9 '11 at 15:03
  • \$\begingroup\$ Oh man! Coding is so much easier with this! Thank you for the help! \$\endgroup\$ – Mike Wills Dec 9 '11 at 15:22
  • \$\begingroup\$ Lambda is a bit funny in the beginning, but it's so useful! :) \$\endgroup\$ – ANeves Dec 9 '11 at 15:30
  • \$\begingroup\$ Can I add a second +1? Thanks! \$\endgroup\$ – Mike Wills Dec 9 '11 at 15:38
  • \$\begingroup\$ p.s., Predicate<T> is a Func<T, bool>. ;) Also you declared the lambda to accept an IEnumerable<T> so you won't be able to add items to it. Based on the usage, it should be the type of whatever cmd.Parameters is. \$\endgroup\$ – Jeff Mercado Dec 9 '11 at 16:12
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ I would have to write the whole framework from scratch since the driver I use does not support EF. \$\endgroup\$ – Mike Wills Dec 9 '11 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.