OracleADO.NETDesign / GraphicsLINQArchitectXMLMySQLC# 2.0BeginnerIntermediateDevSQL ServerSQLWindows.NETASP.NETC#
Data Access Component
An open source Data Access Component
- Download DAC - 429.33 KB
- Download library - 186.39 KB
- Download source - 4.28 MB
- Download application - 634.52 KB
- Get the latest version from project website
Introduction
Just as the name suggests, it's a data access component. It provides a library of classes and a tool. Using it in a .NET project can make data accessing more easy.
It Covers
- Multiple types of database are supported
- Provides data in DataSet, DataTable and data object
- Executes SQL script and stored procedure
- Condition expression
- Basic functions of SQL, like
MAX
,MIN
support in query - Data object code and XML file generator
Using the Code
Basic Functions
- Use the "
EntitiesGenerator
" tool to generate an Entities Project.
Reference to the blog How to use the "Entities Generator" tool to create an entities project. - Add a connection configuration file named "connection.config" into the base path of the application project, the format of context should be as below:.
<?xml version="1.0" encoding="utf-8" ?> <connections> <connection databaseType="SQL"> Data Source=.\SQLEXPRESS;AttachDbFilename="|DataDirectory|Database1.mdf"; Integrated Security=True;User Instance=True </connection> </connections>
- If we have an entity object class named "
Issue
", it can be inserted into the database using the following code.RaisingStudio.Data.Providers.DataContext dc = new RaisingStudio.Data.Providers.DataContext(); dc.Insert<Issue>(issue);
- To update an object.
dc.Update<Issue>(issue);
- To delete an object, we can give the entity object as parameter or give it only the key value.
dc.Delete<Issue>(issue);
or
dc.Delete<Issue>(issueID);
- To query objects, we can get three kinds of results,
IEnumerable<T>
,IList<T>
orDataTable
through 3 different methods.IEnumerable<Issue> query = dc.Query<Issue>(); foreach(Issue issue in query) { } IList<Issue> issueList = dc.QueryForList<Issue>();
and we can use condition expression with these Query methods:
DataTable dataTable = dc.QueryForDataTable<Issue>(Issue._.IssueID > 1);
- Query for one object, we can use the
GetData()
method, pass into an object with key value or only key value, or a condition expression.Issue issue = new Issue(); issue.IssueID = 2; issue = dc.GetData<Issue>(issue); Issue issue = dc.GetData<Issue>(2); Issue issue = dc.GetData<Issue>(Issue._.IssueID == 2);
- Update a
DataTable
int result = dc.UpdateDataTable<Issue>(dataTable);
Advanced Features
- Basic SQL functions, including
GetCount
,GetMin
,GetMax
,GetSum
andGetAvg
int result = dc.GetCount<Issue>(); object minValue = dc.GetMin<Issue>(Issue._.Progress); decimal maxValue = Convert.ToDecimal(dc.GetMax<Issue>(Issue._.Progress, Issue._.Title == "test title"));
- Save and Exists
int result = dc.Save<Issue>(issue); bool saved = dc.Exists<Issue>(issue); bool ex = dc.Exists<Issue>(Issue._.Title == "test title");
- Partial columns
Issue issue = dc.GetData<issue>(2, Issue._.Status); issue.Status = IssueStatus.Fixed; int result = dc.Update<Issue>(issue, Issue._.Status);
- Batch operation
int result = dc.Delete<issue>(Issue._.Status == IssueStatus.Fixed); result = dc.Update<Issue>(issue, Issue._.Status == IssueStatus.Fixed, Issue._.Status);
- Sort or Ordering, the "
OrderBy
" method and the operator^
and^ !
can order the query result.IEnumerable<Issue> query = dc.Query<Issue>(Issue.All.OrderBy(Issue._.IssueID)); query = dc.Query<Issue>(Issue._.Status == IssueStatus.Fixed ^ Issue._.IssueID);
- Pagination
IList<Issue> issueList = dc.QueryForList<Issue>(Issue.All, 0, 100);
- Transaction
try { this.dc.BeginTransaction(); try { int result = this.dc.Insert<Issue>(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; }
- Multiple keys
MutipleKeysTable mt = dc.GetData<MutipleKeysTable>(new object[] { key1, key2 }, MutipleKeysTable.Except(MutipleKeysTable._.Value2));
- Query with 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<Issue>(cmd);
- Execute common command, these methods are
ExecuteForDataTable
,ExecuteForList
,ExecuteQuery
,ExecuteReader
,ExecuteScalar
andExecuteNoQuery
.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<Issue>(cmd);
- SQL log
DataContext dc = new DataContext(); dc.Log = System.Console.Out;
- Multiple database providers, add the following XML element into the "providers.config" file, then it can be used in the "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" />
- Custom data type converter, the following code is an example "
TypeConverter
" code, and config it into the "converters.config" file.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 }
<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"> </converter>
- Definition config file, the ".definition.xml" file of entity can be embedded into assembly as resource, or leave it in the file system. the Entities projected that generated by the "
EntitiesGenerator
" tool is default as embedded, if we want to put it in the file system under some folder, we need a "definitions.config" file.<?xml version="1.0" encoding="utf-8"?> <definitionsConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <aliases> <alias name="UTIssue" type="UnitTest.UTIssue, UnitTest, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" /> </aliases> <definitions> <definition name="UTIssue" resource="definitions/Issue.definition.xml" /> <definition name="UTSystemUser" resource="definitions/SystemUser.definition.xml" /> </definitions> </definitionsConfig>
- Common command manager. config the SQL command into the "commands.config" file, then use it as follows:
<?xml version="1.0" encoding="utf-8" ?> <commands parameterPrefix=":"> <command name="select">SELECT * FROM DAC_ISSUE</command> <command name="select2"> <![CDATA[ SELECT * FROM DAC_USER ]]> </command> <command name="select3" commandType="StoredProcedure">SELECT_DAC_ISSUE</command> <command name="select4"> <![CDATA[ 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);
Team Blogs:
History
- 30th April, 2009: Initial post