数据访问与sql语句的管理(一)

source:http://www.cnblogs.com/ttcre2/archive/2008/08/01/1257966.html

在开发过程中数据访问是必不可少的。每个框架都会有自己数据访问机制。大家在一般的情况下会为自己的框架配备2套数据访问机制，ORM和DataHelper。当然，根据项目的需要有时候也可能只一种。

其实这2个东西差不多被写烂了，我在这里再谈数据访问可鼓足了很大的勇气，如果写的不好请大家见谅。

 

首先说说需求。

1.       快速的编写sql配置文件，很完善的配置文件验证和智能提示功能。

2.       把sql语句按模块划分，都放在配置文件中。不再会出现程序中到处可见sql语句，和找不到sql语句的情况了。

3.       修改sql语句再也不需要编译程序集了。

4.       数据访问对象抽象化。统一的数据访问对象，更换数据库只修改web.config就可以了。程序完全不需要修改。

5.       数据库访问更简便。数据访问永远只需三步。获取配置文件中的信息生成dbcommand（只需要指定一个key），给dbcommand中的参数赋值（参数对象已经根据配置文件生成好了，你只需要赋值就可以了，其他的不用管。如果没有参数这一步都省了），执行这个dbcommand。

6.       连接字符串统一管理，不管连接字符串是明文还是密文，都不需要修改任何程序。

 

写的比较抽象啊，往下看慢慢会明白。

 

首先说说sql配置文件。

写这个配置文件的时候和写webconfig一样，智能提示，非常方便。而且支持多个系统集成，配置文件一共分三层，系统（一个文件）、模块、sql语句。系统集成的时候把多个sql配置文件放一起，完全不冲突。而且管理sql语句更清晰，部署后调试修改sql更方便。

 

Code
<?xml version="1.0" encoding="utf-8" ?>
<!--配置说明。

<SqlConfig>
  <Module moduleName="模块名">
    <Sql sqlName="sql语句名称" text="sql语句或存储过程名" type="可以不指定，默认为Text">
      <Parameters>
        <add parameterName="参数名" defaultVaule="默认值，如果没有可以不指定。" direction="可以不指定，默认为Input" dbType="可以不指定。默认为String"/>
      </Parameters>
      <DynamicSql>
        <add parameterName="参数名" dbType="可以不指定。默认为String" direction="可以不指定，默认为Input" filterText="必须指定，如果该参数赋值了，要动态添加到sql语句中的查询条件。"/>
      </DynamicSql>
    </Sql>
  </Module>
</SqlConfig>-->

<!--示例
<SqlConfig>
  <Module moduleName="module1">
    <Sql sqlName="AllGroupName" text="select * from groups" />
    <Sql sqlName="GetAllUserName" text="select username from users" />
    <Sql sqlName="GetUserNameByGroupID" text="select username from users where groupid=@GroupID">
      <Parameters>
        <add parameterName="@GroupID" dbType="Int32"/>
      </Parameters>
    </Sql>
    <Sql sqlName="GetUserCount" text="GetUserCount" type="StoredProcedure">
      <Parameters>
        <add parameterName="@GroupID" defaultVaule="1" dbType="Int32" direction="Input"/>
        <add parameterName="@returnValue" dbType="Int32" direction="ReturnValue"/>
      </Parameters>
    </Sql>
    <Sql sqlName="test" text="select * from table where parm=@parm">
      <Parameters>
        <add parameterName="@parm"/>
      </Parameters>
      <DynamicSql>
        <add parameterName="@parm1" filterText="and parm1=@parm1"/>
        <add parameterName="@parm2" filterText="or parm2=@parm2"/>
        <add parameterName="@parm3" filterText="and parm3=@parm3"/>
      </DynamicSql>
    </Sql>
  </Module>
  <Module moduleName="Product"></Module>
</SqlConfig>-->

