/// <summary>
/// Method used to find out if a column contains data that is susceptible of being encased into quotes (string literals).
/// </summary>
/// <param name="column">Column metadata information</param>
/// <returns>true if the column metadata type indicates is one of a literal kind</returns>
private bool IsLiteralType(ColumnMetaData column)
{
switch (column.columnType)
{
case OleDbType.Boolean: //A Boolean value (DBTYPE_BOOL). This maps to Boolean.
case OleDbType.Date: // Date data, stored as a double (DBTYPE_DATE). The whole portion is the number of days since December 30, 1899, and the fractional portion is a fraction of a day. This maps to DateTime.
case OleDbType.DBDate: // Date data in the format yyyymmdd (DBTYPE_DBDATE). This maps to DateTime.
case OleDbType.DBTime: // Time data in the format hhmmss (DBTYPE_DBTIME). This maps to TimeSpan.
case OleDbType.DBTimeStamp: // Data and time data in the format yyyymmddhhmmss (DBTYPE_DBTIMESTAMP). This maps to DateTime.
case OleDbType.Binary: //A stream of binary data (DBTYPE_BYTES). This maps to an Array of type Byte.
case OleDbType.LongVarBinary: // A long binary value (OleDbParameter only). This maps to an Array of type Byte.
case OleDbType.VarBinary: // A variable-length stream of binary data (OleDbParameter only). This maps to an Array of type Byte.
case OleDbType.BSTR: //A null-terminated character string of Unicode characters (DBTYPE_BSTR). This maps to String.
case OleDbType.Char: //A character string (DBTYPE_STR). This maps to String.
case OleDbType.LongVarChar: // A long string value (OleDbParameter only). This maps to String.
case OleDbType.LongVarWChar: // A long null-terminated Unicode string value (OleDbParameter only). This maps to String.
case OleDbType.VarChar: // A variable-length stream of non-Unicode characters (OleDbParameter only). This maps to String.
case OleDbType.VarWChar: // A variable-length, null-terminated stream of Unicode characters (OleDbParameter only). This maps to String.
case OleDbType.WChar: //
case OleDbType.Guid: // A globally unique identifier (or GUID) (DBTYPE_GUID). This maps to Guid.
return(true);
default:
return(false);
}
}
/// <summary>
/// Method used to find out if a column contains data that is susceptible of being encased into quotes (string literals).
/// </summary>
/// <param name="column">Column metadata information</param>
/// <returns>true if the column metadata type indicates is one of a literal kind</returns>
private bool IsLiteralType(ColumnMetaData column)
{
switch (column.columnType)
{
case OleDbType.Boolean: //A Boolean value (DBTYPE_BOOL). This maps to Boolean.
case OleDbType.Date: // Date data, stored as a double (DBTYPE_DATE). The whole portion is the number of days since December 30, 1899, and the fractional portion is a fraction of a day. This maps to DateTime.
case OleDbType.DBDate:// Date data in the format yyyymmdd (DBTYPE_DBDATE). This maps to DateTime.
case OleDbType.DBTime:// Time data in the format hhmmss (DBTYPE_DBTIME). This maps to TimeSpan.
case OleDbType.DBTimeStamp:// Data and time data in the format yyyymmddhhmmss (DBTYPE_DBTIMESTAMP). This maps to DateTime.
case OleDbType.Binary: //A stream of binary data (DBTYPE_BYTES). This maps to an Array of type Byte.
case OleDbType.LongVarBinary:// A long binary value (OleDbParameter only). This maps to an Array of type Byte.
case OleDbType.VarBinary:// A variable-length stream of binary data (OleDbParameter only). This maps to an Array of type Byte.
case OleDbType.BSTR: //A null-terminated character string of Unicode characters (DBTYPE_BSTR). This maps to String.
case OleDbType.Char: //A character string (DBTYPE_STR). This maps to String.
case OleDbType.LongVarChar:// A long string value (OleDbParameter only). This maps to String.
case OleDbType.LongVarWChar:// A long null-terminated Unicode string value (OleDbParameter only). This maps to String.
case OleDbType.VarChar:// A variable-length stream of non-Unicode characters (OleDbParameter only). This maps to String.
case OleDbType.VarWChar:// A variable-length, null-terminated stream of Unicode characters (OleDbParameter only). This maps to String.
case OleDbType.WChar: //
case OleDbType.Guid:// A globally unique identifier (or GUID) (DBTYPE_GUID). This maps to Guid.
return true;
default:
return false;
}
}
/// <summary>
/// Comparer function for ColumnMetadata Objects
/// </summary>
/// <param name="x">left object to compare</param>
/// <param name="y">right object to compare</param>
/// <returns></returns>
public static int CompareColumnOrder(ColumnMetaData x, ColumnMetaData y)
{
if (x == null)
{
if (y == null)
{
return(0);
}
else
{
return(-1);
}
}
else
{
if (y == null)
{
return(1);
}
else
{
int retval = x.ordinalPosition.CompareTo(y.ordinalPosition);
return(retval);
}
}
}
/// <summary>
/// Comparer function for ColumnMetadata Objects
/// </summary>
/// <param name="x">left object to compare</param>
/// <param name="y">right object to compare</param>
/// <returns></returns>
public static int CompareColumnOrder(ColumnMetaData x, ColumnMetaData y)
{
if (x == null)
{
if (y == null)
{
return 0;
}
else
{
return -1;
}
}
else
{
if (y == null)
{
return 1;
}
else
{
int retval = x.ordinalPosition.CompareTo(y.ordinalPosition);
return retval;
}
}
}
/// <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>
/// Conversion function, gets a table column metadata, returns a string indicating the SQLite type for the Column Data Type
/// </summary>
/// <param name="column">A Table Column Metadata Info</param>
/// <returns>String indicating the data type for the same column in SQLite</returns>
private string ConvertDbTypeToSQLiteType(ColumnMetaData column)
{
switch (column.columnType)
{
case OleDbType.Boolean: //A Boolean value (DBTYPE_BOOL). This maps to Boolean.
return("BOOLEAN");
case OleDbType.BigInt: //A 64-bit signed integer (DBTYPE_I8). This maps to Int64.
case OleDbType.Filetime: // A 64-bit unsigned integer representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME). This maps to DateTime.
case OleDbType.Integer: // A 32-bit signed integer (DBTYPE_I4). This maps to Int32.
case OleDbType.SmallInt: // A 16-bit signed integer (DBTYPE_I2). This maps to Int16.
case OleDbType.TinyInt: // A 8-bit signed integer (DBTYPE_I1). This maps to SByte.
case OleDbType.UnsignedBigInt: // A 64-bit unsigned integer (DBTYPE_UI8). This maps to UInt64.
case OleDbType.UnsignedInt: // A 32-bit unsigned integer (DBTYPE_UI4). This maps to UInt32.
case OleDbType.UnsignedSmallInt: // A 16-bit unsigned integer (DBTYPE_UI2). This maps to UInt16.
case OleDbType.UnsignedTinyInt: // A 8-bit unsigned integer (DBTYPE_UI1). This maps to Byte.
return("INTEGER");
case OleDbType.Currency: //A currency value ranging from -2 63 (or -922,337,203,685,477.5808) to 2 63 -1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit (DBTYPE_CY). This maps to Decimal.
// Dudo si currency deberia ser text...
case OleDbType.Decimal: // A fixed precision and scale numeric value between -10 38 -1 and 10 38 -1 (DBTYPE_DECIMAL). This maps to Decimal.
case OleDbType.Double: // A floating-point number within the range of -1.79E +308 through 1.79E +308 (DBTYPE_R8). This maps to Double.
case OleDbType.Numeric: // An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC). This maps to Decimal.
case OleDbType.Single: // A floating-point number within the range of -3.40E +38 through 3.40E +38 (DBTYPE_R4). This maps to Single.
case OleDbType.VarNumeric: // A variable-length numeric value (OleDbParameter only). This maps to Decimal.
return("DOUBLE");
case OleDbType.Binary: //A stream of binary data (DBTYPE_BYTES). This maps to an Array of type Byte.
case OleDbType.LongVarBinary: // A long binary value (OleDbParameter only). This maps to an Array of type Byte.
case OleDbType.VarBinary: // A variable-length stream of binary data (OleDbParameter only). This maps to an Array of type Byte.
return("BLOB");
case OleDbType.BSTR: //A null-terminated character string of Unicode characters (DBTYPE_BSTR). This maps to String.
case OleDbType.Char: //A character string (DBTYPE_STR). This maps to String.
case OleDbType.LongVarChar: // A long string value (OleDbParameter only). This maps to String.
case OleDbType.LongVarWChar: // A long null-terminated Unicode string value (OleDbParameter only). This maps to String.
case OleDbType.VarChar: // A variable-length stream of non-Unicode characters (OleDbParameter only). This maps to String.
case OleDbType.VarWChar: // A variable-length, null-terminated stream of Unicode characters (OleDbParameter only). This maps to String.
case OleDbType.WChar: //
case OleDbType.Guid: // A globally unique identifier (or GUID) (DBTYPE_GUID). This maps to Guid.
return("TEXT");
case OleDbType.Date: // Date data, stored as a double (DBTYPE_DATE). The whole portion is the number of days since December 30, 1899, and the fractional portion is a fraction of a day. This maps to DateTime.
case OleDbType.DBDate: // Date data in the format yyyymmdd (DBTYPE_DBDATE). This maps to DateTime.
case OleDbType.DBTime: // Time data in the format hhmmss (DBTYPE_DBTIME). This maps to TimeSpan.
case OleDbType.DBTimeStamp: // Data and time data in the format yyyymmddhhmmss (DBTYPE_DBTIMESTAMP). This maps to DateTime.
return("DATETIME");
default:
throw new Exception("Unhandled MS Acess datatype: " + column.columnType);
}
}
/// <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>
///
/// </summary>
/// <param name="col"></param>
public void AddColumn(ColumnMetaData col)
{
columns.Add(col);
columns.Sort(ColumnMetaData.CompareColumnOrder);
}
/// <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>
/// Sets the parameter value for a SQLite query
/// </summary>
/// <param name="column">Column metadata info. The parameter will be named after the column name</param>
/// <param name="cmd">SQLite command where to set the parameter</param>
/// <param name="value">Value to set the parameter will be obtained from a row in this OleDbDataReader whose column has the column name</param>
private void SetSqlCommandParameterValueForColumn(ColumnMetaData column, SQLiteCommand cmd, DbDataReader value)
{
string parameterName = String.Format("@{0}", SchemaTablesMetaData.HexString(column.columnName));
cmd.Parameters[parameterName].Value = value[column.columnName];
}
/// <summary>
/// Sets the parameter value for a SQLite query
/// </summary>
/// <param name="column">Column metadata info. The parameter will be named after the column name</param>
/// <param name="cmd">SQLite command where to set the parameter</param>
/// <param name="value">Value to set the parameter will be obtained from a row in this OleDbDataReader whose column has the column name</param>
private void SetSqlCommandParameterValueForColumn(ColumnMetaData column, SQLiteCommand cmd, DbDataReader value)
{
string parameterName = String.Format("@{0}", SchemaTablesMetaData.HexString(column.columnName));
cmd.Parameters[parameterName].Value = value[column.columnName];
}
/// <summary>
/// Conversion function, gets a table column metadata, returns a string indicating the SQLite type for the Column Data Type
/// </summary>
/// <param name="column">A Table Column Metadata Info</param>
/// <returns>String indicating the data type for the same column in SQLite</returns>
private string ConvertDbTypeToSQLiteType(ColumnMetaData column)
{
switch (column.columnType)
{
case OleDbType.Boolean: //A Boolean value (DBTYPE_BOOL). This maps to Boolean.
return "BOOLEAN";
case OleDbType.BigInt: //A 64-bit signed integer (DBTYPE_I8). This maps to Int64.
case OleDbType.Filetime :// A 64-bit unsigned integer representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME). This maps to DateTime.
case OleDbType.Integer :// A 32-bit signed integer (DBTYPE_I4). This maps to Int32.
case OleDbType.SmallInt:// A 16-bit signed integer (DBTYPE_I2). This maps to Int16.
case OleDbType.TinyInt:// A 8-bit signed integer (DBTYPE_I1). This maps to SByte.
case OleDbType.UnsignedBigInt:// A 64-bit unsigned integer (DBTYPE_UI8). This maps to UInt64.
case OleDbType.UnsignedInt:// A 32-bit unsigned integer (DBTYPE_UI4). This maps to UInt32.
case OleDbType.UnsignedSmallInt:// A 16-bit unsigned integer (DBTYPE_UI2). This maps to UInt16.
case OleDbType.UnsignedTinyInt:// A 8-bit unsigned integer (DBTYPE_UI1). This maps to Byte.
return "INTEGER";
case OleDbType.Currency: //A currency value ranging from -2 63 (or -922,337,203,685,477.5808) to 2 63 -1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit (DBTYPE_CY). This maps to Decimal.
// Dudo si currency deberia ser text...
case OleDbType.Decimal:// A fixed precision and scale numeric value between -10 38 -1 and 10 38 -1 (DBTYPE_DECIMAL). This maps to Decimal.
case OleDbType.Double:// A floating-point number within the range of -1.79E +308 through 1.79E +308 (DBTYPE_R8). This maps to Double.
case OleDbType.Numeric:// An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC). This maps to Decimal.
case OleDbType.Single:// A floating-point number within the range of -3.40E +38 through 3.40E +38 (DBTYPE_R4). This maps to Single.
case OleDbType.VarNumeric:// A variable-length numeric value (OleDbParameter only). This maps to Decimal.
return "DOUBLE";
case OleDbType.Binary: //A stream of binary data (DBTYPE_BYTES). This maps to an Array of type Byte.
case OleDbType.LongVarBinary:// A long binary value (OleDbParameter only). This maps to an Array of type Byte.
case OleDbType.VarBinary:// A variable-length stream of binary data (OleDbParameter only). This maps to an Array of type Byte.
return "BLOB";
case OleDbType.BSTR: //A null-terminated character string of Unicode characters (DBTYPE_BSTR). This maps to String.
case OleDbType.Char: //A character string (DBTYPE_STR). This maps to String.
case OleDbType.LongVarChar :// A long string value (OleDbParameter only). This maps to String.
case OleDbType.LongVarWChar :// A long null-terminated Unicode string value (OleDbParameter only). This maps to String.
case OleDbType.VarChar:// A variable-length stream of non-Unicode characters (OleDbParameter only). This maps to String.
case OleDbType.VarWChar:// A variable-length, null-terminated stream of Unicode characters (OleDbParameter only). This maps to String.
case OleDbType.WChar: //
case OleDbType.Guid:// A globally unique identifier (or GUID) (DBTYPE_GUID). This maps to Guid.
return "TEXT";
case OleDbType.Date: // Date data, stored as a double (DBTYPE_DATE). The whole portion is the number of days since December 30, 1899, and the fractional portion is a fraction of a day. This maps to DateTime.
case OleDbType.DBDate :// Date data in the format yyyymmdd (DBTYPE_DBDATE). This maps to DateTime.
case OleDbType.DBTime :// Time data in the format hhmmss (DBTYPE_DBTIME). This maps to TimeSpan.
case OleDbType.DBTimeStamp :// Data and time data in the format yyyymmddhhmmss (DBTYPE_DBTIMESTAMP). This maps to DateTime.
return "DATETIME";
default:
throw new Exception("Unhandled MS Acess datatype: " + column.columnType);
}
}
/// <summary>
///
/// </summary>
/// <param name="col"></param>
public void AddColumn(ColumnMetaData col)
{
columns.Add(col);
columns.Sort(ColumnMetaData.CompareColumnOrder);
}