Month: September 2010

If you want to create a corresponding SQL Server database table, from a ODBC table, you can use the following C# function:

Note

This function is not bullet proof, but gives you a simple example:

        public void ScriptODBCTable()
        {
            string outputFolderPath = @"C:\Temp";
            string tableName = "MyTableName";
            string fullTableName = "[dbo].[MyTableName]";

            // Delete output folder if it exist
            DirectoryInfo outputFolder = new DirectoryInfo(outputFolderPath);
            if (Directory.Exists(outputFolder.FullName)) { Directory.Delete(outputFolder.FullName, true); }

            // Create [Output] folder
            outputFolder.Create();

            // Create [Tables] output folder
            DirectoryInfo tablesOutputFolder = new DirectoryInfo(Path.Combine(outputFolder.FullName, "Tables"));
            tablesOutputFolder.Create();

            // Generate script
            StringBuilder resultScript = new StringBuilder(string.Empty);
            resultScript.AppendFormat("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'U'))", fullTableName).Append(Environment.NewLine);
            resultScript.AppendLine("BEGIN");
            resultScript.Append("CREATE TABLE ").Append(fullTableName).Append(" (").Append(Environment.NewLine);

            using (OdbcConnection connection = new OdbcConnection("uid=myUserName;Dsn=MySystemDnsName;pwd=MyPassword;"))
            {
                connection.Open();

                List<ScriptColumn> scriptColumns = new List<ScriptColumn>();
                using (DataTable table = connection.GetSchema("Columns"))
                {
                    int i = 0;
                    foreach (DataRow row in table.Rows)
                    {
                        if (i == 0)
                        {
                            foreach (System.Data.DataColumn column in row.Table.Columns)
                            {
                                string name = column.ColumnName;
                                Console.WriteLine(name);
                            }
                            i++;
                        }
                        if (row["TABLE_NAME"].ToString().Equals(tableName))
                        {
                            scriptColumns.Add(new ScriptColumn 
                            {
                                Name = row["COLUMN_NAME"].ToString(),
                                OrdinalPosition = (int)row["ORDINAL_POSITION"],
                                ColumnSize = row["COLUMN_SIZE"].ToString(),
                                Type = row["TYPE_NAME"].ToString(),
                                Nullable = (row["NULLABLE"].ToString().Equals("1")) ? "NULL" : "NOT NULL"
                            });
                        }
                    }
                }
                var sortedScriptColumns =
                    from sc in scriptColumns
                    orderby sc.OrdinalPosition
                    select sc;

                scriptColumns = sortedScriptColumns.ToList<ScriptColumn>();

                foreach (ScriptColumn column in scriptColumns)
                {
                    if (column.Type.Equals("varchar") || column.Type.Equals("nvarchar"))
                    {
                        resultScript.AppendLine(string.Format("[{0}] [{1}] ({2}) {3},", column.Name, column.Type, column.ColumnSize, column.Nullable));
                    }
                    else
                    {
                        resultScript.AppendLine(string.Format("[{0}] [{1}] {2},", column.Name, column.Type, column.Nullable));
                    }
                }
            }

            // Remove ',' from last line
            string comma = "," + Environment.NewLine;
            resultScript = new StringBuilder(resultScript.ToString().TrimEnd(comma.ToCharArray()));
            resultScript.Append(Environment.NewLine);
            resultScript.AppendLine(")");
            resultScript.AppendLine("END");

            // Write file content to disk
            string fileName = string.Format("{0}.sql", fullTableName);
            File.WriteAllText(Path.Combine(tablesOutputFolder.FullName, fileName), resultScript.ToString());
        }
    }

 

public class ScriptColumn
    {
        private string _type;
        public string Name { get; set; }
        public int OrdinalPosition { get; set; }
        public string Type 
        {
            get
            {
                return _type;
            }
            set
            {
                _type = value;
                if (!string.IsNullOrEmpty(_type))
                {
                    if (_type.Equals("integer")) { _type = "int"; }
                    if (_type.Equals("date")) { _type = "datetime"; }
                    if (_type.Equals("varchar")) { _type = "nvarchar"; }
                }
            }
        }
        public string ColumnSize { get; set; }
        public string Nullable { get; set; }
    }