/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(string tableName)
{
var result = new List <DbColumn>();
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
using (var adapter = new SQLiteDataAdapter(String.Format("SELECT * FROM {0} LIMIT 1 ", tableName), _dbConnection)) {
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable columnsSchema;
// Jjust to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet()) {
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
using (DataTable columnsList = new DataTable()) {
tempDs.Tables.Add(columnsList);
// Get from db
adapter.Fill(columnsList);
// Get schema
using (var reader = new DataTableReader(columnsList))
columnsSchema = reader.GetSchemaTable();
}
}
// Used to get columns Sql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("COLUMNS")) {
// Fetch the rows
foreach (DataRow dr in columnsSchema.Rows)
{
string columnName = dr["ColumnName"].ToString();
DbColumn column = new DbColumn(columnName, columnName)
{
DataTypeDotNet = dr["DataType"].ToString(),
Length = Convert.ToInt32(dr["ColumnSize"]),
PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
AutoIncrement = Convert.ToBoolean(dr["IsAutoIncrement"]),
AllowNull = Convert.ToBoolean(dr["AllowDBNull"]),
ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]),
};
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// Add to result
result.Add(column);
}
}
}
return(result);
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(string tableName)
{
List <DbColumn> result = new List <DbColumn>();
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
var cmd = string.Format("SELECT * FROM information_schema.columns WHERE TABLE_NAME='{0}' ", tableName);
using (var adapter = new SqlCeDataAdapter(cmd, _dbConnection.ConnectionString))
using (var columnsDbTypeTable = new DataTable("Columns"))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Used to get columns Sql DataType
// readd the columns info
adapter.Fill(columnsDbTypeTable);
// Fetch the rows
foreach (DataRow dr in columnsDbTypeTable.Rows)
{
string columnName = dr["COLUMN_NAME"].ToString();
var column = new DbColumn(columnName, columnName)
{
DataTypeDb = dr["DATA_TYPE"].ToString(),
Length = Common.TryConvertInt32(dr["CHARACTER_MAXIMUM_LENGTH"].ToString(), 0),
AllowNull = dr["IS_NULLABLE"].ToString().ToUpper().StartsWith("Y"),
ColumnOrdinal = Convert.ToInt32(dr["ORDINAL_POSITION"]),
NumericPrecision = Common.TryConvertInt32(dr["NUMERIC_PRECISION"].ToString(), 0),
NumericScale = Common.TryConvertInt32(dr["NUMERIC_SCALE"].ToString(), 0),
AutoIncrement = Common.TryConvertBoolean(dr["AUTOINC_INCREMENT"], false),
//PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
UserDescription = dr["DESCRIPTION"].ToString()
};
var dotNetType = _sqlCeDataTypes.First(x => x.TypeName == column.DataTypeDb);
column.DataTypeDotNet = dotNetType.DotNetDataType;
column.DataTypeMaxLength = (int)dotNetType.ColumnSize;
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
FillColumnAdditionalInfo(column, tableName, columnName);
// Add to result
result.Add(column);
}
}
return(result);
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(String tableName)
{
List <DbColumn> result = new List <DbColumn>();
string[] restrictions = null;
if (!string.IsNullOrWhiteSpace(this.SpecificOwner))
{
restrictions = new string[]
{
SpecificOwner.ToUpper(),
tableName
};
}
// Used to get columns Sql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("COLUMNS", restrictions))
{
// Fetch the rows
foreach (DataRow dr in columnsDbTypeTable.Rows)
{
string columnName = dr["COLUMN_NAME"].ToString();
DbColumn column = new DbColumn(columnName)
{
Owner = dr["OWNER"].ToString(),
DataTypeDotNet = FindMatchingDotNetDataType(dr["DATATYPE"].ToString()),
DataTypeDb = dr["DATATYPE"].ToString(),
Length = Common.TryConvertInt32(dr["LENGTH"].ToString(), 0),
ColumnOrdinal = Common.TryConvertInt32(dr["ID"].ToString(), -1),
AllowNull = dr["Nullable"].ToString().ToUpper() == "Y",
NumericPrecision = Common.TryConvertInt32(dr["PRECISION"].ToString(), -1),
NumericScale = Common.TryConvertInt32(dr["SCALE"].ToString(), -1),
// needed to be read
AutoIncrement = false,
PrimaryKey = false,
UserDescription = ""
};
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
FillColumnAdditionalInfo(column, tableName, columnName);
// Add to result
result.Add(column);
}
}
return(result);
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(string tableName, string ownerName)
{
List <DbColumn> result = new List <DbColumn>();
using (SqlDataAdapter adapter = new SqlDataAdapter(String.Format("SELECT TOP 1 * FROM [{0}].[{1}]", ownerName, tableName), (SqlConnection)_dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable columnsSchema;
// Jjust to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
using (DataTable columnsList = new DataTable())
{
tempDs.Tables.Add(columnsList);
// Get from db
adapter.Fill(columnsList);
// Get schema
using (DataTableReader reader = new DataTableReader(columnsList))
columnsSchema = reader.GetSchemaTable();
}
}
// Used to get columns Sql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("COLUMNS"))
{
// Fetch the rows
foreach (DataRow dr in columnsSchema.Rows)
{
string columnName = dr["ColumnName"].ToString();
DbColumn column = new DbColumn(columnName, "[" + columnName + "]")
{
DataTypeDotNet = ((Type)dr["DataType"]).AliasOrName(),
Length = Convert.ToInt32(dr["ColumnSize"]),
PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
AutoIncrement = Convert.ToBoolean(dr["IsAutoIncrement"]),
AllowNull = Convert.ToBoolean(dr["AllowDBNull"]),
ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]),
};
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Primary keys that aren't a valid source of dapper access patterns aren't valid
if (column.PrimaryKey)
{
if (column.DataTypeDotNet == "System.DateTime")
{
column.PrimaryKey = false;
}
}
// Columns which needs additional fetch
var succeed = FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// if additional info readin is failed, don't add it to the list
if (succeed)
{
// Add to result
result.Add(column);
}
else
{
// TODO: inform the user
}
}
}
}
return(result);
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(String tableName, string ownerName)
{
var result = new List <DbColumn>();
using (var adapter = new MySqlDataAdapter(String.Format("SELECT * FROM {0}.{1} LIMIT 1", ownerName, tableName), _dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable columnsSchema;
// Jjust to avoid stupid "Failed to enable constraints" error!
using (var tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
using (var columnsList = new DataTable())
{
tempDs.Tables.Add(columnsList);
// Get from db
adapter.Fill(columnsList);
// Get schema
using (var reader = new DataTableReader(columnsList))
columnsSchema = reader.GetSchemaTable();
}
}
// Used to get columns MySql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("Columns"))
{
// Fetch the rows
foreach (DataRow dr in columnsSchema.Rows)
{
var columnName = dr["ColumnName"].ToString();
var column = new DbColumn(columnName, "`" + columnName + "`")
{
DataTypeDotNet = dr["DataType"].ToString(),
Length = Convert.ToInt32(dr["ColumnSize"]),
PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
AutoIncrement = Convert.ToBoolean(dr["IsAutoIncrement"]),
AllowNull = Convert.ToBoolean(dr["AllowDBNull"]),
ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]),
DataTypeMaxLength = Convert.ToInt32(dr["ColumnSize"]),
};
// Fixup MySqlDateTime - dapper automatically converts that object
if (column.DataTypeDotNet.IndexOf("MySql.Data.Types.MySqlDateTime") >= 0)
{
column.DataTypeDotNet = "DateTime";
}
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
var succeed = FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// if additional info readin is failed, don't add it to the list
if (succeed)
{
// Add to result
result.Add(column);
}
else
{
// TODO: inform the user
}
}
}
}
return(result);
}
