protected override bool RunOperation(
SqlConnection sqlConnection,
IDbTransaction dbTransaction,
DbFunctionType dbfunctionType,
string operationName,
IEnumerable<KeyValuePair<string, object>> parameters,
out object result)
{
var context = parameters.First().Value as ISearchContext;
if (context != null)
{
var sql = new SearchBuilder().BuildSearchSql(context);
using (var cmd = this.DbAccess.GetCommand(sql, CommandType.Text))
{
result = this.DbAccess.GetReader(cmd, dbTransaction);
return true;
}
}
// failure...
result = null;
return false;
}
/// <summary>
/// The run operation.
/// </summary>
/// <param name="sqlConnection">
/// The sql connection.
/// </param>
/// <param name="dbTransaction">
/// The unit Of Work.
/// </param>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The run operation.
/// </returns>
protected abstract bool RunOperation(
[NotNull] SqlConnection sqlConnection,
[NotNull] IDbTransaction dbTransaction,
DbFunctionType dbfunctionType,
[NotNull] string operationName,
[NotNull] IEnumerable <KeyValuePair <string, object> > parameters,
[CanBeNull] out object result);
/// <summary>
/// 生成函数执行 sql 语句
/// </summary>
/// <param name="providerName">ADO.NET 数据库对象</param>
/// <param name="dbFunctionType">函数类型</param>
/// <param name="funcName">函数名词</param>
/// <param name="parameters">函数参数</param>
/// <returns>sql 语句</returns>
internal static string GenerateFunctionSql(string providerName, DbFunctionType dbFunctionType, string funcName, params DbParameter[] parameters)
{
// 检查是否支持函数
DatabaseProvider.CheckFunctionSupported(providerName, dbFunctionType);
parameters ??= Array.Empty <DbParameter>();
var stringBuilder = new StringBuilder();
stringBuilder.Append($"SELECT{(dbFunctionType == DbFunctionType.Table ? " * FROM" : "")} {funcName}(");
// 生成函数参数
for (var i = 0; i < parameters.Length; i++)
{
var sqlParameter = parameters[i];
// 添加 @ 前缀参数
stringBuilder.Append($"@{(sqlParameter.ParameterName.Replace("@", ""))}");
// 处理最后一个参数逗号
if (i != parameters.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append("); ");
return(stringBuilder.ToString());
}
/// <summary>
/// The run operation.
/// </summary>
/// <param name="sqlConnection">The sql connection.</param>
/// <param name="dbTransaction">The unit Of Work.</param>
/// <param name="dbfunctionType">The dbfunction type.</param>
/// <param name="operationName">The operation name.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="result">The result.</param>
/// <returns>
/// The run operation.
/// </returns>
protected override bool RunOperation(
SqlConnection sqlConnection,
IDbTransaction dbTransaction,
DbFunctionType dbfunctionType,
string operationName,
IEnumerable <KeyValuePair <string, object> > parameters,
out object result)
{
var context = parameters.First().Value as ISearchContext;
if (context != null)
{
var sql = new SearchBuilder().BuildSearchSql(context);
using (var cmd = this.DbAccess.GetCommand(sql, CommandType.Text))
{
result = this.DbAccess.GetReader(cmd, dbTransaction);
return(true);
}
}
// failure...
result = null;
return(false);
}
/// <summary>
/// 生成函数执行 sql 语句
/// </summary>
///<param name="providerName">ADO.NET 数据库对象</param>
/// <param name="dbFunctionType">函数类型</param>
/// <param name="funcName">函数名词</param>
/// <param name="keyValues">字典类型参数</param>
/// <returns></returns>
internal static string GenerateFunctionSql(string providerName, DbFunctionType dbFunctionType, string funcName, Dictionary <string, object> keyValues)
{
// 检查是否支持函数
DatabaseProvider.CheckFunctionSupported(providerName, dbFunctionType);
var stringBuilder = new StringBuilder();
stringBuilder.Append($"SELECT{(dbFunctionType == DbFunctionType.Table ? " * FROM" : "")} {funcName}(");
if (keyValues != null && keyValues.Count > 0)
{
var i = 0;
foreach (var key in keyValues.Keys)
{
stringBuilder.Append($"@{key}");
// 处理最后一个参数逗号
if (i != keyValues.Count - 1)
{
stringBuilder.Append(", ");
}
i++;
}
}
stringBuilder.Append("); ");
return(stringBuilder.ToString());
}
/// <summary>
/// The db function execute.
/// </summary>
/// <param name="functionType">
/// The function Type.
/// </param>
/// <param name="binder">
/// The binder.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="executeDb">
/// The execute db.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The db function execute.
/// </returns>
protected bool DbFunctionExecute(
DbFunctionType functionType,
[NotNull] InvokeMemberBinder binder,
[NotNull] IList <KeyValuePair <string, object> > parameters,
[NotNull] Func <IDbCommand, object> executeDb,
[CanBeNull] out object result)
{
CodeContracts.ArgumentNotNull(binder, "binder");
CodeContracts.ArgumentNotNull(parameters, "parameters");
CodeContracts.ArgumentNotNull(executeDb, "executeDb");
var operationName = binder.Name;
// see if there's a specific function override for the current provider...
var specificFunction = this._serviceLocator.Get <IEnumerable <IDbSpecificFunction> >()
.WhereProviderName(this._dbAccessProvider.ProviderName)
.BySortOrder()
.WhereOperationSupported(operationName)
.FirstOrDefault();
if (specificFunction == null || !specificFunction.Execute(functionType, operationName, parameters, out result))
{
using (var cmd = this._dbAccessProvider.Instance.GetCommand(operationName.ToLower(), true, parameters))
{
result = executeDb(cmd);
}
}
this.RunFilters(functionType, parameters, operationName, ref result);
return(true);
}
/// <summary>
/// The run operation.
/// </summary>
/// <param name="sqlConnection">The sql connection.</param>
/// <param name="dbTransaction">The db unit of work.</param>
/// <param name="dbfunctionType">The dbfunction type.</param>
/// <param name="operationName">The operation name.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="result">The result.</param>
/// <returns>
/// The run operation.
/// </returns>
protected override bool RunOperation(
SqlConnection sqlConnection,
IDbTransaction dbTransaction,
DbFunctionType dbfunctionType,
string operationName,
IEnumerable <KeyValuePair <string, object> > parameters,
out object result)
{
// create statistic SQL...
var sb = new StringBuilder();
sb.AppendLine("DECLARE @TableName sysname");
sb.AppendLine("DECLARE cur_showfragmentation CURSOR FOR");
sb.AppendLine(
"SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_name LIKE '{objectQualifier}%'");
sb.AppendLine("OPEN cur_showfragmentation");
sb.AppendLine("FETCH NEXT FROM cur_showfragmentation INTO @TableName");
sb.AppendLine("WHILE @@FETCH_STATUS = 0");
sb.AppendLine("BEGIN");
sb.AppendLine("DBCC SHOWCONTIG (@TableName)");
sb.AppendLine("FETCH NEXT FROM cur_showfragmentation INTO @TableName");
sb.AppendLine("END");
sb.AppendLine("CLOSE cur_showfragmentation");
sb.AppendLine("DEALLOCATE cur_showfragmentation");
using (var cmd = this.DbAccess.GetCommand(sb.ToString(), CommandType.Text))
{
this.DbAccess.ExecuteNonQuery(cmd, dbTransaction);
}
result = this._sqlMessages.Select(s => s.Message).ToDelimitedString("\r\n");
return(true);
}
/// <summary>
/// The execute.
/// </summary>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The execute.
/// </returns>
public virtual bool Execute(
DbFunctionType dbfunctionType,
[NotNull] string operationName,
[NotNull] IEnumerable <KeyValuePair <string, object> > parameters,
[CanBeNull] out object result)
{
if (this.IsSupportedOperation(operationName))
{
this._sqlMessages.Clear();
using (var dbTransaction = this.DbAccess.BeginTransaction())
{
if (dbTransaction.Connection is SqlConnection)
{
var sqlConnection = dbTransaction.Connection as SqlConnection;
sqlConnection.FireInfoMessageEventOnUserErrors = true;
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(this.sqlConnection_InfoMessage);
return(this.RunOperation(sqlConnection, dbTransaction, dbfunctionType, operationName, parameters, out result));
}
}
}
result = null;
return(false);
}
/// <summary>
/// 生成函数执行 sql 语句
/// </summary>
/// <param name="providerName">ADO.NET 数据库对象</param>
/// <param name="dbFunctionType">函数类型</param>
/// <param name="funcName">函数名词</param>
/// <param name="parameters">函数参数</param>
/// <returns>sql 语句</returns>
internal static string GenerateFunctionSql(string providerName, DbFunctionType dbFunctionType, string funcName, params DbParameter[] parameters)
{
// 检查是否支持函数
DbProvider.CheckFunctionSupported(providerName, dbFunctionType);
// 生成数据库表值函数 sql
return(GenerateDbFunctionSql(providerName, dbFunctionType, funcName, parameters.Select(u => u.ParameterName).ToArray()));
}
/// <summary>
/// The execute.
/// </summary>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <param name="transaction"></param>
/// <returns>
/// The execute.
/// </returns>
public virtual bool Execute(
DbFunctionType dbfunctionType,
[NotNull] string operationName,
[NotNull] IEnumerable <KeyValuePair <string, object> > parameters,
[CanBeNull] out object result,
IDbTransaction transaction = null)
{
if (this.IsSupportedOperation(operationName))
{
this._sqlMessages.Clear();
var createdTransaction = transaction == null;
try
{
if (transaction == null)
{
transaction = this.DbAccess.BeginTransaction();
}
if (transaction.Connection is SqlConnection)
{
var sqlConnection = transaction.Connection as SqlConnection;
sqlConnection.FireInfoMessageEventOnUserErrors = true;
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(this.sqlConnection_InfoMessage);
var operationSuccessful = this.RunOperation(
sqlConnection,
transaction,
dbfunctionType,
operationName,
parameters,
out result);
if (createdTransaction && operationSuccessful)
{
transaction.Commit();
}
return(operationSuccessful);
}
}
finally
{
if (createdTransaction && transaction != null)
{
transaction.Dispose();
}
}
}
result = null;
return(false);
}
/// <summary>
/// 检查是否支持函数
/// </summary>
/// <param name="providerName">数据库提供器名</param>
/// <param name="dbFunctionType">数据库函数类型</param>
internal static void CheckFunctionSupported(string providerName, DbFunctionType dbFunctionType)
{
if (NotSupportFunctionDatabases.Contains(providerName))
{
throw Oops.Oh(NotSupportException, typeof(NotSupportedException), "function");
}
if (dbFunctionType == DbFunctionType.Table && NotSupportTableFunctionDatabases.Contains(providerName))
{
throw Oops.Oh(NotSupportException, typeof(NotSupportedException), "table function");
}
}
/// <summary>
/// The run filters.
/// </summary>
/// <param name="functionType">
/// The function type.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="result">
/// The result.
/// </param>
private void RunFilters(DbFunctionType functionType, IList <KeyValuePair <string, object> > parameters, string operationName, ref object result)
{
// execute filter...
var filterFunctions = this._serviceLocator.Get <IEnumerable <IDbDataFilter> >()
.BySortOrder()
.WhereOperationSupported(operationName)
.ToList();
foreach (var filter in filterFunctions)
{
filter.Run(functionType, operationName, parameters, result);
}
}
/// <summary>
/// The execute.
/// </summary>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The <see cref="bool"/> .
/// </returns>
public bool Execute(DbFunctionType dbfunctionType, string operationName, IEnumerable <KeyValuePair <string, object> > parameters, out object result)
{
// find operation...
var method = this._methods.FirstOrDefault(x => string.Equals(x.Key.Name, operationName, StringComparison.OrdinalIgnoreCase));
if (method.IsDefault())
{
result = null;
return(false);
}
var mappedParameters = new List <object>();
var incomingParameters = parameters.ToList();
var incomingIndex = 0;
// match up parameters...
foreach (var param in method.Value)
{
if (param.ParameterType == typeof(IDbFunction))
{
// put the db function in...
mappedParameters.Add(this.DbFunction);
continue;
}
ParameterInfo param1 = param;
var matchedNameParam = incomingParameters.FirstOrDefault(x => x.Key == param1.Name);
if (matchedNameParam.IsNotDefault())
{
// use this named parameter...
mappedParameters.Add(matchedNameParam.Value);
}
else if (incomingIndex < incomingParameters.Count)
{
// just use the indexed value of...
mappedParameters.Add(incomingParameters[incomingIndex++].Value);
}
}
// execute the method...
result = method.Key.Invoke(null, mappedParameters.ToArray());
return(true);
}
/// <summary>
/// The run.
/// </summary>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="data">
/// The data.
/// </param>
public void Run(DbFunctionType dbfunctionType, string operationName, IEnumerable<KeyValuePair<string, object>> parameters, object data)
{
if (!this.ServiceLocator.IsYafContext() || !this._styledNickOperations.Contains(operationName.ToLower()) || dbfunctionType != DbFunctionType.DataTable)
{
return;
}
var colorOnly = false;
if (!this.BoardSettings.UseStyledNicks)
{
return;
}
var dataTable = (DataTable)data;
this.StyleTransform.DecodeStyleByTable(dataTable, false);
}
/// <summary>
/// The db function execute.
/// </summary>
/// <param name="functionType">
/// The function Type.
/// </param>
/// <param name="binder">
/// The binder.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="executeDb">
/// The execute db.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The db function execute.
/// </returns>
protected bool DbFunctionExecute(
DbFunctionType functionType,
[NotNull] InvokeMemberBinder binder,
[NotNull] IList <KeyValuePair <string, object> > parameters,
[NotNull] Func <IDbCommand, object> executeDb,
[CanBeNull] out object result)
{
CodeContracts.VerifyNotNull(binder, "binder");
CodeContracts.VerifyNotNull(parameters, "parameters");
CodeContracts.VerifyNotNull(executeDb, "executeDb");
var operationName = binder.Name;
// see if there's a specific function override for the current provider...
var specificFunction = this.DbSpecificFunctions
.WhereOperationSupported(operationName)
.FirstOrDefault();
result = null;
if (specificFunction != null)
{
if (!specificFunction.Execute(functionType, operationName, parameters, out result, this.DbTransaction))
{
// unsuccessful -- execute command below
specificFunction = null;
}
}
if (specificFunction == null)
{
using (var cmd = this._dbAccessProvider.Instance.GetCommand(operationName, CommandType.StoredProcedure, parameters))
{
result = executeDb(cmd);
}
}
this.RunFilters(functionType, parameters, operationName, ref result);
return(true);
}
/// <summary>
/// 生成函数执行 sql 语句
/// </summary>
///<param name="providerName">ADO.NET 数据库对象</param>
/// <param name="dbFunctionType">函数类型</param>
/// <param name="funcName">函数名词</param>
/// <param name="model">参数模型</param>
/// <returns>(string sql, DbParameter[] parameters)</returns>
internal static string GenerateFunctionSql(string providerName, DbFunctionType dbFunctionType, string funcName, object model)
{
// 检查是否支持函数
DbProvider.CheckFunctionSupported(providerName, dbFunctionType);
var modelType = model?.GetType();
// 处理字典类型参数
if (modelType == typeof(Dictionary <string, object>))
{
return(GenerateFunctionSql(providerName, dbFunctionType, funcName, (Dictionary <string, object>)model));
}
// 获取模型所有公开的属性
var properities = model == null
? Array.Empty <PropertyInfo>()
: modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
// 生成数据库表值函数 sql
return(GenerateDbFunctionSql(providerName, dbFunctionType, funcName, properities.Select(u => u.Name).ToArray()));
}
/// <summary>
/// 生成函数执行 sql 语句
/// </summary>
///<param name="providerName">ADO.NET 数据库对象</param>
/// <param name="dbFunctionType">函数类型</param>
/// <param name="funcName">函数名词</param>
/// <param name="model">参数模型</param>
/// <returns>(string sql, DbParameter[] parameters)</returns>
internal static string GenerateFunctionSql(string providerName, DbFunctionType dbFunctionType, string funcName, object model)
{
// 检查是否支持函数
DatabaseProvider.CheckFunctionSupported(providerName, dbFunctionType);
var modelType = model?.GetType();
// 处理字典类型参数
if (modelType == typeof(Dictionary <string, object>))
{
return(GenerateFunctionSql(providerName, dbFunctionType, funcName, (Dictionary <string, object>)model));
}
// 获取模型所有公开的属性
var properities = model == null
? Array.Empty <PropertyInfo>()
: modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
var stringBuilder = new StringBuilder();
stringBuilder.Append($"SELECT{(dbFunctionType == DbFunctionType.Table ? " * FROM" : "")} {funcName}(");
for (var i = 0; i < properities.Length; i++)
{
var property = properities[i];
stringBuilder.Append($"@{property.Name}");
// 处理最后一个参数逗号
if (i != properities.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append("); ");
return(stringBuilder.ToString());
}
/// <summary>
/// The execute.
/// </summary>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The execute.
/// </returns>
public virtual bool Execute(
DbFunctionType dbfunctionType,
[NotNull] string operationName,
[NotNull] IEnumerable<KeyValuePair<string, object>> parameters,
[CanBeNull] out object result)
{
if (this.IsSupportedOperation(operationName))
{
this._sqlMessages.Clear();
using (var dbTransaction = this.DbAccess.BeginTransaction())
{
if (dbTransaction.Connection is SqlConnection)
{
var sqlConnection = dbTransaction.Connection as SqlConnection;
sqlConnection.FireInfoMessageEventOnUserErrors = true;
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(this.sqlConnection_InfoMessage);
return this.RunOperation(sqlConnection, dbTransaction, dbfunctionType, operationName, parameters, out result);
}
}
}
result = null;
return false;
}
/// <summary>
/// 生成函数执行 sql 语句
/// </summary>
///<param name="providerName">ADO.NET 数据库对象</param>
/// <param name="dbFunctionType">函数类型</param>
/// <param name="funcName">函数名词</param>
/// <param name="model">参数模型</param>
/// <returns>(string sql, SqlParameter[] parameters)</returns>
internal static (string sql, SqlParameter[] parameters) CombineFunctionSql(string providerName, DbFunctionType dbFunctionType, string funcName, object model)
{
// 检查是否支持函数
DatabaseProvider.CheckFunctionSupported(providerName, dbFunctionType);
// 获取模型所有公开的属性
var properities = model == null
? Array.Empty <PropertyInfo>()
: model.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
var parameters = new List <SqlParameter>();
var stringBuilder = new StringBuilder();
stringBuilder.Append($"SELECT{(dbFunctionType == DbFunctionType.Table ? " * FROM" : "")} {funcName}(");
for (var i = 0; i < properities.Length; i++)
{
var property = properities[i];
var propertyValue = property.GetValue(model) ?? DBNull.Value;
stringBuilder.Append($"@{property.Name}");
// 处理最后一个参数逗号
if (i != properities.Length - 1)
{
stringBuilder.Append(", ");
}
// 判断属性是否贴有 [DbParameter] 特性
if (property.IsDefined(typeof(DbParameterAttribute), true))
{
var dbParameterAttribute = property.GetCustomAttribute <DbParameterAttribute>(true);
parameters.Add(DbHelpers.CreateSqlParameter(property.Name, propertyValue, dbParameterAttribute));
continue;
}
parameters.Add(new SqlParameter(property.Name, propertyValue));
}
stringBuilder.Append("); ");
return(stringBuilder.ToString(), parameters.ToArray());
}
public abstract string ConvertDbFunction(DbFunctionType functionType, params object[] parameters);
public override string ConvertDbFunction(DbFunctionType functionType, params object[] parameters)
{
string result = "''";
object param0 = parameters[0];
if (functionType == DbFunctionType.Length)
{
result = "LENGTH(" + param0 + ")";
}
//模糊搜索
else if (functionType == DbFunctionType.StartsWith)
{
result = param0 + " || '%'";
}
else if (functionType == DbFunctionType.EndsWith)
{
result = "'%' || " + param0;
}
else if (functionType == DbFunctionType.Contains)
{
result = "'%' || " + param0 + " || '%'";
}
else if (functionType == DbFunctionType.LikeRight)
{
result = param0 + " || '%'";
}
else if (functionType == DbFunctionType.LikeLeft)
{
result = "'%' || " + param0;
}
else if (functionType == DbFunctionType.Like)
{
result = "'%' || " + param0 + " || '%'";
}
//转换
else if (functionType == DbFunctionType.ToUpper)
{
result = "UPPER(" + param0 + ")";
}
else if (functionType == DbFunctionType.ToLower)
{
result = "LOWER(" + param0 + ")";
}
else if (functionType == DbFunctionType.Trim)
{
result = "TRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.TrimEnd)
{
result = "RTRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.TrimStart)
{
result = "LTRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.Substring)
{
result = "SUBSTR(" + param0;
if (parameters.Length > 1)
{
result += "," + parameters[1] + "+1";
}
///截取长度
if (parameters.Length > 2)
{
result += "," + parameters[2];
}
else
{
result += "," + ConvertDbFunction(DbFunctionType.Length, param0);
}
result += (")");
}
else if (functionType == DbFunctionType.IsNullOrEmpty)
{
result = "(" + param0 + " IS NULL OR " + param0 + "= '')";
}
else if (functionType == DbFunctionType.Parse)
{
Type type = parameters[1] as Type;
result = ConvertSqlValue(param0, type);
}
else if (functionType == DbFunctionType.NewGuid)
{
//返回的是一个长度为 16 的 byte[]
//// "SYS_GUID()";
result = "(substr(lower(RAWTOHEX(sys_guid())),1,8) || '-'||substr(lower(RAWTOHEX(sys_guid())),9,4) || '-' || substr(lower(RAWTOHEX(sys_guid())),13,4) || '-' || substr(lower(RAWTOHEX(sys_guid())),17,4) || '-' || substr(lower(RAWTOHEX(sys_guid())),21,12))";
}
//聚合函数
else if (functionType == DbFunctionType.Count)
{
result = "COUNT(1)";
}
else if (functionType == DbFunctionType.Sum)
{
result = "SUM(" + param0 + ")";
}
else if (functionType == DbFunctionType.Max)
{
result = "MAX(" + param0 + ")";
}
else if (functionType == DbFunctionType.Min)
{
result = "MIN(" + param0 + ")";
}
else if (functionType == DbFunctionType.Avg)
{
result = "AVG(" + param0 + ")";
}
//数学函数
else if (functionType == DbFunctionType.Abs)
{
result = ("ABS(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Round)
{
object precision = 2;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("ROUND(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Ceiling)
{
result = ("CEIL(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Floor)
{
result = ("FLOOR(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Sqrt)
{
result = ("SQRT(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Log)
{
object precision = 10;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("LOG(");
result += (precision);
result += (",");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Pow)
{
object precision = 10;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("POWER(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Sign)
{
result = ("SIGN(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Truncate)
{
object precision = 0;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("TRUNC(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Mod)
{
object precision = 2;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("MOD(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Rand)
{
result = ("DBMS_RANDOM.VALUE");
}
else if (functionType == DbFunctionType.IfNull)
{
result = ("NVL(" + param0 + "," + parameters[1] + ")");
}
//Add DateTime 函数
else if (functionType == DbFunctionType.AddYears)
{
/* add_months(systimestamp,12 * 2) */
result = ("ADD_MONTHS(");
result += param0;
result += (",12 * ");
result += (parameters[1]);
result += (")");
}
else if (functionType == DbFunctionType.AddMonths)
{
/* add_months(systimestamp,2) */
result = ("ADD_MONTHS(");
result += param0;
result += (",");
result += (parameters[1]);
result += (")");
}
else if (functionType == DbFunctionType.AddDays)
{
/* (systimestamp + 3) */
result = ("(");
result += param0;
result += (" + ");
result += (parameters[1]);
result += (")");
}
else if (functionType == DbFunctionType.AddHours)
{
result = DbFunction_DATEADD("HOUR", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddMinutes)
{
result = DbFunction_DATEADD("MINUTE", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddSeconds)
{
result = DbFunction_DATEADD("SECOND", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddMilliseconds)
{
throw new NotSupportedException("ORACLE is not supports DbFunctionType.AddMilliseconds");
//result = DbFunction_DATEADD("MILLISECOND", param0, parameters[1]);
}
//diff datetime
else if (functionType == DbFunctionType.DiffYears)
{
result = DbFunction_DATEDIFF("YEAR", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMonths)
{
result = DbFunction_DATEDIFF("MONTH", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffDays)
{
result = DbFunction_DATEDIFF("DAY", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffHours)
{
result = DbFunction_DATEDIFF("HOUR", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMinutes)
{
result = DbFunction_DATEDIFF("MINUTE", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffSeconds)
{
result = DbFunction_DATEDIFF("SECOND", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMilliseconds)
{
result = DbFunction_DATEDIFF("MILLISECOND", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMicroseconds)
{
result = DbFunction_DATEDIFF("MICROSECOND", param0, parameters[1]);
}
return(result);
}
/// <summary>
/// Handle the run operation.
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="dbTransaction"></param>
/// <param name="dbfunctionType"></param>
/// <param name="operationName"></param>
/// <param name="parameters"></param>
/// <param name="result"></param>
/// <returns></returns>
protected override bool RunOperation(
SqlConnection sqlConnection,
IDbTransaction dbTransaction,
DbFunctionType dbfunctionType,
string operationName,
IEnumerable <KeyValuePair <string, object> > parameters,
out object result)
{
// find operation...
var method = this._methods.FirstOrDefault(x => string.Equals(x.Key.Name, operationName, StringComparison.OrdinalIgnoreCase));
if (method.IsDefault())
{
result = null;
return(false);
}
var mappedParameters = new List <object>();
var globalParams = new Dictionary <Type, object>()
{
{ typeof(IDbTransaction), dbTransaction },
{ typeof(SqlConnection), sqlConnection },
{ typeof(DbFunctionType), dbfunctionType },
{ typeof(IDbAccess), this.DbAccess }
};
var incomingParameters = parameters.ToList();
var incomingIndex = 0;
// match up parameters...
foreach (var param in method.Value)
{
var global = globalParams.FirstOrDefault(p => p.Key == param.ParameterType);
if (global.IsNotDefault())
{
mappedParameters.Add(global.Value);
continue;
}
var param1 = param;
var matchedNameParam = incomingParameters.FirstOrDefault(x => x.Key == param1.Name);
if (matchedNameParam.IsNotDefault())
{
// use this named parameter...
mappedParameters.Add(matchedNameParam.Value);
}
else if (incomingIndex < incomingParameters.Count)
{
// just use the indexed value of...
mappedParameters.Add(incomingParameters[incomingIndex++].Value);
}
}
// execute the method...
result = method.Key.Invoke(null, mappedParameters.ToArray());
return(true);
}
/// <summary>
/// The db function execute.
/// </summary>
/// <param name="functionType">
/// The function Type.
/// </param>
/// <param name="binder">
/// The binder.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="executeDb">
/// The execute db.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The db function execute.
/// </returns>
protected bool DbFunctionExecute(
DbFunctionType functionType,
[NotNull] InvokeMemberBinder binder,
[NotNull] IList<KeyValuePair<string, object>> parameters,
[NotNull] Func<DbCommand, object> executeDb,
[CanBeNull] out object result)
{
CodeContracts.ArgumentNotNull(binder, "binder");
CodeContracts.ArgumentNotNull(parameters, "parameters");
CodeContracts.ArgumentNotNull(executeDb, "executeDb");
var operationName = binder.Name;
// see if there's a specific function override for the current provider...
var specificFunction = this._dbSpecificFunctions
.GetForProviderAndOperation(this._dbAccessProvider.ProviderName, operationName)
.FirstOrDefault();
if (specificFunction == null)
{
throw new NotSupportedException(
string.Format("Operation: [{0}] Is not Supported for Provider: [{1}]", operationName, this._dbAccessProvider.ProviderName));
}
if (specificFunction.Execute(functionType, operationName, parameters, out result))
{
return true;
}
using (var cmd = this._dbAccessProvider.Instance.GetCommand(operationName.ToLower(), true, parameters))
{
result = executeDb(cmd);
}
return true;
}
/// <summary>
/// The execute.
/// </summary>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <param name="transaction"></param>
/// <returns>
/// The execute.
/// </returns>
public virtual bool Execute(
DbFunctionType dbfunctionType,
[NotNull] string operationName,
[NotNull] IEnumerable<KeyValuePair<string, object>> parameters,
[CanBeNull] out object result,
IDbTransaction transaction = null)
{
if (this.IsSupportedOperation(operationName))
{
this._sqlMessages.Clear();
bool createdTransaction = transaction == null;
try
{
if (transaction == null)
{
transaction = this.DbAccess.BeginTransaction();
}
if (transaction.Connection is SqlConnection)
{
var sqlConnection = transaction.Connection as SqlConnection;
sqlConnection.FireInfoMessageEventOnUserErrors = true;
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(this.sqlConnection_InfoMessage);
var operationSuccessful = this.RunOperation(sqlConnection, transaction, dbfunctionType, operationName, parameters, out result);
if (createdTransaction && operationSuccessful)
{
transaction.Commit();
}
return operationSuccessful;
}
}
finally
{
if (createdTransaction && transaction != null)
{
transaction.Dispose();
}
}
}
result = null;
return false;
}
/// <summary>
/// Handle the run operation.
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="dbTransaction"></param>
/// <param name="dbfunctionType"></param>
/// <param name="operationName"></param>
/// <param name="parameters"></param>
/// <param name="result"></param>
/// <returns></returns>
protected override bool RunOperation(
SqlConnection sqlConnection,
IDbTransaction dbTransaction,
DbFunctionType dbfunctionType,
string operationName,
IEnumerable<KeyValuePair<string, object>> parameters,
out object result)
{
// find operation...
var method = this._methods.FirstOrDefault(x => string.Equals(x.Key.Name, operationName, StringComparison.OrdinalIgnoreCase));
if (method.IsDefault())
{
result = null;
return false;
}
var mappedParameters = new List<object>();
var globalParams = new Dictionary<Type, object>()
{
{ typeof(IDbTransaction), dbTransaction },
{ typeof(SqlConnection), sqlConnection },
{ typeof(DbFunctionType), dbfunctionType },
{ typeof(IDbAccess), this.DbAccess }
};
var incomingParameters = parameters.ToList();
var incomingIndex = 0;
// match up parameters...
foreach (var param in method.Value)
{
var global = globalParams.FirstOrDefault(p => p.Key == param.ParameterType);
if (global.IsNotDefault())
{
mappedParameters.Add(global.Value);
continue;
}
ParameterInfo param1 = param;
var matchedNameParam = incomingParameters.FirstOrDefault(x => x.Key == param1.Name);
if (matchedNameParam.IsNotDefault())
{
// use this named parameter...
mappedParameters.Add(matchedNameParam.Value);
}
else if (incomingIndex < incomingParameters.Count)
{
// just use the indexed value of...
mappedParameters.Add(incomingParameters[incomingIndex++].Value);
}
}
// execute the method...
result = method.Key.Invoke(null, mappedParameters.ToArray());
return true;
}
public override string ConvertDbFunction(DbFunctionType functionType, params object[] parameters)
{
string result = "''";
object param0 = parameters[0];
if (functionType == DbFunctionType.Length)
{
result = "LENGTH(" + param0 + ")";
}
else if (functionType == DbFunctionType.StartsWith)
{
result = param0 + " || '%'";
}
else if (functionType == DbFunctionType.EndsWith)
{
result = "'%' || " + param0;
}
else if (functionType == DbFunctionType.Contains)
{
result = "'%' || " + param0 + " || '%'";
}
else if (functionType == DbFunctionType.LikeRight)
{
result = param0 + " || '%'";
}
else if (functionType == DbFunctionType.LikeLeft)
{
result = "'%' || " + param0;
}
else if (functionType == DbFunctionType.Like)
{
result = "'%' || " + param0 + " || '%'";
}
else if (functionType == DbFunctionType.ToUpper)
{
result = "UPPER(" + param0 + ")";
}
else if (functionType == DbFunctionType.ToLower)
{
result = "LOWER(" + param0 + ")";
}
else if (functionType == DbFunctionType.Trim)
{
result = "TRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.TrimEnd)
{
result = "RTRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.TrimStart)
{
result = "LTRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.Substring)
{
result = "SUBSTR(" + param0;
if (parameters.Length > 1)
{
result += "," + parameters[1] + "+1";
}
///截取长度
if (parameters.Length > 2)
{
result += "," + parameters[2];
}
else
{
result += "," + ConvertDbFunction(DbFunctionType.Length, param0);
}
result += (")");
}
else if (functionType == DbFunctionType.IsNullOrEmpty)
{
result = "(" + param0 + " IS NULL OR " + param0 + "= '')";
}
else if (functionType == DbFunctionType.Parse)
{
Type type = parameters[1] as Type;
result = ConvertSqlValue(param0, type);
}
else if (functionType == DbFunctionType.NewGuid)
{
throw new NotSupportedException("PostgreSQL is not supports DbFunctionType.NewGuid ");
}
//聚合函数
else if (functionType == DbFunctionType.Count)
{
result = "COUNT(1)";
}
else if (functionType == DbFunctionType.Sum)
{
result = "SUM(" + param0 + ")";
}
else if (functionType == DbFunctionType.Max)
{
result = "MAX(" + param0 + ")";
}
else if (functionType == DbFunctionType.Min)
{
result = "MIN(" + param0 + ")";
}
else if (functionType == DbFunctionType.Avg)
{
result = "AVG(" + param0 + ")";
}
//数学函数
else if (functionType == DbFunctionType.Abs)
{
result = ("ABS(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Round)
{
object precision = 2;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("ROUND(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Ceiling)
{
result = ("CEIL(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Floor)
{
result = ("FLOOR(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Sqrt)
{
result = ("SQRT(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Log)
{
object precision = 10;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("LOG(");
result += (precision);
result += (",");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Pow)
{
object precision = 10;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("POWER(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Sign)
{
result = ("SIGN(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Truncate)
{
object precision = 0;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("TRUNC(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Mod)
{
object precision = 2;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("MOD(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Rand)
{
result = ("RANDOM()");
}
else if (functionType == DbFunctionType.IfNull)
{
result = ("COALESCE(" + param0 + "," + parameters[1] + ")");
}
//Add DateTime 函数
else if (functionType == DbFunctionType.AddYears)
{
result = DbFunction_DATEADD("YEAR", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddMonths)
{
result = DbFunction_DATEADD("MONTH", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddDays)
{
result = DbFunction_DATEADD("DAY", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddHours)
{
result = DbFunction_DATEADD("HOUR", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddMinutes)
{
result = DbFunction_DATEADD("MINUTE", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddSeconds)
{
result = DbFunction_DATEADD("SECOND", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddMilliseconds)
{
result = DbFunction_DATEADD("MILLISECOND", param0, parameters[1]);
}
//diff datetime
else if (functionType == DbFunctionType.DiffYears)
{
result = DbFunction_DATEDIFF("YEAR", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMonths)
{
result = DbFunction_DATEDIFF("MONTH", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffDays)
{
result = DbFunction_DATEDIFF("DAY", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffHours)
{
result = DbFunction_DATEDIFF("HOUR", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMinutes)
{
result = DbFunction_DATEDIFF("MINUTE", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffSeconds)
{
result = DbFunction_DATEDIFF("SECOND", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMilliseconds)
{
result = DbFunction_DATEDIFF("MILLISECOND", param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMicroseconds)
{
result = DbFunction_DATEDIFF("MICROSECOND", param0, parameters[1]);
}
return(result);
}
/// <summary>
/// The run.
/// </summary>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="data">
/// The data.
/// </param>
public void Run(DbFunctionType dbfunctionType, string operationName, IEnumerable<KeyValuePair<string, object>> parameters, object data)
{
if (!this.ServiceLocator.IsYafContext() || !this._styledNickOperations.Contains(operationName.ToLower()) || dbfunctionType != DbFunctionType.DataTable)
{
return;
}
bool colorOnly = false;
if (!this.BoardSettings.UseStyledNicks)
{
return;
}
var dataTable = (DataTable)data;
this.StyleTransform.DecodeStyleByTable(dataTable, colorOnly);
}
/// <summary>
/// The execute.
/// </summary>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The <see cref="bool"/> .
/// </returns>
public bool Execute(DbFunctionType dbfunctionType, string operationName, IEnumerable<KeyValuePair<string, object>> parameters, out object result)
{
// find operation...
var method = this._methods.FirstOrDefault(x => string.Equals(x.Key.Name, operationName, StringComparison.OrdinalIgnoreCase));
if (method.IsDefault())
{
result = null;
return false;
}
var mappedParameters = new List<object>();
var incomingParameters = parameters.ToList();
var incomingIndex = 0;
// match up parameters...
foreach (var param in method.Value)
{
if (param.ParameterType == typeof(IDbFunction))
{
// put the db function in...
mappedParameters.Add(this.DbFunction);
continue;
}
ParameterInfo param1 = param;
var matchedNameParam = incomingParameters.FirstOrDefault(x => x.Key == param1.Name);
if (matchedNameParam.IsNotDefault())
{
// use this named parameter...
mappedParameters.Add(matchedNameParam.Value);
}
else if (incomingIndex < incomingParameters.Count)
{
// just use the indexed value of...
mappedParameters.Add(incomingParameters[incomingIndex++].Value);
}
}
// execute the method...
result = method.Key.Invoke(null, mappedParameters.ToArray());
return true;
}
/// <summary>
/// The run operation.
/// </summary>
/// <param name="sqlConnection">
/// The sql connection.
/// </param>
/// <param name="dbTransaction">
/// The db unit of work.
/// </param>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The run operation.
/// </returns>
protected override bool RunOperation(
SqlConnection sqlConnection,
IDbTransaction dbTransaction,
DbFunctionType dbfunctionType,
string operationName,
IEnumerable<KeyValuePair<string, object>> parameters,
out object result)
{
// create statistic SQL...
var sb = new StringBuilder();
sb.AppendLine("DECLARE @TableName sysname");
sb.AppendLine("DECLARE cur_showfragmentation CURSOR FOR");
sb.AppendLine("SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_name LIKE '{objectQualifier}%'");
sb.AppendLine("OPEN cur_showfragmentation");
sb.AppendLine("FETCH NEXT FROM cur_showfragmentation INTO @TableName");
sb.AppendLine("WHILE @@FETCH_STATUS = 0");
sb.AppendLine("BEGIN");
sb.AppendLine("DBCC SHOWCONTIG (@TableName)");
sb.AppendLine("FETCH NEXT FROM cur_showfragmentation INTO @TableName");
sb.AppendLine("END");
sb.AppendLine("CLOSE cur_showfragmentation");
sb.AppendLine("DEALLOCATE cur_showfragmentation");
using (var cmd = this.DbAccess.GetCommand(sb.ToString(), CommandType.Text))
{
this.DbAccess.ExecuteNonQuery(cmd, dbTransaction);
}
result = this._sqlMessages.Select(s => s.Message).ToDelimitedString("\r\n");
return true;
}
/// <summary>
/// The run operation.
/// </summary>
/// <param name="sqlConnection">
/// The sql connection.
/// </param>
/// <param name="dbTransaction">
/// The unit Of Work.
/// </param>
/// <param name="dbfunctionType">
/// The dbfunction type.
/// </param>
/// <param name="operationName">
/// The operation name.
/// </param>
/// <param name="parameters">
/// The parameters.
/// </param>
/// <param name="result">
/// The result.
/// </param>
/// <returns>
/// The run operation.
/// </returns>
protected abstract bool RunOperation(
[NotNull] SqlConnection sqlConnection,
[NotNull] IDbTransaction dbTransaction,
DbFunctionType dbfunctionType,
[NotNull] string operationName,
[NotNull] IEnumerable<KeyValuePair<string, object>> parameters,
[CanBeNull] out object result);
public override string ConvertDbFunction(DbFunctionType functionType, params object[] parameters)
{
string result = "''";
object param0 = parameters[0];
if (functionType == DbFunctionType.Length)
{
result = "LENGTH(" + param0 + ")";
}
else if (functionType == DbFunctionType.StartsWith)
{
result = param0 + " || '%'";
}
else if (functionType == DbFunctionType.EndsWith)
{
result = "'%' || " + param0;
}
else if (functionType == DbFunctionType.Contains)
{
result = "'%' || " + param0 + " || '%'";
}
else if (functionType == DbFunctionType.LikeRight)
{
result = param0 + " || '%'";
}
else if (functionType == DbFunctionType.LikeLeft)
{
result = "'%' || " + param0;
}
else if (functionType == DbFunctionType.Like)
{
result = "'%' || " + param0 + " || '%'";
}
else if (functionType == DbFunctionType.ToUpper)
{
result = "UPPER(" + param0 + ")";
}
else if (functionType == DbFunctionType.ToLower)
{
result = "LOWER(" + param0 + ")";
}
else if (functionType == DbFunctionType.Trim)
{
result = "TRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.TrimEnd)
{
result = "RTRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.TrimStart)
{
result = "LTRIM(" + param0 + ")";
}
else if (functionType == DbFunctionType.Substring)
{
result = "SUBSTR(" + param0;
if (parameters.Length > 1)
{
result += "," + parameters[1] + "+1";
}
///截取长度
if (parameters.Length > 2)
{
result += "," + parameters[2];
}
else
{
result += "," + ConvertDbFunction(DbFunctionType.Length, param0);
}
result += (")");
}
else if (functionType == DbFunctionType.IsNullOrEmpty)
{
result = "(" + param0 + " IS NULL OR " + param0 + "= '')";
}
else if (functionType == DbFunctionType.Parse)
{
Type type = parameters[1] as Type;
result = ConvertSqlValue(param0, type);
}
else if (functionType == DbFunctionType.NewGuid)
{
throw new NotSupportedException("SQLite is not supports DbFunctionType.NewGuid");
}
//聚合函数
else if (functionType == DbFunctionType.Count)
{
result = "COUNT(1)";
}
else if (functionType == DbFunctionType.Sum)
{
result = "SUM(" + param0 + ")";
}
else if (functionType == DbFunctionType.Max)
{
result = "MAX(" + param0 + ")";
}
else if (functionType == DbFunctionType.Min)
{
result = "MIN(" + param0 + ")";
}
else if (functionType == DbFunctionType.Avg)
{
result = "AVG(" + param0 + ")";
}
//数学函数
else if (functionType == DbFunctionType.Abs)
{
result = ("ABS(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Round)
{
object precision = 2;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("ROUND(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Ceiling)
{
result = ("CEIL(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Floor)
{
result = ("FLOOR(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Sqrt)
{
result = ("SQRT(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Log)
{
//throw new NotSupportedException("SQLite is not supports DbFunctionType.NewGuid");
//object precision = 10;
//if (parameters.Length > 1 && parameters[1] != null)
//{
// precision = parameters[1];
//}
result = ("LOG(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Pow)
{
object precision = 10;
if (parameters.Length > 1 && parameters[1] != null)
{
precision = parameters[1];
}
result = ("POWER(");
result += (param0);
result += (",");
result += (precision);
result += (")");
}
else if (functionType == DbFunctionType.Sign)
{
result = ("SIGN(");
result += (param0);
result += (")");
}
else if (functionType == DbFunctionType.Truncate)
{
throw new NotSupportedException("SQLite is not supports DbFunctionType.Truncate");
//object precision = 0;
//if (parameters.Length > 1 && parameters[1] != null)
//{
// precision = parameters[1];
//}
//result = ("TRUNCATE(");
//result += (param0);
//result += (",");
//result += (precision);
//result += (")");
}
else if (functionType == DbFunctionType.Mod)
{
throw new NotSupportedException("SQLite is not supports DbFunctionType.Mod");
//object precision = 2;
//if (parameters.Length > 1 && parameters[1] != null)
//{
// precision = parameters[1];
//}
//result = ("MOD(");
//result += (param0);
//result += (",");
//result += (precision);
//result += (")");
}
else if (functionType == DbFunctionType.Rand)
{
result = ("RANDOM()");
}
else if (functionType == DbFunctionType.IfNull)
{
result = ("IFNULL(" + param0 + "," + parameters[1] + ")");
}
//Add DateTime 函数
else if (functionType == DbFunctionType.AddYears)
{
result = DbFunction_DATEADD("years", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddMonths)
{
result = DbFunction_DATEADD("months", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddDays)
{
result = DbFunction_DATEADD("days", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddHours)
{
result = DbFunction_DATEADD("hours", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddMinutes)
{
result = DbFunction_DATEADD("minutes", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddSeconds)
{
result = DbFunction_DATEADD("seconds", param0, parameters[1]);
}
else if (functionType == DbFunctionType.AddMilliseconds)
{
result = DbFunction_DATEADD("MILLISECOND", param0, parameters[1]);
}
//diff datetime
else if (functionType == DbFunctionType.DiffYears)
{
result = Append_DiffYears(param0, parameters[1]);
}
else if (functionType == DbFunctionType.DiffMonths)
{
object startDateTimeExp = param0, endDateTimeExp = parameters[1];
/*
* This method will generating sql like following:
* (cast(STRFTIME('%Y','2016-07-06 09:01:24') as INTEGER) - cast(STRFTIME('%Y','2015-08-06 09:01:24') as INTEGER)) * 12 + (cast(STRFTIME('%m','2016-07-06 09:01:24') as INTEGER) - cast(STRFTIME('%m','2015-08-06 09:01:24') as INTEGER))
*/
StringBuilder sb = new StringBuilder();
sb.Append("(");
/* (cast(STRFTIME('%Y','2016-07-06 09:01:24') as INTEGER) - cast(STRFTIME('%Y','2015-08-06 09:01:24') as INTEGER)) * 12 */
sb.Append(Append_DiffYears(startDateTimeExp, endDateTimeExp));
sb.Append(" * 12");
sb.Append(" + ");
/* (cast(STRFTIME('%m','2016-07-06 09:01:24') as INTEGER) - cast(STRFTIME('%m','2015-08-06 09:01:24') as INTEGER)) */
sb.Append("(");
sb.Append(DbFunction_DATEPART("m", endDateTimeExp));
sb.Append(" - ");
sb.Append(DbFunction_DATEPART("m", startDateTimeExp));
sb.Append(")");
sb.Append(")");
result = sb.ToString();
}
else if (functionType == DbFunctionType.DiffDays)
{
result = Append_DateDiff(param0, parameters[1], null);
}
else if (functionType == DbFunctionType.DiffHours)
{
result = Append_DateDiff(param0, parameters[1], 24);
}
else if (functionType == DbFunctionType.DiffMinutes)
{
result = Append_DateDiff(param0, parameters[1], 24 * 60);
}
else if (functionType == DbFunctionType.DiffSeconds)
{
result = Append_DateDiff(param0, parameters[1], 24 * 60 * 60);
}
else if (functionType == DbFunctionType.DiffMilliseconds)
{
result = Append_DateDiff(param0, parameters[1], 24 * 60 * 60 * 1000);
}
else if (functionType == DbFunctionType.DiffMicroseconds)
{
throw new NotSupportedException("SQLite is not supports DbFunctionType.DiffMicroseconds");
}
return(result);
}
