如何用C#来部署数据库

现在好多程序，都是与数据库相关的，因此在做安装的时候，部署数据库看似是一件很复杂的事情。其实就我个人而言，部署数据库是很简单，大致的思路如下：

1．  用本身的DBMS来产生数据库创建的SQL脚本；

2．  接下来就是写程序来执行SQL脚本，从而达到创建数据库的目的。

 

以下用一个举例来说明，数据库服务器用的是SQL Server。

 

首先要在数据库生成好的SQL脚本最前头，加入如下语句：

       use master

GO

      

if exists (select * from sysdatabases where name='mytest')

            drop database mytest

GO

      

create database mytest

GO

      

use mytest

GO

注：其中“mytest”是要创建的数据库名。

 

而程序的代码如下：

//---------------------------Create DB-------------------------------------

//-------------------------------------------------------------------------

//---File:frmCreateDB.cs

//---Description:The main form file to create database using specific SQL file

//---Author:Knight

//---Date:Mar.18, 2006

//-------------------------------------------------------------------------

//-------------------------{ Create DB }-----------------------------------

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

 

using System.IO;

namespace CreateDB

{

    /// <summary>

    /// Summary description for frmCreateDB.

    /// </summary>

    public class frmCreateDB : System.Windows.Forms.Form

    {

        private System.Windows.Forms.Label label1;

        private System.Windows.Forms.TextBox txtServerName;

        private System.Windows.Forms.Label label2;

        private System.Windows.Forms.Label label3;

        private System.Windows.Forms.TextBox txtUserName;

        private System.Windows.Forms.TextBox txtPassword;

        private System.Windows.Forms.Button btnCreateDB;

        /// <summary>

        /// Required designer variable.

        /// </summary>

        private System.ComponentModel.Container components = null;

 

        public frmCreateDB()

        {

            //

            // Required for Windows Form Designer support

            //

            InitializeComponent();

 

            //

            // TODO: Add any constructor code after InitializeComponent call

            //

        }

 

        /// <summary>

        /// Clean up any resources being used.

        /// </summary>

        protected override void Dispose( bool disposing )

        {

            if( disposing )

            {

                if (components != null)

                {

                    components.Dispose();

                }

            }

            base.Dispose( disposing );

        }

 

        #region Windows Form Designer generated code

        /// <summary>

        /// Required method for Designer support - do not modify

        /// the contents of this method with the code editor.

        /// </summary>

        private void InitializeComponent()

