/// <summary>
/// Get Metadata information about the tables in a schema in the current database
/// </summary>
/// <param name="schema">Name of the schema in the database.</param>
/// <returns></returns>
public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
{
SchemaTablesMetaData result = new SchemaTablesMetaData();
result.schemaName = schema;
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
{
foreach (DataRow row in dt.Rows)
{
TableMetaData table = new TableMetaData();
table.tableName = row[2].ToString();
result.AddTable(table);
}
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
}
return result;
}
/// <summary>
/// Creates a SQL INSERT sentence to insert a row of data to a table in the target database.
/// </summary>
/// <param name="table">Table metadata from the table to copy</param>
/// <returns>SQL Sentence of the insertion in the target database</returns>
private string CreateInsertQuery(TableMetaData table)
{
string csvColumList = string.Empty;
string csvParamsList = string.Empty;
foreach (ColumnMetaData column in table.columns)
{
csvColumList = String.Format("{0}{1},", csvColumList, SchemaTablesMetaData.EscapeIdentifier(column.columnName));
csvParamsList = String.Format("{0}@{1},", csvParamsList, SchemaTablesMetaData.HexString(column.columnName));
}
string query = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", SchemaTablesMetaData.EscapeIdentifier(table.tableName), csvColumList.Remove(csvColumList.Length - 1), csvParamsList.Remove(csvParamsList.Length - 1));
return(query);
}
/// <summary>
/// Dumps table contents obtained from a OleDbDataReader into the target database
/// </summary>
/// <param name="table">Table metadata from where the DataReader is obtained</param>
/// <param name="reader">DataReader that provides the original data</param>
public override void DumpTable(TableMetaData table, DbDataReader reader)
{
SQLiteTransaction transaction = null;
string currentQuery = string.Empty;
try
{
using (SQLiteConnection connector = new SQLiteConnection(connectionString))
{
connector.Open();
using (SQLiteCommand Cmd = new SQLiteCommand())
{
Cmd.CommandText = CreateInsertQuery(table);
Cmd.Connection = connector;
CreateSqlCommandParameters(table, Cmd);
int countRecords = 0;
transaction = connector.BeginTransaction();
while (reader.Read())
{
foreach (ColumnMetaData colum in table.columns)
{
SetSqlCommandParameterValueForColumn(colum, Cmd, reader);
}
currentQuery = Cmd.CommandText;
Cmd.ExecuteNonQuery();
if (countRecords++ % 100 == 0)
{
System.Console.Out.Write(".");
}
}
transaction.Commit();
System.Console.Out.WriteLine("done");
System.Console.Out.WriteLine("Table dump complete, {0} records copied.", countRecords);
}
connector.Close();
}
}
catch (SQLiteException ex)
{
if (null != transaction)
{
transaction.Rollback();
}
Console.Out.WriteLine("SQLite Exception dumping table {0} : {1}\n{2}", table.tableName, ex.Message, currentQuery);
}
}
/// <summary>
/// Get Metadata information about the tables in a schema in the current database
/// </summary>
/// <param name="schema">Name of the schema in the database.</param>
/// <returns></returns>
public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
{
SchemaTablesMetaData result = new SchemaTablesMetaData();
result.schemaName = schema;
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
{
foreach (DataRow row in dt.Rows)
{
TableMetaData table = new TableMetaData();
table.tableName = row[2].ToString();
result.AddTable(table);
}
}
// get all views fo ms access db
DataTable dt2 = connector.GetSchema("Views");
foreach (DataRow row in dt2.Rows)
{
string queryText = (string)row["VIEW_DEFINITION"];
queryText = queryText.Replace(System.Environment.NewLine, " ");
string queryName = (string)row["TABLE_NAME"];
Console.WriteLine(queryName);
ViewMetaData vm = new ViewMetaData();
vm.ViewName = queryName;
vm.ViewQuery = queryText;
result.views.Add(vm);
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
}
return(result);
}
/// <summary>
/// Copy the table contents of a table in the current database into a table with the same name in an SQLite DB
/// </summary>
/// <param name="table">Metadata of the table to copy</param>
/// <param name="target">SQLite backend that will be used to copy the contents of the table</param>
public override void DumpTableContents(TableMetaData table, IDBBackEnd target)
{
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (OleDbCommand cmd = connector.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM [{0}]", table.tableName);
using (OleDbDataReader db = cmd.ExecuteReader())
{
target.DumpTable(table, db);
}
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("MSAccess Exception dumping table {0} : {1}", table.tableName, ex.Message);
}
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="reader"></param>
public override void DumpTable(TableMetaData table, DbDataReader reader)
{
throw new NotImplementedException();
}
/// <summary>
/// Get Metadata information about the tables in a schema in the current database
/// </summary>
/// <param name="schema">Name of the schema in the database.</param>
/// <returns></returns>
public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
{
SchemaTablesMetaData result = new SchemaTablesMetaData();
result.schemaName = schema;
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
{
foreach (DataRow row in dt.Rows)
{
TableMetaData table = new TableMetaData();
table.tableName = row[2].ToString();
result.AddTable(table);
}
}
// get all views fo ms access db
DataTable dt2 = connector.GetSchema("Views");
foreach (DataRow row in dt2.Rows)
{
string queryText = (string)row["VIEW_DEFINITION"];
queryText = queryText.Replace(System.Environment.NewLine," ");
string queryName = (string)row["TABLE_NAME"];
Console.WriteLine(queryName);
ViewMetaData vm = new ViewMetaData();
vm.ViewName = queryName;
vm.ViewQuery = queryText;
result.views.Add(vm);
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
}
return result;
}
/// <summary>
/// Copy the table contents of a table in the current database into a table with the same name in an SQLite DB
/// </summary>
/// <param name="table">Metadata of the table to copy</param>
/// <param name="target">SQLite backend that will be used to copy the contents of the table</param>
public override void DumpTableContents(TableMetaData table, IDBBackEnd target)
{
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (OleDbCommand cmd = connector.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM [{0}]", table.tableName);
using (OleDbDataReader db = cmd.ExecuteReader())
{
target.DumpTable(table, db);
}
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("MSAccess Exception dumping table {0} : {1}", table.tableName, ex.Message);
}
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
public override void QueryTableDefinition(TableMetaData table)
{
throw new NotImplementedException();
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="target"></param>
public override void DumpTableContents(TableMetaData table, IDBBackEnd target)
{
throw new NotImplementedException();
}
/// <summary>
/// Adds a table to the schema collection.
/// </summary>
/// <param name="table">Table Metadata info to add</param>
public void AddTable(TableMetaData table)
{
tables.Add(table);
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="target"></param>
public override void DumpTableContents(TableMetaData table, IDBBackEnd target)
{
throw new NotImplementedException();
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="reader"></param>
public override void DumpTable(TableMetaData table, DbDataReader reader)
{
throw new NotImplementedException();
}
/// <summary>
/// Adds a table to the schema collection.
/// </summary>
/// <param name="table">Table Metadata info to add</param>
public void AddTable(TableMetaData table)
{
tables.Add(table);
}
/// <summary>
/// Creates the text of the SQL command to create a table in SQLite
/// </summary>
/// <param name="table">Table Metadata to create</param>
/// <returns>The DML sentence (SQL) to create the given table in a SQLite schema</returns>
private string CreateTableDML(TableMetaData table)
{
StringBuilder stmtBuilder = new StringBuilder();
string primary_keys_string = string.Empty;
stmtBuilder.Append("CREATE TABLE " + SchemaTablesMetaData.EscapeIdentifier(table.tableName) + " (");
for (int i = 0 ; i < table.columns.Count; i++)
{
ColumnMetaData column = table.columns[i];
stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.columnName));
stmtBuilder.Append(" ");
stmtBuilder.Append(ConvertDbTypeToSQLiteType(column));
if (!column.isNullable)
{
stmtBuilder.Append(" NOT NULL");
}
if (column.hasDefault)
{
stmtBuilder.Append(" DEFAULT ");
if (IsLiteralType(column))
{
stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.defaultValue));
}
else
{
stmtBuilder.Append(column.defaultValue);
}
}
if (column.hasForeignKey)
{
stmtBuilder.Append(String.Format(" REFERENCES {0} ({1})", SchemaTablesMetaData.EscapeIdentifier(column.fkTable), SchemaTablesMetaData.EscapeIdentifier(column.fkColumn)));
}
if (column.isPrimaryKey)
{
if (string.IsNullOrEmpty(primary_keys_string))
primary_keys_string = SchemaTablesMetaData.EscapeIdentifier(column.columnName);
else primary_keys_string = String.Format("{0},{1}", primary_keys_string , SchemaTablesMetaData.EscapeIdentifier(column.columnName));
}
if (i + 1 < table.columns.Count || !string.IsNullOrEmpty(primary_keys_string))
stmtBuilder.Append(", ");
}
if (!string.IsNullOrEmpty(primary_keys_string))
stmtBuilder.Append(String.Format("PRIMARY KEY({0})", primary_keys_string));
stmtBuilder.Append(")");
return stmtBuilder.ToString();
}
/// <summary> /// Copies table contents obtained from a OleDbDataReader into the database /// </summary> /// <param name="table">Table metadata from where the DataReader is obtained</param> /// <param name="reader">DataReader that provides the original data</param> public abstract void DumpTable(TableMetaData table, DbDataReader reader);
/// <summary>
/// Populates a SQLiteCommand parameter list with parameter names based on the column names of a table
/// </summary>
/// <param name="table">Table metadata to obtain column names</param>
/// <param name="cmd">SQLite command whose parameter List will be filled</param>
private void CreateSqlCommandParameters(TableMetaData table, SQLiteCommand cmd)
{
cmd.Parameters.Clear();
foreach (ColumnMetaData column in table.columns)
{
cmd.Parameters.Add(new SQLiteParameter(String.Format("@{0}", SchemaTablesMetaData.HexString(column.columnName))));
}
}
/// <summary>
/// Creates a SQL INSERT sentence to insert a row of data to a table in the target database.
/// </summary>
/// <param name="table">Table metadata from the table to copy</param>
/// <returns>SQL Sentence of the insertion in the target database</returns>
private string CreateInsertQuery(TableMetaData table)
{
string csvColumList = string.Empty;
string csvParamsList = string.Empty;
foreach (ColumnMetaData column in table.columns)
{
csvColumList = String.Format("{0}{1},",csvColumList , SchemaTablesMetaData.EscapeIdentifier(column.columnName));
csvParamsList = String.Format("{0}@{1},", csvParamsList, SchemaTablesMetaData.HexString(column.columnName));
}
string query = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", SchemaTablesMetaData.EscapeIdentifier(table.tableName), csvColumList.Remove(csvColumList.Length - 1), csvParamsList.Remove(csvParamsList.Length - 1));
return query;
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
public override void QueryTableDefinition(TableMetaData table)
{
throw new NotImplementedException();
}
/// <summary> /// Copies table contents obtained from a OleDbDataReader into the database /// </summary> /// <param name="table">Table metadata from where the DataReader is obtained</param> /// <param name="reader">DataReader that provides the original data</param> public abstract void DumpTable(TableMetaData table, DbDataReader reader);
/// <summary> /// Copy the table contents of a table in the current database into a table with the same name in another database /// </summary> /// <param name="table">Metadata of the table to copy</param> /// <param name="target">IDBBackend that will be used to copy the contents of the table</param> public abstract void DumpTableContents(TableMetaData table, IDBBackEnd target);
/// <summary>
/// Get Metadata information about a table in a schema in the current database
/// </summary>
/// <param name="table">Name of the table to obtain the metadata (columns and primary keys)</param>
public override void QueryTableDefinition(TableMetaData table)
{
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, table.tableName, null }))
{
foreach (DataRow row in dt.Rows)
{
ColumnMetaData metadata = new ColumnMetaData();
metadata.columnName = row["COLUMN_NAME"].ToString();
metadata.columnDescription = row["DESCRIPTION"].ToString(); ;
metadata.ordinalPosition = Convert.ToInt32(row["ORDINAL_POSITION"].ToString());
if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
metadata.maxCharSize = Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"].ToString());
if (row["NUMERIC_PRECISION"] != DBNull.Value)
metadata.numericPrecision = Convert.ToInt32(row["NUMERIC_PRECISION"].ToString());
if (row["NUMERIC_SCALE"] != DBNull.Value)
metadata.numericScale = Convert.ToInt32(row["NUMERIC_SCALE"].ToString());
if (row["DATETIME_PRECISION"] != DBNull.Value)
metadata.datetimePrecision = Convert.ToInt32(row["DATETIME_PRECISION"].ToString());
metadata.hasDefault = Convert.ToBoolean(row["COLUMN_HASDEFAULT"].ToString());
metadata.defaultValue = row["COLUMN_DEFAULT"].ToString(); ;
metadata.columnType = ((OleDbType)row["DATA_TYPE"]);
metadata.isNullable = Convert.ToBoolean(row["IS_NULLABLE"].ToString());
table.AddColumn(metadata);
}
}
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, table.tableName }))
{
foreach (DataRow row in dt.Rows)
{
// Find Columns by name
string isPK = row["PK_NAME"].ToString();
string colPK = row["COLUMN_NAME"].ToString();
table.FindColumn(colPK).isPrimaryKey = true;
}
}
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, null, null, table.tableName }))
{
foreach (DataRow row in dt.Rows)
{
// Find Columns by name
string localColumn = row["FK_COLUMN_NAME"].ToString();
ColumnMetaData column = table.FindColumn(localColumn);
column.hasForeignKey = true;
column.fkColumn = row["PK_COLUMN_NAME"].ToString();
column.fkTable = row["PK_TABLE_NAME"].ToString();
}
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching metadata for table {0} : {1}", table.tableName, ex.Message);
}
}
/// <summary> /// Get Metadata information about a table in a schema in the current database /// </summary> /// <param name="table">Name of the table to obtain the metadata (columns and primary keys)</param> public abstract void QueryTableDefinition(TableMetaData table);
/// <summary> /// Copy the table contents of a table in the current database into a table with the same name in another database /// </summary> /// <param name="table">Metadata of the table to copy</param> /// <param name="target">IDBBackend that will be used to copy the contents of the table</param> public abstract void DumpTableContents(TableMetaData table, IDBBackEnd target);
/// <summary>
/// Dumps table contents obtained from a OleDbDataReader into the target database
/// </summary>
/// <param name="table">Table metadata from where the DataReader is obtained</param>
/// <param name="reader">DataReader that provides the original data</param>
public override void DumpTable(TableMetaData table, DbDataReader reader)
{
SQLiteTransaction transaction = null;
string currentQuery = string.Empty;
try
{
using (SQLiteConnection connector = new SQLiteConnection(connectionString))
{
connector.Open();
using (SQLiteCommand Cmd = new SQLiteCommand())
{
Cmd.CommandText = CreateInsertQuery(table);
Cmd.Connection = connector;
CreateSqlCommandParameters(table, Cmd);
int countRecords = 0;
transaction = connector.BeginTransaction();
while (reader.Read())
{
foreach (ColumnMetaData colum in table.columns)
{
SetSqlCommandParameterValueForColumn(colum, Cmd, reader);
}
currentQuery = Cmd.CommandText;
Cmd.ExecuteNonQuery();
if (countRecords++ % 100 == 0) System.Console.Out.Write(".");
}
transaction.Commit();
System.Console.Out.WriteLine("done");
System.Console.Out.WriteLine("Table dump complete, {0} records copied.", countRecords);
}
connector.Close();
}
}
catch (SQLiteException ex)
{
if (null != transaction)
transaction.Rollback();
Console.Out.WriteLine("SQLite Exception dumping table {0} : {1}\n{2}", table.tableName, ex.Message, currentQuery);
}
}
/// <summary>
/// Get Metadata information about a table in a schema in the current database
/// </summary>
/// <param name="table">Name of the table to obtain the metadata (columns and primary keys)</param>
public override void QueryTableDefinition(TableMetaData table)
{
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, table.tableName, null }))
{
foreach (DataRow row in dt.Rows)
{
ColumnMetaData metadata = new ColumnMetaData();
metadata.columnName = row["COLUMN_NAME"].ToString();
metadata.columnDescription = row["DESCRIPTION"].ToString();;
metadata.ordinalPosition = Convert.ToInt32(row["ORDINAL_POSITION"].ToString());
if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
metadata.maxCharSize = Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"].ToString());
}
if (row["NUMERIC_PRECISION"] != DBNull.Value)
{
metadata.numericPrecision = Convert.ToInt32(row["NUMERIC_PRECISION"].ToString());
}
if (row["NUMERIC_SCALE"] != DBNull.Value)
{
metadata.numericScale = Convert.ToInt32(row["NUMERIC_SCALE"].ToString());
}
if (row["DATETIME_PRECISION"] != DBNull.Value)
{
metadata.datetimePrecision = Convert.ToInt32(row["DATETIME_PRECISION"].ToString());
}
metadata.hasDefault = Convert.ToBoolean(row["COLUMN_HASDEFAULT"].ToString());
metadata.defaultValue = row["COLUMN_DEFAULT"].ToString();;
metadata.columnType = ((OleDbType)row["DATA_TYPE"]);
metadata.isNullable = Convert.ToBoolean(row["IS_NULLABLE"].ToString());
table.AddColumn(metadata);
}
}
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, table.tableName }))
{
foreach (DataRow row in dt.Rows)
{
// Find Columns by name
string isPK = row["PK_NAME"].ToString();
string colPK = row["COLUMN_NAME"].ToString();
table.FindColumn(colPK).isPrimaryKey = true;
}
}
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, null, null, table.tableName }))
{
foreach (DataRow row in dt.Rows)
{
// Find Columns by name
string localColumn = row["FK_COLUMN_NAME"].ToString();
ColumnMetaData column = table.FindColumn(localColumn);
column.hasForeignKey = true;
column.fkColumn = row["PK_COLUMN_NAME"].ToString();
column.fkTable = row["PK_TABLE_NAME"].ToString();
}
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching metadata for table {0} : {1}", table.tableName, ex.Message);
}
}
/// <summary> /// Get Metadata information about a table in a schema in the current database /// </summary> /// <param name="table">Name of the table to obtain the metadata (columns and primary keys)</param> public abstract void QueryTableDefinition(TableMetaData table);
