用C#來部署資料庫

現在好多程式，都是與資料庫相關的，是以在做安裝的時候，部署資料庫看似是一件很複雜的事情。其實就我個人而言，部署資料庫是很簡單，大緻的思路如下：

1． 用本身的dbms來産生資料庫建立的sql腳本；

2． 接下來就是寫程式來執行sql腳本，進而達到建立資料庫的目的。

以下用一個舉例來說明，資料庫伺服器用的是sql server。

首先要在資料庫生成好的sql腳本最前頭，加入如下語句：

       use master

go

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

            drop database mytest

create database mytest

use mytest

注：其中“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

        }

        /// clean up any resources being used.

        protected override void dispose( bool disposing )

            if( disposing )

            {

                if (components != null)

                {

                    components.dispose();

                }

            }

            base.dispose( disposing );

        #region windows form designer generated code

        /// required method for designer support - do not modify

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

        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

        /// the main entry point for the application.

        [stathread]

        static void main()

            application.run(new frmcreatedb());

        private void btncreatedb_click(object sender, system.eventargs e)

            sqlconnection sqlconn = new sqlconnection();

  try

                    sqlcomm.executenonquery();

                    return true;

                catch( sqlexception sqlerr )

                    messagebox.show( sqlerr.message );

                catch

                sqlcomm.dispose();

            return true;

    }

}

       要注意的是在sql腳本中的“/r/n”，在sqlcommand中是無法識别，是以要替換為空格；其次“go” 在sqlcommand中也是無法識别，但為了使每條語句都執行，是以我在這裡，用“；”來替換。

       注：程式的位置和sql腳本檔案的位置為同一目錄下，如果覺得不友善的話，可以在我的基礎上再延伸。