        {

            this.label1 = new System.Windows.Forms.Label();

            this.txtServerName = new System.Windows.Forms.TextBox();

            this.txtUserName = new System.Windows.Forms.TextBox();

            this.label2 = new System.Windows.Forms.Label();

            this.txtPassword = new System.Windows.Forms.TextBox();

            this.label3 = new System.Windows.Forms.Label();

            this.btnCreateDB = new System.Windows.Forms.Button();

            this.SuspendLayout();

            //

            // label1

            //

            this.label1.AutoSize = true;

            this.label1.Location = new System.Drawing.Point(32, 32);

            this.label1.Name = "label1";

            this.label1.Size = new System.Drawing.Size(74, 16);

            this.label1.TabIndex = 0;

            this.label1.Text = "Server Name:";

            //

            // txtServerName

            //

            this.txtServerName.Location = new System.Drawing.Point(120, 32);

            this.txtServerName.Name = "txtServerName";

            this.txtServerName.Size = new System.Drawing.Size(152, 20);

            this.txtServerName.TabIndex = 1;

            this.txtServerName.Text = "";

            //

            // txtUserName

            //

            this.txtUserName.Location = new System.Drawing.Point(120, 64);

            this.txtUserName.Name = "txtUserName";

            this.txtUserName.Size = new System.Drawing.Size(152, 20);

            this.txtUserName.TabIndex = 3;

            this.txtUserName.Text = "";

            //

            // label2

            //

            this.label2.AutoSize = true;

            this.label2.Location = new System.Drawing.Point(40, 64);

            this.label2.Name = "label2";

            this.label2.Size = new System.Drawing.Size(64, 16);

            this.label2.TabIndex = 2;

            this.label2.Text = "User Name:";

            //

            // txtPassword

            //

            this.txtPassword.Location = new System.Drawing.Point(120, 96);

            this.txtPassword.Name = "txtPassword";

            this.txtPassword.PasswordChar = '*';

            this.txtPassword.Size = new System.Drawing.Size(152, 20);

            this.txtPassword.TabIndex = 5;

            this.txtPassword.Text = "";

            //

            // label3

            //

            this.label3.AutoSize = true;

            this.label3.Location = new System.Drawing.Point(48, 96);

            this.label3.Name = "label3";

            this.label3.Size = new System.Drawing.Size(57, 16);

            this.label3.TabIndex = 4;

            this.label3.Text = "Password:";

            //

            // btnCreateDB

            //

            this.btnCreateDB.Location = new System.Drawing.Point(168, 136);

            this.btnCreateDB.Name = "btnCreateDB";

            this.btnCreateDB.Size = new System.Drawing.Size(104, 23);

            this.btnCreateDB.TabIndex = 6;

            this.btnCreateDB.Text = "&Create DB";

            this.btnCreateDB.Click += new System.EventHandler(this.btnCreateDB_Click);

            //

            // frmCreateDB

            //

            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

            this.ClientSize = new System.Drawing.Size(306, 175);

            this.Controls.Add(this.btnCreateDB);

            this.Controls.Add(this.txtPassword);

            this.Controls.Add(this.label3);

            this.Controls.Add(this.txtUserName);

            this.Controls.Add(this.label2);

            this.Controls.Add(this.txtServerName);

            this.Controls.Add(this.label1);

            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;

            this.MaximizeBox = false;

            this.Name = "frmCreateDB";

            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;

            this.Text = "Create DB";

            this.ResumeLayout(false);

 

        }

        #endregion

 

        /// <summary>

        /// The main entry point for the application.

        /// </summary>

        [STAThread]

        static void Main ()

        {

            Application.Run(new frmCreateDB());

        }

 

        private void btnCreateDB_Click(object sender, System.EventArgs e)

        {

            SqlConnection sqlConn = new SqlConnection();

            sqlConn.ConnectionString = "Data Source= " + txtServerName.Text + ";"

                + " User id=" + txtUserName.Text + ";"

                + " Password=" + txtPassword.Text + "; Initial Catalog=master";

 

            try

            {

                sqlConn.Open();

            }

            catch

            {

                MessageBox.Show( "Failed to connect to DB!" );

                return;

            }

 

            //Create DB using specific file

            CreateDB( ref sqlConn );

            sqlConn.Close();

            sqlConn.Dispose();

        }

 

        private bool ReadSQLFromFile(out string strQuery)

        {

            const string strFileName = "DBFile.sql";//Give specific SQL file

            strQuery = ""; //Init return value

 

            if( File.Exists(strFileName) )

            {

                StreamReader sr = File.OpenText(strFileName);

                strQuery = sr.ReadToEnd();

               

                sr.Close();

                return true;

            }

            else

                return false;

        }

 

        private bool CreateDB( ref SqlConnection sqlConn )

        {

            string strQuery;

            if( ReadSQLFromFile( out strQuery ) )

            {

                strQuery = strQuery.Replace( "/r/n", " " );

                strQuery = strQuery.Replace( " GO ", " ; " );

   

                SqlCommand sqlComm = new SqlCommand( strQuery, sqlConn );

 

                try

                {

                    sqlComm.ExecuteNonQuery();

                    return true;

                }

                catch( SqlException sqlErr )

                {

                    MessageBox.Show( sqlErr.Message );

                }

                catch

                {

                }

               

                sqlComm.Dispose();

            }

            return true;

        }

    }

}

 

       要注意的是在SQL脚本中的“/r/n”，在SQLCommand中是无法识别，因此要替换为空格；其次“GO” 在SQLCommand中也是无法识别，但为了使每条语句都执行，因此我在这里，用“；”来替换。

 

       注：程序的位置和SQL脚本文件的位置为同一目录下，如果觉得不方便的话，可以在我的基础上再延伸。