Click here to Skip to main content
15,867,939 members
Articles / Web Development / ASP.NET

Data Access Component

Rate me:
Please Sign up or sign in to vote.
3.86/5 (3 votes)
30 Apr 2009LGPL32 min read 147.9K   2.2K   26   6
An open source 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

  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
    <?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.
    C#
    RaisingStudio.Data.Providers.DataContext 
    dc = new RaisingStudio.Data.Providers.DataContext();
    dc.Insert<Issue>(issue);
  4. To update an object.
    C#
    dc.Update<Issue>(issue);
  5. To delete an object, we can give the entity object as parameter or give it only the key value.
    C#
    dc.Delete<Issue>(issue);

    or

    C#
    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.
    C#
    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:

    C#
    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.
    C#
    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
    C#
    int result = dc.UpdateDataTable<Issue>(dataTable);

Advanced Features

  1. Basic SQL functions, including GetCount, GetMin, GetMax, GetSum and GetAvg
    C#
    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
    C#
    int result = dc.Save<Issue>(issue);
    bool saved = dc.Exists<Issue>(issue);
    bool ex = dc.Exists<Issue>(Issue._.Title == "test title");
  3. Partial columns
    C#
    Issue issue = dc.GetData<issue>(2, Issue._.Status);
    issue.Status = IssueStatus.Fixed;
    int result  = dc.Update<Issue>(issue, Issue._.Status);
  4. Batch operation
    C#
    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.
    C#
    IEnumerable<Issue> query = dc.Query<Issue>(Issue.All.OrderBy(Issue._.IssueID));
    query = dc.Query<Issue>(Issue._.Status == IssueStatus.Fixed ^ Issue._.IssueID);
  6. Pagination
    C#
    IList<Issue> issueList = dc.QueryForList<Issue>(Issue.All, 0, 100);
    
  7. Transaction 
    C#
    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
    C#
    MutipleKeysTable mt = dc.GetData<MutipleKeysTable>(new object[] { key1, key2 }, 
                 MutipleKeysTable.Except(MutipleKeysTable._.Value2));
  9. Query with common command
    C#
    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.
    C#
    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
    C#
    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".
    XML
     <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.
    C#
    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
    }
    XML
    <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
    <?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
    <?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

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)


Written By
China China
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralLooks very similar to iBatis.Net Pin
Hardy Wang15-May-09 2:12
Hardy Wang15-May-09 2:12 
JokeNothing special maybe is a kind of special! Pin
zhongzf1-May-09 2:42
zhongzf1-May-09 2:42 
QuestionWhy reinvent the wheel? Pin
zlezj30-Apr-09 20:52
zlezj30-Apr-09 20:52 
AnswerRe: Why reinvent the wheel? Pin
Mycroft Holmes30-Apr-09 23:20
professionalMycroft Holmes30-Apr-09 23:20 
GeneralRe: Why reinvent the wheel? Pin
zlezj1-May-09 0:45
zlezj1-May-09 0:45 
GeneralRe: Why reinvent the wheel? Pin
Mycroft Holmes1-May-09 2:07
professionalMycroft Holmes1-May-09 2:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.