/// <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); }
/// <summary>List the columns in a SQLITE database table</summary> /// <param name="dbName">Database file name</param> /// <param name="tableName">Database table name</param> /// <returns>Array of database column names</returns> /// <exception cref="System.ArgumentException">Throws an argument exception if dbName or tableName are not supplied</exception> public static String[] DBColumns(String dbName, String tableName) { //Tidy up input parameters dbName = dbName.Trim(); tableName = tableName.Trim(); //Fail if dbName or tableName not passed in if (dbName == String.Empty) throw new ArgumentException("database name required", "dbName"); if (tableName == String.Empty) throw new ArgumentException("table name required", "tableName"); //Get the list of column names for the specified table DBEngineBase db = new SQLiteDBEngine(dbName); return db.columns(tableName); }
public static DataTable SQL2DT(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"); //Do the query String sql = System.IO.File.ReadAllText(sqlFileName); DBEngineBase db = new SQLiteDBEngine(dbName); return db.select(sql); }
//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); }