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

1. 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.
2. 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>

3. 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);

4. To update an object.

   dc.Update<Issue>(issue);

5. 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);

6. 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);

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

8. Update a DataTable

   int result = dc.UpdateDataTable<Issue>(dataTable);

Advanced Features

1. 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"));

2. Save and Exists

   int result = dc.Save<Issue>(issue);
   bool saved = dc.Exists<Issue>(issue);
   bool ex = dc.Exists<Issue>(Issue._.Title == "test title");

3. Partial columns

   Issue issue = dc.GetData<issue>(2, Issue._.Status);
   issue.Status = IssueStatus.Fixed;
   int result  = dc.Update<Issue>(issue, Issue._.Status);

4. Batch operation

   int result = dc.Delete<issue>(Issue._.Status == IssueStatus.Fixed);
   result  = dc.Update<Issue>(issue, Issue._.Status == 
   			IssueStatus.Fixed, Issue._.Status);

5. 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);

6. Pagination

    IList<Issue> issueList = dc.QueryForList<Issue>(Issue.All, 0, 100);

7. 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;
   } 

8. Multiple keys

   MutipleKeysTable mt = dc.GetData<MutipleKeysTable>(new object[] { key1, key2 }, 
                MutipleKeysTable.Except(MutipleKeysTable._.Value2));

9. 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);

10. 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);

11. SQL log

    DataContext dc = new DataContext();
    dc.Log = System.Console.Out;

12. 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"    
        />

13. 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>

14. 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>

15. 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:

• zhong_zhaofeng's blog

History

• 30^th April, 2009: Initial post