public DatabaseEntity GetEntityInfo(string tableName)
{
if (!IsLoaded)
{
throw new ApplicationException("The Controller isn't loaded");
}
DatabaseEntity entity = new DatabaseEntity();
MySqlConnection connection = new MySqlConnection(_connectionString);
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "DESCRIBE " + tableName + ";";
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string field = Convert.ToString(reader.GetValue(0));
string type = Convert.ToString(reader.GetValue(1));
string _null = Convert.ToString(reader.GetValue(2));
string key = Convert.ToString(reader.GetValue(3));
Match match = Regex.Match(type, "[0-9]+");
string dataType = Regex.Replace(type, "\\([0-9a-zA-Z,\']+\\)", string.Empty).Replace("unsigned", string.Empty).Trim().ToLower();
string fullDataType = type.Replace("unsigned", string.Empty).Trim().ToLower();
DatabaseEntityField columna = new DatabaseEntityField
{
ColumnName = field,
IsPrimaryKey = key.Equals("PRI"),
DataType = 0,
TypeName = fullDataType,
SimpleTypeName = dataType,
Precision = match.Success ? Convert.ToInt32(match.Value) : -1,
Length = 0,
Scale = (Int16?)null,
Radix = (Int16?)null,
IsNullable = _null.Equals("YES"),
};
entity.Fields.Add(columna);
}
}
return(entity);
}
/// <summary>
/// GetEntityInfo
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
/// <exception cref="System.ApplicationException">The Controller isn't loaded</exception>
public DatabaseEntity GetEntityInfo(string tableName)
{
if (!IsLoaded)
{
throw new ApplicationException("The Controller isn't loaded");
}
DatabaseEntity entity = new DatabaseEntity();
OracleConnection connection = new OracleConnection(_connectionString);
connection.Open();
using (OracleCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM ALL_TABLES WHERE TABLE_NAME = '" + tableName + "';";
DataTable table = new DataTable();
OracleDataReader reader = command.ExecuteReader();
table.Load(reader);
if (table.Rows.Count == 0)
{
throw new DataException("Table not found in database");
}
DataRow row = table.Rows[0];
entity.Qualifier = row["TABLESPACE_NAME"].ToString();
entity.Owner = row["OWNER"].ToString();
entity.Name = row["TABLE_NAME"].ToString();
//entity.Type = row["TABLE_TYPE"].ToString();
}
List <string> typesWithPrecision = new List <string> {
"varchar2", "char"
};
using (OracleCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM ALL_TAB_COLS WHERE TABLE_NAME = '" + tableName + "';";
DataTable table = new DataTable();
OracleDataReader reader = command.ExecuteReader();
table.Load(reader);
foreach (DataRow row in table.Rows)
{
string dataType = Convert.ToString(row["DATA_TYPE"]);
string fullDataType = typesWithPrecision.Exists(t => t.Equals(dataType, StringComparison.InvariantCultureIgnoreCase)) ? string.Format("{0} ({1})", dataType, row["DATA_LENGTH"]) : dataType;
DatabaseEntityField entityField = new DatabaseEntityField
{
ColumnName = Convert.ToString(row["COLUMN_NAME"]),
IsPrimaryKey = false,
DataType = Convert.ToInt16(row["DATA_TYPE"]),
TypeName = fullDataType.Trim(),
SimpleTypeName = dataType,
Precision = row["DATA_PRECISION"] != DBNull.Value ? Convert.ToInt32(row["DATA_PRECISION"]) : 0,
Length = Convert.ToInt32(row["DATA_LENGTH"]),
Scale = row["DATA_SCALE"] != DBNull.Value ? Convert.ToInt16(row["DATA_SCALE"]) : (Int16?)null,
//Radix = row["RADIX"] != DBNull.Value ? Convert.ToInt16(row["RADIX"]) : (Int16?)null,
IsNullable = row["NULLABLE"].ToString().Equals("Y"),
};
entity.Fields.Add(entityField);
}
}
return(entity);
}
/// <summary>
/// Gets the entity information.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
/// <exception cref="System.ApplicationException">The Controller isn't loaded</exception>
/// <exception cref="DataException">Table not found in database</exception>
public DatabaseEntity GetEntityInfo(string tableName)
{
if (!IsLoaded)
{
throw new ApplicationException("The Controller isn't loaded");
}
DatabaseEntity entity = new DatabaseEntity();
SqlConnection connection = new SqlConnection(_connectionString);
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_tables";
command.Parameters.AddWithValue("@table_name", tableName);
DataTable table = new DataTable();
SqlDataReader reader = command.ExecuteReader();
table.Load(reader);
if (table.Rows.Count == 0)
{
throw new DataException("Table not found in database");
}
DataRow row = table.Rows[0];
entity.Qualifier = row["TABLE_QUALIFIER"].ToString();
entity.Owner = row["TABLE_OWNER"].ToString();
entity.Name = row["TABLE_NAME"].ToString();
entity.Type = row["TABLE_TYPE"].ToString();
}
List <string> typesWithoutPrecision = new List <string> {
"int", "tinyint", "bigint", "bit", "datetime", "date", "real", "float", "text"
};
List <string> typesWithDoublePrecision = new List <string> {
"decimal", "money"
};
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_columns";
command.Parameters.AddWithValue("@table_name", tableName);
DataTable table = new DataTable();
SqlDataReader reader = command.ExecuteReader();
table.Load(reader);
foreach (DataRow row in table.Rows)
{
string typeName = Convert.ToString(row["TYPE_NAME"]);
Match match = Regex.Match(typeName, "(identity)");
string dataType = match.Success ? typeName.Substring(0, match.Index).Trim() : typeName;
string fullDataType;
if (typesWithDoublePrecision.Exists(t => t.Equals(dataType)))
{
fullDataType = string.Format("{0}({1},{2})", dataType, row["PRECISION"], row["SCALE"]);
}
else
{
fullDataType = !typesWithoutPrecision.Exists(t => t.Equals(dataType)) ? string.Format("{0}({1})", dataType, row["PRECISION"]) : dataType;
}
DatabaseEntityField entityField = new DatabaseEntityField
{
ColumnName = Convert.ToString(row["COLUMN_NAME"]),
IsPrimaryKey = match.Success,
DataType = Convert.ToInt16(row["DATA_TYPE"]),
TypeName = fullDataType.Trim(),
SimpleTypeName = dataType,
Precision = Convert.ToInt32(row["PRECISION"]),
Length = Convert.ToInt32(row["LENGTH"]),
Scale = row["SCALE"] != DBNull.Value ? Convert.ToInt16(row["SCALE"]) : (Int16?)null,
Radix = row["RADIX"] != DBNull.Value ? Convert.ToInt16(row["RADIX"]) : (Int16?)null,
IsNullable = Convert.ToBoolean(row["NULLABLE"]),
};
entity.Fields.Add(entityField);
}
}
return(entity);
}
