/// <summary>
/// Gets the Inserts into tables if selectSqlCondition(such as 'select 1 from student where name=@name') has no record SQL.
/// </summary>
public static SqlBatch GetInsertIfNotExistPrimeryKeySql <T>(this IDataHelper dh, T model, params object[] allParmaryKeysSortByColumnName)
{
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, type);
var primaryKeyProps = AttributeHelper.GetPrimaryKeys(dh.Driver.DirverType, type, false);
if (primaryKeyProps.Count <= 0)
{
throw new NoConfigurePrimaryKeyExceptionException("Not config any primary key in model.");
}
primaryKeyProps.Sort((s1, s2) => string.Compare(s1, s2));
List <string> wherePropList = new List <string>();
List <Params> paramList = new List <Params>();
List <object> primaryValues = new List <object>();
if (allParmaryKeysSortByColumnName != null && allParmaryKeysSortByColumnName.Length > 0)
{
primaryValues.AddRange(allParmaryKeysSortByColumnName);
}
for (int i = 0; i < Math.Min(primaryKeyProps.Count, primaryValues.Count); i++)
{
var key = primaryKeyProps[i];
var columnName = columnNameDic[key];
wherePropList.Add(string.Format("{0}=@SysWhere{1}", dh.Driver.SafeName(columnName), columnName));
paramList.Add(new Params("SysWhere" + columnName, primaryValues[i]));
}
var whereSql = "SELECT 1 FROM " + tableName + " WHERE " + string.Join(" AND ", wherePropList);
return(GetInsertIfNotExistSql(dh, model, whereSql, paramList.ToArray()));
}
/// <summary>
/// Gets the insert or replace SQL. The Save method is not suitable for tables that contain the primary key in the auto-increment column.
/// </summary>
public static List <SqlBatch> GetSaveSql <T>(this IDataHelper dh, params T[] models)
{
List <SqlBatch> sqls = new List <SqlBatch>();
if (models == null || models.Length == 0)
{
return(sqls);
}
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, type);
var primaryKeys = AttributeHelper.GetPrimaryKeys(dh.Driver.DirverType, type, true);
foreach (var md in models)
{
List <string> columnList = new List <string>();
List <Params> param = new List <Params>();
var modelValues = ReflectHelper.GetPropertyValues(type, md, false, true, true);
foreach (var key in modelValues.Keys)
{
columnList.Add(columnNameDic[key]);
param.Add(new Params(columnNameDic[key], modelValues[key]));
}
var sql = dh.Driver.GetSaveSql(tableName, primaryKeys, columnList, param.ToArray());
sqls.Add(sql);
}
return(sqls);
}
/// <summary>
/// Gets the insert SQL.
/// </summary>
public static List <SqlBatch> GetInsertSql <T>(this IDataHelper dh, params T[] models)
{
List <SqlBatch> sqls = new List <SqlBatch>();
if (models == null || models.Length == 0)
{
return(sqls);
}
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, type);
foreach (var md in models)
{
List <string> columnList = new List <string>();
List <Params> param = new List <Params>();
var modelValues = ReflectHelper.GetPropertyValues(type, md, false, true, true);
foreach (var key in modelValues.Keys)
{
columnList.Add(columnNameDic[key]);
param.Add(new Params(columnNameDic[key], modelValues[key]));
}
var columnString = string.Join(",", columnList.ConvertToAll(p => dh.Driver.SafeName(p)));
var columnParamString = string.Join(",", columnList.ConvertToAll(p => "@" + p));
var sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", tableName, columnString, columnParamString);
sqls.Add(new SqlBatch(sql, param.ToArray()));
}
return(sqls);
}
/// <summary>
/// Gets the list.
/// </summary>
public static List <T> GetList <T>(this IDataHelper dh) where T : new()
{
var tableName = AttributeHelper.GetTableName(typeof(T), true, dh.Driver.SafeName);
var tsqlParamed = "SELECT * FROM " + tableName;
return(GetList <T>(dh, tsqlParamed));
}
/// <summary>
/// Gets the one by where.
/// </summary>
public static T GetOneByWhere <T>(this IDataHelper dh, string whereCondition, params Params[] paramKeyAndValue) where T : new()
{
if (string.IsNullOrWhiteSpace(whereCondition))
{
whereCondition = " 1=1 ";
}
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
var tsql = string.Format("SELECT * FROM {0} WHERE {1}", tableName, whereCondition);
var one = GetOne <T>(dh, tsql, paramKeyAndValue);
return(one);
}
/// <summary>
/// Gets the one by primary key.
/// </summary>
public static T GetOneByPrimaryKey <T>(this IDataHelper dh, params object[] allParmaryKeysSortByColumnName) where T : new()
{
var type = typeof(T);
var primaryKeys = AttributeHelper.GetPrimaryKeys(dh.Driver.DirverType, type);
if (primaryKeys.Count == 0)
{
throw new NoConfigurePrimaryKeyExceptionException("the class of '" + type.FullName + "' has no configure any primary key column.");
}
primaryKeys.Sort((s1, s2) => string.Compare(s1, s2));
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
List <object> primaryKeyValues = new List <object>();
if (allParmaryKeysSortByColumnName != null && allParmaryKeysSortByColumnName.Length > 0)
{
primaryKeyValues.AddRange(allParmaryKeysSortByColumnName);
}
if (primaryKeyValues.Count > primaryKeys.Count)
{
throw new PrimaryKeyCountUnequalExceptionException("primary key and it's values count unequal.")
{
PrimaryKeys = primaryKeys.ToArray(), PrimaryKeyValues = primaryKeyValues.ToArray()
}
}
;
var conditionPrimaryKeys = new List <string>();
List <Params> paraList = new List <Params>();
for (int i = 0; i < primaryKeyValues.Count; i++)
{
var pk = primaryKeys[i];
var pkValue = primaryKeyValues[i];
if (pkValue == null || pkValue is DBNull)
{
conditionPrimaryKeys.Add(string.Format("{0} IS NULL", dh.Driver.SafeName(pk)));
}
else
{
conditionPrimaryKeys.Add(string.Format("{0}=@SysParam{1}", dh.Driver.SafeName(pk), pk));
paraList.Add(new Params("SysParam" + primaryKeys[i], primaryKeyValues[i]));
}
}
var whereCondition = string.Join(" AND ", conditionPrimaryKeys);
var tsql = string.Format("SELECT * FROM {0} WHERE {1}", tableName, whereCondition);
var one = GetOne <T>(dh, tsql, paraList.ToArray());
return(one);
}
/// <summary>
/// Gets the Inserts into tables if selectSqlCondition(such as 'select 1 from student where name=@name') has no record SQL.
/// </summary>
public static SqlBatch GetInsertIfNotExistSql <T>(this IDataHelper dh, T model, string selectSqlCondition, params Params[] paramKeyAndValue)
{
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, type);
var insertColumnsValues = ReflectHelper.GetPropertyValues(type, model, false, true, true);
Collections.Concurrent.ConcurrentDictionary <string, object> columnValueDic = new Collections.Concurrent.ConcurrentDictionary <string, object>();
foreach (var item in insertColumnsValues)
{
if (columnNameDic.ContainsKey(item.Key))
{
columnValueDic.TryAdd(columnNameDic[item.Key], item.Value);
}
}
var insertColumns = columnValueDic.Keys.ToList();
return(dh.Driver.GetInsertIfNotExistSql(tableName, insertColumns, columnValueDic, selectSqlCondition, paramKeyAndValue));
}
/// <summary>
/// Gets the update table schema SQL.
/// </summary>
public static SqlBatch GetUpdateTableSql(this IDataHelper dh, Type modelType)
{
List <SqlBatch> sqls = new List <SqlBatch>();
var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, modelType);
var colIndexs = AttributeHelper.GetColumn2IndexNameDics(dh.Driver.DirverType, modelType);
var primaryKeyProps = AttributeHelper.GetPrimaryKeys(dh.Driver.DirverType, modelType, false);
var createDdls = AttributeHelper.GetPropertyName2DDLs(dh.Driver.DirverType, modelType, dh.Driver.TypeMapping);
var tableName = AttributeHelper.GetTableName(modelType, false, null);
ConcurrentDictionary <string, string> columnDdls = new ConcurrentDictionary <string, string>();
foreach (var ddl in createDdls)
{
columnDdls.TryAdd(columnNameDic[ddl.Key], ddl.Value);
}
var primaryKeyColumns = primaryKeyProps.ConvertToAll(p => columnNameDic[p]);
SqlBatch sql = dh.Driver.GetUpdateTableSql(tableName, columnDdls, primaryKeyColumns, colIndexs);
return(sql);
}
/// <summary>
/// Gets the delete SQL.
/// </summary>
public static SqlBatch GetDeleteSql <T>(this IDataHelper dh, T model, string otherWhereCondition, params Params[] paramKeyAndValue)
{
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, type);
var modelValues = ReflectHelper.GetPropertyValues(type, model, false, true, true);
List <string> conditionList = new List <string>();
List <Params> paramList = new List <Params>();
foreach (var mod in modelValues)
{
var columnName = columnNameDic[mod.Key];
var columnValue = mod.Value;
if (columnValue == null)
{
continue;
}
conditionList.Add(string.Format("{0}=@SysWhere{1}", dh.Driver.SafeName(columnName), columnName));
paramList.Add(new Params("SysWhere" + columnName, columnValue));
}
if (!string.IsNullOrWhiteSpace(otherWhereCondition))
{
conditionList.Add(otherWhereCondition);
}
if (paramKeyAndValue != null && paramKeyAndValue.Length > 0)
{
paramList.AddRange(paramKeyAndValue);
}
if (conditionList.Count <= 0)
{
throw new PropertyNoneValueException("model's one property or the parameter 'otherWhereCondition' must have a value at least.")
{
ModelObject = model
}
}
;
var whereString = string.Join(" AND ", conditionList);
var sql = GetDeleteSqlByWhere <T>(dh, whereString, paramList.ToArray());
return(sql);
}
/// <summary>
/// Gets the delete SQL by where.
/// </summary>
public static SqlBatch GetDeleteSqlByWhere <T>(this IDataHelper dh, string whereCondition, params Params[] paramKeyAndValue)
{
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
List <Params> paramList = new List <Params>();
if (string.IsNullOrWhiteSpace(whereCondition))
{
throw new ArgumentNullException("The parameter 'whereCondition' must have a value.");
}
var sqlString = string.Format("DELETE FROM {0} WHERE {1}", tableName, whereCondition);
if (paramKeyAndValue != null && paramKeyAndValue.Length > 0)
{
paramList.AddRange(paramKeyAndValue);
}
var sql = new SqlBatch(sqlString, paramList.ToArray());
return(sql);
}
/// <summary>
/// Gets the update SQL by where.
/// </summary>
public static SqlBatch GetUpdateSqlByWhere <T>(this IDataHelper dh, T model, bool isNullMeansIgnore, string whereCondition, params Params[] paramKeyAndValue)
{
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, type);
var modelValues = ReflectHelper.GetPropertyValues(type, model, !isNullMeansIgnore, true, true);
List <string> upPropList = new List <string>();
List <Params> paramList = new List <Params>();
if (modelValues.Count == 0)
{
throw new PropertyNoneValueException()
{
ModelObject = model
};
}
foreach (var prop in modelValues)
{
var key = prop.Key;
var columnName = columnNameDic[key];
upPropList.Add(string.Format("{0}=@SysParam{1}", dh.Driver.SafeName(columnName), columnName));
paramList.Add(new Params("SysParam" + columnName, prop.Value));
}
if (string.IsNullOrWhiteSpace(whereCondition))
{
throw new ArgumentNullException("The parameter 'whereCondition' must have a value.");
}
var updateSql = string.Join(",", upPropList);
var sqlString = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, updateSql, whereCondition);
if (paramKeyAndValue != null && paramKeyAndValue.Length > 0)
{
paramList.AddRange(paramKeyAndValue);
}
var sql = new SqlBatch(sqlString, paramList.ToArray());
return(sql);
}
/// <summary>
/// Gets the update SQL.
/// </summary>
public static SqlBatch GetUpdateSql <T>(this IDataHelper dh, T model, bool isNullMeansIgnore, string otherWhereCondition)
{
var type = typeof(T);
var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName);
//var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, type);
var modelValues = ReflectHelper.GetPropertyValues(type, model, !isNullMeansIgnore, true, true);
var primaryKeyProps = AttributeHelper.GetPrimaryKeys(dh.Driver.DirverType, type, false);
if (primaryKeyProps.Count <= 0)
{
throw new NoConfigurePrimaryKeyExceptionException("Not config any primary key in model.");
}
primaryKeyProps.Sort((s1, s2) => string.Compare(s1, s2));
List <object> primaryKeyValues = new List <object>();
for (int i = 0; i < primaryKeyProps.Count; i++)
{
if (modelValues.ContainsKey(primaryKeyProps[i]))
{
var val = modelValues[primaryKeyProps[i]];
if (val != null)
{
primaryKeyValues.Add(val);
}
}
}
if (primaryKeyValues.Count <= 0)
{
throw new PropertyNoneValueException("Primary key property must have a value.")
{
ModelObject = model
}
}
;
var sql = GetUpdateSqlByPrimaryKey(dh, model, isNullMeansIgnore, otherWhereCondition, primaryKeyValues.ToArray());
return(sql);
}