How to generate script in sql for databases in ms access in c#

See more:

SQL

C#5.0

HI
I want to generate a script for creating tables in a folder that containing .mdb files and that script will convert into sql and create into sql as a database.

That means I want to convert total mdb database into sql server database by writing code in c# or when we click a button in frontend it will perform that operation.

I have written cod like this retrieving all tables from mdb is ok but how to generate script with those tables.

private void Chb_Connect_CheckedChanged(object sender, EventArgs e)
{
if (Chb_Connect.Checked == true)
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DataTable userTables = null;
using (connection)
{
string mappath = dataGridView1.CurrentRow.Cells["Path"].Value.ToString();
string[] filePaths = Directory.GetFiles(@"" + mappath + "", "*.mdb", SearchOption.TopDirectoryOnly);
// c:\test\test.mdb
foreach (string tr in filePaths)
{
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tr + "";
string[] restrictions = new string[4];
restrictions[3] = "Table";
connection.Open();
userTables = connection.GetSchema("Tables", restrictions);
List<string> tableNames = new List<string>();
for (int i = 0; i < userTables.Rows.Count; i++)
tableNames.Add(userTables.Rows[i][2].ToString());
try
{
foreach (string tableName in tableNames)
{
OleDbCommand cmd = new OleDbCommand("select * from [" + tableName + "]");
cmd.Connection = connection;
dataadapter = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
dataadapter.Fill(ds, tableName);
dt = ds.Tables[0];
}
catch (Exception Ex) { }
string s = BuildCreateTableScript(dt);
sb.Append(s);
sb.Append(Environment.NewLine);
}
}
catch (Exception Ex) { connection.Close(); }
connection.Close();
//BindingSource bs = new BindingSource();
//bs.DataSource = userTables;
//foreach (Table tbl in userTables)
//{
// ScriptingOptions options = new ScriptingOptions();
// options.ClusteredIndexes = true;
// options.Default = true;
// options.DriAll = true;
// options.Indexes = true;
// options.IncludeHeaders = true;

// StringCollection coll = tbl.Script(options);
// foreach (string str in coll)
// {
// sb.Append(str);
// sb.Append(Environment.NewLine);
// }
//}

}
}
}
catch (Exception Ex) { }
string fileName = @"D:\Script.sql";
if (File.Exists(fileName))
{
File.Delete(fileName);
}
// Create a new file
using (FileStream fs = File.Create(fileName))
{
StreamWriter writer = new StreamWriter(fs);
writer.Write(sb);
writer.Close();
}
try
{

}
catch (Exception Ex) { }
}
}
public static string BuildCreateTableScript(DataTable Table)
{
//if (!Helper.IsValidDatatable(Table, IgnoreZeroRows: true))
// return string.Empty;

StringBuilder result = new StringBuilder();
result.AppendFormat("CREATE TABLE [{1}] ({0} ", Environment.NewLine, Table.TableName);

bool FirstTime = true;
foreach (DataColumn column in Table.Columns.OfType<datacolumn>())
{
if (FirstTime) FirstTime = false;
else
result.Append(" ,");

result.AppendFormat("[{0}] {1} {2} {3}",
column.ColumnName, // 0
GetSQLTypeAsString(column.DataType), // 1
column.AllowDBNull ? "NULL" : "NOT NULL", // 2
Environment.NewLine // 3
);
}
result.AppendFormat(") ON [PRIMARY]{0}GO{0}{0}", Environment.NewLine);

// Build an ALTER TABLE script that adds keys to a table that already exists.
if (Table.PrimaryKey.Length > 0)
result.Append(BuildKeysScript(Table));

return result.ToString();
}

///

/// Builds an ALTER TABLE script that adds a primary or composite key to a table that already exists.
///

private static string BuildKeysScript(DataTable Table)
{
// Already checked by public method CreateTable. Un-comment if making the method public
// if (Helper.IsValidDatatable(Table, IgnoreZeroRows: true)) return string.Empty;
if (Table.PrimaryKey.Length < 1) return string.Empty;

StringBuilder result = new StringBuilder();

if (Table.PrimaryKey.Length == 1)
result.AppendFormat("ALTER TABLE {1}{0} ADD PRIMARY KEY ({2}){0}GO{0}{0}", Environment.NewLine, Table.TableName, Table.PrimaryKey[0].ColumnName);
else
{
List<string> compositeKeys = Table.PrimaryKey.OfType<datacolumn>().Select(dc => dc.ColumnName).ToList();
string keyName = compositeKeys.Aggregate((a, b) => a + b);
string keys = compositeKeys.Aggregate((a, b) => string.Format("{0}, {1}", a, b));
result.AppendFormat("ALTER TABLE {1}{0}ADD CONSTRAINT pk_{3} PRIMARY KEY ({2}){0}GO{0}{0}", Environment.NewLine, Table.TableName, keys, keyName);
}

return result.ToString();
}

///

/// Returns the SQL data type equivalent, as a string for use in SQL script generation methods.
///

private static string GetSQLTypeAsString(Type DataType)
{
switch (DataType.Name)
{
case "Boolean": return "[bit]";
case "Char": return "[char]";
case "SByte": return "[tinyint]";
case "Int16": return "[smallint]";
case "Int32": return "[int]";
case "Int64": return "[bigint]";
case "Byte": return "[tinyint] UNSIGNED";
case "UInt16": return "[smallint] UNSIGNED";
case "UInt32": return "[int] UNSIGNED";
case "UInt64": return "[bigint] UNSIGNED";
case "Single": return "[float]";
case "Double": return "[double]";
case "Decimal": return "[decimal]";
case "DateTime": return "[datetime]";
case "Guid": return "[uniqueidentifier]";
case "Object": return "[variant]";
case "String": return "[nvarchar](250)";
default: return "[nvarchar](MAX)";
}



I don't want to take 250 chars in nvarchar in destination sql table i want to take as per source table i.e some fields have nvarchar(50) and some fields have nvarchar(2) and I want to copy Primary keys also.

What should i do to get this Would u pls suggest me the solution