public static Table GetTableInfo(string tableName, string connectionString)
{
var cmd = new SqlCommand("SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH " +
"FROM INFORMATION_SCHEMA.COLUMNS " +
$"WHERE TABLE_NAME = '{tableName}'");
var newTable = new Table {
TableName = tableName
};
var columnList = new List <Column>();
using (var con = new SqlConnection(connectionString))
{
cmd.Connection = con;
con.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var newColumn = new Column
{
Name = dr.GetString(0),
IsNullable = dr.GetString(2) == "YES"
};
var dataTypeName = dr.GetString(1);
newColumn.Type = DataTypeFactory.GetDataType(dataTypeName);
if (newColumn.Type == null)
{
MessageBox.Show($"Can't find {dataTypeName} type", "Gather data information", MessageBoxButtons.OK, MessageBoxIcon.Warning);
continue;
}
columnList.Add(newColumn);
}
}
}
if (columnList.Count == 0)
{
MessageBox.Show($"There is no fields found ***", "Gather data information", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(null);
}
newTable.Columns = columnList.ToArray();
return(newTable);
}
public static Table GetTableInfoFromQuery(string query, string connectionString)
{
var schema = GetShema(query, connectionString);
var newTable = new Table {
TableName = schema.TableName
};
var columnList = new List <Column>();
//DataRow row = schema.Rows[0];
//foreach (DataColumn column in schema.Columns)
//{
// Console.WriteLine(String.Format("{0} = {1}",
// column.ColumnName, row[column]));
//}
//foreach (DataRow row1 in schema.Rows)
//{
// Console.WriteLine(String.Format("{0} = {1}, {2}", row1["ColumnName"].ToString(), row1["DataTypeName"].ToString(), row1["AllowDBNull"].ToString()));
//}
foreach (DataRow row1 in schema.Rows)
{
var newColumn = new Column
{
Name = row1["ColumnName"].ToString(),
IsNullable = row1["AllowDBNull"].ToString() == "True"
};
var dataTypeName = row1["DataTypeName"].ToString();
newColumn.Type = DataTypeFactory.GetDataType(dataTypeName);
if (newColumn.Type == null)
{
MessageBox.Show($"Can't find {dataTypeName} type", "Gather data information", MessageBoxButtons.OK, MessageBoxIcon.Warning);
continue;
}
columnList.Add(newColumn);
}
newTable.Columns = columnList.ToArray();
return(newTable);
}