<SqlConfig xmlns="http://www.w3school.com.cn" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.w3school.com.cn SqlStringConfigSchema.xsd">
  <Module moduleName="CE.SqlString.SqlStringTest.Author">
    <Sql sqlName="GetCity" text="select distinct city from authors" />
    <Sql sqlName="GetState" text="select distinct state from authors" />
    <Sql sqlName="SearchAuthors" text="select au_id,au_lname,au_fname,phone,address,city,state,zip,[contract] from authors where 1=1">
      <DynamicSql>
        <add parameterName="@au_id" filterText="and au_id=@au_id"/>
        <add parameterName="@au_lname" filterText="and au_lname like '%' + @au_lname + '%'"/>
        <add parameterName="@au_fname" filterText="and au_fname like '%' + @au_fname + '%'"/>
        <add parameterName="@address" filterText="and address like '%' + @address + '%'"/>
        <add parameterName="@city" filterText="and city = @city"/>
        <add parameterName="@state" filterText="and state = @state"/>
        <add parameterName="@contract" dbType="Boolean" filterText="and [contract] = @contract"/>
      </DynamicSql>
    </Sql>
    <Sql sqlName="GetAuthorInfo" text="select au_id,au_lname,au_fname,phone,address,city,state,zip,[contract] from authors where au_id=@au_id">
      <Parameters>
        <add parameterName="@au_id"/>
      </Parameters>
    </Sql>
    <Sql sqlName="UpdateAuthor" text="update authors set au_lname=@au_lname,au_fname=@au_fname,phone=@phone,address=@address,city=@city,state=@state,zip=@zip,[contract]=@contract where au_id=@au_id">
      <Parameters>
        <add parameterName="@au_lname"/>
        <add parameterName="@au_fname"/>
        <add parameterName="@phone"/>
        <add parameterName="@address"/>
        <add parameterName="@city"/>
        <add parameterName="@state"/>
        <add parameterName="@zip"/>
        <add parameterName="@contract" dbType="Boolean"/>
        <add parameterName="@au_id"/>
      </Parameters>
    </Sql>
  </Module>
</SqlConfig>

 

 

再说说数据访问。

永远的只需要三步，可以控制开发人员编码规范。都用这个，不会一人写一个，或者要用的时候自己open一个连接了。

Code
//获取command。
command = DataHelper.SqlModule["GetAuthorInfo"].Command;

//给参数赋值。
command.Parameters["@au_id"].Value = au_id;

//执行它，DataHelper.Database是封装的Enterprise Library中的Database类型。支持直接返回dataset，事务等等。不过我特别喜欢用datareader。
using (reader = DataHelper.Database.ExecuteReader(command))
{

    if (reader.Read())
    {
        author = new Author();
        author.Au_id = reader[0].ToString();
        author.Au_lname = reader[1].ToString();
        author.Au_fname = reader[2].ToString();
        author.Phone = reader[3].ToString();
        author.Address = reader[4].ToString();
        author.City = reader[5].ToString();
        author.State = reader[6].ToString();
        author.Zip = reader[7].ToString();
        author.Contract = Convert.ToBoolean(reader[8].ToString());
    }
}

 

 

访问对象抽象化。

可以到到下面这个配置。

Code
<connectionStrings>
    <add name="PubsConnectionString" connectionString="Data Source=.;Initial Catalog=pubs;Persist Security Info=True;User ID=sa;Password=sa"
      providerName="System.Data.SqlClient" />
</connectionStrings>

如果你是System.Data.SqlClient，那么dbcommand就是sqlcommand，dbcommand里面参数也是SqlParameter。

你是哪种providerName，我就可以自动跟你生成那种command。暂时支持四种，System.Data.Odbc、System.Data.OleDb、System.Data.OracleClient和System.Data.SqlClient。

用兴趣你自己加，俺不拦你，加好后给我cc一份就好了。

 

 

再看看上面connectionstring，你加密不加密都不需要改代码，我们一样读的出来。当然，你自己写个算法出来肯定是读不出来的。你只能用RsaProtectedConfigurationProvider和DataProtectionConfigurationProvider加密。

第二篇开始上代码。谢谢。。。。

Tag标签: SqlString, 数据访问, sql

posted on 2008-08-01 10:45 猫咬狗 阅读(322) 评论(15)   编辑 收藏 所属分类: Framework

Feedback

#1楼  2008-08-01 10:56 在路上的牛       

阅读代码的时候都要对照这个CONFIG，有点不方便啊。最好是采用ORM，能消灭95%以上的SQL，代码也好理解好维护。
   回复   引用   查看     

#2楼  2008-08-01 10:58 sjzshen1       

感谢分享
辛苦啦，呵呵
   回复   引用   查看     

#3楼  [ 楼主] 2008-08-01 11:02 猫咬狗       

@在路上的牛
orm不能取代sql。2个互补才是最好的解决方案。
我不是不认同orm，在项目中也经常用。
但绝不是有了他就不用自己写sql了。那你开发出来的东西将会很可怕。
   回复   引用   查看     

#4楼  2008-08-01 11:20 kennyzhang [未注册用户]

orm不能取代sql。2个互补才是最好的解决方案。我现在参与的项目（千万）采用ORM和sql并存，楼主的想法是可以的，我们没有那么做，我们直接作为查询模板存入数据库（自己写的定制工具），这样就特别简单了 呵呵
   回复   引用     

