public SqlGenerator()
{
if (DbHelper == null)
{
DbHelper = new CommDbHelper();
}
}
protected static string GetCreateSqlStrFromObj <T>()
{
var type = typeof(T);
var tableAttrs = type.GetCustomAttributes(typeof(TableAttribute), true);
var tableName = type.Name;
if (tableAttrs.Length > 0)
{
var tableAttr = tableAttrs[0] as TableAttribute;
tableName = tableAttr == null ? type.Name : tableAttr.Name;
}
var columkey = new StringBuilder();
var columkeyStr = String.Empty;
var columval = new StringBuilder();
var columvalStr = String.Empty;
var propertis = CommDbHelper.GetModelDataPropertyInfos(typeof(T));
var propertyList = propertis.FindAll(t => !String.IsNullOrEmpty(t.ColumnName));
foreach (var propertyInfo in propertyList)
{
columkey.AppendFormat("{0},", propertyInfo.ColumnName);
columval.AppendFormat("'{0}',", propertyInfo.PropertyName);
}
columkeyStr = columkey.ToString().TrimEnd(',');
columvalStr = columval.ToString().TrimEnd(',');
var startsql = String.Format("insert into {0}({1}) values", tableName, columkeyStr);
return(startsql + "|" + columvalStr);
}
public static string GetDeleteSqlByParmDic <T>(T t)
{
var whereSql = new StringBuilder();
var tis = CommDbHelper.GetModelDataTableInfo(typeof(T));
foreach (var keys in tis.KeysName)
{
var ks = keys.Split('|');
foreach (var s in ks)
{
var pis = tis.AllPropertyInfos.Find(m => String.Equals(m.ColumnName, s, StringComparison.CurrentCultureIgnoreCase) || String.Equals(m.PropertyName, s, StringComparison.CurrentCultureIgnoreCase));
if (pis == null)
{
throw new Exception("ERROR DELETE KEY: " + s + " IN TABLE: " + tis.TableName);
}
var val = ReflectionHandler.PropertyFastGetValue(pis.PropertyInfo, t);
if (pis.PropertyType == typeof(DateTime))
{
whereSql.AppendFormat("{0}=to_date('{1}','{2}')", pis.ColumnName, ((DateTime)val).ToString(DateTimeHandler.LongDateTimeStyleToOracleString), DateTimeHandler.LongDateTimeStyleForOracle);
}
else
{
whereSql.AppendFormat("{0}='{1}' and ", pis.ColumnName, val);
}
}
whereSql.AppendFormat("1=1 or ");
}
whereSql.Append("1=0");
return(String.Format("delete from {0} where {1}", tis.TableName, whereSql));
}
private static Dictionary <string, object> MakeDbparmsWithObj <T>(T t)
{
var pis = CommDbHelper.GetModelDataPropertyInfos(typeof(T));
var dbparms = new Dictionary <string, object>();
foreach (var pi in pis)
{
var v = pi.PropertyInfo.GetValue(t, new object[0]);
dbparms.Add((pPre + pi.PropertyName).ToUpper(), v);
}
return(dbparms);
}
private static Dictionary <string, object> GetObjDicFromT <T>(T t)
{
var dic = new Dictionary <string, object>();
var pis = CommDbHelper.GetModelDataPropertyInfos(typeof(T));
foreach (var modelPropertyInfo in pis)
{
var v = modelPropertyInfo.PropertyInfo.GetValue(t, new object[0]);
dic.Add(pPre + modelPropertyInfo.PropertyName, v);
}
return(dic);
}
public static string GetSequeceIdSql <T>()
{
var pis = CommDbHelper.GetModelDataPropertyInfos(typeof(T));
var pi = pis.Find(t => t.IsAuto);
if (pi == null)
{
return(String.Empty);
}
var sql = String.Format("SELECT LAST_INSERT_ID()");
return(sql);
}
/// <summary>
/// Construct sql value with the single object.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="templete"></param>
/// <param name="t"></param>
/// <returns></returns>
private static string GetCreateSqlValue <T>(string templete, T t)
{
var pis = CommDbHelper.GetModelDataPropertyInfos(typeof(T));
templete = templete.ToLower();
foreach (var p in pis)
{
var o = p.PropertyInfo.GetValue(t, new object[0]);
var pureO = GetPureSQLValue(o, p.PropertyType);
var k = "'" + p.PropertyName.ToLower() + "'";
templete = templete.Replace(k, pureO);
}
return(templete);
}
private static string GetAttributes <T>(string key)
{
var result = "";
var tableInfo = CommDbHelper.GetModelDataTableInfo(typeof(T));
foreach (var propertyInfo in tableInfo.AllPropertyInfos)
{
if (propertyInfo.PropertyName.ToUpper().Equals(key.Substring(key.IndexOf("_", StringComparison.Ordinal) + 1)))
{
result = propertyInfo.ColumnName;
break;
}
}
return(result);
}
public T Get <T>(object value) where T : class
{
var properties = CommDbHelper.GetModelDataPropertyInfos(typeof(T));
var parmDic = new Dictionary <string, object>();
foreach (var propertyInfo in properties)
{
if (!propertyInfo.IsPrimaryColumn)
{
continue;
}
parmDic.Add(propertyInfo.ColumnName, value);
break;
}
var resultLst = GetFromDictionary <T>(CommonSqlKey.Null, parmDic);
return(resultLst != null && resultLst.Count > 0 ? resultLst[0] : null);
}
public static string GetSelectSqlByObj <T>(T t)
{
var tis = CommDbHelper.GetModelDataTableInfo(typeof(T));
var sqlTxt = new StringBuilder("select * from ");
sqlTxt.Append(tis.TableName);
sqlTxt.Append(" where ");
foreach (var modelPropertyInfo in tis.AllPropertyInfos)
{
var value = modelPropertyInfo.PropertyInfo.GetValue(t, null);
if (modelPropertyInfo.DefaultValue != value)
{
sqlTxt.AppendFormat("{0}={1} and ", modelPropertyInfo.ColumnName, GetPureSQLValue(value, modelPropertyInfo.PropertyType));
}
}
sqlTxt.Append("1=1");
return(sqlTxt.ToString());
}
public static string GetDeleteSqlByParmDic <T>(object kv)
{
var whereSql = new StringBuilder();
var tis = CommDbHelper.GetModelDataTableInfo(typeof(T));
foreach (var keys in tis.KeysName)
{
if (keys.Contains('|'))
{
continue;
}
var pis = tis.AllPropertyInfos.Find(m => String.Equals(m.ColumnName, keys, StringComparison.CurrentCultureIgnoreCase) || String.Equals(m.PropertyName, keys, StringComparison.CurrentCultureIgnoreCase));
if (pis == null)
{
throw new Exception("ERROR DELETE KEY: " + keys + " IN TABLE: " + tis.TableName);
}
whereSql.AppendFormat("{0}='{1}' and ", pis.ColumnName, kv);
}
whereSql.Append("1=1");
return(String.Format("delete from {0} where {1}", tis.TableName, whereSql));
}
/// <summary>
/// 进行数据验证判断
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
private List <string> ObjectValidation <T>(IEnumerable <T> t)
{
var pis = CommDbHelper.GetModelDataPropertyInfos(typeof(T));
var result = new List <string>();
//Check null
var notAllowedNullColumn = pis.Where(modelPropertyInfo => modelPropertyInfo.IsNotNull).ToList();
if (notAllowedNullColumn.Count <= 0)
{
return(result);
}
foreach (var obj in t)
{
foreach (var column in notAllowedNullColumn)
{
var v = column.PropertyInfo.GetValue(obj, null);
if (v == null || String.IsNullOrEmpty(v.ToString()))
{
result.Add(column.PropertyName);
}
}
}
return(result);
}
private static object GetResult(Type retType, Dictionary <string, object> drDic)
{
var result = Activator.CreateInstance(retType);
var curKey = String.Empty;
// try
// {
var pis = CommDbHelper.GetModelDataPropertyInfos(retType);
foreach (var propertyInfo in pis)
{
if (propertyInfo != null)
{
if (propertyInfo.PropertyType.IsSubclassOf(typeof(BaseModel))) //如果是模型类，则递归获取对象实例
{
var parms = new List <Object>
{
propertyInfo.PropertyType,
drDic
};
var obj = typeof(SqlGenerator).GetMethod("GetResult", BindingFlags.NonPublic | BindingFlags.Static).Invoke(new object(), parms.ToArray());
ReflectionHandler.PropertyFastSetValue(propertyInfo.PropertyInfo, result, obj);
}
curKey = (String.IsNullOrEmpty(propertyInfo.ColumnName)
? propertyInfo.PropertyName
: propertyInfo.ColumnName).ToUpper().Replace("_", "");
if (!drDic.ContainsKey(curKey))
{
continue;
}
var value = drDic[curKey];
if (value != DBNull.Value)
{
if (propertyInfo.PropertyType == typeof(DateTime))
{
DateTime temp;
if (!DateTime.TryParse(value.ToString().Trim(), out temp))
{
value = temp.ToString(CultureInfo.InvariantCulture);
}
}
if (propertyInfo.PropertyType == typeof(int?))
{
ReflectionHandler.PropertyFastSetValue(propertyInfo.PropertyInfo, result, (int?)value);
}
else if (propertyInfo.PropertyType == typeof(float?))
{
ReflectionHandler.PropertyFastSetValue(propertyInfo.PropertyInfo, result, (float?)value);
}
else if (propertyInfo.PropertyType == typeof(byte[]))
{
ReflectionHandler.PropertyFastSetValue(propertyInfo.PropertyInfo, result, (byte[])value);
}
else if (propertyInfo.PropertyType.IsValueType || propertyInfo.PropertyType == typeof(string))
{
ReflectionHandler.PropertyFastSetValue(propertyInfo.PropertyInfo, result, Convert.ChangeType(value, propertyInfo.PropertyType));
}
else
{
//TODO 考虑转换json
// var mi = typeof(JsonHandler).GetMethod("GetObjectFromJson");
// var valObj = Activator.CreateInstance(propertyInfo.PropertyType);
//var gM = mi.MakeGenericMethod(propertyInfo.PropertyType);
//ReflectionHandler.Execute(gM, valObj, value.ToString());
ReflectionHandler.PropertyFastSetValue(propertyInfo.PropertyInfo, result, value.ToString());
}
}
}
}
// }
// catch (Exception ee)
// {
// throw new Exception("ERROR KEY:" + curKey + " MSG: " + ee.Message);
// }
return(result);
}
private static string GetTableName <T>()
{
var tableInfo = CommDbHelper.GetModelDataTableInfo(typeof(T));
return(tableInfo.TableName);
}
