/// <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> /// 创建插入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="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> /// 转换一个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); }