/// <summary>
/// Gets the delete SQL by primary key.
/// </summary>
public static SqlBatch GetDeleteSqlByPrimaryKey <T>(this IDataHelper dh, string otherWhereCondition, params object[] allParmaryKeysSortByColumnName)
{
var type = typeof(T);
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]));
}
if (!string.IsNullOrWhiteSpace(otherWhereCondition))
{
wherePropList.Add(otherWhereCondition);
}
var whereSql = string.Join(" AND ", wherePropList);
var sql = GetDeleteSqlByWhere <T>(dh, whereSql, paramList.ToArray());
return(sql);
}
/// <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 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 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 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);
}
