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