#5楼  2008-08-01 11:29 金色海洋（jyk）       

我的思路和你的有点类似。你是把sql语句统一放在xml文件里了。
而我是把sql语句放在数据库里存放。

其实准确地说，我存放的不是单独的sql，而是模块名、表名、字段名，模块需要的字段，就是关联关系。

比如我有一个新闻管理模块，这个模块需要显示新闻名称，发布时间等字段，那么我就可以记录，模块ID，模块需要的字段ID，然后需要的时候组合成需要的sql语句。

这么做的好处就是，数据库里有一个字段，“配置信息”里面只有一条记录与之对应，其他的地方都是应用字段ID。就是说当数据库里的字段名有变化的时候，我只需要改一条记录就可以了。


你的这种方法，当字段名发生变化的时候，是不是要改好多处呢？


   回复   引用   查看     

#6楼  2008-08-01 11:31 金色海洋（jyk）       

http://www.cnblogs.com/jyk/archive/2008/07/28/1255101.html

http://www.cnblogs.com/jyk/archive/2008/07/26/1252212.html

这里有思路。
   回复   引用   查看     

#7楼  [ 楼主] 2008-08-01 11:35 猫咬狗       

@金色海洋（jyk）
呵呵，这个我也想过，当时设计的时候想到放在资源文件里面。当如果把网站编译成单独程序集，你改sql还是需要重新编译。
放到数据库到还真没想到。
放到配置文件中是后缀名字config的，不是xml。这两个可大不一样，xml是有办法下载下来的，但config是绝对下不下来的。跟webconfig一样，要是webconfig都可以下载下来，那系统还存在什么安全性呢？
   回复   引用   查看     

#8楼  2008-08-01 11:35 在路上的牛       

我自己现在的项目中，写SQL的情况基本只限于部分报表和某些对性能要求特别高的地方，所以SQL很少，业务逻辑层基本不见SQL。

   回复   引用   查看     

#9楼  2008-08-01 11:39 在路上的牛       

用楼主的方法，对付报表的修改有用，改SQL不用重新编译了。
   回复   引用   查看     

#10楼  [ 楼主] 2008-08-01 11:43 猫咬狗       

@在路上的牛
--引用--------------------------------------------------
在路上的牛: 用楼主的方法，对付报表的修改有用，改SQL不用重新编译了。
--------------------------------------------------------

呵呵，确实是这样，但当时做这个最重要的是方便部署后调试。我们研发部门和运行部门在两个不同的事业部，交流起来很困难。
还有一点就是数据量的问题，我们公司几万人的企业。非常海量的数据，ORM基本上应用不多。。
   回复   引用   查看     

#11楼  2008-08-01 11:58 私家侦探       

有没有用过ibatis.net 这个框架呢,它也是把项目所有的sql语句集中在很多xml文件欧
   回复   引用   查看     

#12楼  2008-08-01 12:07 私家侦探       

有个疑问,从xml读出的sql有没有缓存啊,每次都要再去读文件不是要s
   回复   引用   查看     

#13楼  [ 楼主] 2008-08-01 12:18 猫咬狗       

@私家侦探
肯定有啊。下次贴代码的时候会贴出来的。
我们项目orm用的是AR。datahelper就是用的这个，这些问题一定会考虑到的。
   回复   引用   查看     

#14楼  [ 楼主] 2008-08-01 12:22 猫咬狗       

@私家侦探
没用过啊。
spring、dnn、castle、nhibernate都用过。就你说的这个了解不多，有时间去看看。
   回复   引用   查看     

#15楼  2008-08-01 13:04 金色海洋（jyk）       

你的这个文件格式是xml的，不管扩展名是什么。
Access文件的扩展名也可以改成.config的，来防止下载（当然可以正常使用），但是他还是Access数据库，呵呵。

<Sql sqlName="GetCity" text="select distinct city from authors" />
<Sql sqlName="GetState" text="select distinct state from authors" />
<Sql sqlName="SearchAuthors" text="select au_id,au_lname,au_fname,phone,address,city,state,zip,[contract] from authors where 1=1">

你的这个例子里面 authors 出现了两次，city,state 都出现了两次，而且还是同一个表里面的。其他的地方可能还会出现。如果字段名有变化的话，改起来就不方便了，谁然可以用查找替换，但是还是很容易遗漏，和勿替换。

放在数据库里面，单独存放字段名，然后做关联就好修改了。
   回复   引用   查看