快速入门 - DataService


定义存储服务接口

有如下短信存储服务接口
using System;
using Fost.MobileMessages.Server.Models;

namespace Fost.MobileMessages.Server.Repositories
{
	internal interface IMobileMessageRepository
	{
		void InsertMobileMessage(MobileMessageInternal message);
		void InsertMobileMessage(MobileMessageInternal[] messages);
		MessageCategory GetMessageCategory(int categoryId);
		string GetMessageCategoryTemplate(int categoryId);

		MobileMessageInternal[] GetMobileMessages(long lastId, DateTime createdTime);
		void UpdateMobileMessage(MobileMessageInternal message);
		SPChannelSetting[] GetSPChannelSettings();
	}
}

实现存储服务

using System;
using HTB.DevFx.Data;
using Fost.MobileMessages.Server.Models;

namespace Fost.MobileMessages.Server.Repositories
{
	internal class MobileMessageRepository : IMobileMessageRepository
	{
		public void InsertMobileMessage(MobileMessageInternal message) {
			DataService.Execute("InsertMobileMessage", message);//直接执行某预定义的SQL语句
		}

		public void InsertMobileMessage(MobileMessageInternal[] messages) {//使用事务
			using(var session = DataService.BeginSession()) {
				foreach(var message in messages) {
					session.Execute("InsertMobileMessage", message);
				}
			}
		}

		public MessageCategory GetMessageCategory(int categoryId) {//获取单条
			return DataService.Execute<MessageCategory>("GetMessageCategory", new { categoryId });
		}

		public string GetMessageCategoryTemplate(int categoryId) {//获取单字段
			return DataService.Execute<string>("GetMessageCategoryTemplate", new { categoryId });
		}

		public MobileMessageInternal[] GetMobileMessages(long lastId, DateTime createTime) {//获取多条
			return DataService.Execute<MobileMessageInternal[]>("GetMobileMessages", new { lastId, createTime });
		}

		public void UpdateMobileMessage(MobileMessageInternal message) {//更新单条
			DataService.Execute("UpdateMobileMessage", message);
		}

		public SPChannelSetting[] GetSPChannelSettings() {//获取多条
			return DataService.Execute<SPChannelSetting[]>("GetSPChannelSettings", null);
		}
	}
}

定义配置文件

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<htb.devfx>
		<cache><!--数据缓存配置-->
			<add name="CategoryTemplateCache" />
			<add name="CategoryCache" />
		</cache>

		<data configSet="{tag:'statements', nullable:'true'}">
			<results moduleEnabled="true" /><!--moduleEnabled表示是否进行返回值得过滤(比如缓存)-->
			<dataStorages defaultStorage="Default"><!--物理数据库定义-->
				<add name="Default" connectionString="MobileMessageServiceConnection" />
			</dataStorages>

			<statements><!--SQL定义-->
				<templates><!--模板定义-->
					<parameters><!--参数模板定义-->
						<add name="Category" parameterName="@Category" type="int" size="4" nullable="false" />						
						<add name="Mobiles" parameterName="@Mobiles" type="string" size="256" nullable="false" />
					</parameters>
				</templates>

				<add name="InsertMobileMessage" resultHandler="@Data.NoneResultHandler">
					<parameters>
						<add name="Category" ref="Category" /><!--ref为参数模板引用-->
						<add name="Mobiles" ref="Mobiles" /><!--ref为参数模板引用-->
						<add name="Content" parameterName="@Content" type="string" size="1024" nullable="false" />
						<add name="NullableSendTime" parameterName="@SendTime" type="DateTime" size="8" />
						<add name="UserName" parameterName="@UserName" type="string" size="50" />
						<add name="AdminName" parameterName="@AdminName" type="string" size="50" />
						<add name="RelativeInfo" parameterName="@RelativeInfo" type="string" size="256" />
						<add name="Remark" parameterName="@Remark" type="string" size="256" />
						<add name="Status" parameterName="@Status" type="int" size="4" nullable="false" />
						<add name="ContentEncrypted" parameterName="@ContentEncrypted" type="bool" nullable="false" />
					</parameters>
					<commandText>
						DECLARE @ID BIGINT
						INSERT INTO [MessageID] (Status) VALUES (1)
						SET @ID = SCOPE_IDENTITY()
						INSERT INTO [MobileMessages]
							([ID], [Category], [Mobiles], [Content], [SendTime], [UserName], [AdminName], [RelativeInfo], [Remark], [Status], [ContentEncrypted])
						VALUES (@ID, @Category, @Mobiles, @Content, @SendTime, @UserName, @AdminName, @RelativeInfo, @Remark, @Status, @ContentEncrypted)
					</commandText>
				</add>

				<add name="GetMessageCategory" resultHandler="@Data.SingleResultHandler">
					<cache cacheable="true" cacheName="CategoryCache" />
					<parameters>
						<add name="categoryId" parameterName="@CategoryID" type="int" size="4" nullable="false" />
					</parameters>
					<commandText>
						SELECT * FROM [MessageCategories] WHERE [ID] = @CategoryID
					</commandText>
				</add>

				<add name="GetMessageCategoryTemplate" resultHandler="@Data.ScalarResultHandler">
					<cache cacheable="true" cacheName="CategoryTemplateCache" />
					<parameters>
						<add name="categoryId" parameterName="@CategoryID" type="int" size="4" nullable="false" />
					</parameters>
					<commandText>
						SELECT ContentTemplate FROM [MessageCategories] WHERE [ID] = @CategoryID
					</commandText>
				</add>

				<!--分页-->
				<add name="GetMobileMessages" resultHandler="@Data.ArrayResultHandler">
					<parameters>
						<add name="FetchCount" parameterName="@FetchCount" type="int" nullable="false" size="4" defaultValue="100" />
						<add name="lastId" parameterName="@LastID" type="long" nullable="false" size="8" />
						<add name="createTime" parameterName="@CreateTime" type="DateTime" nullable="false" size="8" />
					</parameters>
					<commandText>
						SELECT TOP (@FetchCount) [ID], [Mobiles], [Content], [Status], [ContentEncrypted], [SentCount], [SentTime], [SentRemark], [SentChannel], [TimeOfSPResponse]
						FROM MobileMessages WITH(NOLOCK)
						WHERE [ID]> @LastID AND Status=0 AND CreateTime>=@CreateTime
						ORDER BY [ID] DESC
					</commandText>
				</add>

				<add name="UpdateMobileMessage" resultHandler="@Data.NoneResultHandler">
					<parameters>
						<add name="ID" parameterName="@ID" type="long" size="8" nullable="false" />
						<add name="Status" parameterName="@Status" type="int" nullable="false" />
						<add name="SentCount" parameterName="@SentCount" type="int" size="4" nullable="false" />
						<add name="SentTime" parameterName="@SentTime" type="DateTime" size="8" nullable="true" />
						<add name="SentRemark" parameterName="@SentRemark" type="string" size="256" nullable="true" />
						<add name="SentChannel" parameterName="@SentChannel" type="int" size="4" nullable="true" />
						<add name="TimeOfSPResponse" parameterName="@TimeOfSPResponse" type="double" size="8" nullable="true" />
					</parameters>
					<commandText parser="@Data.DynamicCommandTextParser" configSet="{tag:'if',key:'parameter'}"><!--内置的动态SQL解析器-->
						UPDATE MobileMessages SET
						<if parameter="SentCount" present="true">SentCount = @SentCount,</if>
						<if parameter="SentTime" present="true">SentTime = @SentTime,</if>
						<if parameter="SentRemark" present="true">SentRemark = @SentRemark,</if>
						<if parameter="SentChannel" present="true">SentChannel = @SentChannel,</if>
						<if parameter="TimeOfSPResponse" present="true">TimeOfSPResponse = @TimeOfSPResponse,</if>
						Status = @Status
						WHERE [ID] = @ID
					</commandText>
				</add>

				<add name="GetSPChannelSettings" resultHandler="@Data.ArrayResultHandler">
					<parameters>
						<add name="ChannelID" parameterName="@ChannelID" type="int" dbType="Int32" size="4" nullable="true" />
					</parameters>
					<commandText parser="@Data.DynamicCommandTextParser">
						SELECT * FROM [SPChannels] WITH(NOLOCK)
						<if parameter="ChannelID" present="true">WHERE ChannelID = @ChannelID</if>
					</commandText>
				</add>
			</statements>
		</data>
	</htb.devfx>
</configuration>

配置数据库链接字符串

数据库链接字符串直接使用配置在web.config/app.config中的connectionStrings节的,需要注意的是,链接字符串节必须配置providerName属性,这个属性指示物理数据库是什么类型的(SQL Server、Oracle、MySQL?),不配置默认是SQL Server数据库。
<?xml version="1.0" encoding="utf-8" ?>
<configuration>

	......

	<connectionStrings>
		<remove name="MobileMessageServiceConnection" />
		<add name="MobileMessageServiceConnection" connectionString="Server=localhost;Database=Test;User ID=test;Password=test" providerName="System.Data.SqlClient" />
	</connectionStrings>

	......

</configuration>

高级用法


DataService定义


  • IDataService
namespace HTB.DevFx.Data
{
	/// <summary>
	/// 数据存储服务接口
	/// </summary>
	public interface IDataService : IDataOperation
	{
		/// <summary>
		/// 开始一个会话
		/// </summary>
		/// <returns>数据会话接口</returns>
		IDataSession BeginSession();

		/// <summary>
		/// 开始一个会话(指定存储数据库)
		/// </summary>
		/// <param name="storageName">存储数据库名</param>
		/// <returns>数据会话接口</returns>
		IDataSession BeginSession(string storageName);

		/// <summary>
		/// 开始一个会话(指定存储数据库,并指示是否需要数据库事务支持)
		/// </summary>
		/// <param name="storageName">存储数据库名</param>
		/// <param name="beginTransation">是否需要数据库事务支持</param>
		/// <returns>数据会话接口</returns>
		IDataSession BeginSession(string storageName, bool beginTransation);
	}
}
  • IDataOperation
namespace HTB.DevFx.Data
{
	/// <summary>
	/// 数据存储操作接口
	/// </summary>
	public interface IDataOperation
	{
		/// <summary>
		/// 执行指定数据语句
		/// </summary>
		/// <param name="statementName">数据语句名</param>
		/// <param name="parameters">参数</param>
		/// <returns>执行结果(根据配置而定)</returns>
		object Execute(string statementName, object parameters);

		/// <summary>
		/// 执行指定数据语句,并返回强类型(实体)
		/// </summary>
		/// <typeparam name="T">实体类型</typeparam>
		/// <param name="statementName">数据语句名</param>
		/// <param name="parameters">参数</param>
		/// <returns>强类型(实体)</returns>
		T Execute<T>(string statementName, object parameters);

		/// <summary>
		/// 执行指定数据语句,返回结果填入<paramref name="result"/>中
		/// </summary>
		/// <param name="statementName">数据语句名</param>
		/// <param name="parameters">参数</param>
		/// <param name="result">返回结果实体</param>
		/// <returns>返回结果实体,一般是<paramref name="result"/></returns>
		object Execute(string statementName, object parameters, object result);

		/// <summary>
		/// 执行指定数据语句,返回结果填入强类型<paramref name="result"/>中
		/// </summary>
		/// <typeparam name="T">结果类型</typeparam>
		/// <param name="statementName">数据语句名</param>
		/// <param name="parameters">参数</param>
		/// <param name="result">返回结果实体</param>
		/// <returns>返回结果实体,一般是<paramref name="result"/></returns>
		T Execute<T>(string statementName, object parameters, T result);
	}
}
  • IDataSession
using System;

namespace HTB.DevFx.Data
{
	/// <summary>
	/// 数据存储会话接口
	/// </summary>
	public interface IDataSession : IDataOperation, IDisposable
	{
		/// <summary>
		/// 提交事务
		/// </summary>
		void CommitTransaction();

		/// <summary>
		/// 回滚事务
		/// </summary>
		void RollbackTransaction();
	}
}

配置文件详解


<data debug="true|false">
	<!--是否为调试模式,true则会将SQL写入日志,默认为false-->
	<results handlerFactory="缺省的结果集处理器工厂,处理器工厂会根据返回值选择适当的处理器" moduleEnabled="false|true, 是否开启结果集过滤(比如数据缓存)">
		<!--结果集处理配置-->
		<handlers defaultHandler="缺省的处理器" configSet="{key:'type'}">
			<!--预定义的处理器和相关的结果-->
			<add type="System.Array" handler="@Data.ArrayResultHandler" />
			<add type="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" handler="@Data.DataReaderResultHandler" />
			<add type="HTB.DevFx.Data.Entities.IPaginateResult, HTB.DevFx.Data" handler="@Data.PaginateResultHandler" />
			<add type="System.ValueType" handler="@Data.ScalarResultHandler" />
			<add type="System.String" handler="@Data.ScalarResultHandler" />
			<add type="System.Data.DataSet, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" handler="@Data.DataSetResultHandler" />
			<add type="System.Data.DataTable, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" handler="@Data.DataTableResultHandler" />
		</handlers>
	</results>

	<dataStorages />
	<!--存储配置-->
	<statements />
	<!--SQL配置-->
</data>

数据缓存配置示例


<htb.devfx>
	<cache>
		<add name="UserCache"/>
		<!--指定数据缓存器-->
	</cache>

	<objects configSet="{tag:'object', nullable:'false'}">
		<object name="UserCacheByIDDependency" type="ExpirationCacheDependency">
			<!--定义数据过期策略对象-->
			<constructor configSet="{tag:'parameter', nullable:'false'}">
				<parameter name="expiration" type="TimeSpan" value="00:00:03" />
				<!--相对过期时间,设定为3秒钟后过期-->
			</constructor>
		</object>
	</objects>

	<data configSet="{tag:'statements', nullable:'true'}" debug="true">
		<results moduleEnabled="true" />
		<!--开启结果集过滤(同时也开启了内置的数据缓存过滤器)-->
		<statements>
			<add name="GetUserByID">
				<!--此节指示数据缓存的配置-->
				<cache cacheable="true|false,是否开启缓存" cacheName="UserCache,缓存器名" cacheKey="UserCacheByID,缓存键"
					parameters="缓存的参数列表,以英文逗号分隔,不填默认为所有参数" dependency="@UserCacheByIDDependency,缓存依赖对象"
					cacheAction="Cache|Remove,缓存的动作,Cache表示缓存,Remove表示从缓存中清除(一般用于UPDATE语句)" />
				<parameters>
					<add name="ID" parameterName="@ID" type="int" size="4" />
				</parameters>
				<commandText>
					SELECT * FROM UserInfo
					WHERE [ID]=@ID
				</commandText>
			</add>
			<add name="UpdateUserByID" resultHandler="@Data.NoneResultHandler">
				<cache cacheName="UserCache" cacheKey="UserCacheByID" cacheAction="Remove" parameters="ID" />
				<parameters>
					<add name="ID" parameterName="@ID" type="int" size="4" />
					<add name="UserName" parameterName="@UserName" type="string" size="50" />
				</parameters>
				<commandText>
					UPDATE UserInfo SET UserName = @UserName
					WHERE [ID]=@ID
				</commandText>
			</add>
		</statements>
	</data>
</htb.devfx>

数据分页示例


[TestMethod]
public void TestQueryForPaginateObjects() {
	const string userName = "相当的麻烦";
	var instance = DataService.Execute<IPaginateResult<User>>("GetPaginateUsersByName", new { UserName = userName, StartIndex = 0, Length = 10 });
	Assert.IsNotNull(instance);
	Assert.IsNotNull(instance.Items);
	Assert.AreEqual(instance.Items.Length, 10);
	Assert.AreEqual(instance.Items[0].ID, 1001);

	instance = DataService.Execute<IPaginateResult<User>>("GetPaginateUsersByName", new { UserName = userName, StartIndex = 11, Length = 10 });
	Assert.IsNotNull(instance);
	Assert.IsNotNull(instance.Items);
	Assert.AreEqual(instance.Items.Length, 10);
	Assert.AreEqual(instance.Items[0].ID, 1011);
}

<add name="GetPaginateUsersByName">
	<parameters>
		<add name="UserName" parameterName="@UserName" type="string" size="50" />
		<add name="StartIndex" parameterName="@StartIndex" type="int" size="4" />
		<add name="Length" parameterName="@Length" type="int" size="4" />
	</parameters>
	<commandText>
		SELECT COUNT(*) FROM UserInfo
		WHERE UserName=@UserName;

		WITH T AS (
		SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS RowIndex FROM UserInfo
		WHERE UserName=@UserName
		) SELECT TOP (@Length) * FROM T WHERE RowIndex >= @StartIndex
	</commandText>
</add>

让StringTemplate解析动态SQL


非内置功能,需引用StringTemplate的相关类库

借助于StringTemplate的强大功能,动态SQL用它来解析是最好不过了。实现ICommandTextParser即可实现目的,代码如下:
using System.Collections;
using Antlr.StringTemplate;
using Fost.Data.Config;
using HTB.DevFx.Utils;
using HTB.DevFx.Config;
using HTB.DevFx.Data;
using HTB.DevFx.Data.Config;

namespace Fost.Data
{
	internal class StringTemplateCommandTextParser : CommandTextParser
	{
		protected override string GetCommandText(IStatementSetting statement, IDictionary parameters) {
			var setting = statement.StatementText.ConfigSetting.ToCachedSetting<StringTemplateContextSetting>();
			if(setting == null) {
				return base.GetCommandText(statement, parameters);
			}
			var group = statement.GetObjectContext(typeof(StringTemplateCommandTextParser), () => {
				var g = new StringTemplateGroup(statement.Name);
				foreach (var t in setting.Templates) {
					g.DefineTemplate(t.Name, t.Template);
				}
				return g;
			});
			var template = new StringTemplate(group, setting.Text, new Hashtable(parameters));
			return template.ToString();
		}
	}
}

配置示例:
<data configSet="{tag:'statements', nullable:'true'}">
	<statements>
		<add name="GetPagedSecurityValidations">
			<parameters>
				<add name="ActionID" parameterName="@ActionID" type="string" size="50" nullable="true"/>
				<add name="UserName" parameterName="@UserName" type="string" size="1024" nullable="true" />
				<add name="ClientIp" parameterName="@ClientIp" type="string" size="50" nullable="true" />
				<add name="ClientLocation" parameterName="@ClientLocation" type="string" size="50" nullable="true" />
				<add name="ClientMac" parameterName="@ClientMac" type="string" nullable="true" />
				<add name="BeginDate" parameterName="@BeginDate" type="DateTime" size="8" nullable="true"/>
				<add name="EndDate" parameterName="@EndDate" type="DateTime" size="8" nullable="true"/>
				<add name="StartIndex" parameterName="@StartIndex" type="int" size="4" />
				<add name="Length" parameterName="@Length" type="int" size="4" />
			</parameters>
			<!--由于StringTemplateCommandTextParser已经定义在FostBase项目中了,所以这里parser填写相应的objectName-->
			<commandText parser="@Data.StringTemplateCommandTextParser" configSet="{tag:'template'}">
				<template name="where"><!--ST的模板定义,可添加多个-->
					WHERE 1=1
					$if(ActionID)$ AND ActionID = @ActionID $endif$
					$if(UserName)$ AND UserName = @UserName $endif$
					$if(ClientIp)$ AND ClientIp = @ClientIp $endif$
					$if(ClientLocation)$ AND ClientLocation = @ClientLocation $endif$
					$if(ClientMac)$ AND ClientMac LIKE '%' + @ClientMac + '%' $endif$
					$if(BeginDate)$ AND CreatedTime &gt;= @BeginDate $endif$
					$if(EndDate)$ AND CreatedTime&lt;= @EndDate $endif$
				</template>
				SELECT COUNT(*) FROM SecurityValidation
					$where()$;
						
				WITH T AS(
					SELECT *, ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowIndex FROM SecurityValidation
						$where()$
				) SELECT TOP (@Length) * FROM T WHERE RowIndex >=@StartIndex
			</commandText>
		</add>
	</statements>
</data>

全局模板的配置示例:
<data configSet="{tag:'statements', nullable:'true'}">
	<statements>
		<templates><!--模板定义-->
			<stringTemplates><!--全局的动态SQL模板定义-->
				<template name="globalWhere"><!--ST的模板定义,可添加多个-->
					WHERE 1=1
					$if(ActionID)$ AND ActionID = @ActionID $endif$
					$if(UserName)$ AND UserName = @UserName $endif$
					$if(ClientIp)$ AND ClientIp = @ClientIp $endif$
					$if(ClientLocation)$ AND ClientLocation = @ClientLocation $endif$
					$if(ClientMac)$ AND ClientMac LIKE '%' + @ClientMac + '%' $endif$
					$if(BeginDate)$ AND CreatedTime &gt;= @BeginDate $endif$
					$if(EndDate)$ AND CreatedTime&lt;= @EndDate $endif$
				</template>
			</stringTemplates>
		</templates>
	
		<add name="GetPagedSecurityValidations">
			<parameters>
				<add name="ActionID" parameterName="@ActionID" type="string" size="50" nullable="true"/>
				<add name="UserName" parameterName="@UserName" type="string" size="1024" nullable="true" />
				<add name="ClientIp" parameterName="@ClientIp" type="string" size="50" nullable="true" />
				<add name="ClientLocation" parameterName="@ClientLocation" type="string" size="50" nullable="true" />
				<add name="ClientMac" parameterName="@ClientMac" type="string" nullable="true" />
				<add name="BeginDate" parameterName="@BeginDate" type="DateTime" size="8" nullable="true"/>
				<add name="EndDate" parameterName="@EndDate" type="DateTime" size="8" nullable="true"/>
				<add name="StartIndex" parameterName="@StartIndex" type="int" size="4" />
				<add name="Length" parameterName="@Length" type="int" size="4" />
			</parameters>
			<!--由于StringTemplateCommandTextParser已经定义在FostBase项目中了,所以这里parser填写相应的objectName-->
			<commandText parser="@Data.StringTemplateCommandTextParser" configSet="{tag:'template'}">
				<template name="where"><!--ST的模板定义,可添加多个-->
					$globalWhere()$  --这里引用全局模板
				</template>
				SELECT COUNT(*) FROM SecurityValidation
					$where()$;
						
				WITH T AS(
					SELECT *, ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowIndex FROM SecurityValidation
						$where()$
				) SELECT TOP (@Length) * FROM T WHERE RowIndex >=@StartIndex
			</commandText>
		</add>
	</statements>
</data>

使用Razor来解析动态SQL


非内置功能,需.NET 4运行库以及需引用RazorEngine的相关类库

下面是使用Razor分页获取数据的示例,SQL语法是MySQL语法。
注意参数前缀@与Razor的关键字冲突,必须写成@@
判断某些参数是否有提供值,需要使用Model.Contains("参数名")来判断
<add name="GetPagedWhiteBlackCategories">
	<parameters>
		<add name="Index" parameterName="@StartIndex" type="int" size="4" nullable="false" />
		<add name="Length" parameterName="@Length" type="int" size="4" nullable="false" />
		<add name="Title" parameterName="@Title" type="string" size="50" />
		<add name="Operator" parameterName="@Operator" type="string" size="50" />
		<add name="BeginDate" parameterName="@BeginDate" type="DateTime" size="8" />
		<add name="EndDate" parameterName="@EndDate" type="DateTime" size="8" />
		<add name="Enabled" parameterName="@Enabled" type="int" size="4" />
		<add name="ParentID" parameterName="@ParentID" type="int" size="4" />
	</parameters>
	<commandText parser="@Data.RazorCommandTextParser">
		@helper where() {
			@:WHERE 1=1
			@if(Model.Contains("Title")) {
				@:AND c.Title LIKE CONCAT('%', @@Title, '%')
			}
			@if(Model.Contains("Enabled")) {
				@:AND c.Enabled = @@Enabled
			}
			@if(Model.Contains("Operator")) {
				@:AND c.Operator = @@Operator
			}
			@if(Model.Contains("BeginDate")) {
				@:AND c.CreatedTime &gt;= @@BeginDate
			}
			@if(Model.Contains("EndDate")) {
				@:AND  c.CreatedTime &lt;= @@EndDate
			}
			@if(Model.Contains("ParentID")) {
				@:AND c.ParentID = @@ParentID
			}
		}
		SELECT COUNT(*) FROM OctopusWhiteBlackCategories AS c
		@where();
		SELECT c.*, p.Title AS ParentTitle FROM OctopusWhiteBlackCategories AS c LEFT JOIN OctopusWhiteBlackCategories AS p ON (c.ParentID = p.ID)
		@where() LIMIT @@StartIndex, @@Length
	</commandText>
</add>

参数定义引用


为了方便的定义Command的参数,322版本增加了参数引用功能,示例如下:
<statements><!--SQL定义-->
	<templates><!--模板定义-->
		<parameters><!--参数模板定义-->
			<add name="Category" parameterName="@Category" type="int" size="4" nullable="false" />
			<add name="Mobiles" parameterName="@Mobiles" type="string" size="256" nullable="false" />
		</parameters>
	</templates>

	<add name="InsertMobileMessage" resultHandler="@Data.NoneResultHandler">
		<parameters>
			<add name="Category" ref="Category" /><!--ref为参数模板引用-->
			<add name="Mobiles" ref="Mobiles" size="1024" /><!--ref为参数模板引用,如果模板参数不满足,可重写(如size参数)-->
			<add name="Content" parameterName="@Content" type="string" size="1024" nullable="false" />
		</parameters>
		<commandText>
			...
		</commandText>
	</add>
</statements>

内嵌(Inline)参数


<add name="GetMacBills" resultHandler="@Data.ArrayResultHandler">
	<parameters>
		<add name="userName" parameterName="@UserName" type="string" size="50" nullable="false" />
		<add name="macType" parameterName="@MacType" type="string" size="50" nullable="false" />
		<add name="macValues" parameterName="@MacValues" type="string" inline="true" nullable="false" /><!--inline="true"表示内嵌参数,是指解析器直接把参数值替换了参数,而不是使用ADO.NET Command参数的形式来调用-->
	</parameters>
	<commandText>
		SELECT [UserName],[MacType],[ClientMac],[Recharges],[Amount],[UpdateTime] FROM MacBills
		WHERE UserName = @UserName AND MacType = @MacType
			AND ClientMac IN (@MacValues)
	</commandText>
	<!--比如参数macValues="'a', 'b', 'c', 'd'",那么最终的SQL类似:... AND ClientMac IN ('a', 'b', 'c', 'd')-->
</add>
注意:内嵌参数会有SQL注入问题,请慎重使用!!

多数据库支持


<data configSet="{tag:'statements', nullable:'true'}">
	<dataStorages defaultStorage="Default">
		<add name="Default" connectionString="MobileMessageServiceConnection" />
		<add name="NoticeDB" connectionString="MobileMessageServiceConnection" />
	</dataStorages>
	<statements><!--name="" dataStorage="NoticeDB"--><!--可以在statements加dataStorage-->
		<add name="DeleteSelledAmountNotice" resultHandler="@Data.AffectedRowsResultHandler" dataStorage="NoticeDB"><!--也可以在statement里加dataStorage-->
			<parameters>
				<add name="UserName" parameterName="UserName" type="string" size="50" nullable="false" />
			</parameters>
			<commandText parser="@Data.StringTemplateCommandTextParser">
				DELETE FROM [SelledAmountNotice]
				WHERE $ST_EqField(f=UserName)$
			</commandText>
		</add>
	</statements>
</data>

Last edited Sep 23, 2011 at 6:57 AM by R2, version 1

Comments

No comments yet.