开源数据访问组件 DAC

fmms 13年前
     <h4>项目描述: </h4>    <p><br /> <span id="ctl00_ctl00_MasterContent_Content_wikiSourceLabel">数据访问组件，提供了一组类库和一个代码生成工具，使.net项目中数据访问更简化.<br /> <br /> </span></p>    <h4>功能: </h4>    <ul>     <li>多种数据库支持.</li>     <li>提供DataSet, DataTable 和数据实体查询.</li>     <li>执行SQL脚本及存储过程.</li>     <li>条件表达式.</li>     <li>常用SQL方法, 如MAX, MIN等可能被应用在查询中.</li>     <li>数据实体代码及XML文件生成.</li>    </ul>    <br />    <h4>使用:</h4>    <h5>基本功能: </h5>    <br /> 1. 使用 "EntitiesGenerator" 生成工具生成实体项目。    <br />   参见 blog：    <a href="/misc/goto?guid=4959500505534085244">How to use the "Enties Generator" tool to create an entities project</a>.    <br />    <br /> 2. 添加一个文件名为"connection.config"的数据库连接配置文件，到应用程序的运行目录，文件格式及内容如下：    <div style="background-color:white;color:black;">     <pre><span style="color:blue;"><?</span><span style="color:#a31515;">xml</span> <span style="color:red;">version</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">1.0</span><span style="color:black;">"</span> <span style="color:red;">encoding</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">utf-8</span><span style="color:black;">"</span> <span style="color:blue;">?></span> <span style="color:blue;"><</span><span style="color:#a31515;">connections</span><span style="color:blue;">></span>   <span style="color:blue;"><</span><span style="color:#a31515;">connection</span> <span style="color:red;">databaseType</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">SQL</span><span style="color:black;">"</span><span style="color:blue;">></span>Data Source=.\SQLEXPRESS;AttachDbFilename="|DataDirectory|Database1.mdf";   Integrated Security=True;User Instance=True<span style="color:blue;"></</span><span style="color:#a31515;">connection</span><span style="color:blue;">></span> <span style="color:blue;"></</span><span style="color:#a31515;">connections</span><span style="color:blue;">></span> </pre>    </div>    <br /> 3. 假定我们有个实体类，名叫 "Issue", 可以使用以下代码将它插入数据库    <br />    <div style="background-color:white;color:black;">     <pre> RaisingStudio.Data.Providers.DataContext dc = <span style="color:blue;">new</span> RaisingStudio.Data.Providers.DataContext();  dc.Insert<Issue>(issue);</pre>    </div>    <br /> 4. 更新实体.    <br />    <div style="background-color:white;color:black;">     <pre> dc.Update<Issue>(issue);</pre>    </div>    <br /> 5. 删除实体, 可以通过给定实体或实体的主键值进行。    <br />    <div style="background-color:white;color:black;">     <pre> dc.Delete<Issue>(issue);</pre>    </div> 或    <br />    <div style="background-color:white;color:black;">     <pre> dc.Delete<Issue>(issueID);</pre>    </div>    <br /> 6. 查询实体, 通过三个不同的方法，可以分别获得 IEnumerable<T>, IList<T> or DataTable 作为返回结果。    <br />    <div style="background-color:white;color:black;">     <pre> IEnumerable<Issue> query = dc.Query<Issue>();  <span style="color:blue;">foreach</span>(Issue issue <span style="color:blue;">in</span> query)  {  }</pre>    </div>    <div style="background-color:white;color:black;">     <pre> IList<Issue> issueList = dc.QueryForList<Issue>();</pre>    </div> 在查询中，还可以使用“条件表达式”.    <br />    <div style="background-color:white;color:black;">     <pre> DataTable dataTable = dc.QueryForDataTable<Issue>(Issue._.IssueID > 1);</pre>    </div>    <br /> 7. 可以通过GetData()方法，查询单个实体，使用包含主键值的实体，主键值或条件表达式作为参数。    <br />    <div style="background-color:white;color:black;">     <pre> Issue issue = <span style="color:blue;">new</span> Issue();  issue.IssueID = 2;  issue = dc.GetData<Issue>(issue);</pre>    </div>    <div style="background-color:white;color:black;">     <pre> Issue issue = dc.GetData<Issue>(2);</pre>    </div>    <div style="background-color:white;color:black;">     <pre> Issue issue = dc.GetData<Issue>(Issue._.IssueID == 2);</pre>    </div>    <br /> 8. 更新DataTable.    <br />    <div style="background-color:white;color:black;">     <pre> <span style="color:blue;">int</span> result = dc.UpdateDataTable<Issue>(dataTable);</pre>    </div>    <br />    <h5>高级特性: </h5>    <br /> 1. 常用SQL方法, 包括 GetCount, GetMin, GetMax, GetSum and GetAvg.    <br />    <div style="background-color:white;color:black;">     <pre><span style="color:blue;">int</span> result = dc.GetCount<Issue>();<span style="color:blue;">object</span> minValue = dc.GetMin<Issue>(Issue._.Progress);<span style="color:blue;">decimal</span> maxValue = Convert.ToDecimal(dc.GetMax<Issue>(Issue._.Progress,               Issue._.Title == <span style="color:#a31515;">"test title"</span>));</pre>    </div>    <br /> 2. Save 和 Exists.    <br />    <div style="background-color:white;color:black;">     <pre><span style="color:blue;">int</span> result = dc.Save<Issue>(issue);<span style="color:blue;">bool</span> saved = dc.Exists<Issue>(issue);<span style="color:blue;">bool</span> ex = dc.Exists<Issue>(Issue._.Title == <span style="color:#a31515;">"test title"</span>);</pre>    </div>    <br /> 3. 部分列.    <br />    <div style="background-color:white;color:black;">     <pre>Issue issue = dc.GetData<issue>(2, Issue._.Status); issue.Status = IssueStatus.Fixed;<span style="color:blue;">int</span> result  = dc.Update<Issue>(issue, Issue._.Status);</pre>    </div>    <br /> 4. 批量操作.    <br />    <div style="background-color:white;color:black;">     <pre><span style="color:blue;">int</span> result = dc.Delete<issue>(Issue._.Status == IssueStatus.Fixed); result  = dc.Update<Issue>(issue, Issue._.Status == IssueStatus.Fixed, Issue._.Status);</pre>    </div>    <br /> 5. 排序, 使用 "OrderBy" 方法或  ^ 和  ^ ! 运算符应用在查询中，可以对查询进行排序.    <br />    <div style="background-color:white;color:black;">     <pre>IEnumerable<Issue> query = dc.Query<Issue>(Issue.All.OrderBy(Issue._.IssueID)); query = dc.Query<Issue>(Issue._.Status == IssueStatus.Fixed ^ Issue._.IssueID);</pre>    </div>    <br /> 6. 分页.    <br />    <div style="background-color:white;color:black;">     <pre> IList<Issue> issueList = dc.QueryForList<Issue>(Issue.All, 0, 100);</pre>    </div>    <br /> 7. 事务.    <br />    <div style="background-color:white;color:black;">     <pre><span style="color:blue;">try</span> {     <span style="color:blue;">this</span>.dc.BeginTransaction();     <span style="color:blue;">try</span>     {         <span style="color:blue;">int</span> result = <span style="color:blue;">this</span>.dc.Insert<Issue>(issue);                       <span style="color:blue;">this</span>.dc.CommitTransaction();     }     <span style="color:blue;">catch</span> (Exception ex)     {         System.Diagnostics.Debug.WriteLine(ex);         <span style="color:blue;">this</span>.dc.RollbackTransaction();         <span style="color:blue;">throw</span>;     } }<span style="color:blue;">catch</span> (Exception ex) {     System.Diagnostics.Debug.WriteLine(ex);     <span style="color:blue;">throw</span>; }</pre>    </div>    <br /> 8. 多主键.    <br />    <div style="background-color:white;color:black;">     <pre>MutipleKeysTable mt = dc.GetData<MutipleKeysTable>(<span style="color:blue;">new</span> <span style="color:blue;">object</span>[] { key1, key2 },               MutipleKeysTable.Except(MutipleKeysTable._.Value2));</pre>    </div>    <br /> 9. 使用 common command 查询.    <br />    <div style="background-color:white;color:black;">     <pre>CommonCommand cmd = <span style="color:blue;">new</span> CommonCommand(); cmd.CommandText = <span style="color:blue;">string</span>.Format(<span style="color:#a31515;">"SELECT [IssueID], [{0}] FROM .[Issue] WHERE [{0}] = @p1"</span>, Issue._.Title); cmd.Parameters.Add(<span style="color:#a31515;">"@p1"</span>, <span style="color:#a31515;">"test title"</span>); Issue issue = dc.GetData<Issue>(cmd);</pre>    </div>    <br /> 10. 执行 common command, 支持 ExecuteForDataTable, ExecuteForList, ExecuteQuery, ExecuteReader, ExecuteScalar 和 ExecuteNoQuery 等方法.    <br />    <div style="background-color:white;color:black;">     <pre>RaisingStudio.Data.CommonCommand cmd = <span style="color:blue;">new</span> CommonCommand(               <span style="color:blue;">string</span>.Format(<span style="color:#a31515;">"UPDATE .[{0}] SET [{1}] = [{1}] + 1 WHERE [{2}] = @p1"</span>,               Issue._, Issue._.Progress, Issue._.IssueID)); cmd.AddParameter(<span style="color:#a31515;">"@p1"</span>, System.Data.DbType.Int32, maxID);<span style="color:blue;">int</span> result = <span style="color:blue;">this</span>.dc.ExecuteNoQuery<Issue>(cmd);</pre>    </div>    <br /> 11. SQL 脚本日志.    <br />    <div style="background-color:white;color:black;">     <pre>DataContext dc = <span style="color:blue;">new</span> DataContext(); dc.Log = System.Console.Out;</pre>    </div>    <br /> 12. 多种数据库 providers, 添加如下的 xml 项到 "providers.config" 配置文件中, 就可以在 "connections.config" 中使用.    <br />    <div style="background-color:white;color:black;">     <pre>    <span style="color:blue;"><</span><span style="color:#a31515;">provider</span>      <span style="color:red;">name</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MYSQL</span><span style="color:black;">"</span>      <span style="color:red;">description</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySQL, MySQL provider </span><span style="color:black;">"</span>      <span style="color:red;">enabled</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">false</span><span style="color:black;">"</span>      <span style="color:red;">assemblyName</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySql.Data, Version=5.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d</span><span style="color:black;">"</span>     <span style="color:red;">connectionClass</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySql.Data.MySqlClient.MySqlConnection</span><span style="color:black;">"</span>      <span style="color:red;">commandClass</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySql.Data.MySqlClient.MySqlCommand</span><span style="color:black;">"</span>      <span style="color:red;">parameterClass</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySql.Data.MySqlClient.MySqlParameter</span><span style="color:black;">"</span>      <span style="color:red;">parameterDbTypeClass</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySql.Data.MySqlClient.MySqlDbType</span><span style="color:black;">"</span>      <span style="color:red;">parameterDbTypeProperty</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySqlDbType</span><span style="color:black;">"</span>      <span style="color:red;">dataAdapterClass</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySql.Data.MySqlClient.MySqlDataAdapter</span><span style="color:black;">"</span>      <span style="color:red;">commandBuilderClass</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">MySql.Data.MySqlClient.MySqlCommandBuilder</span><span style="color:black;">"</span>      <span style="color:red;">usePositionalParameters</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">false</span><span style="color:black;">"</span>      <span style="color:red;">useParameterPrefixInSql</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">true</span><span style="color:black;">"</span>      <span style="color:red;">useParameterPrefixInParameter</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">true</span><span style="color:black;">"</span>      <span style="color:red;">parameterPrefix</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">?</span><span style="color:black;">"</span>     <span style="color:red;">allowMARS</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">false</span><span style="color:black;">"</span>       <span style="color:blue;">/></span> </pre>    </div>    <br /> 13. 自定义数据类型“转换器”,  以下就是一个 "TypeConverter" 示例代码，及如何配置到 "converters.config" 配置文件中.    <br />    <div style="background-color:white;color:black;">     <pre><span style="color:blue;">public</span> <span style="color:blue;">class</span><span style="color:mediumturquoise;"> PointConverter : IDbTypeConverter</span>{  <span style="color:blue;">#region</span> IDbTypeConvertermember  <span style="color:blue;">public</span> <span style="color:blue;">object</span> ConvertFromDbType(<span style="color:blue;">object</span> value)  {   <span style="color:blue;">string</span> s = value <span style="color:blue;">as</span> <span style="color:blue;">string</span>;   <span style="color:blue;">if</span> (!<span style="color:blue;">string</span>.IsNullOrEmpty(s))   {    <span style="color:blue;">string</span>[] sa = s.Split(<span style="color:#a31515;">','</span>);    <span style="color:blue;">if</span> ((sa != <span style="color:blue;">null</span>) && (sa.Length == 3))    {     <span style="color:blue;">int</span> x = <span style="color:blue;">int</span>.Parse(sa[0]);     <span style="color:blue;">int</span> y = <span style="color:blue;">int</span>.Parse(sa[1]);     <span style="color:blue;">int</span> z = <span style="color:blue;">int</span>.Parse(sa[2]);     <span style="color:blue;">return</span> <span style="color:blue;">new</span> Point(x, y, z);    }   }   <span style="color:blue;">return</span> <span style="color:blue;">null</span>;  }  <span style="color:blue;">public</span> <span style="color:blue;">object</span> ConvertToDbType(<span style="color:blue;">object</span> value)  {   <span style="color:blue;">if</span> (value <span style="color:blue;">is</span> Point)   {    Point point = (Point)value;    <span style="color:blue;">return</span> point.ToString();   }   <span style="color:blue;">return</span> <span style="color:blue;">null</span>;  }  <span style="color:blue;">#endregion</span> }</pre>    </div>    <div style="background-color:white;color:black;">     <pre>    <converter type="RaisingStudio.Data.Entities.Point,  RaisingStudio.Data.Entities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"  dbType="string"  converterType="RaisingStudio.Data.Entities.PointConverter,  RaisingStudio.Data.Entities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"><span style="color:blue;"></</span><span style="color:#a31515;">converter</span><span style="color:blue;">></span> </pre>    </div>    <br /> 14. “实体定义”配置, "*.definition.xml" 文件可以作为资源文件嵌入到程序集在，也可以留在文件系统上，"EntitiesGenerator" 实体生成工具生在项目是采用的嵌入资源的方式， 如果要使用文件的方式，则需要配置一个名叫"definitions.config"的配置文件，样式如下：    <br />    <div style="background-color:white;color:black;">     <pre><span style="color:blue;"><?</span><span style="color:#a31515;">xml</span> <span style="color:red;">version</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">1.0</span><span style="color:black;">"</span> <span style="color:red;">encoding</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">utf-8</span><span style="color:black;">"</span><span style="color:blue;">?></span> <span style="color:blue;"><</span><span style="color:#a31515;">definitionsConfig</span> <span style="color:red;">xmlns:xsi</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">http://www.w3.org/2001/XMLSchema-instance</span><span style="color:black;">"</span><span style="color:blue;">></span>   <span style="color:blue;"><</span><span style="color:#a31515;">aliases</span><span style="color:blue;">></span>     <alias name="UTIssue" type="UnitTest.UTIssue, UnitTest, Version=1.0.0.0,               Culture=neutral, PublicKeyToken=null" />   <span style="color:blue;"></</span><span style="color:#a31515;">aliases</span><span style="color:blue;">></span>   <span style="color:blue;"><</span><span style="color:#a31515;">definitions</span><span style="color:blue;">></span>     <span style="color:blue;"><</span><span style="color:#a31515;">definition</span> <span style="color:red;">name</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">UTIssue</span><span style="color:black;">"</span> <span style="color:red;">resource</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">definitions/Issue.definition.xml</span><span style="color:black;">"</span> <span style="color:blue;">/></span>     <span style="color:blue;"><</span><span style="color:#a31515;">definition</span> <span style="color:red;">name</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">UTSystemUser</span><span style="color:black;">"</span> <span style="color:red;">resource</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">definitions/SystemUser.definition.xml</span><span style="color:black;">"</span> <span style="color:blue;">/></span>   <span style="color:blue;"></</span><span style="color:#a31515;">definitions</span><span style="color:blue;">></span>  <span style="color:blue;"></</span><span style="color:#a31515;">definitionsConfig</span><span style="color:blue;">></span> </pre>    </div>    <br />    <br /> 15. Common command 管理器. 把 SQL脚本配置在 "commands.config" 中后，可以用如下代码读取使用。    <br />    <div style="background-color:white;color:black;">     <pre><span style="color:blue;"><?</span><span style="color:#a31515;">xml</span> <span style="color:red;">version</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">1.0</span><span style="color:black;">"</span> <span style="color:red;">encoding</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">utf-8</span><span style="color:black;">"</span> <span style="color:blue;">?></span> <span style="color:blue;"><</span><span style="color:#a31515;">commands</span> <span style="color:red;">parameterPrefix</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">:</span><span style="color:black;">"</span><span style="color:blue;">></span>   <span style="color:blue;"><</span><span style="color:#a31515;">command</span> <span style="color:red;">name</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">select</span><span style="color:black;">"</span><span style="color:blue;">></span>SELECT * FROM DAC_ISSUE<span style="color:blue;"></</span><span style="color:#a31515;">command</span><span style="color:blue;">></span>   <span style="color:blue;"><</span><span style="color:#a31515;">command</span> <span style="color:red;">name</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">select2</span><span style="color:black;">"</span><span style="color:blue;">></span>     <span style="color:blue;"><![CDATA[</span><span style="color:gray;">     SELECT * FROM DAC_USER     </span><span style="color:blue;">]]></span>   <span style="color:blue;"></</span><span style="color:#a31515;">command</span><span style="color:blue;">></span>   <span style="color:blue;"><</span><span style="color:#a31515;">command</span> <span style="color:red;">name</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">select3</span><span style="color:black;">"</span> <span style="color:red;">commandType</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">StoredProcedure</span><span style="color:black;">"</span><span style="color:blue;">></span>SELECT_DAC_ISSUE<span style="color:blue;"></</span><span style="color:#a31515;">command</span><span style="color:blue;">></span>   <span style="color:blue;"><</span><span style="color:#a31515;">command</span> <span style="color:red;">name</span><span style="color:blue;">=</span><span style="color:black;">"</span><span style="color:blue;">select4</span><span style="color:black;">"</span><span style="color:blue;">></span>     <span style="color:blue;"><![CDATA[</span><span style="color:gray;">     SELECT * FROM DAC_ISSUE DI     WHERE DI.ISSUE_ID = :ISSUE_ID     </span><span style="color:blue;">]]></span>   <span style="color:blue;"></</span><span style="color:#a31515;">command</span><span style="color:blue;">></span> <span style="color:blue;"></</span><span style="color:#a31515;">commands</span><span style="color:blue;">></span> </pre>    </div>    <pre>CommonCommand cmd = CommandManager.Instance.GetCommand(<span style="color:#a31515;">"select"</span>); System.Data.DataTable dt = <span style="color:blue;">this</span>.dc.ExecuteForDataTable(cmd);</pre>    <strong> <p><strong>项目主页：</strong><a href="http://www.open-open.com/lib/view/home/1326091726734" target="_blank">http://www.open-open.com/lib/view/home/1326091726734</a></p> </strong>
开源数据访问组件 DAC

相关经验

目录
