/// <summary>
/// 通过参数化SQL、匿名对象的方式,创建CPQuery对象实例
/// </summary>
/// <example>
/// <para>下面的代码演示了通过参数化SQL,匿名对象的方式,创建CPQuery对象实例的用法</para>
/// <code>
/// //声明匿名类型
/// var product = new {
/// ProductName = "产品名称",
/// Quantity = 10
/// };
///
/// //SQL中的参数名就是@加匿名类型的属性名
/// CPQuery.From("INSERT INTO Products(ProductName, Quantity) VALUES(@ProductName, @Quantity)", product).ExecuteNonQuery();
/// </code>
/// </example>
/// <param name="parameterizedSQL">参数化的SQL字符串</param>
/// <param name="argsObject">匿名对象</param>
/// <returns>CPQuery对象实例</returns>
public static CPQuery From(string parameterizedSQL, object argsObject)
{
if (string.IsNullOrEmpty(parameterizedSQL))
{
throw new ArgumentNullException("parameterizedSQL");
}
CPQuery query = new CPQuery(parameterizedSQL);
if (argsObject != null)
{
PropertyInfo[] properties = argsObject.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo pInfo in properties)
{
object value = pInfo.FastGetValue(argsObject);
string name = "@" + pInfo.Name;
if (value == null || value == DBNull.Value)
{
query._command.Parameters.AddWithValue(name, DBNull.Value);
//SqlParameter paramter = new SqlParameter(name, DBNull.Value);
//paramter.SqlDbType = SqlDbType.Variant;
//query._command.Parameters.Add(paramter);
//throw new ArgumentException("输入参数的属性值不能为空。");
}
else
{
SqlParameter parameter = value as SqlParameter;
if (parameter != null)
{
query._command.Parameters.Add(parameter);
}
else
{
query._command.Parameters.AddWithValue(name, value);
}
}
}
}
return(query);
}
/// <summary>
/// 更新数据实体对应的记录。
/// </summary>
/// <example>
/// <para>下面的代码演示了Update()方法的用法</para>
/// <code>
/// //Contract类需要继承自BaseEntity,并且需要放在以*.Entity.dll结尾的程序集中
/// Contract contract = new Contract();
///
/// //为类的主键赋值
/// contract.ContractGUID = Reuqest.QueryString["ContractGUID"];
///
/// //为类的其他字段赋值
/// //contract.ContractName = Request.Form["ContractName"];
/// //...
///
/// int count = contract.Update();
/// //更新成功后,count等于1
/// </code>
/// </example>
/// <exception cref="InvalidProgramException">如果数据实体类型的定义不符合规范，就会抛出此异常</exception>
/// <exception cref="InvalidOperationException">类没有定义主键,即没有任何一个属性被标记为PrimaryKey=true,则抛出此异常</exception>
/// <returns>返回ADO.NET的原始结果</returns>
public virtual int Update()
{
CPQuery query = GetCPQuery(7, new object[] { this, bakObject });
if (query == null)
{
return(0);
}
if (_funcBefore != null)
{
if (_funcBefore(query) == false)
{
return(-1);
}
}
return(query.ExecuteNonQuery());
}
/// <summary>
/// 将数据实体插到对应的数据库表中。
/// </summary>
/// <example>
/// <para>下面的代码演示了Insert()方法的用法</para>
/// <code>
/// //Contract类需要继承自BaseEntity,并且需要放在以*.Entity.dll结尾的程序集中
/// Contract contract = new Contract();
///
/// contract.ContractGUID = Guid.NewGuid();
/// contract.ContractName = "...";
/// //...其他字段
///
/// int count = contract.Insert();
/// //插入成功后,count等于1
/// </code>
/// </example>
/// <exception cref="InvalidOperationException">1.如果没有对实体类的任何一个字段赋值,就进行Insert()操作,则会抛出此异常2.类没有定义主键,即没有任何一个属性被标记为PrimaryKey=true,则抛出此异常</exception>
/// <exception cref="InvalidProgramException">如果数据实体类型的定义不符合规范，就会抛出此异常</exception>
/// <returns>返回ADO.NET的原始结果</returns>
public virtual int Insert()
{
CPQuery query = GetCPQuery(3, new object[] { this });
if (query == null)
{
throw new InvalidOperationException("传入对象不能生成有效的SQL语句。");
}
if (_funcBefore != null)
{
if (_funcBefore(query) == false)
{
return(-1);
}
}
return(query.ExecuteNonQuery());
}
/// <summary>
/// 用并发检测的方式，更新数据实体对应的记录。
/// </summary>
/// <example>
/// <para>下面的代码演示了并发检测模式下,Update()方法的用法</para>
/// <code>
/// <![CDATA[
/// //Contract类需要继承自BaseEntity,并且需要放在以*.Entity.dll结尾的程序集中
///
/// public void Load(){
///
/// //在页面加载时,查询数据库信息,时间戳字段需要通过CAST转换为长整型,绑定到界面中
/// Contract contract = CPQuery.From("SELECT ContractGUID, CAST(ContractVersion AS BigInt) ContractVersion .... FROM cb_Contract WHERE ...").ToSingle<Contract>();
///
/// //其他数据绑定代码
/// //...
/// }
///
/// public void Update(string dataXML, long contractVersion){
/// //将AppFrom的xml直接转换为实体对象
/// Contract contract = XmlDataEntity.ConvertXmlToSingle<CbContract>(dataXML)
///
/// //构造用于并发检测的原对象
/// Contract origContract = new Contract();
/// origContract.ContractGUID = contract.ContractGUID; //并发检测时,原对象的主键是必须提供的
/// contract.ContractVersion = contractVersion.Int64ToTimeStamp(); //界面中长整型的时间戳字段可以通过Int64ToTimeStamp扩展方法转换为byte[]数组
///
/// try{
/// //根据时间戳字段,进行并发检测
/// int count = contract.Update(origContract, ConcurrencyMode.TimeStamp);
/// //如果更新成功,则count为1
///
/// //根据原始值,进行并发检测
/// //count = contract.Update(origContract, ConcurrencyMode.OriginalValue);
/// }
/// catch(OptimisticConcurrencyException ex){
/// //并发检测失败,将会抛出OptimisticConcurrencyException异常
/// }
/// }
/// ]]>
/// </code>
/// </example>
/// <param name="original">用于并发检测的原始对象</param>
/// <param name="concurrencyMode">并发检测模式</param>
/// <exception cref="InvalidProgramException">如果数据实体类型的定义不符合规范，就会抛出此异常</exception>
/// <exception cref="InvalidOperationException">类没有定义主键,即没有任何一个属性被标记为PrimaryKey=true,则抛出此异常</exception>
/// <exception cref="ArgumentException">用于并发检测的原始对象不能是当前对象</exception>
/// <exception cref="Hack.Fast.Extensions.Exception.OptimisticConcurrencyException">并发检测失败时,则会抛出此异常</exception>
/// <returns>返回ADO.NET的原始结果</returns>
public virtual int Update(BaseEntity original, ConcurrencyMode concurrencyMode)
{
if (original == null)
{
throw new ArgumentNullException("original");
}
if (concurrencyMode == ConcurrencyMode.OriginalValue && object.ReferenceEquals(this, original))
{
throw new ArgumentException("用于并发检测的原始对象不能是当前对象。");
}
int flag = concurrencyMode == ConcurrencyMode.TimeStamp ? 8 : 9;
CPQuery query = GetCPQuery(flag, new object[] { this, original, bakObject });
if (query == null)
{
return(0);
}
if (_funcBefore != null)
{
if (_funcBefore(query) == false)
{
return(-1);
}
}
int effectRows = query.ExecuteNonQuery();
if (effectRows == 0)
{
throw new Hack.Fast.Extensions.OptimisticConcurrencyException(
"并发操作失败，本次操作没有更新任何记录，请确认当前数据行没有被其他用户更新或删除。");
}
return(effectRows);
}
/// <summary>
/// 用并发检测的方式，将数据实体对应的记录从数据库表中删除。
/// </summary>
/// <example>
/// <para>下面的代码演示了并发检测模式下,Delete()方法的用法</para>
/// <code>
/// <![CDATA[
/// //Contract类需要继承自BaseEntity,并且需要放在以*.Entity.dll结尾的程序集中
///
/// public void Load(){
///
/// //在页面加载时,查询数据库信息,时间戳字段需要通过CAST转换为长整型,绑定到界面中
/// Contract contract = CPQuery.From("SELECT ContractGUID, CAST(ContractVersion AS BigInt) ContractVersion .... FROM cb_Contract WHERE ...").ToSingle<Contract>();
///
/// //其他数据绑定代码
/// //...
/// }
///
/// //删除通道,前端需要传递合同GUID,时间戳字段
/// public void Delete(Guid contractGUID, long contractVersion){
///
/// //删除动作,需要构建一个实体对象
/// Contract contract = new Contract();
/// contract.ContractGUID = contractGUID; //主键必须赋值,这是删除语句的首要条件
/// contract.ContractVersion = contractVersion.Int64ToTimeStamp(); //界面中长整型的时间戳字段可以通过Int64ToTimeStamp扩展方法转换为byte[]数组
///
/// try{
/// //根据时间戳字段,进行并发检测
/// int count = contract.Delete(ConcurrencyMode.TimeStamp);
/// //如果删除成功,则count为1
///
/// //根据原始值,进行并发检测
/// //count = contract.Delete(oldContract, ConcurrencyMode.OriginalValue);
/// }
/// catch(OptimisticConcurrencyException ex){
/// //并发检测失败,将会抛出OptimisticConcurrencyException异常
/// }
/// }
/// ]]>
/// </code>
/// </example>
/// <exception cref="InvalidProgramException">如果数据实体类型的定义不符合规范，就会抛出此异常</exception>
/// <exception cref="InvalidOperationException">类没有定义主键,即没有任何一个属性被标记为PrimaryKey=true,则抛出此异常</exception>
/// <exception cref="Hack.Fast.Extensions.Exception.OptimisticConcurrencyException">并发检测失败时,则会抛出此异常</exception>
/// <param name="concurrencyMode">并发检测模式</param>
/// <returns>返回ADO.NET的原始结果</returns>
public virtual int Delete(ConcurrencyMode concurrencyMode)
{
int flag = concurrencyMode == ConcurrencyMode.TimeStamp ? 5 : 6;
CPQuery query = GetCPQuery(flag, new object[] { this });
if (_funcBefore != null)
{
if (_funcBefore(query) == false)
{
return(-1);
}
}
int effectRows = query.ExecuteNonQuery();
if (effectRows == 0)
{
throw new Hack.Fast.Extensions.OptimisticConcurrencyException(
"并发操作失败，本次操作没有删除任何记录，请确认当前数据行没有被其他用户更新或删除。");
}
return(effectRows);
}
/// <summary>
/// 立即提交所有的操作请求
/// 如果需要事务支持，请用using(ConnectionScope)的方式来实现
/// </summary>
public void Submit()
{
foreach (DBExecuteInfo info in _list)
{
if (info.FuncBefore != null)
{
CPQuery query = info.DBExecute as CPQuery;
if (info.FuncBefore(query) == false)
{
continue;
}
}
int effectRows = info.DBExecute.ExecuteNonQuery();
if (info.Concurrency && effectRows == 0)
{
throw new Hack.Fast.Extensions.OptimisticConcurrencyException(
"并发操作失败，本次操作没有更新任何记录，请确认当前数据行没有被其他用户更新或删除。");
}
}
_list.Clear();
}
/// <summary>
/// 根据PagingInfo信息，返回分页后的实体集合
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="pageInfo">分页信息</param>
/// <returns>实体集合</returns>
public List <T> ToPageList <T>(PagingInfo pageInfo) where T : class, new()
{
//--需要配置的SQL语句
//select row_number() over (order by UpCount asc) as RowIndex,
// Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
//from CaoItem
//where CreateTime < @CreateTime
//--在运行时，将会生成下面二条SQL
//select * from (
//select row_number() over (order by UpCount asc) as RowIndex,
// Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
//from CaoItem
//where CreateTime < @CreateTime
//) as t1
//where RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1))
//select count(*) from ( select
//-- 去掉 select row_number() over (order by UpCount asc) as RowIndex,
// Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
//from CaoItem as p
//where CreateTime < @CreateTime
//) as t1
// 为了方便得到 count 的语句，先直接定位 ") as RowIndex,"
// 然后删除这之前的部分，将 select count(*) from (select 加到SQL语句的前面。
// 所以，这里就检查SQL语句是否符合要求。
//string flag = ") as RowIndex,";
//int p = xmlCommandText.IndexOf(flag, StringComparison.OrdinalIgnoreCase);
//if( p <= 0 )
// throw new InvalidOperationException("XML中配置的SQL语句不符合分页语句的要求。");
string xmlCommandText = _query.ToString();
Match match = _pagingRegex.Match(xmlCommandText);
if (match.Success == false)
{
throw new InvalidOperationException("XML中配置的SQL语句不符合分页语句的要求。");
}
int p = match.Index;
// 获取命令参数数组
SqlParameter[] parameters1 = _query.Command.Parameters.Cast <SqlParameter>().ToArray();
_query.Command.Parameters.Clear(); // 断开参数对象与原命令的关联。
// 克隆参数数组，因为参数对象只能属于一个命令对象。
SqlParameter[] parameters2 = (from pp in parameters1
select new SqlParameter {
ParameterName = pp.ParameterName,
SqlDbType = pp.SqlDbType,
Size = pp.Size,
Scale = pp.Scale,
Value = pp.Value,
Direction = pp.Direction
}).ToArray();
// 生成 SELECT 命令
string selectCommandText = string.Format(@"select * from ( {0} ) as t1
where RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1))", xmlCommandText);
CPQuery query1 = CPQuery.From(selectCommandText, parameters1);
query1.Command.Parameters.Add(new SqlParameter {
ParameterName = "@PageIndex",
SqlDbType = System.Data.SqlDbType.Int,
Value = pageInfo.PageIndex
});
query1.Command.Parameters.Add(new SqlParameter {
ParameterName = "@PageSize",
SqlDbType = System.Data.SqlDbType.Int,
Value = pageInfo.PageSize
});
// 生成 COUNT 命令
string getCountText = string.Format("select count(*) from (select {0} ) as t1",
xmlCommandText.Substring(p + match.Length));
CPQuery query2 = CPQuery.From(getCountText, parameters2);
// 执行二次数据库操作（在一个连接中）
using (ConnectionScope scope = new ConnectionScope()) {
List <T> list = query1.ToList <T>();
pageInfo.TotalRecords = query2.ExecuteScalar <int>();
return(list);
}
}