开发者社区> 灵风幻火> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL---数据库从入门走向大神系列(三)-修改数据库编码/DOS窗口编码
MySQL---数据库从入门走向大神系列(三)-修改数据库编码/DOS窗口编码
0 0
Confluence 6 SQL Server 输入你的数据库细节
Confluence 的安装向导将会指导你一步一步的在 Confluence 中配置安装 SQL Server 数据库。 使用 JDBC 连接(默认) JDBC 是推荐的连接你的 Confluence 到数据库中的方式。
774 0
《数据库基础及实践技术——SQL Server 2008》一3.3 查看和设置数据库选项
本节书摘来自华章出版社《 数据库基础及实践技术——SQL Server 2008》一 书中的第3章,第3.3节,作者:何玉洁,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
716 0
通过excel的VBA 是否可以 直接访问阿里云的RDS数据库(SQL Server)
通过excel的VBA 直接访问阿里云的云数据库(SQL Server)
2834 0
【SQL Server】数据库是单个用户的 无法顺利进行操作 怎么解决
1.打开数据库 2.新建查询 ,输入以下的SQL 语句 1 DECLARE @SQL VARCHAR(MAX); 2 SET @SQL='' 3 SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID) 4 FROM master.
666 0
sql server与access、excel的数据转换_数据库技巧
熟悉SQL SERVER 2000的数据库管理员都知道,其DTS可以进行数据的导入导出,其实,我们也可以使用Transact-SQL语句进行导入导出操作。在Transact-SQL语句中,我们主要使用OpenDataSource函数、OPENROWSET 函数,关于函数的详细说明,请参考SQL联机帮助。
643 0
SQL server(MSSQL)客户端工具登录数据库的两种命令行登录方式
我们安装了sqlserver服务器和sqlserver客户端工具之后,可以使用两种命令行方式登录数据库: >sqlwb -S servername或IP -E     #使用当前windows系统的账户登录sqlserver服务器 如下图: >sqlwb -S servername或IP -U username -P password     #使用数据库账户和密码登录sqlserver服务器 如下图: PS:安装好sqlserver数据库之后,默认sa账户无法登录,需要调整数据库服务配置才可。
3382 0
+关注
灵风幻火
程序诗人,向往自由。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
阿里云数据库案例集下载
立即下载
SQL Sever迁移PG经验
立即下载
PostgresChina2018_董红禹_SQL_Server迁移PG经验分享
立即下载