exportsqlserver.cs

sqlce查询分析器

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ScriptGenerator;
using System.Data.SqlServerCe;
using System.IO;

namespace CEQuery
{
    public partial class ExportSqlServer : Form
    {
        //private bool isNewDb = false;
        private string dbName = string.Empty;
        private string ceConnString = string.Empty;

        public ExportSqlServer()
        {
            InitializeComponent();
        }     

        private void btnExport_Click(object sender, EventArgs e)
        {
            pgStatus.Value = 0;
            lblStatus.Text = string.Empty;
            if (ValidateInput())
            {
                btnExport.Enabled = false;
                txtServer.Enabled = false;
                txtDB.Enabled = false;
                txtUserId.Enabled = false;
                txtPassword.Enabled = false;
                txtCEPassword.Enabled = false;
                rdbExisting.Enabled = false;
                rdbNew.Enabled = false;                
                bgProcess.RunWorkerAsync();
                pgStatus.Visible = true;
                lblStatus.Visible = true;
            }
        }

        private bool ValidateInput()
        {
            bool isValid = true;
            if (this.txtServer.Text.Trim() == string.Empty)
            {
                isValid = false;
                errValidation.SetError(txtServer, "Provide an available Server.");
            }
            else
            {
                errValidation.SetError(txtServer, string.Empty);
            }
            if (this.txtDB.Text.Trim() == string.Empty)
            {
                isValid = false;
                errValidation.SetError(txtDB, "Provide a Database.");
            }
            else
            {
                errValidation.SetError(txtDB, string.Empty);
            }
            if (this.txtUserId.Text.Trim() == string.Empty)
            {
                isValid = false;
                errValidation.SetError(txtUserId, "Provide UserId.");
            }
            else
            {
                errValidation.SetError(txtUserId, string.Empty);
            }
            if (this.txtPassword.Text.Trim() == string.Empty)
            {
                isValid = false;
                errValidation.SetError(txtPassword, "Provide Password.");
            }
            else
            {
                errValidation.SetError(txtPassword, string.Empty);
            }
            if (this.dbName == string.Empty)
            {
                isValid = false;
                errValidation.SetError(lblDB, "Provide a Destination CE database.");
            }
            else
            {
                errValidation.SetError(lblDB, string.Empty);
            }
            //if (this.txtCEPassword.Text.Trim() == string.Empty)
            //{
            //    isValid = false;
            //    errValidation.SetError(txtCEPassword, "Provide Password.");
            //}
            //else
            //{
            //    errValidation.SetError(txtCEPassword, string.Empty);
            //}
            return isValid;
        }

        private void rdbExisting_CheckedChanged(object sender, EventArgs e)
        {
            if (rdbExisting.Checked)
            {
                openFileDialog.Title = "Open the SQL CE File";
                openFileDialog.Filter = "SQL CE Files (*.sdf)|*.sdf";
                openFileDialog.RestoreDirectory = true;
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    //isNewDb = false;
                    dbName = openFileDialog.FileName;
                    lblDB.Text = openFileDialog.FileName + " [Existing]";
                    txtCEPassword.Focus();
                }
            }
        }

        private void rdbNew_CheckedChanged(object sender, EventArgs e)
        {
            if (rdbNew.Checked)
            {
                saveFileDialog.Title = "Create the SQL CE File";
                saveFileDialog.Filter = "SQL CE Files (*.sdf)|*.sdf";
                saveFileDialog.RestoreDirectory = true;
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    //isNewDb = true;
                    dbName = saveFileDialog.FileName;
                    lblDB.Text = saveFileDialog.FileName + " [New]";
                    txtCEPassword.Focus();
                }
            }
        }

        private void bgProcess_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                bgProcess.ReportProgress(0, "Processing Destination");
                //ceConnString = string.Format("Data Source={0};Password={1};Max Database Size=4091;Max Buffer Size = 1024;Default Lock Escalation =100;Encrypt Database=TRUE", dbName, txtCEPassword.Text);
                ceConnString = string.Format("Data Source={0};Password={1};Max Database Size=4091;Max Buffer Size = 1024;Default Lock Escalation =100;Encrypt Database={2}", dbName, txtCEPassword.Text, txtCEPassword.Text.Length == 0 ? "FALSE" : "TRUE");
                if (File.Exists(dbName))
                {
                    File.Delete(dbName);
                }

                using (SqlCeEngine engine = new SqlCeEngine(ceConnString))
                {
                    engine.CreateDatabase();
                }
                bgProcess.ReportProgress(5, "Processing Source");
                string connString = string.Format("data source={0};database={1};uid={2};password={3};", txtServer.Text.Trim(), txtDB.Text.Trim(), txtUserId.Text.Trim(), txtPassword.Text);
                IRepository repository = new SQLDBRepository(connString);
                SQLGenerator generator = new SQLGenerator(repository);
                bgProcess.ReportProgress(10, "Getting Table Info");
                // The execution below has to be in this sequence
                generator.GenerateTables();
                bgProcess.ReportProgress(15, "Getting Content Info");
                generator.GenerateTableContent();
                bgProcess.ReportProgress(20, "Getting PK Info");
                generator.GeneratePrimaryKeys();
                bgProcess.ReportProgress(25, "Getting Index/Unique Key Info");
                generator.GenerateIndex();
                bgProcess.ReportProgress(30, "Getting FK Info");
                generator.GenerateForeignKeys();
                Helper.WriteIntoFile(generator.GeneratedScript, "C:\\Query.txt");
                string fullScripts = generator.GeneratedScript;
                using (SqlCeConnection connection = new SqlCeConnection(ceConnString))
                {

                    string[] commands = fullScripts.Split(';');
                    double steps = Convert.ToDouble(70) / Convert.ToDouble(commands.Length > 0 ? commands.Length : 1);
                    double percentage = Convert.ToDouble(25) + steps;
                    SqlCeCommand cmd = new SqlCeCommand();
                    cmd.Connection = connection;
                    connection.Open();
                    foreach (string command in commands)
                    {
                        bgProcess.ReportProgress((int)Math.Round(percentage,0), "Executing Query");
                        percentage += steps;
                        if (command.Replace("\r\n", string.Empty).Trim() != string.Empty)
                        {
                            try
                            {
                                cmd.CommandText = command
                                    .Replace((char)192, ';')
                                    .Replace((char)193, '\\');
                                cmd.ExecuteNonQuery();
                            }
                            catch
                            {                                
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                bgProcess.ReportProgress(100, "Completed");
            }
        }

        private void bgProcess_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            pgStatus.Value = e.ProgressPercentage;
            lblStatus.Text = string.Format("{0} : {1}% Completed",e.UserState.ToString(),e.ProgressPercentage.ToString());
        }

        private void bgProcess_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            btnExport.Enabled = true;
            txtServer.Enabled = true;
            txtDB.Enabled = true;
            txtUserId.Enabled = true;
            txtPassword.Enabled = true;
            txtCEPassword.Enabled = true;
            rdbExisting.Enabled = true;
            rdbNew.Enabled = true;
        }

        private void ExportSqlServer_Load(object sender, EventArgs e)
        {
            pgStatus.Visible = false;
            lblStatus.Visible = false;
        }
    }
}