/// <summary>New 04/07/11 - split field values containing nested delimiters into a new table (named as tableName_columnName_split)</summary>
/// <param name="dbName">Database file name</param>
/// <param name="tableName">Database table name</param>
/// <param name="columnName">Name of field containing nested delimiters</param>
/// <param name="keyColumnName">Name of the key (ID) field from the specified table</param>
/// <param name="delimiter">Character used as delimiter in the column</param>
/// <returns>Number of new rows created</returns>
public static int DBColumnSplit(string dbName, string tableName, string columnName, string keyColumnName, char delimiter)
{
// (re)create the split table as tableName_columnName_split
String newTableName = tableName.Trim() + "_" + columnName.Trim() + "_split";
DBEngineBase db = new SQLiteDBEngine(dbName);
db.executeNonQuery(String.Format("DROP TABLE IF EXISTS \"{0}\";", newTableName));
db.executeNonQuery(String.Format("CREATE TABLE \"{0}\" (\"{1}\" TEXT COLLATE NOCASE, \"{2}\" TEXT COLLATE NOCASE);", newTableName, keyColumnName, columnName));
//get a DataTable of the values of the ID column and the delimited column
DataTable dt1 = db.select(String.Format("SELECT {0}, {1} FROM {2} WHERE {1} IS NOT NULL", keyColumnName, columnName, tableName));
// Create a new DataTable (same structure) to hold the split values
DataTable dt2 = dt1.Clone();
dt2.Clear();
dt2.TableName = newTableName;
//Split the values from dt1 and put them into dt2
char[] delimiters = new char[] {delimiter};
foreach(DataRow dr in dt1.Rows)
{
String[] splitValues = dr[columnName].ToString().Split(delimiters);
foreach(String s in splitValues)
{
String[] fieldValues = new String[2];
fieldValues[0] = dr[keyColumnName].ToString();
fieldValues[1] = s.Trim();
if (fieldValues[1] != String.Empty)
dt2.Rows.Add(fieldValues);
}
}
//now insert all the data
return db.bulkInsert(dt2);
}
//Write DataTable to database, recreate the table first to ensure structure is correct
public static int DT2DB(String dbName, DataTable table, bool append)
{
// Why didnt I use IDBEngine here? test...
// IDBEngine db = new SQLiteDBEngine(dbName);
DBEngineBase db = new SQLiteDBEngine(dbName);
if (!append)
{
//Drop the table if it already exists
db.executeNonQuery(String.Format("DROP TABLE IF EXISTS \"{0}\";", table.TableName));
//Create the table based on the structure of the DataTable
String sql = String.Format("CREATE TABLE \"{0}\" (", table.TableName);
foreach (DataColumn dc in table.Columns)
{
//Column names should already be valid (CSV2DT cleaned them up)
//sql += String.Format("\"{0}\" TEXT NOT NULL DEFAULT '',", dc.ColumnName);
//sql += String.Format("\"{0}\" TEXT,", dc.ColumnName);//04/03/2010 - ensure case insensitive comparisons
sql += String.Format("\"{0}\" TEXT COLLATE NOCASE,", dc.ColumnName);
}
//remove the last comma, complete and run the command
sql = sql.Remove(sql.LastIndexOf(',')) + ");";
db.executeNonQuery(sql);
}
//Created the table, now insert the data
return db.bulkInsert(table);
}
// Execute SQL command(s) against named database -
// used for data cleansing prior to data extraction
public static int SQLExecute(string dbName, string sqlFileName)
{
//Tidy up input parameters
dbName = dbName.Trim();
sqlFileName = sqlFileName.Trim();
//Fail if dbName or sqlFileName not passed in
if (dbName == String.Empty)
throw new ArgumentException("database name required", "dbName");
if (sqlFileName == String.Empty)
throw new ArgumentException("SQL file name required", "sqlFileName");
String sql = System.IO.File.ReadAllText(sqlFileName);
DBEngineBase db = new SQLiteDBEngine(dbName);
return db.executeNonQuery(sql);
}