/// <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); }