/// <summary>
/// 创建插入SQL
/// </summary>
/// <param name="paramz"></param>
/// <param name="TableName"></param>
/// <param name="FieldValues"></param>
/// <returns></returns>
public virtual string CreateInsertSQL(out System.Data.IDataParameter[] paramz, string TableName, Dictionary <string, object> FieldValues)
{
paramz = null;
if (String.IsNullOrEmpty(TableName))
{
throw new ArgumentNullException("TableName is null");
}
if (FieldValues == null || FieldValues.Count == 0)
{
throw new ArgumentException("FieldValues is empty");
}
StringBuilder StrFileds = new StringBuilder();
StringBuilder StrValues = new StringBuilder();
IDataParameters paras = CreateDataParameters();
int index = 0;
foreach (String key in FieldValues.Keys)
{
StrFileds.Append(m_Adapter.AdaptColumnName(key) + ",");
String pName = paras.AdaptParameterName(key) + index;
paras.AddParameterValue(pName, FieldValues[key]);
StrValues.Append(pName + ",");
index++;
}
paramz = paras.Parameters;
StrFileds.Remove(StrFileds.Length - 1, 1);
StrValues.Remove(StrValues.Length - 1, 1);
String SQL = String.Format(CommonSQL.SQL_INSERT, TableName, StrFileds, StrValues);
return(SQL);
}
/// <summary>
/// 创建更新SQL语句
/// </summary>
/// <param name="Paras"></param>
/// <returns></returns>
public string CreateUpdataSQL(out IDataParameter[] Paras)
{
Paras = null;
AbstractEntity Entity = m_Entity as AbstractEntity;
if (Entity.ChangedProperties.Count > 0)
{
StringBuilder sBuilderPairs = new StringBuilder();
IDataParameters list = CreateDataParameters(m_DBAdapter);
foreach (string Property in Entity.ChangedProperties.Keys)
{
object PropertyValue = Entity.ChangedProperties[Property];
if (!IsPrimaryKeyProperty(Property))//更新的时候不更新主键
{
string segment = ConvertOneExpressToSQL(Expression.Eq(Property, PropertyValue), list);
sBuilderPairs.Append(segment + ",");
}
}
sBuilderPairs.Remove(sBuilderPairs.Length - 1, 1);//去掉最后的逗号;
//Where 子句
string where = GetPrimaryKeySQL(ref list);
//组装SQL
string UpdateSQL = String.Format(CommonSQL.SQL_UPDATE, AdaptedTableName, sBuilderPairs.ToString(), where);
Paras = list.Parameters;
return(UpdateSQL);
}
return("");
}
/// <summary>
/// 常见删除SQL
/// </summary>
/// <param name="paramz"></param>
/// <param name="TableName"></param>
/// <param name="Condition"></param>
/// <returns></returns>
public virtual string CreateDeleteSQL(out System.Data.IDataParameter[] paramz, string TableName, Dictionary <string, object> Condition)
{
paramz = null;
if (String.IsNullOrEmpty(TableName))
{
throw new ArgumentNullException("TableName is null");
}
StringBuilder StrWhere = new StringBuilder();
if (Condition != null && Condition.Count > 0)
{
IDataParameters paras = CreateDataParameters();
int index = 0;
foreach (String key in Condition.Keys)
{
String pName = paras.AdaptParameterName(key) + index;
paras.AddParameterValue(pName, Condition[key]);
String Segment = key + "=" + pName;
StrWhere.Append(Segment + CommonSQL.AND);
index++;
}
StrWhere.Remove(StrWhere.ToString().LastIndexOf(CommonSQL.AND), CommonSQL.AND.Length);
paramz = paras.Parameters;
}
if (StrWhere.Length == 0)
{
StrWhere.Append("1<2");
}
String SQL = String.Format(CommonSQL.SQL_DELETE, TableName, StrWhere);
return(SQL);
}
/// <summary>
/// Executes the funtion synchronous with the specified parameters
/// </summary>
/// <param name="Parameters">An implementation of IDataParameters</param>
/// <returns>The result of the function</returns>
public IDataResult ExecuteFunction(IDataParameters Parameters)
{
var TaskValue = Task.Run(async() => await ExecuteFunctionAsync(Parameters));
TaskValue.Wait();
return(TaskValue.Result);
}
/// <summary>
/// 把一个条件表达式转换为SQL
/// </summary>
/// <param name="exp"></param>
/// <param name="Paras"></param>
/// <returns></returns>
protected virtual string ConvertOneExpressToSQL(Expression exp, IDataParameters Paras)
{
IFieldMapping FiledMapping = m_Mapping.GetFieldMapping(exp.PropertyName) as IFieldMapping;//字段映射
switch (exp.OP)
{
case OP.Eq: return(ConvertEqToSQL(FiledMapping, exp.value, Paras));
case OP.Gt: return(ConvertGtToSQL(FiledMapping, exp.value, Paras));
case OP.GtAndEq: return(ConvertGtAndEqToSQL(FiledMapping, exp.value, Paras));
case OP.Lt: return(ConvertLtToSQL(FiledMapping, exp.value, Paras));
case OP.LtAndEq: return(ConvertLtAndEqToSQL(FiledMapping, exp.value, Paras));
case OP.Like: return(ConvertLikeToSQL(FiledMapping, exp.value, Paras));
case OP.Between: return(ConvertBetweenToSQL(FiledMapping, exp.value, exp.value2, Paras));
case OP.IsNotNull: return(ConvertIsNotNullToSQL(FiledMapping));
case OP.IsNull: return(ConvertIsNullToSQL(FiledMapping));
case OP.Or: return(ConvertOrToSQL(exp, Paras));
default: throw new Exception("不被支持的运算类型:" + exp.OP.ToString());
}
}
public virtual IDataResult ExecuteFunction(IDataParameters Parameters)
{
DataResult dataResult = new DataResult();
LoginParameters LoginParameters = objectSerializationService.GetObjectsFromByteArray <LoginParameters>(Parameters.ParametersValue);
dataResult.ResultValue = objectSerializationService.ToByteArray(this.Authenticate(LoginParameters));
return(dataResult);
}
public override async Task <IDataResult> Post()
{
//TODO Jm should the datastore id be part of the heders or the parameters?
var DataStoreId = this.GetHeader("DataStoreId");
IDataParameters parameters = await DeserializeFromStream(Request.Body);
parameters.AdditionalValues.Add("DataStoreId", DataStoreId);
return(DataStoreFunctionServer.ExecuteFunction(parameters));
}
/// <summary>
/// 获取确认主键是否存在的SQL语句
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public virtual string CheckDataExistSQL(out IDataParameter[] para)
{
IDataParameters paras = CreateDataParameters(m_DBAdapter);
string where = GetPrimaryKeySQL(ref paras);
string SQL = String.Format(CommonSQL.SQL_SELECT, "COUNT(*)", AdaptedTableName, where);
para = paras.Parameters;
return(SQL);
}
public override async Task <IDataResult> Post()
{
var DataStoreId = this.GetHeader("DataStoreId");
IDataParameters parameters = await DeserializeFromStream(Request.Body);
parameters.AdditionalValues.Add("DataStoreId", DataStoreId);
IDataResult dataResult = DataStoreFunctionServer.ExecuteFunction(parameters);
return(dataResult);
}
/// <summary>
/// 以主键删除记录
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public string CreateDeleteSQL(out IDataParameter[] para)
{
IDataParameters paras = CreateDataParameters(m_DBAdapter);
string where = GetPrimaryKeySQL(ref paras);
para = paras.Parameters;
string DeleteSQL = String.Format(CommonSQL.SQL_DELETE, AdaptedTableName, where);
return(DeleteSQL);
}
public async virtual Task <IHttpActionResult> Post()
{
var body = this.Request.Content;
var DataStoreId = this.Request.Headers.FirstOrDefault(x => x.Key == Key).Value.FirstOrDefault();
IDataParameters parameters = await DeserializeFromStream(await body.ReadAsStreamAsync());
parameters.AdditionalValues.Add(Key, DataStoreId);
IDataResult content = this.DataStoreFunctionServer.ExecuteFunction(parameters);
return(base.Ok(content));
}
/// <summary>
/// 转换一个OR运算
/// </summary>
/// <param name="exp"></param>
/// <param name="Paras"></param>
/// <returns></returns>
protected virtual string ConvertOrToSQL(Expression exp, IDataParameters Paras)
{
if (exp.Or_Expressions != null && exp.Or_Expressions.Count > 0)
{
StringBuilder sBuilder = new StringBuilder();
foreach (Expression iexp in exp.Or_Expressions)
{
sBuilder.Append(ConvertOneExpressToSQL(exp, Paras) + CommonSQL.OR);
}
sBuilder.Remove(sBuilder.Length - CommonSQL.OR.Length, CommonSQL.OR.Length);
return("(" + sBuilder.ToString() + ")");
}
return("");
}
/// <summary>
/// 把查询表达式集合转换为SQL
/// </summary>
/// <param name="Paras"></param>
/// <returns></returns>
public virtual string ConvertExpressToSQL(out IDataParameter[] Paras)
{
Paras = null;
if (m_Expressions.Count > 0)
{
StringBuilder sBuilder = new StringBuilder();
IDataParameters Pas = CreateDataParameters(m_DBAdapter);
foreach (Expression exp in m_Expressions)
{
sBuilder.Append(ConvertOneExpressToSQL(exp, Pas) + CommonSQL.AND);
}
sBuilder.Remove(sBuilder.Length - CommonSQL.AND.Length, CommonSQL.AND.Length);
Paras = Pas.Parameters;
return(sBuilder.ToString());
}
return("");
}
private String GetPrimaryKeySQL(ref IDataParameters paras)
{
if (paras == null)
{
paras = CreateDataParameters(m_DBAdapter);
}
StringBuilder sBuilderKeys = new StringBuilder();
foreach (IPrimaryKeyMapping PK in m_Mapping.PrimaryKeyCollection)
{
PropertyInfo KeyProperty = m_Entity.GetType().GetProperty(PK.PropertyName); //主键属性
object KeyValue = KeyProperty.GetValue(m_Entity, null); //主键值
string KeyParaName = m_DBAdapter.AdaptParameterName("pK_" + PK.PropertyName); //主键字段名称
string keyWhere = m_DBAdapter.AdaptColumnName(PK.ColumnName) + "=" + KeyParaName; //[ID]=@pK_ID
sBuilderKeys.Append(keyWhere + " AND ");
paras.AddParameterValue(KeyParaName, KeyValue);
}
sBuilderKeys.Remove(sBuilderKeys.ToString().LastIndexOf("AND"), 3);//去掉最后一个AND
return(sBuilderKeys.ToString());
}
/// <summary>
/// 创建插入SQL
/// </summary>
/// <returns></returns>
public virtual string CreateInsertSQL(out IDataParameter[] Paras)
{
Paras = null;
AbstractEntity Entity = m_Entity as AbstractEntity;
if (Entity.ChangedProperties.Count > 0)
{
IDataParameters paras = CreateDataParameters(m_DBAdapter);
ArrayList Columns = new ArrayList();
foreach (string Property in Entity.ChangedProperties.Keys)
{
IFieldMapping FiledMapping = m_Mapping.GetFieldMapping(Property);
object PropertyValue = Entity.ChangedProperties[Property];
//填充参数
paras.AddParameterValue(FiledMapping.ColumnName, PropertyValue, InnerFieldTypeConvert.ConvertToDbType(FiledMapping.FieldType));
Columns.Add(FiledMapping.ColumnName);
}
Paras = paras.Parameters;
string InsertSQL = GeneratInsertSql(AdaptedTableName, Columns.ToArray(typeof(string)) as String[]);
return(InsertSQL);
}
return("");
}
protected override IDataResult Process(IDataParameters parameters)
{
return(base.Process(parameters));
}
public IDataResult ExecuteFunction(IDataParameters Parameters)
{
DataResult dataResult = new DataResult();
string id = Parameters.AdditionalValues[DataStoreId].ToString();
IDataStore DataStore = null;
DataStore = this.ConfigResolver.GetById(id);
if (Parameters.MemberName == nameof(IDataStore.SelectData))
{
dataResult.ResultValue =
ObjectSerializationService
.ToByteArray(
DataStore.SelectData(
ObjectSerializationService
.GetObjectsFromByteArray <SelectStatement[]>(Parameters.ParametersValue)
)
);
}
if (Parameters.MemberName == nameof(IDataStore.ModifyData))
{
dataResult.ResultValue =
ObjectSerializationService
.ToByteArray(
DataStore.ModifyData(
ObjectSerializationService
.GetObjectsFromByteArray <ModificationStatement[]>(Parameters.ParametersValue)
)
);
}
if (Parameters.MemberName == nameof(IDataStore.UpdateSchema))
{
UpdateSchemaParameters updateSchemaParameters = ObjectSerializationService
.GetObjectsFromByteArray <UpdateSchemaParameters>(Parameters.ParametersValue);
dataResult.ResultValue =
ObjectSerializationService
.ToByteArray(
DataStore.UpdateSchema(updateSchemaParameters.dontCreateIfFirstTableNotExist,
updateSchemaParameters.tables)
);
}
if (Parameters.MemberName == nameof(ICommandChannel.Do))
{
CommandChannelDoParams DoParams = ObjectSerializationService
.GetObjectsFromByteArray <CommandChannelDoParams>(Parameters.ParametersValue);
ICommandChannel commandChannel = DataStore as ICommandChannel;
if (commandChannel != null)
{
object data = commandChannel.Do(DoParams.Command,
DoParams.Args);
switch (DoParams.Command)
{
case CommandChannelHelper.Command_ExecuteScalarSQL:
case CommandChannelHelper.Command_ExecuteScalarSQLWithParams:
dataResult.ResultValue =
ObjectSerializationService
.ToByteArray <object>(
data
);
break;
case CommandChannelHelper.Command_ExecuteQuerySQL:
case CommandChannelHelper.Command_ExecuteQuerySQLWithParams:
case CommandChannelHelper.Command_ExecuteQuerySQLWithMetadata:
case CommandChannelHelper.Command_ExecuteQuerySQLWithMetadataWithParams:
case CommandChannelHelper.Command_ExecuteStoredProcedure:
case CommandChannelHelper.Command_ExecuteStoredProcedureParametrized:
dataResult.ResultValue =
ObjectSerializationService
.ToByteArray <SelectedData>(
data as SelectedData
);
break;
case CommandChannelHelper.Command_ExecuteNonQuerySQL:
case CommandChannelHelper.Command_ExecuteNonQuerySQLWithParams:
dataResult.ResultValue =
ObjectSerializationService
.ToByteArray <int>(
(int)data
);
break;
default:
throw new Exception($"ICommandChannel Do method retuned an unknow data type while processing {DoParams.Command}");
}
}
}
return(dataResult);
}
/// <summary>
/// 转换一个Between运算
/// </summary>
/// <param name="FiledMapping"></param>
/// <param name="value1"></param>
/// <param name="value2"></param>
/// <param name="Paras"></param>
/// <returns></returns>
protected virtual string ConvertBetweenToSQL(IFieldMapping FiledMapping, object value1, object value2, IDataParameters Paras)
{
string ColName = FiledMapping.ColumnName; //数据库列名
string ParameterName1 = Paras.AdaptParameterName(ColName + "_1"); //参数1名称
string ParameterName2 = Paras.AdaptParameterName(ColName + "_2"); //参数2名称
string Sentence = m_DBAdapter.AdaptColumnName(ColName) + " Between " + ParameterName1 + " AND " + ParameterName2; //Like SQL子句
Paras.AddParameterValue(ParameterName1, value1, InnerFieldTypeConvert.ConvertToDbType(FiledMapping.FieldType));
Paras.AddParameterValue(ParameterName2, value2, InnerFieldTypeConvert.ConvertToDbType(FiledMapping.FieldType));
return(Sentence);
}
/// <summary>
/// Executes the funtion asynchronous with the specified parameters
/// </summary>
/// <param name="Parameters">An implementation of IDataParameters</param>
/// <returns>A task with the result of the function</returns>
public async Task <IDataResult> ExecuteFunctionAsync(IDataParameters Parameters)
{
var result = await client.PostAsync <DataResult, IDataParameters>(Parameters, resource, GetHeaders());
return(result.Body);
}
/// <summary>
/// 转换一个Like运算
/// </summary>
/// <param name="FiledMapping"></param>
/// <param name="value"></param>
/// <param name="Paras"></param>
/// <returns></returns>
protected virtual string ConvertLikeToSQL(IFieldMapping FiledMapping, object value, IDataParameters Paras)
{
string ColName = FiledMapping.ColumnName; //数据库列名
string ParameterName = Paras.AdaptParameterName(ColName); //参数名称
string Sentence = m_DBAdapter.AdaptColumnName(ColName) + " LIKE " + ParameterName; //Like SQL子句
Paras.AddParameterValue(ParameterName, value, InnerFieldTypeConvert.ConvertToDbType(FiledMapping.FieldType));
return(Sentence);
}
/// <summary>
/// 转换一个小于等于运算
/// </summary>
/// <param name="FiledMapping"></param>
/// <param name="value"></param>
/// <param name="Paras"></param>
/// <returns></returns>
protected override string ConvertLtAndEqToSQL(IFieldMapping FiledMapping, object value, IDataParameters Paras)
{
string ColName = FiledMapping.ColumnName; //数据库列名
string ParameterName = m_DBAdapter.AdaptParameterName(ColName); //参数名称
string Sentence = m_DBAdapter.AdaptColumnName(ColName) + "<=" + ParameterName; //小于等于SQL子句
Paras.AddParameterValue(ColName, value, InnerFieldTypeConvert.ConvertToDbType(FiledMapping.FieldType));
return(Sentence);
}
