Data Access Component

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> or DataTable 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 and GetAvg

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 and 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<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);