C# Code Snippets

Convert MySQL CreateTable Statements to MS-SQL

Sunny Kumar

This snippet will convert the create table definition from MySQL ready to use into Microsoft SQL Server 2008

Platform: .NET Framework 1.1+

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Collections;
using System.Text.RegularExpressions;

namespace MySQL2SQLServer_Qry_Conv
{
    class Program
    {
        static void Main(string[] args)
        {
            string tableName = "";
            
            StringBuilder AfterCreateTable = new StringBuilder();
            StreamReader sr = new StreamReader(new FileStream("tabledef.txt", FileMode.Open));
            string tabledef = sr.ReadToEnd();
            sr.Close();
            tabledef = tabledef.Replace("\\n", "");
            tabledef = tabledef.Replace("`", ""); 

            StringBuilder CreateTable = new StringBuilder(tabledef.Substring(0, tabledef.IndexOf(\'(\') + 1)+"\\n");
            tableName = CreateTable.ToString().Trim(); ;
            tableName = tableName.Replace("(", "").Split(\' \')[2];

            string AllcolDef = tabledef.Substring(tabledef.IndexOf(\'(\') + 1, tabledef.LastIndexOf(\')\') - CreateTable.Length).Trim(); ;
            string[] ColDefs = AllcolDef.Split(\'\\r\');
            for (int i = 0; i < ColDefs.Length; i++)
                ColDefs[i] = ColDefs[i].Trim();


            foreach (string Str in ColDefs)
            {
                string SS = Str;
                if (SS.EndsWith(","))
                {
                    SS = Str.Substring(0, Str.LastIndexOf(\',\'));
                }


                if (SS.ToLower().StartsWith("primary key"))
                {
                    CreateTable.Append(SS.ToLower());
                    CreateTable.Append("\\n,");
                }

                else if (SS.ToLower().StartsWith("unique key"))
                {
                    AfterCreateTable.Append("create unique nonclustered index " + SS.Split(\' \')[2] + " on " + tableName + SS.Split(\' \')[SS.Split(\' \').Length - 1]);
                    AfterCreateTable.Append(";\\n");
                   
                }

                else if (SS.ToLower().StartsWith("constraint"))
                {
                    if (SS.ToLower().Contains("foreign key"))
                    {
                        AfterCreateTable.Append(SS.Substring(SS.ToLower().IndexOf("foreign key"), SS.Length - SS.ToLower().IndexOf("foreign key")));
                        AfterCreateTable.Append(";\\n");
                    }
                }               
                
                
                else if (SS.ToLower().StartsWith("fulltext"))
                {
                }

                else if (SS.ToLower().Contains("key"))
                {
                    Match match = Regex.Match(SS.ToLower(), "key ");
                    if (match.Success)
                    {
                        AfterCreateTable.Append("create nonclustered index " + SS.Split(\' \')[1] + " on " + tableName + SS.Split(\' \')[2]);
                        AfterCreateTable.Append(";\\n");
                    }
                }
                else
                {

                    string[] Coldef = SS.Split(\' \');

                    for (int i = 0; i < Coldef.Length; i++)
                    {
                        if (i == 0)
                        {
                            CreateTable.Append(" " + Coldef[i] + " ");
                            continue;
                        }
                        else
                        {
                            if (Coldef[i].Contains(\'(\'))
                            {
                                string dtType = Coldef[i].Substring(0, Coldef[i].IndexOf(\'(\'));
                                string value = Coldef[i].Substring(Coldef[i].IndexOf(\'(\') + 1, Coldef[i].IndexOf(\')\') - 1 - dtType.Length);
                                CreateTable.Append(FindMatchingType(dtType.ToLower(), value) + " ");
                            }
                            else
                            {
                                CreateTable.Append(FindMatchingType(Coldef[i].ToLower()) + " ");
                            }

                        }

                    }
                    CreateTable.Append("\\n,");
                }
                
            }

            CreateTable = CreateTable.Remove(CreateTable.Length - 1, 1);
            CreateTable.Append(");\\n");
            CreateTable.Append(AfterCreateTable.ToString());
            Console.WriteLine(CreateTable);
            Console.ReadLine();

        }

        static string FindMatchingType(string str)
        {
            switch (str)
            {
                case "bool":
                    return "tinyint";
                case "boolean":
                    return "tinyint";
                case "time":
                    return "time";
                case "timestamp":
                    return "smalldatetime";
                case "mediumtext":
                    return "nvarchar(max)";
                case "longtext":
                    return "nvarchar(max)";

                case "bigint":
                    return "bigint";
                case "varchar":
                    return "varchar";
                case "comment":
                    return "--";
                case "int":
                    return "int";
                case "tinyint":
                    return "tinyint";
                case "double":
                    return "float";
                case "tinytext":
                    return "nvarchar(255)";
                case "tinyblob":
                    return "varbinary(255)";
                case "mediumblob":
                    return "varbinary(max)";
                case "longblob":
                    return "varbinary(max)";
                default:
                    return str;

            }

        }

        static string FindMatchingType(string str, string val)
        {
            switch (str)
            {
                case "bigint":
                    return "bigint";
                case "varchar":
                    return "varchar(" + val + ")";
                case "int":
                    return "int";
                case "tinyint":
                    return "tinyint";
                case "bit":
                    return "varbinary(8)";
                case "smallint":
                    return "smallint";
                case "mediumint":
                    return "mediumint";
                case "integer":
                    return "integer";
                case "float":
                    if (val.Contains(\'(\'))
                    {
                        return "float(24)";
                    }
                    else
                    {
                        return "float(" + val + ")";
                    }

                case "double":
                    return "float(53)";
                case "double precision":
                    return "float(53)";
                case "real":
                    return "float(53)";
                case "decimal":
                    return "decimal(" + val + ")";
                case "dec":
                    return "dec(" + val + ")";
                case "numeric":
                    return "numeric(" + val + ")";
                case "fixed":
                    return "fixed(" + val + ")";
                case "datetime":
                    return "datetime2";
                case "date":
                    return "date";
                case "year":
                    return "smallint";
                case "char":
                    return "nchar(" + val + ")";
                case "text":
                    if (Convert.ToInt32(val) >= 8000)
                    {
                        return "nvarchar(max)";
                    }
                    else
                    {
                        return "nvarchar(" + val + ")";
                    }
                case "binary":
                    return "binary(" + val + ")";
                case "varbinary":
                    return "varbinary(" + val + ")";
                case "blob":
                    if (Convert.ToInt32(val) >= 65535)
                    {
                        return "varbinary(max)";
                    }
                    else
                    {
                        return "varbinary(" + val + ")";
                    }


                default:
                    return str + "(" + val + ")";

            }

        }

    }
}

 

Back to C# Code Snippet List