Skip to content

Instantly share code, notes, and snippets.

@MikeWills
Created December 9, 2011 15:44
Show Gist options
  • Save MikeWills/1452038 to your computer and use it in GitHub Desktop.
Save MikeWills/1452038 to your computer and use it in GitHub Desktop.
A generic method that can pull data from the IBM i to a DataTable for processing. Comments? http://mikewills.me/projects/ibm-i-to-c-database-class/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IBM.Data.DB2.iSeries;
namespace Mike.Wills.IbmI
{
public static class IbmISql
{
#region Get data
/// <summary>
/// Gets the data from the IBM i.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
/// <returns>DataTable</returns>
public static DataTable GetData(string connString, string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Connection conn = new iDB2Connection(connString))
{
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
conn.Open();
if (parameters != null) { parameters(cmd.Parameters); }
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
conn.Close();
}
}
return dt;
}
/// <summary>
/// Gets the data from the IBM i.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <returns>DataTable</returns>
public static DataTable GetData(string connString, string sqlStatement)
{
return GetData(connString, sqlStatement, null);
}
#endregion
#region Get data
/// <summary>
/// Executes a statement on the IBM i that doesn't return data (INSERT, UPDATE, DELETE)
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
public static void ExecuteNonQuery(string connString, string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
using (iDB2Connection conn = new iDB2Connection(connString))
{
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
conn.Open();
if (parameters != null) { parameters(cmd.Parameters); }
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
/// <summary>
/// Executes a statement on the IBM i that doesn't return data (INSERT, UPDATE, DELETE)
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
public static void ExecuteNonQuery(string connString, string sqlStatement)
{
ExecuteNonQuery(connString, sqlStatement, null);
}
#endregion
#region ExecuteStoredProcedure
/// <summary>
/// Executes a stored procedure on the IBM i that doesn't return data.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
public static void ExecuteStoredProcedure(string connString, string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
using (iDB2Connection conn = new iDB2Connection(connString))
{
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
if (parameters != null) { parameters(cmd.Parameters); }
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
/// <summary>
/// Executes a stored procedure on the IBM i that doesn't return data.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
public static void ExecuteStoredProcedure(string connString, string sqlStatement)
{
ExecuteStoredProcedure(connString, sqlStatement, null);
}
#endregion
}
}
public Customer GetUtilityBillCustomer(int id)
{
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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment