/// <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);
}
/// <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);
}
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("");
}