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

  1. 云栖社区>
  2. 博客列表>
  3. 正文

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

灵风幻火 2017-12-12 13:54:16 浏览625 评论0

摘要: frmMain.cs public partial class frmMain : Form { private Thread TransferWorkerThread = null; public frmMain() { ...

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
这里写图片描述

【云栖快讯】阿里云栖开发者沙龙(Java技术专场)火热来袭!快来报名参与吧!  详情请点击

网友评论