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