编程开发论坛 - Powered by Discuz! Board

标题: 快速操作SQLServer数据库（应用FrameCountry架构） [打印本页]

作者: lizheng82 时间: 2007-7-25 10:26 标题: 快速操作SQLServer数据库（应用FrameCountry架构）

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"

assword=;Initial Catalog=pubs

rovider="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数据访问层架构通过对数据库的操作完全封装，简化开发过程，开发人员只需要关注业务逻辑的实现即可。
更多内容请看我的博客：
http://blog.csdn.net/lizheng82
下载最新的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("操作成功！");
      }
}
}

欢迎光临编程开发论坛 (http://bbs.lihuasoft.net/)