快速操作SQLServer数据库（应用FrameCountry架构）

  

快速操作 SQLServer 数据库

 

       FrameCountry数据访问层架构是以.Net为开发平台，专注于多层开发架构中数据访问层功能的应用程序。本文章介绍如何通过FrameCountry快速访问SQLServer数据库，数据库表为SQLServer2000的pubs库中的jobs表。（更多内容请下载最新版本FrameCountry架构，其中含实例程序和使用手册）

 

一、新建C#工程

1.       建立C#工程，名称QuickSQLServer；

2.       添加引用，选择FrameCountry架构文件FrameCountry.dll；

3.       在界面增加4个按钮：名称分别为“查询”、“添加”、“修改”、“删除”；

      增加4个对话框：name属性为“job_id”、“job_desc”、“min_lvl”、“max_lvl”；

      增加一个DataGrid空间，name定义为dgGrid，如图：

二、配置FrameCountry架构文件

1.         在工程的FrameCountry.dll目录中建立两个配置文件FrameConnect.xml和FrameConfig.xml。

2.         FarmeConnect.xml配置文件内容如下：

<? xml version="1.0" encoding="GB2312" ?>
< ConfigList >
     < Datebase >
         < DBString > User ID=sa;Data Source="127.0.0.1";Password=;Initial Catalog=pubs;Provider="SQLOLEDB.1" </ DBString >
         < DBTimeout > 30 </ DBTimeout >
     </ Datebase >
     < Debug >
         < DebugType > 1 </ DebugType >
     </ Debug >
     < Register >
         < RegisterString > [2999-12-31]W5dBN6YXQ8ks6xXCwPZKloxtB11Y9ExGnG3WaSNCkWKZ6HP52XU47l2dT4GXrxdS4/v9LT7rTasYGoyDs4BwEPLbfMSbCFGITZqLuJKbArLkLTnpmA5IPd27qfOiBhMnemzhMpYGk7+7dU4wbCcrn0rssJf7kRd7HTaNJGHKNE5cHDtW3wUraC4bkyWhnNXcluyImPy555w= </ RegisterString >
         < RegisterExplain > 限时到2999年12月31日 </ RegisterExplain >
     </ Register >
</ ConfigList >

3.         FarmeConfig.xml配置文件内容如下：

<? xml version="1.0" encoding="GB2312" ?>
< ConfigList >
     < jobs  CommandName ="Table"  Depiction ="工作表" >
         < MainInfo >
            jobs
         </ MainInfo >
         < WhereList >
             < job_id  Depiction ="工作ID号"  DataType ="Number"   Operator ="=" > job_id </ job_id >
             < job_desc  Depiction ="工作描述"  DataType ="String"   Operator ="=" > job_desc </ job_desc >
             < min_lvl  Depiction ="最大数"  DataType ="Number"   Operator ="=" > min_lvl </ min_lvl >
             < max_lvl  Depiction ="最小数"  DataType ="Number"   Operator ="=" > max_lvl </ max_lvl >
         </ WhereList >
     </ jobs >
</ ConfigList >

三、双击“查询”按钮添加如下代码：

             // 查询操作
             string  sError = "" ;
            DataSet dsData = new  DataSet();
            FrameCountry.FrameTown fTown = new  FrameCountry.FrameTown();             // 创建主执行类FrameTown对象
            FrameCountry.FrameBridge fBridge = new  FrameCountry.FrameBridge();     // 创建提交字符串类FrameBridge
            fBridge.ChapterName = " jobs " ;             // 设定操作节点名称
            fBridge.CommandName = " select " ;         // 设置操作类型 
             if (job_desc.Text != "" )                 // 当描述框不为空时执行近似“like”查询
                fBridge.TransWhere[ " job_desc " , " like " ] = " % " + job_desc.Text + " % " ;
             if ( ! fTown.ExeQuery(fBridge, ref  dsData, ref  sError))         // 执行查询操作，当错误返回错误信息到变量sError
                MessageBox.Show(sError);
             else
                dgGrid.DataSource = dsData.Tables[ 0 ];

 

