如何在cql中为ms中的ms访问数据库生成脚本 [英] How to generate script in sql for databases in ms access in c#

问题描述

HI


我想生成一个脚本，用于在包含.mdb文件的文件夹中创建表，该脚本将转换为sql并作为数据库创建为sql。 />


这意味着我想通过在c＃中编写代码将mdb数据库转换为sql server数据库，或者当我们点击前端的按钮时它将执行该操作。



我写了这样的鳕鱼从mdb中检索所有表是好的，但如何用这些表生成脚本。



private void Chb_Connect_CheckedChanged（object sender，EventArgs e）

{

if（Chb_Connect.Checked == true）

{

尝试

{

DbProviderFactory factory = DbProviderFactories.GetFactory（System.Data.OleDb）;

DataTable userTables = null;

使用（连接）

{$ / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ mappath +，* .mdb，SearchOption.TopDirectoryOnly）;

// c：\ test\test.mdb

foreach（filePaths中的字符串tr） ）

{

connection.ConnectionString =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =+ tr +;

string [] restrictions = new string [4];

limits [3] =Table;

connection.Open（）;

userTables = connection.GetSchema（Tables，限制）;

List< string> tableNames = new List< string>（）;

for（int i = 0; i< userTables.Rows.Count; i ++）

tableNames.Add（userTables。行[i] [2] .ToString（））;

尝试

{

foreach（tableName中的字符串tableName）

{

OleDbCommand cmd = new OleDbCommand（select * from [+ tableName +]）;

cmd.Connection = connection;

dataadapter = new OleDbDataAdapter（cmd）;

DataSet ds = new DataSet（）;

DataTable dt = new DataTable（）;

尝试

{

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（userTables中的表tbl）

// {

// ScriptingOptions options = new ScriptingOptions（）;

// options.ClusteredIndexes = true;

// options.Default = true;

// options.DriAll = true;

// options.Indexes = true;

// options.IncludeHeaders = true;



// StringCollection coll = tbl.Script（选项）;

// foreach（字符串str in coll）

// {

// sb.Append（str）;

// sb.Append（Environment.NewLine）;

//}

//}



}

}

}

catch（异常）例）{}

string fileName = @D：\ Script.sql;

if（File.Exists（fileName））

{

File.Delete（fileName）;

}

//创建一个新文件

using（ FileStream fs = File.Create（fileName））

{

StreamWriter writer = new StreamWriter（fs）;

writer.Write（sb） ;

writer.Close（）;

}

试试

{



}

catch（Ex Ex Ex）{}

}

}

公共静态字符串BuildCreateTableScript（DataTable表）

{

// if（！Helper。 IsValidDatatable（Table，IgnoreZeroRows：true））

//返回string.Empty;



StringBuilder结果= new StringBuilder（）;

result.AppendFormat（CREATE TABLE [{1}]（{0}，Environment.NewLine，Table.TableName）;



bool FirstTime = true;

foreach（Table.Columns.OfType中的DataColumn列< datacolumn>（））

{

if（FirstTime）FirstTime = false;

else

result.Append（，）;



result.AppendFormat（ [{0}] {1} {2} {3}，

column.ColumnNa我，// 0

GetSQLTypeAsString（column.DataType），// 1

column.AllowDBNull？ NULL：NOT NULL，// 2

Environment.NewLine // 3

）;

}

result.AppendFormat（）ON [PRIMARY] {0} GO {0} {0}，Environment.NewLine）;



//建立一个ALTER TABLE脚本，用于向已存在的表添加键。

if（Table.PrimaryKey.Length> 0）

result.Append（BuildKeysScript（Table））;



返回result.ToString（）;

}



// /< summary>

///构建一个ALTER TABLE脚本，将主键或复合键添加到已存在的表中。

///

私有静态字符串BuildKeysScript（DataTable表）

{

//已经通过公共方法CreateTable检查。如果将方法设为公开则取消注释

// if（Helper.IsValidDatatable（Table，IgnoreZeroRows：true））return string.Empty;

if（Table.PrimaryKey。长度< 1）返回string.Empty;



StringBuilder结果=新的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>（）。选择（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，表.TableName，keys，keyName）;

}



返回result.ToString（）;

}



///< summary>

///返回等效的SQL数据类型，作为在SQL脚本生成方法中使用的字符串。

///

私有静态字符串GetSQLTypeAsString（类型数据类型）

{

switch（DataType.Name ）

{

caseBoolean：返回[bit];

caseChar：返回[char];

caseSByte：返回[tinyint];

caseInt16：return[smallint] ;

caseInt32：返回[int];

caseInt64：返回[bigint];

caseByte：返回[tinyint] UNSIGNED;

caseUInt16：返回[smallint] UNSIGNED;

caseUInt32：return[ int] UNSIGNED;

caseUInt64：返回[bigint] UNSIGNED;

caseSingle：返回[float];

caseDouble：返回[double];

caseDecimal：返回[decimal];

caseDateTime：return [datetime];

案例Guid：返回[uniqueidentifier];

案例对象：返回[variant];

caseString：返回[nvarchar]（250）;

默认：返回[nvarchar]（MAX）;

}







我不想在目标sql表中的nvarchar中使用250个字符我想根据源表获取，即某些字段有nvarchar（50）而某些字段有nvarchar（2）并且我想要复制主键还有。



我该怎么做才能得到这个建议我解决方案

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

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

/// <summary>
/// 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

推荐答案

你可以使用ADO .NET来读取MS Access DB并在SQL中创建相同的内容。每个数据库需要一个适配器。

You can make use of ADO .NET to read MS Access DB and Create the same in SQL. You'll need to adapters one for each database.

这篇关于如何在cql中为ms中的ms访问数据库生成脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！