private static void LoadRecords(IfxConnection cn, string connectionString, string tableName)
{
DatabaseSchemaHelper databaseSchemaHelper = new DatabaseSchemaHelper(connectionString);
var schemaColumns = databaseSchemaHelper.GetColumns(tableName);
string[] columnNames = (from schemaColumn in schemaColumns select schemaColumn.ActualName).ToArray();
string joinedColumnNames = string.Join(", ", columnNames);
int numColumns = columnNames.Length;
// Load the .unl file (renamed to .txt and added to the project as a resource file).
string fileContents = Resources.Resources.ResourceManager.GetString(tableName);
string[] lines = fileContents.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
string[] placeHolders = new string[numColumns];
for (int i = 0; i < numColumns; i++) {
placeHolders[i] = "?";
}
string joinedPlaceHolders = string.Join(", ", placeHolders);
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, joinedColumnNames, joinedPlaceHolders);
for (int i = 0; i < numColumns; i++) {
cmd.Parameters.Add(columnNames[i], null);
}
foreach (var line in lines) {
string[] values = line.Split('|');
// "value1|value2|value3|" after splitting on '|' yields "value1", "value2", value3, ""
// i.e. there is an empty extra value at the end of the array.
// And, there is usually a blank line at the end of the file.
if (values.Length != (numColumns + 1)) {
break;
}
for (int i = 0; i < numColumns; i++) {
if (!string.IsNullOrEmpty(values[i])) {
// This is a temporary hack for the "lead_time" column of the "manufact" table.
// We should lookup the coltype and collength of the column
// using tableName and schemaColumns[i].ActualName. Then, if coltype
// is 14 (INTERVAL) then we need to look at collength to determine the
// detailed type of INTERVAL. Then, create a new instance of IfxTimeSpan
// or IfxMonthSpan and parse the string, values[i]. There may be other column types
// that need to be handled specially. I guess we need to test lots more
// different column types.
//
// SYSCOLUMNS
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_025.htm
//
// Storing Column Length
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_027.htm
//
// Using the Informix System Catalogs
// http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
//
// collength = 836 = 0x344
// length = 3
// first_qualifier = 4
// last_qualifier = 4
//
if (tableName == "manufact" && schemaColumns[i].ActualName == "lead_time") {
cmd.Parameters[i].Value = new IfxTimeSpan(Convert.ToInt32(values[i]), IfxTimeUnit.Day);
}
else {
cmd.Parameters[i].Value = values[i];
}
}
else {
cmd.Parameters[i].Value = DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}
private static void LoadRecords(IfxConnection cn, string connectionString, string tableName)
{
DatabaseSchemaHelper databaseSchemaHelper = new DatabaseSchemaHelper(connectionString);
var schemaColumns = databaseSchemaHelper.GetColumns(tableName);
string[] columnNames = (from schemaColumn in schemaColumns select schemaColumn.ActualName).ToArray();
string joinedColumnNames = string.Join(", ", columnNames);
int numColumns = columnNames.Length;
// Load the .unl file (renamed to .txt and added to the project as a resource file).
string fileContents = Resources.Resources.ResourceManager.GetString(tableName);
string[] lines = fileContents.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
string[] placeHolders = new string[numColumns];
for (int i = 0; i < numColumns; i++)
{
placeHolders[i] = "?";
}
string joinedPlaceHolders = string.Join(", ", placeHolders);
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, joinedColumnNames, joinedPlaceHolders);
for (int i = 0; i < numColumns; i++)
{
cmd.Parameters.Add(columnNames[i], null);
}
foreach (var line in lines)
{
string[] values = line.Split('|');
// "value1|value2|value3|" after splitting on '|' yields "value1", "value2", value3, ""
// i.e. there is an empty extra value at the end of the array.
// And, there is usually a blank line at the end of the file.
if (values.Length != (numColumns + 1))
{
break;
}
for (int i = 0; i < numColumns; i++)
{
if (!string.IsNullOrEmpty(values[i]))
{
// This is a temporary hack for the "lead_time" column of the "manufact" table.
// We should lookup the coltype and collength of the column
// using tableName and schemaColumns[i].ActualName. Then, if coltype
// is 14 (INTERVAL) then we need to look at collength to determine the
// detailed type of INTERVAL. Then, create a new instance of IfxTimeSpan
// or IfxMonthSpan and parse the string, values[i]. There may be other column types
// that need to be handled specially. I guess we need to test lots more
// different column types.
//
// SYSCOLUMNS
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_025.htm
//
// Storing Column Length
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_027.htm
//
// Using the Informix System Catalogs
// http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
//
// collength = 836 = 0x344
// length = 3
// first_qualifier = 4
// last_qualifier = 4
//
if (tableName == "manufact" && schemaColumns[i].ActualName == "lead_time")
{
cmd.Parameters[i].Value = new IfxTimeSpan(Convert.ToInt32(values[i]), IfxTimeUnit.Day);
}
else
{
cmd.Parameters[i].Value = values[i];
}
}
else
{
cmd.Parameters[i].Value = DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}