四、双击“插入”按钮添加如下按钮：

             // 插入操作
             string  sError = "" ;
            DataSet dsData = new  DataSet();
            FrameCountry.FrameTown fTown = new  FrameCountry.FrameTown();             // 创建主执行类FrameTown对象
            FrameCountry.FrameBridge fBridge = new  FrameCountry.FrameBridge();     // 创建提交字符串类FrameBridge
            fBridge.ChapterName = " jobs " ;             // 设定操作节点名称
            fBridge.CommandName = " insert " ;         // 设置操作类型
             // fBridge.TransValue["job_id"]=job_id.Text;         // 该列自动增加，不用设置
            fBridge.TransValue[ " job_desc " ] = job_desc.Text;
            fBridge.TransValue[ " min_lvl " ] = min_lvl.Text;
            fBridge.TransValue[ " max_lvl " ] = max_lvl.Text;
             if ( ! fTown.ExeSingleNonQuery(fBridge, ref  sError))         // 执行非查询操作，当错误返回错误信息到变量sError
                MessageBox.Show(sError);
             else
                MessageBox.Show( " 操作成功！ " );

 

五、双击“修改”按钮添加如下按钮：

             // 修改操作
             string  sError = "" ;
            DataSet dsData = new  DataSet();
            FrameCountry.FrameTown fTown = new  FrameCountry.FrameTown();             // 创建主执行类FrameTown对象
            FrameCountry.FrameBridge fBridge = new  FrameCountry.FrameBridge();     // 创建提交字符串类FrameBridge
            fBridge.ChapterName = " jobs " ;             // 设定操作节点名称
            fBridge.CommandName = " update " ;         // 设置操作类型
            fBridge.TransWhere[ " job_id " ] = job_id.Text;         // 该列作为更新条件
            fBridge.TransValue[ " job_desc " ] = job_desc.Text;
            fBridge.TransValue[ " min_lvl " ] = min_lvl.Text;
            fBridge.TransValue[ " max_lvl " ] = max_lvl.Text;
             if ( ! fTown.ExeSingleNonQuery(fBridge, ref  sError))         // 执行非查询操作，当错误返回错误信息到变量sError
                MessageBox.Show(sError);
             else
                MessageBox.Show( " 操作成功！ " );

 

六、双击“删除”按钮添加如下按钮：

             // 删除操作
             string  sError = "" ;
            DataSet dsData = new  DataSet();
            FrameCountry.FrameTown fTown = new  FrameCountry.FrameTown();             // 创建主执行类FrameTown对象
            FrameCountry.FrameBridge fBridge = new  FrameCountry.FrameBridge();     // 创建提交字符串类FrameBridge
            fBridge.ChapterName = " jobs " ;             // 设定操作节点名称
            fBridge.CommandName = " delete " ;         // 设置操作类型
            fBridge.TransWhere[ " job_id " ] = job_id.Text;         // 该列作为更新条件
             if ( ! fTown.ExeSingleNonQuery(fBridge, ref  sError))         // 执行非查询操作，当错误返回错误信息到变量sError
                MessageBox.Show(sError);
             else
                MessageBox.Show( " 操作成功！ " );    

 

七、总结

       以上步骤实现了对SQLServer数据库的增加、修改、删除、查询操作，代码过程简单，可读性强，FrameCountry数据访问层架构通过对数据库的操作完全封装，简化开发过程，开发人员只需要关注业务逻辑的实现即可。

下载最新的FrameCountry数据访问层架构： http://blog.csdn.net/lizheng82/archive/2007/06/18/1656140.aspx

 

 八、全部程序

using  System;
using  System.Drawing;
using  System.Collections;
using  System.ComponentModel;
using  System.Windows.Forms;
using  System.Data;

namespace  QuickSQLServer
... {
    /**//// <summary>
    /// Form1 的摘要说明。
    /// </summary>
    public class Form1 : System.Windows.Forms.Form
    ...{
        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.Button button2;
        private System.Windows.Forms.Button button3;
        private System.Windows.Forms.Button button4;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.Label label3;
        private System.Windows.Forms.Label label4;
        private System.Windows.Forms.TextBox job_id;
        private System.Windows.Forms.TextBox job_desc;
        private System.Windows.Forms.TextBox min_lvl;
        private System.Windows.Forms.TextBox max_lvl;
        private System.Windows.Forms.DataGrid dgGrid;
        /**//// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.Container components = null;

        public Form1()
        ...{
            //
            // Windows 窗体设计器支持所必需的
            //
            InitializeComponent();

            //
            // TODO: 在 InitializeComponent 调用后添加任何构造函数代码
            //
        }

        /**//// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        protected override void Dispose( bool disposing )
        ...{
            if( disposing )
            ...{
                if (components != null) 
                ...{
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

        Windows 窗体设计器生成的代码#region Windows 窗体设计器生成的代码
        /**//// <summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// </summary>
        private void InitializeComponent()
        ...{
            this.button1 = new System.Windows.Forms.Button();
            this.dgGrid = new System.Windows.Forms.DataGrid();
            this.button2 = new System.Windows.Forms.Button();
            this.button3 = new System.Windows.Forms.Button();
            this.button4 = new System.Windows.Forms.Button();
            this.job_id = new System.Windows.Forms.TextBox();
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.job_desc = new System.Windows.Forms.TextBox();
            this.label3 = new System.Windows.Forms.Label();
            this.min_lvl = new System.Windows.Forms.TextBox();
            this.label4 = new System.Windows.Forms.Label();
            this.max_lvl = new System.Windows.Forms.TextBox();
            ((System.ComponentModel.ISupportInitialize)(this.dgGrid)).BeginInit();
            this.SuspendLayout();
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(8, 8);
            this.button1.Name = "button1";
            this.button1.TabIndex = 0;
            this.button1.Text = "查询";
            this.button1.Click += new System.EventHandler(this.button1_Click);
            // 
            // dgGrid
            // 
            this.dgGrid.DataMember = "";
            this.dgGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.dgGrid.Location = new System.Drawing.Point(168, 40);
            this.dgGrid.Name = "dgGrid";
            this.dgGrid.Size = new System.Drawing.Size(304, 280);
            this.dgGrid.TabIndex = 4;
            // 
            // button2
            // 
            this.button2.Location = new System.Drawing.Point(88, 8);
            this.button2.Name = "button2";
            this.button2.TabIndex = 0;
            this.button2.Text = "插入";
            this.button2.Click += new System.EventHandler(this.button2_Click);
            // 
            // button3
            // 
            this.button3.Location = new System.Drawing.Point(168, 8);
            this.button3.Name = "button3";
            this.button3.TabIndex = 0;
            this.button3.Text = "修改";
            this.button3.Click += new System.EventHandler(this.button3_Click);
            // 
            // button4
            // 
            this.button4.Location = new System.Drawing.Point(248, 8);
            this.button4.Name = "button4";
            this.button4.TabIndex = 0;
            this.button4.Text = "删除";
            this.button4.Click += new System.EventHandler(this.button4_Click);
            // 
            // job_id
            // 
            this.job_id.Location = new System.Drawing.Point(64, 40);
            this.job_id.Name = "job_id";
            this.job_id.TabIndex = 2;
            this.job_id.Text = "";
            // 
            // label1
            // 
            this.label1.Location = new System.Drawing.Point(8, 40);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(48, 23);
            this.label1.TabIndex = 3;
            this.label1.Text = "job_id";
            // 
            // label2
            // 
            this.label2.Location = new System.Drawing.Point(8, 72);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(56, 23);
            this.label2.TabIndex = 3;
            this.label2.Text = "job_desc";
            // 
            // job_desc
            // 
            this.job_desc.Location = new System.Drawing.Point(64, 72);
            this.job_desc.Name = "job_desc";
            this.job_desc.TabIndex = 2;
            this.job_desc.Text = "";
            // 
            // label3
            // 
            this.label3.Location = new System.Drawing.Point(8, 104);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(48, 23);
            this.label3.TabIndex = 3;
            this.label3.Text = "min_lvl";
            // 
            // min_lvl
            // 
            this.min_lvl.Location = new System.Drawing.Point(64, 104);
            this.min_lvl.Name = "min_lvl";
            this.min_lvl.TabIndex = 2;
            this.min_lvl.Text = "";
            // 
            // label4
            // 
            this.label4.Location = new System.Drawing.Point(8, 136);
            this.label4.Name = "label4";
            this.label4.Size = new System.Drawing.Size(48, 23);
            this.label4.TabIndex = 3;
            this.label4.Text = "max_lvl";
            // 
            // max_lvl
            // 
            this.max_lvl.Location = new System.Drawing.Point(64, 136);
            this.max_lvl.Name = "max_lvl";
            this.max_lvl.TabIndex = 2;
            this.max_lvl.Text = "";
            // 
            // Form1
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
            this.ClientSize = new System.Drawing.Size(480, 326);
            this.Controls.Add(this.job_id);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.dgGrid);
            this.Controls.Add(this.button1);
            this.Controls.Add(this.button2);
            this.Controls.Add(this.button3);
            this.Controls.Add(this.button4);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.job_desc);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.min_lvl);
            this.Controls.Add(this.label4);
            this.Controls.Add(this.max_lvl);
            this.Name = "Form1";
            this.Text = "Form1";
            ((System.ComponentModel.ISupportInitialize)(this.dgGrid)).EndInit();
            this.ResumeLayout(false);

        }
        #endregion

        /**//// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main() 
        ...{
            Application.Run(new Form1());
        }


        private void button1_Click(object sender, System.EventArgs e)
        ...{
            //查询操作
            string sError="";
            DataSet dsData=new DataSet();
            FrameCountry.FrameTown fTown=new FrameCountry.FrameTown();            //创建主执行类FrameTown对象
            FrameCountry.FrameBridge fBridge=new FrameCountry.FrameBridge();    //创建提交字符串类FrameBridge
            fBridge.ChapterName="jobs";            //设定操作节点名称
            fBridge.CommandName="select";        //设置操作类型 
            if(job_desc.Text!="")                //当描述框不为空时执行近似“like”查询
                fBridge.TransWhere["job_desc","like"]="%"+job_desc.Text+"%";
            if(!fTown.ExeQuery(fBridge,ref dsData,ref sError))        //执行查询操作，当错误返回错误信息到变量sError
                MessageBox.Show(sError);
            else
                dgGrid.DataSource=dsData.Tables[0];

        }

        private void button2_Click(object sender, System.EventArgs e)
        ...{
            //插入操作
            string sError="";
            DataSet dsData=new DataSet();
            FrameCountry.FrameTown fTown=new FrameCountry.FrameTown();            //创建主执行类FrameTown对象
            FrameCountry.FrameBridge fBridge=new FrameCountry.FrameBridge();    //创建提交字符串类FrameBridge
            fBridge.ChapterName="jobs";            //设定操作节点名称
            fBridge.CommandName="insert";        //设置操作类型
            //fBridge.TransValue["job_id"]=job_id.Text;        //该列自动增加，不用设置
            fBridge.TransValue["job_desc"]=job_desc.Text;
            fBridge.TransValue["min_lvl"]=min_lvl.Text;
            fBridge.TransValue["max_lvl"]=max_lvl.Text;
            if(!fTown.ExeSingleNonQuery(fBridge,ref sError))        //执行非查询操作，当错误返回错误信息到变量sError
                MessageBox.Show(sError);
            else
                MessageBox.Show("操作成功！");
        }

        private void button3_Click(object sender, System.EventArgs e)
        ...{
            //修改操作
            string sError="";
            DataSet dsData=new DataSet();
            FrameCountry.FrameTown fTown=new FrameCountry.FrameTown();            //创建主执行类FrameTown对象
            FrameCountry.FrameBridge fBridge=new FrameCountry.FrameBridge();    //创建提交字符串类FrameBridge
            fBridge.ChapterName="jobs";            //设定操作节点名称
            fBridge.CommandName="update";        //设置操作类型
            fBridge.TransWhere["job_id"]=job_id.Text;        //该列作为更新条件
            fBridge.TransValue["job_desc"]=job_desc.Text;
            fBridge.TransValue["min_lvl"]=min_lvl.Text;
            fBridge.TransValue["max_lvl"]=max_lvl.Text;
            if(!fTown.ExeSingleNonQuery(fBridge,ref sError))        //执行非查询操作，当错误返回错误信息到变量sError
                MessageBox.Show(sError);
            else
                MessageBox.Show("操作成功！");
        }

        private void button4_Click(object sender, System.EventArgs e)
        ...{
            //删除操作
            string sError="";
            DataSet dsData=new DataSet();
            FrameCountry.FrameTown fTown=new FrameCountry.FrameTown();            //创建主执行类FrameTown对象
            FrameCountry.FrameBridge fBridge=new FrameCountry.FrameBridge();    //创建提交字符串类FrameBridge
            fBridge.ChapterName="jobs";            //设定操作节点名称
            fBridge.CommandName="delete";        //设置操作类型
            fBridge.TransWhere["job_id"]=job_id.Text;        //该列作为更新条件
            if(!fTown.ExeSingleNonQuery(fBridge,ref sError))        //执行非查询操作，当错误返回错误信息到变量sError
                MessageBox.Show(sError);
            else
                MessageBox.Show("操作成功！");        
        }

    }
}