中文介绍
在Windows Mobile上如何用DAC操作SQLite数据库
如何使用dac获取sharepoint list的数据
隆重推荐:DAC 4.0 全新发布
https://github.com/zhongzf/dac

Project Description
Just as the name, it's an data access component. It provides a library of classes and a tool, using it in .net project can make data accessing more easily.

It covers:

  • Multiple types of database are supported.
  • Provides data in DataSet, DataTable and data object.
  • Execute SQL script and store procedure.
  • Condition expression.
  • Basic function of SQL, like MAX, MIN support in query.
  • Data object code and xml file generator.

Changes in version 4.2(http://dac.codeplex.com/releases/view/91736):

  • Add Query to support jqGrid.
  • Add Commands config file.
  • Add IS NULL and IS NOT NULL for condition expression.

Changes in version 4.0(http://dac.codeplex.com/releases/view/80094):

  • Based on .net framework 4.0.
  • Change the method "GetData" to "GetEntity".
  • Add method "GetIdentity" to retrive the auto increase id.
  • Remove functions about DataTable and DataSet.
  • Add "ToDataTable" to extension "IEnumerable".
  • Remove "QueryForList" method.
  • Add "ToList" to extension "IEnumerable".
  • Add "Query" function with no entity, IEnumerable Query(string commandText), work with "dynamic" keyword.
  • Use app.config and web.config, no more custom config files.
  • Use RazorEngine(http://razorengine.codeplex.com/) in EntitiesGenerator tools, the template is easy to customize.
  • Works with mono(http://www.mono-project.com/) under linux.

How to use it?

Basic functions:
1. Use the "EntitiesGenerator" tool generate an Entities Project.
reference to the blog How to use the "Enties Generator" tool to create an entities project.

2. Add a connection configuration file named "connection.config" into the base path of application project, the format of context should 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 database use 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 3 kinds of result, 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 function, include 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. Mutiple 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. Mutiple 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 Entites projected that generated by the "EntitiesGenerator" tool is default as embedded, if we want 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 following.
<?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

Last edited Jan 31, 2014 at 10:58 AM by zhong_zhaofeng, version 39