public static string GetCSharpDataType(TableRowMetaData ki)
{
var item = ki;
String result = "";
if (item.DataType.IndexOf("varchar") > -1 || item.DataType.IndexOf("text") > -1 || item.DataType.IndexOf("xml") > -1)
{
result = "String";
}
else if (item.DataType.IndexOf("int") > -1)
{
result = "int";
}
else if (item.DataType.IndexOf("date") > -1)
{
result = "DateTime ";
}
else if (item.DataType.IndexOf("bit") > -1)
{
result = "Boolean ";
}
else if (item.DataType.IndexOf("float") > -1)
{
result = "float ";
}
else if (item.DataType.IndexOf("char") > -1)
{
result = "char ";
}
return(result.Trim());
}
public static string GetSqlDataTypeFromColumnDataType(TableRowMetaData ki)
{
String result = "SqlDbType.{0}";
var item = ki;
if (item.DataType.IndexOf("varchar") > -1 || item.DataType.IndexOf("text") > -1)
{
result = String.Format(result, "NVarChar");
}
else if (item.DataType.IndexOf("int") > -1)
{
result = String.Format(result, "Int");
}
else if (item.DataType.IndexOf("date") > -1)
{
result = String.Format(result, "DateTime");
}
else if (item.DataType.IndexOf("bit") > -1)
{
result = String.Format(result, "Bit");
}
else if (item.DataType.IndexOf("float") > -1)
{
result = String.Format(result, "Float");
}
else if (item.DataType.IndexOf("char") > -1)
{
result = String.Format(result, "NVarChar");
}
else if (item.DataType.IndexOf("xml") > -1)
{
result = String.Format(result, "Xml");
}
else
{
result = GeneralHelper.ConvertTypeToSQL(item.DataType);
}
return(result);
}
public static DatabaseMetadata ParseSqlCreateStatement(string txt = "")
{
DatabaseMetadata databaseMetaData = new DatabaseMetadata();
string tableName = "";
string primaryKey = "";
var bracketsRegex = @"\[(.*?)\]";
var paranthesesRegex = @"\((.*?)\)";
var mysqlQuoteRegex = @"\`(.*?)\`";
DatabaseType databaseType = DatabaseType.UnDefined;
String databaseName = "UNKNOWN";
databaseMetaData.DatabaseName = databaseName;
var tableMetaDataList = new List <TableRowMetaData>();
databaseMetaData.SelectedTable = new TableMetaData();
databaseMetaData.SelectedTable.TableRowMetaDataList = tableMetaDataList;
int counter = 0;
var textLines = new List <String>();
// First determine database type
using (StringReader reader = new StringReader(txt))
{
string line;
while ((line = reader.ReadLine()) != null)
{
if (!String.IsNullOrEmpty(line.Trim()))
{
textLines.Add(line);
string lineLower = line.ToLower();
try
{
if (lineLower.IndexOf("identity") > -1 || lineLower.IndexOf("pad_index") > -1 || lineLower.IndexOf("primary key clustered") > -1)
{
databaseType = DatabaseType.MsSql;
}
else if (lineLower.IndexOf("auto_increment") > -1 || lineLower.IndexOf("primary key") > -1 || lineLower.IndexOf("engine") > -1)
{
databaseType = DatabaseType.MySql;
}
}
catch (Exception ex)
{
Logger.Error(ex, ex.Message, txt);
}
}
}
}
if (databaseType == DatabaseType.UnDefined)
{
databaseType = DatabaseType.UnKnown;
}
for (int i = 0; i < textLines.Count; i++)
{
string line = textLines[i];
string lineLower = line.ToLower();
if (databaseType == DatabaseType.MsSql)
{
try
{
if (lineLower.IndexOf("primary key clustered") > -1)
{
string linePrimaryKey = RemoveBrackets(textLines[i + 2]);
var regex = new Regex("asc", RegexOptions.IgnoreCase);
linePrimaryKey = regex.Replace(linePrimaryKey, "");
regex = new Regex("desc", RegexOptions.IgnoreCase);
primaryKey = regex.Replace(linePrimaryKey, "").ToStr().Trim();
}
}
catch (Exception ex)
{
Logger.Error(ex, ex.Message, line);
}
}
else if (databaseType == DatabaseType.MySql)
{
try
{
if (lineLower.IndexOf("primary key") > -1)
{
Regex r = r = new Regex(paranthesesRegex, RegexOptions.IgnoreCase | RegexOptions.Singleline);
var matches3 = r.Matches(line);
primaryKey = RemoveParatheses(RemoveNail(matches3[0].Groups[0].ToStr()));
}
}
catch (Exception ex)
{
Logger.Error(ex, ex.Message, line);
}
}
else if (databaseType == DatabaseType.MySql)
{
}
}
for (int i = 0; i < textLines.Count; i++)
{
string line = textLines[i];
string lineLower = line.ToLower();
var p = new TableRowMetaData();
var isSqlColumnLine = IsLineColumnFieldLine(line);
try
{
// Do something with the line
var lineRemovedBracket = RemoveBrackets(line);
if (lineRemovedBracket.ToLower().StartsWith("use"))
{
var regex = new Regex("use", RegexOptions.IgnoreCase);
databaseName = regex.Replace(lineRemovedBracket, "");
}
else if (line.ToLower().Contains("create table"))
{
try
{
string tbl = "table";
var f = lineLower.IndexOf(tbl) + tbl.Length;
string s1 = RemoveBrackets(line.Substring(f, line.Length - f - 1).ToStr());
tableName = RemoveNail(s1);
}
catch (Exception ex)
{
Logger.Error(ex, ex.Message, line);
}
}
else if (isSqlColumnLine)
{
try
{
line = line.TrimEnd(',').ToStr();
var lineParts = Regex.Split(line, @"\s+").Select(r => r.Trim()).Where(s => !String.IsNullOrEmpty(s)).ToList();
var isNotNull = lineLower.Contains("not null");
p.IsNull = isNotNull ? "NO" : "YES";
string tbl = isNotNull ? "not null" : "null";
if (databaseType == DatabaseType.MsSql)
{
try
{
Regex r = new Regex(bracketsRegex, RegexOptions.IgnoreCase | RegexOptions.Singleline);
var matches = r.Matches(line);
if (line.IndexOf("(") > -1 && line.IndexOf(")") > -1)
{
r = new Regex(paranthesesRegex, RegexOptions.IgnoreCase | RegexOptions.Singleline);
var matches3 = r.Matches(line);
p.MaxChar = matches3[0].Groups[0].ToStr();
}
p.ColumnName = RemoveBrackets(matches[0].Groups[0].ToStr());
p.PrimaryKey = false;
p.ID = counter++;
p.Order = counter++;
p.DataType = RemoveBrackets(matches[1].Groups[0].ToStr());
p.DataTypeMaxChar = p.DataType + p.MaxChar.ToStr();
p.DatabaseType = DatabaseType.MsSql;
}
catch (Exception ex)
{
Logger.Error(ex, ex.Message, line);
}
}
else if (databaseType == DatabaseType.MySql)
{
try
{
// `youtube` varchar(255) DEFAULT NULL,
Regex r = new Regex(mysqlQuoteRegex, RegexOptions.IgnoreCase | RegexOptions.Singleline);
var matches = r.Matches(line);
p.ColumnName = RemoveNail(matches[0].Groups[0].ToStr());
p.DataType = lineParts[1];
if (p.DataType.IndexOf("(") > -1 && p.DataType.IndexOf(")") > -1)
{
r = new Regex(paranthesesRegex, RegexOptions.IgnoreCase | RegexOptions.Singleline);
var matches3 = r.Matches(line);
p.MaxChar = matches3[0].Groups[0].ToStr();
p.DataType = p.DataType.Replace("(" + p.MaxChar + ")", "");
}
p.DataTypeMaxChar = lineParts[1];
p.PrimaryKey = false;
p.ID = counter++;
p.Order = counter++;
p.DatabaseType = DatabaseType.MySql;
}
catch (Exception ex)
{
Logger.Error(ex, ex.Message, line);
}
}
else if (databaseType == DatabaseType.UnKnown)
{
p.ColumnName = lineParts[0];
p.DataType = lineParts[1];
p.DataTypeMaxChar = lineParts[1];
p.PrimaryKey = false;
p.ID = counter++;
p.Order = counter++;
p.DatabaseType = DatabaseType.MySql;
}
tableMetaDataList.Add(p);
}
catch (Exception ex)
{
Logger.Error(ex, ex.Message, line);
}
}
}
catch (Exception ex)
{
Logger.Error(ex, ex.Message, line);
}
}
Console.WriteLine("databaseName:" + databaseName);
Console.WriteLine("tableName:" + tableName);
Console.WriteLine("primaryKey:" + primaryKey);
string tableSchema = "";
if (tableName.Contains("."))
{
tableSchema = tableName.Split(".".ToCharArray()).FirstOrDefault().ToStr();
}
databaseMetaData.SelectedTable.TableName = RemoveNail(tableName);
databaseMetaData.SelectedTable.TableCatalog = databaseName;
databaseMetaData.SelectedTable.TableType = "";
databaseMetaData.SelectedTable.TableSchema = tableSchema;
databaseMetaData.DatabaseType = databaseType;
return(databaseMetaData);
}
public void GetSelectedMysqlTableMetaData(DatabaseMetadata databaseMetaData, string selectedTable)
{
// To get column information with SqlClient or other providers you do:
//DataTable schema = conn.GetSchema("Columns", new string[4] { conn.Database, null, "products", null });
// With MySQL Connector / NET is different:
//DataTable schema = conn.GetSchema("Columns", new string[4] { null, conn.Database, "products", null });
// Note that the first 2 items in the array are swapped.
var con = new MySqlConnection(databaseMetaData.MySqlConnectionString);
con.Open();
string[] objArrRestrict;
var tParts = selectedTable.Split(".".ToCharArray());
objArrRestrict = new string[] { null,
con.Database,
tParts[2],
null };
DataTable tbl = con.GetSchema("Columns", objArrRestrict);
List <TableRowMetaData> TableRowMetaDataList = new List <TableRowMetaData>();
var selectedTableObj = databaseMetaData.Tables.FirstOrDefault(r => r.DatabaseTableName.Equals(selectedTable, StringComparison.InvariantCultureIgnoreCase));
if (selectedTableObj != null)
{
selectedTableObj.TableRowMetaDataList = TableRowMetaDataList;
databaseMetaData.SelectedTable = selectedTableObj;
int i = 0;
foreach (DataRow rowTable in tbl.Rows)
{
String TABLE_CATALOG = rowTable["TABLE_CATALOG"].ToStr();
String TABLE_SCHEMA = rowTable["TABLE_SCHEMA"].ToStr();
String TABLE_NAME = rowTable["TABLE_NAME"].ToStr();
String COLUMN_NAME = rowTable["COLUMN_NAME"].ToStr();
String ORDINAL_POSITION = rowTable["ORDINAL_POSITION"].ToStr();
String COLUMN_DEFAULT = rowTable["COLUMN_DEFAULT"].ToStr();
String IS_NULLABLE = rowTable["IS_NULLABLE"].ToStr();
String DATA_TYPE = rowTable["DATA_TYPE"].ToStr();
String CHARACTER_MAXIMUM_LENGTH = rowTable["CHARACTER_MAXIMUM_LENGTH"].ToStr();
//String NUMERIC_PRECISION = rowTable.Table.Columns.Contains("NUMERIC_PRECISION") ? rowTable["NUMERIC_PRECISION "].ToStr() : "";
//String NUMERIC_SCALE = rowTable["NUMERIC_SCALE"].ToStr();
//String CHARACTER_SET_NAME = rowTable["CHARACTER_SET_NAME"].ToStr();
//String COLLATION_NAME = rowTable["COLLATION_NAME"].ToStr();
//String COLUMN_TYPE = rowTable["COLUMN_TYPE"].ToStr();
String COLUMN_KEY = DataTableHelper.GetValue(rowTable, "COLUMN_KEY").ToStr();
//String EXTRA = rowTable["EXTRA"].ToStr();
//String PRIVILEGES = rowTable["PRIVILEGES"].ToStr();
//String COLUMN_COMMENT = rowTable["COLUMN_COMMENT"].ToStr();
var k = new TableRowMetaData();
k.DatabaseType = DatabaseType.MySql;
k.ID = i++;
k.ColumnName = COLUMN_NAME;
k.DataType = DATA_TYPE;
k.IsNull = IS_NULLABLE;
k.MaxChar = CHARACTER_MAXIMUM_LENGTH;
k.DataTypeMaxChar = k.DataType;
if (k.DataType.Contains("varchar"))
{
k.MaxChar = CHARACTER_MAXIMUM_LENGTH.Equals("-1") ? "4000" : CHARACTER_MAXIMUM_LENGTH;
k.DataTypeMaxChar = k.DataType + "(" + k.MaxChar + ")";
}
k.Order = ORDINAL_POSITION.ToInt();
k.ID = ++i;
k.PrimaryKey = COLUMN_KEY.Equals("PRI", StringComparison.InvariantCultureIgnoreCase);
TableRowMetaDataList.Add(k);
}
}
con.Close();
}
public void GetSelectedTableMetaData(DatabaseMetadata databaseMetaData, string selectedTable)
{
var builder = new SqlConnectionStringBuilder(databaseMetaData.ConnectionString);
var con = new SqlConnection(builder.ConnectionString);
con.Open();
string[] objArrRestrict;
var tParts = selectedTable.Split(".".ToCharArray());
objArrRestrict = new string[] {
tParts[0],
tParts[1],
tParts[2],
null
};
DataTable tbl = con.GetSchema(SqlClientMetaDataCollectionNames.Columns, objArrRestrict);
SqlDataAdapter da = new SqlDataAdapter();
#region Get Primary Key
String primaryKey = "";
DataTable ttt = new DataTable();
SqlCommand cmd = new SqlCommand("select * from " + selectedTable);
cmd.Connection = con;
SqlDataAdapter daa = new SqlDataAdapter();
daa.SelectCommand = cmd;
//da.Fill(tl);
daa.FillSchema(ttt, SchemaType.Mapped);
primaryKey = DataTableHelper.GetPrimaryKeys(ttt);
#endregion
List <TableRowMetaData> TableRowMetaDataList = new List <TableRowMetaData>();
var selectedTableObj = databaseMetaData.Tables.FirstOrDefault(r => r.DatabaseTableName.Equals(selectedTable, StringComparison.InvariantCultureIgnoreCase));
if (selectedTableObj != null)
{
selectedTableObj.TableRowMetaDataList = TableRowMetaDataList;
databaseMetaData.SelectedTable = selectedTableObj;
int i = 0;
foreach (DataRow rowTable in tbl.Rows)
{
String TABLE_CATALOG = rowTable["TABLE_CATALOG"].ToStr();
String TABLE_SCHEMA = rowTable["TABLE_SCHEMA"].ToStr();
String TABLE_NAME = rowTable["TABLE_NAME"].ToStr();
String COLUMN_NAME = rowTable["COLUMN_NAME"].ToStr();
String ORDINAL_POSITION = rowTable["ORDINAL_POSITION"].ToStr();
String COLUMN_DEFAULT = rowTable["COLUMN_DEFAULT"].ToStr();
String IS_NULLABLE = rowTable["IS_NULLABLE"].ToStr();
String DATA_TYPE = rowTable["DATA_TYPE"].ToStr();
String CHARACTER_MAXIMUM_LENGTH = rowTable["CHARACTER_MAXIMUM_LENGTH"].ToStr();
String CHARACTER_OCTET_LENGTH = rowTable["CHARACTER_OCTET_LENGTH"].ToStr();
String NUMERIC_PRECISION = rowTable["NUMERIC_PRECISION"].ToStr();
String NUMERIC_PRECISION_RADIX = rowTable["NUMERIC_PRECISION_RADIX"].ToStr();
String NUMERIC_SCALE = rowTable["NUMERIC_SCALE"].ToStr();
String DATETIME_PRECISION = rowTable["DATETIME_PRECISION"].ToStr();
String CHARACTER_SET_CATALOG = rowTable["CHARACTER_SET_CATALOG"].ToStr();
String CHARACTER_SET_SCHEMA = rowTable["CHARACTER_SET_SCHEMA"].ToStr();
String CHARACTER_SET_NAME = rowTable["CHARACTER_SET_NAME"].ToStr();
String COLLATION_CATALOG = rowTable["COLLATION_CATALOG"].ToStr();
String IS_SPARSE = rowTable["IS_SPARSE"].ToStr();
String IS_COLUMN_SET = rowTable["IS_COLUMN_SET"].ToStr();
String IS_FILESTREAM = rowTable["IS_FILESTREAM"].ToStr();
var k = new TableRowMetaData();
k.DatabaseType = DatabaseType.MsSql;
k.ColumnName = COLUMN_NAME;
k.DataType = DATA_TYPE;
k.IsNull = IS_NULLABLE;
k.MaxChar = CHARACTER_MAXIMUM_LENGTH;
k.DataTypeMaxChar = k.DataType;
if (k.DataType.Contains("varchar"))
{
k.MaxChar = CHARACTER_MAXIMUM_LENGTH.Equals("-1") ? "4000" : CHARACTER_MAXIMUM_LENGTH;
k.DataTypeMaxChar = k.DataType + "(" + k.MaxChar + ")";
}
k.Order = ORDINAL_POSITION.ToInt();
k.ID = ++i;
k.PrimaryKey = COLUMN_NAME == primaryKey;
TableRowMetaDataList.Add(k);
}
}
con.Close();
}