一个将Access数据库转成Mssql数据库的简单工具
程序员文章站
2022-03-08 17:41:40
...
frmMain.cs
public partial class frmMain : Form
{
private Thread TransferWorkerThread = null;
public frmMain()
{
InitializeComponent();
}
private void LoadSettings()
{
StreamReader setting_file = null;
char[] d = new char[] { '=' };
try
{
setting_file = new StreamReader("setting.ini");
object[] text_type;
Program.GeneralAppSetting.Collation = setting_file.ReadLine().Split(d)[1];
text_type = Access2Mssql.AccessToMssql.ParseTextType(setting_file.ReadLine().Split(d)[1]);
Program.GeneralAppSetting.TextType = (string)text_type[0];
Program.GeneralAppSetting.Size = (int)text_type[1];
Program.GeneralAppSetting.AutoFixTextPrimaryAttempt = Convert.ToBoolean(setting_file.ReadLine().Split(d)[1]);
Program.GeneralAppSetting.NewDatabaseDoesNotExist = Convert.ToBoolean(setting_file.ReadLine().Split(d)[1]);
Program.GeneralAppSetting.DatabaseStoragePath = setting_file.ReadLine().Split(d)[1];
Program.GeneralAppSetting.TryAutoDetectStoragePath = Convert.ToBoolean(setting_file.ReadLine().Split(d)[1]);
Program.GeneralAppSetting.DatabaseLevel = Convert.ToUInt16(setting_file.ReadLine().Split(d)[1]);
Program.GeneralAppSetting.MdfMaxSize = Convert.ToInt32(setting_file.ReadLine().Split(d)[1]);
Program.GeneralAppSetting.MdfSizeType = (FileSizeType)Convert.ToInt32(setting_file.ReadLine().Split(d)[1]);
Program.GeneralAppSetting.LdfMaxSize = Convert.ToInt32(setting_file.ReadLine().Split(d)[1]);
Program.GeneralAppSetting.LdfSizeType = (FileSizeType)Convert.ToInt32(setting_file.ReadLine().Split(d)[1]);
setting_file.Close();
}
catch
{
Program.GeneralAppSetting.Collation = "Turkish_CI_AS";
Program.GeneralAppSetting.TextType = "varchar";
Program.GeneralAppSetting.Size = 50;
Program.GeneralAppSetting.NewDatabaseDoesNotExist = false;
Program.GeneralAppSetting.AutoFixTextPrimaryAttempt = false;
Program.GeneralAppSetting.DatabaseStoragePath = string.Empty;
Program.GeneralAppSetting.TryAutoDetectStoragePath = true;
Program.GeneralAppSetting.DatabaseLevel = 9;
Program.GeneralAppSetting.MdfMaxSize = 0;
Program.GeneralAppSetting.MdfSizeType = FileSizeType.GB;
Program.GeneralAppSetting.LdfMaxSize = 1024;
Program.GeneralAppSetting.LdfSizeType = FileSizeType.GB;
}
}
private void frmMain_Load(object sender, EventArgs e)
{
LoadSettings();
cbAuthentication.SelectedIndex = 0;
Program.Converter = new AccessToMssql();
}
private void SetStatus(string status)
{
lblStatus.Text = status;
}
private void ConnectionError(ErrorType type, string extend_error_msg)
{
string ErrorMessage = string.Empty;
switch (type)
{
case ErrorType.COULD_NOT_CONNECT_ACCESS:
ErrorMessage = "无法连接到access数据库!";
break;
case ErrorType.COULD_NOT_CONNECT_MSSQL:
ErrorMessage = "无法连接到mssql server数据库!";
break;
}
ErrorMessage += "\n\n扩展消息:\n";
ErrorMessage += extend_error_msg;
MessageBox.Show(ErrorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
private void Completed(ConvertSummary summary)
{
frmOperationSummary frmOS = new frmOperationSummary();
Program.Converter.Disconnect();
Program.Converter.Dispose();
frmOS.SetSummary(ref summary);
frmOS.ShowDialog();
this.BeginInvoke((MethodInvoker)delegate() { this.btnStart.Text = "开始转换"; });
}
private void OperationFailed(ref string msg)
{
}
private void SetDatabaseSettingStructure(out DatabaseSettings Setting)
{
Setting.AccessDbFile = txtAccessSource.Text;
Setting.Database = txtDatabase.Text;
Setting.IsSqlAuthencation = txtUsername.Enabled;
Setting.TrustedConnection = chkTrustedConn.Checked;
Setting.Username = txtUsername.Text;
Setting.Password = txtPassword.Text;
Setting.Server = txtServer.Text;
}
private void StartOperation()
{
DatabaseSettings setting = new DatabaseSettings();
SetDatabaseSettingStructure(out setting);
if (!Program.Converter.EventSet)
{
Program.Converter.OnProgressStatus += new OnProgressEventHandler(SetStatus);
Program.Converter.OnOperationFailed += new OperationFailedExceptionEventHandler(OperationFailed);
Program.Converter.OnConnectionError += new ConnectionExceptionEventHandler(ConnectionError);
Program.Converter.OnComplete += new OnCompleteEventHandler(Completed);
}
if (!Program.Converter.Connect(setting))
{
Program.Converter.Dispose();
this.BeginInvoke((MethodInvoker)delegate() { this.btnStart.Text = "开始转换"; });
}
else
Program.Converter.StartTransfer();
}
private void ActivateOperationWorkerThread()
{
TransferWorkerThread = null;
TransferWorkerThread = new Thread(new ThreadStart(StartOperation));
btnStart.Text = "Abort";
TransferWorkerThread.Start();
}
private void cbAuthentication_SelectedIndexChanged(object sender, EventArgs e)
{
label4.Enabled = label5.Enabled = txtUsername.Enabled = txtPassword.Enabled = chkMasking.Enabled = (cbAuthentication.SelectedIndex == 1);
}
private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog access = new OpenFileDialog();
access.Filter = "Access Database File|*.mdb";
access.InitialDirectory = "C:\\";
access.ShowDialog();
txtAccessSource.Text = access.FileName;
}
private void chkMasking_CheckedChanged(object sender, EventArgs e)
{
txtPassword.PasswordChar = (chkMasking.Checked) ? '\0' : '*';
}
private void btnStart_Click(object sender, EventArgs e)
{
if (TransferWorkerThread == null || TransferWorkerThread.ThreadState == ThreadState.Stopped)
{
ActivateOperationWorkerThread();
return;
}
switch (TransferWorkerThread.ThreadState)
{
case ThreadState.Unstarted:
case ThreadState.Aborted:
{
ActivateOperationWorkerThread();
}
break;
case ThreadState.Running:
{
frmChoise choise = new frmChoise();
choise.ShowDialog();
switch (choise.m_Stat)
{
case 0:
{
lblStatus.Text = "中止...请稍候...";
Program.Converter.AbortOperation();
lblStatus.Text = "用户中断操作";
btnStart.Text = "开始转换";
}
break;
case 1:
{
TransferWorkerThread.Suspend();
btnStart.Text = "恢复";
lblStatus.Text = "暂停";
}
break;
}
}
break;
case ThreadState.Suspended:
{
TransferWorkerThread.Resume();
btnStart.Text = "Abort";
}
break;
}
}
private void btnSettings_Click(object sender, EventArgs e)
{
frmSettings dlgSettings = new frmSettings();
dlgSettings.Show();
}
private void btnAbout_Click(object sender, EventArgs e)
{
frmAbout frmAboutProgram = new frmAbout();
frmAboutProgram.ShowDialog();
}
private void backupRestoreToolStripMenuItem_Click(object sender, EventArgs e)
{
}
}
frmSettings.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
namespace Access2Mssql
{
public partial class frmSettings : Form
{
private string[] DefaultCollations = new string[]
{
"Chinese_PRC_BIN",
"Chinese_PRC_BIN2",
"Chinese_PRC_CI_AI",
"Chinese_PRC_CI_AI_WS",
"Chinese_PRC_CI_AI_KS",
"Chinese_PRC_CI_AI_KS_WS",
"Chinese_PRC_CI_AS", //Chinese_PRC_指针对大陆简体字UNICODE的排序规则,_CI(CS) :是否区分大小写,CI不区分,CS区分。_AI(AS) :是否区分重音,AI不区分,AS区分。
"Chinese_PRC_CI_AS_WS",
"Chinese_PRC_CI_AS_KS",
"Chinese_PRC_CI_AS_KS_WS",
"Chinese_PRC_CS_AI",
"Chinese_PRC_CS_AI_WS",
"Chinese_PRC_CS_AI_KS",
"Chinese_PRC_CS_AI_KS_WS",
"Chinese_PRC_CS_AS",
"Chinese_PRC_CS_AS_WS",
"Chinese_PRC_CS_AS_KS",
"Chinese_PRC_CS_AS_KS_WS",
"Chinese_Simplified_Stroke_Order_100_CI_AI_KS_WS",
"Chinese_Simplified_Stroke_Order_100_CI_AS",
"Chinese_Simplified_Stroke_Order_100_CI_AS_WS",
"Chinese_Simplified_Stroke_Order_100_CI_AS_KS",
"Chinese_Simplified_Stroke_Order_100_CI_AS_KS_WS",
"Chinese_Simplified_Stroke_Order_100_CS_AI",
"Chinese_Simplified_Stroke_Order_100_CS_AI_WS",
"Chinese_Simplified_Stroke_Order_100_CS_AI_KS",
"Chinese_Simplified_Stroke_Order_100_CS_AI_KS_WS",
"Chinese_Simplified_Stroke_Order_100_CS_AS ",
"Chinese_Simplified_Stroke_Order_100_CS_AS_WS ",
"Chinese_Simplified_Stroke_Order_100_CS_AS_KS ",
"Chinese_Simplified_Stroke_Order_100_CS_AS_KS_WS ",
"Turkish_BIN",
"Turkish_BIN2",
"Turkish_CI_AI",
"Turkish_CI_AI_WS",
"Turkish_CI_AI_KS",
"Turkish_CI_AI_KS_WS",
"Turkish_CI_AS",
"Turkish_CI_AS_WS",
"Turkish_CI_AS_KS",
"Turkish_CI_AS_KS_WS",
"Turkish_CS_AI",
"Turkish_CS_AI_WS",
"Turkish_CS_AI_KS",
"Turkish_CS_AI_KS_WS",
"Turkish_CS_AS",
"Turkish_CS_AS_WS",
"Turkish_CS_AS_KS",
"Turkish_CS_AS_KS_WS"
};
public frmSettings()
{
InitializeComponent();
}
private void PrepateExtendedSettings()
{
pnlDbSettings.Enabled = chkExistDb.Checked;
pnlDbSettings.BackColor = (chkExistDb.Checked) ? Color.DarkSeaGreen : Color.DarkRed;
}
private void cbTextType_SelectedIndexChanged(object sender, EventArgs e)
{
txtVarSize.Enabled = !(cbTextType.SelectedIndex > 3);
}
private void txtVarSize_TextChanged(object sender, EventArgs e)
{
int size = 0;
try
{
size = Convert.ToInt32(txtVarSize.Text);
}
catch
{
txtVarSize.Text = "0";
size = 0;
}
if (size < 0 || size > 65536)
{
txtVarSize.Text = "0";
size = 0;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
StreamWriter setting_file = new StreamWriter("setting.ini");
try
{
setting_file.WriteLine("collation={0}", cbCollation.Text);
setting_file.WriteLine("text_type_def={0}", cbTextType.Text + "(" + txtVarSize.Text + ")");
setting_file.WriteLine("auto_fix={0}", chkFix.Checked.ToString().ToLower());
setting_file.WriteLine("new_database={0}", chkExistDb.Checked.ToString().ToLower());
setting_file.WriteLine("sql_server_storage_path={0}", txtStoragePath.Text);
setting_file.WriteLine("auto_detect_storage_path={0}", chkAutoDetect.Checked.ToString().ToLower());
setting_file.WriteLine("compatible_level={0}", mnCompatibleLevel.Value);
setting_file.WriteLine("mdf_max_size={0}", txtMdfMax.Text);
setting_file.WriteLine("mdf_type={0}", cbMdfType.SelectedIndex);
setting_file.WriteLine("ldf_max_size={0}", txtLdfMax.Text);
setting_file.WriteLine("ldf_type={0}", cbLdfType.SelectedIndex);
Program.GeneralAppSetting.Collation = cbCollation.Text;
Program.GeneralAppSetting.TextType = cbTextType.Text;
Program.GeneralAppSetting.Size = Convert.ToInt32(txtVarSize.Text);
Program.GeneralAppSetting.NewDatabaseDoesNotExist = chkExistDb.Checked;
Program.GeneralAppSetting.AutoFixTextPrimaryAttempt = chkFix.Checked;
Program.GeneralAppSetting.DatabaseStoragePath = txtStoragePath.Text;
Program.GeneralAppSetting.DatabaseLevel = (ushort)mnCompatibleLevel.Value;
Program.GeneralAppSetting.MdfMaxSize = Convert.ToInt32(txtMdfMax.Text);
Program.GeneralAppSetting.MdfSizeType = (FileSizeType)cbLdfType.SelectedIndex;
Program.GeneralAppSetting.LdfMaxSize = Convert.ToInt32(txtLdfMax.Text);
Program.GeneralAppSetting.LdfSizeType = (FileSizeType)cbLdfType.SelectedIndex;
}
catch
{
MessageBox.Show("Settings file corrupted!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
setting_file.Close();
this.Close();
}
private void frmSettings_Load(object sender, EventArgs e)
{
StreamReader collations;
try
{
collations = new StreamReader(Application.StartupPath + "\\collations.cdb");
do
{
cbCollation.Items.Add(collations.ReadLine());
} while (collations.EndOfStream != true);
cbCollation.SelectedIndex = 0;
collations.Close();
collations.Dispose();
}
catch
{
MessageBox.Show("collations.cdb not found!");
for (int i = 0; i < DefaultCollations.Length; i++)
cbCollation.Items.Add(DefaultCollations[i]);
cbCollation.SelectedIndex = 0;
}
cbCollation.Text = Program.GeneralAppSetting.Collation;
cbTextType.SelectedIndex = cbTextType.FindString(Program.GeneralAppSetting.TextType);
txtVarSize.Text = Program.GeneralAppSetting.Size.ToString();
chkExistDb.Checked = Program.GeneralAppSetting.NewDatabaseDoesNotExist;
chkFix.Checked = Program.GeneralAppSetting.AutoFixTextPrimaryAttempt;
txtStoragePath.Text = Program.GeneralAppSetting.DatabaseStoragePath;
chkAutoDetect.Checked = Program.GeneralAppSetting.TryAutoDetectStoragePath;
mnCompatibleLevel.Value = Program.GeneralAppSetting.DatabaseLevel;
txtMdfMax.Text = Program.GeneralAppSetting.MdfMaxSize.ToString();
cbMdfType.SelectedIndex = (int)Program.GeneralAppSetting.MdfSizeType;
txtLdfMax.Text = Program.GeneralAppSetting.LdfMaxSize.ToString();
cbLdfType.SelectedIndex = (int)Program.GeneralAppSetting.LdfSizeType;
PrepateExtendedSettings();
}
private void chkExistDb_CheckedChanged(object sender, EventArgs e)
{
PrepateExtendedSettings();
}
private void chkFix_CheckedChanged(object sender, EventArgs e)
{
}
}
}
运行结果如图:
access数据库内容
转换后MSSQL的内容
C# 未在本地计算机上注册“Microsoft.Jet.OLEDB.4.0”提供程序
win7或win8 64位调试程序,出现这样的错误提示:未在本地计算机上注册 Microsoft.Jet.OLEDB.4.0 提供程序
解决方法如下:
在项目–》*属性—–》生成下面将目标平台改成X86