欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

一个将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数据库的简单工具


一个将Access数据库转成Mssql数据库的简单工具


一个将Access数据库转成Mssql数据库的简单工具


access数据库内容
一个将Access数据库转成Mssql数据库的简单工具

转换后MSSQL的内容
一个将Access数据库转成Mssql数据库的简单工具


C# 未在本地计算机上注册“Microsoft.Jet.OLEDB.4.0”提供程序

win7或win8 64位调试程序,出现这样的错误提示:未在本地计算机上注册 Microsoft.Jet.OLEDB.4.0 提供程序

解决方法如下:

在项目–》*属性—–》生成下面将目标平台改成X86
一个将Access数据库转成Mssql数据库的简单工具