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