Click here to Skip to main content
11,412,578 members (72,546 online)
Click here to Skip to main content

Data Access Component

, 30 Apr 2009 LGPL3
Rate this:
Please Sign up or sign in to vote.
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 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:

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)

Share

About the Author

zhongzf

China China
No Biography provided

Comments and Discussions

 
GeneralLooks very similar to iBatis.Net Pin
Hardy Wang at 15-May-09 3:12
memberHardy Wang15-May-09 3:12 
JokeNothing special maybe is a kind of special! Pin
zhongzf at 1-May-09 3:42
memberzhongzf1-May-09 3:42 
QuestionWhy reinvent the wheel? Pin
zlezj at 30-Apr-09 21:52
memberzlezj30-Apr-09 21:52 
AnswerRe: Why reinvent the wheel? Pin
Mycroft Holmes at 1-May-09 0:20
memberMycroft Holmes1-May-09 0:20 
GeneralRe: Why reinvent the wheel? Pin
zlezj at 1-May-09 1:45
memberzlezj1-May-09 1:45 
GeneralRe: Why reinvent the wheel? Pin
Mycroft Holmes at 1-May-09 3:07
memberMycroft Holmes1-May-09 3:07 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150427.1 | Last Updated 1 May 2009
Article Copyright 2009 by zhongzf
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid