开源数据访问组件 DAC
fmms
12年前
<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>