/// <summary>
/// 创建不等于条件表达式
/// </summary>
/// <param name="fieldname">左边字段名称</param>
/// <param name="parametname">右边参数名称</param>
/// <param name="parametvalue">右边参数值</param>
/// <param name="paramet">返回与条件表达式一起使用的参数表达式</param>
/// <returns></returns>
internal static string CreateNotEqWhere(
string fieldname,
string parametname,
object parametvalue,
out IDataParamet paramet)
{
paramet = null;
if (string.IsNullOrEmpty(fieldname) || string.IsNullOrEmpty(parametname))
{
return(null);
}
string left = fieldname;
if (MsSqlServerHelper.IsValidFieldName(fieldname))
{
left = MsSqlServerHelper.GetFeildNameInSqlExpress(fieldname);
}
if (parametvalue == null)
{
return(string.Format("{0} IS NOT NULL", left));
}
string right = MsSqlServerHelper.GetParametName(parametname);
paramet = new DataParamet()
{
Direction = System.Data.ParameterDirection.Input, Name = parametname, Value = parametvalue, ValueType = parametvalue.GetType()
};
return(string.Format("{0}<>{1}", left, right));
}
/// <summary>
/// 生成删除数据的Sql表达式
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="deleteItem"></param>
/// <param name="paramet"></param>
/// <returns></returns>
internal static string GetDeleteSql <T>(
T deleteItem,
out IEnumerable <IDataParamet> paramet)
{
paramet = null;
if (deleteItem == null)
{
throw new ObjectIsNullException(LocalResource.DeleteItem);
}
StringBuilder wheresql = new StringBuilder();
List <IDataParamet> paramets = new List <IDataParamet>();
IDataModel model = typeof(T).GetDataModel();
string namehz = GetIdKeyStringValues(deleteItem, model);
int c = 0;
foreach (var k in model.PrimaryKeys)
{
IDataFieldModel f = model.Fields[k.Name];
DataParamet p = AppendParamet(paramets, namehz, f, deleteItem);
if (c == 0)
{
wheresql.Append(string.Format("[{0}]={1}", f.Name, p.Name));
}
else
{
wheresql.Append(string.Format(" AND [{0}]={1}", f.Name, p.Name));
}
c++;
}
if (wheresql.Length == 0) //没有任何主键信息
{
throw new NotFoundPrimaryKeyException(typeof(T));
}
paramet = paramets.ToArray();
string sdf = model.GetSoftDeleteFieldName();
if (!string.IsNullOrEmpty(sdf))
{
return(string.Format("UPDATE {0} SET {1}=1 WHERE {2}",
MsSqlServerHelper.GetFeildNameInSqlExpress(model.Name),
MsSqlServerHelper.GetFeildNameInSqlExpress(sdf),
wheresql.ToString()));
}
else
{
return(string.Format("DELETE {0} WHERE {1}", GetFeildNameInSqlExpress(model.Name), wheresql.ToString()));
}
}
/// <summary>
/// 添加参数到paramets中去
/// </summary>
/// <param name="paramets">参数集合,返回的参数将添加到该集合中,如果不为空的话</param>
/// <param name="ft">参数后缀,可为为null</param>
/// <param name="field">字段信息</param>
/// <param name="value">行值</param>
/// <returns></returns>
private static DataParamet AppendParamet(List <IDataParamet> paramets, string parhz, IDataFieldModel field, object rowvalue)
{
object value = field.GetModelFieldValue(rowvalue);
value = ConvertFieldValueToDbValue(field, value);
DataParamet p = new DataParamet()
{
Name = GenerateParametName(field.Name, parhz),
Value = value,
ValueType = field.GetClrType()
};
int s = field.GetFieldSize();
if (s > 0)
{
p.ValueSize = s;
}
if (paramets != null)
{
paramets.Add(p);
}
return(p);
}
/// <summary>
/// 创建以parametvalue开始或在parametvalue中以列的值开始的条件表达式
/// </summary>
/// <param name="fieldname">列字段名称</param>
/// <param name="parametname">参数名称</param>
/// <param name="parametvalue">参数值</param>
/// <param name="parametIsExpress">是否是取在parametvalue中以列的值开始的条件表达式</param>
/// <param name="paramet"></param>
/// <returns></returns>
internal static string CreateFirstIndexOfWhere(
string fieldname,
string parametname,
string parametvalue,
bool parametIsExpress,
out IDataParamet paramet)
{
paramet = null;
if (string.IsNullOrEmpty(fieldname) || string.IsNullOrEmpty(parametname))
{
return(null);
}
string left = fieldname;
if (MsSqlServerHelper.IsValidFieldName(fieldname))
{
left = MsSqlServerHelper.GetFeildNameInSqlExpress(fieldname);
}
if (parametvalue == null)
{
return(null);
}
string right = MsSqlServerHelper.GetParametName(parametname);
paramet = new DataParamet()
{
Direction = System.Data.ParameterDirection.Input, Name = parametname, Value = parametvalue, ValueType = parametvalue.GetType()
};
if (parametIsExpress)
{
return(string.Format("CHARINDEX({1},{0})=1", left, right));
}
else
{
return(string.Format("CHARINDEX({0},{1})=1", left, right));
}
}
/// <summary>
/// 生成修改数据的Sql表达式
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="updateItem"></param>
/// <param name="updaterange"></param>
/// <param name="updatefields"></param>
/// <param name="paramet"></param>
/// <returns></returns>
internal static string GetUpdateSql <T>(
T updateItem,
FieldSelectRange updaterange,
IEnumerable <string> updatefields,
out IEnumerable <IDataParamet> paramet)
where T : class
{
paramet = null;
if (updateItem == null)
{
throw new ObjectIsNullException(LocalResource.UpdateItem);
}
StringBuilder wheresql = new StringBuilder();
StringBuilder setsql = new StringBuilder();
List <IDataParamet> paramets = new List <IDataParamet>();
IDataModel model = typeof(T).GetDataModel();
string namehz = GetIdKeyStringValues(updateItem, model);
int c = 0;
foreach (var k in model.PrimaryKeys)
{
IDataFieldModel f = model.Fields[k.Name];
DataParamet p = AppendParamet(paramets, namehz, f, updateItem);
if (c == 0)
{
wheresql.Append(string.Format("[{0}]={1}", f.Name, p.Name));
}
else
{
wheresql.Append(string.Format(" AND [{0}]={1}", f.Name, p.Name));
}
c++;
}
if (wheresql.Length == 0) //没有任何主键信息
{
throw new NotFoundPrimaryKeyException(typeof(T));
}
IDataFieldModelCollection fields = model.Fields;
c = 0;
foreach (var f in fields)
{
if (f.IsRowVersion())
{
continue;
}
if (f.GetDbGeneratedDefine() != null)
{
continue;
}
if (f.IsPrimaryKey() != null)
{
continue;
}
switch (updaterange)
{
case FieldSelectRange.All: break;
case FieldSelectRange.OnlyFields:
{
if (updatefields == null)
{
continue;
}
if (updatefields.FirstOrDefault(
ff => string.Equals(ff, f.Name, StringComparison.OrdinalIgnoreCase)) == null)
{
continue;
}
break;
}
case FieldSelectRange.IgnoreFields:
{
if (updatefields != null)
{
if (updatefields.FirstOrDefault(
ff => string.Equals(ff, f.Name, StringComparison.OrdinalIgnoreCase)) != null)
{
continue;
}
}
break;
}
}
DataParamet p = AppendParamet(paramets, namehz, f, updateItem);
if (c == 0)
{
setsql.AppendLine(string.Format("[{0}]={1}", f.Name, p.Name));
}
else
{
setsql.AppendLine(string.Format(",[{0}]={1}", f.Name, p.Name));
}
c++;
}
if (setsql.Length == 0)
{
return(null);
}
paramet = paramets.ToArray();
return(string.Format("UPDATE [{0}] SET {1} WHERE {2}", model.Name, setsql.ToString(), wheresql.ToString()));
}
/// <summary>
/// 生成添加数据的Sql表达式
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="newitem"></param>
/// <param name="par"></param>
/// <returns></returns>
internal static string GetAddNewSql <T>(
T newitem, out AddNewSqlExpressOutputParamet par)
where T : class
{
par = null;
if (newitem == null)
{
throw new ObjectIsNullException(LocalResource.AddNewItem);
}
StringBuilder cols = new StringBuilder();
StringBuilder values = new StringBuilder();
StringBuilder setIdsql = new StringBuilder();
IDataModel model = typeof(T).GetDataModel();
string namehz = GetIdKeyStringValues(newitem, model);
List <IDataParamet> paramets = new List <IDataParamet>();
List <string> outputGenerateFieldNames = new List <string>();
IDataFieldModelCollection fields = model.Fields;
int c = 0;
foreach (var f in fields)
{
if (f.IsRowVersion())
{
continue;
}
if (f.GetDbGeneratedDefine() != null)
{
if (f.IsPrimaryKey() != null)
{
DataParamet p = AppendParamet(paramets, namehz, f, newitem);
p.Direction = System.Data.ParameterDirection.InputOutput;
outputGenerateFieldNames.Add(f.Name);
if (setIdsql.Length == 0)
{
setIdsql.AppendFormat("SET {0} = SCOPE_IDENTITY()", p.Name);
}
else
{
setIdsql.AppendFormat("\r\n SET {0} = SCOPE_IDENTITY()", p.Name);
}
}
continue;
}
DataParamet fieldvalue = AppendParamet(paramets, namehz, f, newitem);
if (c == 0)
{
cols.Append(string.Format("[{0}]", f.Name));
values.Append(fieldvalue.Name);
}
else
{
cols.AppendFormat(",[{0}]", f.Name);
values.AppendFormat(",{0}", fieldvalue.Name);
}
c++;
}
par = new AddNewSqlExpressOutputParamet(
outputGenerateFieldNames.ToArray(),
paramets.ToArray(),
namehz);
if (setIdsql.Length == 0)
{
return(string.Format("INSERT INTO [{0}] ({1}) \r\n VALUES({2})", model.Name, cols.ToString(), values.ToString()));
}
else
{
return(string.Format("INSERT INTO [{0}] ({1}) \r\n VALUES({2}) \r\n {3}", model.Name, cols.ToString(), values.ToString(), setIdsql.ToString()));
}
}
