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 Form1()
{
InitializeComponent();
var dataList = new List <IDataType>
{
new ColumnTypeBit(),
new ColumnTypeDate(),
new ColumnTypeSmallDateTime(),
new ColumnTypeDateTime(),
new ColumnTypeDecimal(),
new ColumnTypeHierarchyId(),
new ColumnTypeInt(),
new ColumnTypeBigInt(),
new ColumnTypeMoney(),
new ColumnTypeNChar(),
new ColumnTypeChar(),
new ColumnTypeNVarchar(),
new ColumnTypeReal(),
new ColumnTypeSmallint(),
new ColumnTypeTime(),
new ColumnTypeTinyInt(),
new ColumnTypeUniqueidentifier(),
new ColumnTypeVarbinary(),
new ColumnTypeBinary(),
new ColumnTypeImage(),
new ColumnTypeVarchar(),
new ColumnTypeText(),
new ColumnTypeXml(),
new ColumnTypeTimestamp()
};
DataTypeFactory.AddDataType(dataList.ToArray());
WorkerThread = new BackgroundWorker();
WorkerThread.DoWork += WorkerThread_DoWork;
WorkerThread.ProgressChanged += WorkerThread_ProgressChanged;
WorkerThread.RunWorkerCompleted += WorkerThread_RunWorkerCompleted;
WorkerThread.WorkerReportsProgress = true;
}
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);
}
