I have created an IBM i to C# Database Class

I got sick and tired of typing (or copy-pasting) the following code for
every data access method:

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;
}

So I created a class that does a portion of that so I can cut down how much
I have to key. The following becomes:

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 = IbmISql.GetData(_connString, sb.ToString(),
param => {
param.Add("@custnumber1", iDB2DbType.iDB2Decimal).Value =
id;
param.Add("@custnumber2", iDB2DbType.iDB2Decimal).Value =
id;
});

#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;
}

True I didn't eliminate building the SQL statement nor building the object,
but take a look at a simpler example:

public IEnumerable<SystemCode> GetAllSystemCodes()
{
#region SQL Statement
string sb = "SELECT SYSTEMCODEID, DESCRIPTION,
DEFAULTRANSACTIONTYPE FROM EGOVLOG.SYSTEMCODE";
#endregion

#region Fill object from DataTable
var systemCodes = from i in IbmISql.GetData(_connString,
sb).AsEnumerable()
select new SystemCode
{
SystemCodeId =
i.Field<string>("SYSTEMCODEID").Trim(),
Description =
i.Field<string>("DESCRIPTION").Trim(),
DefaultTransactionType =
i.Field<int>("DEFAULTRANSACTIONTYPE")
};
#endregion

return systemCodes;
}

For comments and the code you can go to
http://mikewills.me/projects/ibm-i-to-c-database-class/. I haven't fully
vetted it nor am I handling any errors yet. I am thinking of changing this
to a full GitHub project so we can build more classes like this. Thoughts?

--
Mike Wills
http://mikewills.me