private IEnumerable <PropertyInfo> SplitWherePropertiesByOption(CreateCommandOption option, IEnumerable <PropertyInfo> props)
{
if (option.WhereProperties != null && option.WhereProperties.Any())
{
props = props.Where(a => option.WhereProperties.Contains(a.Name)).ToArray();
}
return(props);
}
/// <summary>
/// 生成Delete sql语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="parameter"></param>
/// <param name="option"></param>
/// <returns></returns>
public IDbCommand CreateDeleteCommand <T>(T parameter, CreateCommandOption option = null)
{
if (option == null)
{
option = new CreateCommandOption();
}
var commd = Connection.CreateCommand();
commd.Transaction = Transaction;
commd.CommandTimeout = DefaultCommandTimeoutBySeconds;
var type = typeof(T);
var props = GetColumnProperties(type);
var values = CalculteValues(parameter, props);
string tableName = GetTableName(type);
props = SelectProps(props, values, option);
var primaryKeyName = GetPrimaryKeyName(props);
StringBuilder sbSql = new StringBuilder();
sbSql.Append($"DELETE FROM {tableName} WHERE ");
if (option.WhereProperties == null || !option.WhereProperties.Any())
{
sbSql.Append($"{primaryKeyName}=@{primaryKeyName}");
PropertyInfo primaryInfo = props.First(a => GetColumnName(a) == primaryKeyName);
commd.Parameters.Add(CreateIDataParameter(TagName + primaryKeyName, values[primaryInfo.Name], ParameterDirection.Input));
}
else
{
var whereProps = SplitWherePropertiesByOption(option, GetColumnProperties(type)).ToArray();
var whereValues = CalculteValues(parameter, whereProps);
whereProps = SelectProps(whereProps, whereValues, option);
for (var i = 0; i < whereProps.Length; i++)
{
if (i > 0)
{
sbSql.Append(" AND ");
}
sbSql.AppendFormat("{0}=@{0}", GetColumnName(whereProps[i]));
}
foreach (var prop in whereProps)
{
var val = values[prop.Name];
if (val == null)
{
val = DBNull.Value;
}
commd.Parameters.Add(CreateIDataParameter(TagName + GetColumnName(prop), val, ParameterDirection.Input));
}
}
commd.CommandText = sbSql.ToString();
return(commd);
}
private IEnumerable <PropertyInfo> SplitInsertPropertiesByOption(CreateCommandOption option, IEnumerable <PropertyInfo> props)
{
if (option.OnlyProperties != null && option.OnlyProperties.Any())
{
props = props.Where(a => option.OnlyProperties.Contains(a.Name));
}
else if (option.IgnoreProperties != null && option.IgnoreProperties.Any())
{
props = props.Where(a => !option.IgnoreProperties.Contains(a.Name));
}
return(props);
}
private IEnumerable <PropertyInfo> SplitUpdateOrDeletePropertiesByOption(CreateCommandOption option, IEnumerable <PropertyInfo> props)
{
List <PropertyInfo> list = new List <PropertyInfo>();
if (option.WhereProperties != null && option.WhereProperties.Any())
{
list.AddRange(props.Where(a => option.WhereProperties.Contains(a.Name)));
}
else
{
var pri = props.FirstOrDefault(a => GetColumnAttribute(a).IsPrimaryKey);
if (pri != null)
{
list.Add(pri);
}
}
if (option.OnlyProperties != null && option.OnlyProperties.Any())
{
list.AddRange(props.Where(a => option.OnlyProperties.Contains(a.Name)));
}
else
{
foreach (var item in props)
{
if (!list.Contains(item))
{
list.Add(item);
}
}
}
if (option.IgnoreProperties != null && option.IgnoreProperties.Any())
{
foreach (var item in option.IgnoreProperties)
{
var selected = list.FirstOrDefault(a => a.Name == item);
if (selected != null)
{
list.Remove(selected);
}
}
}
return(list.Distinct());
}
private PropertyInfo[] SelectProps(IEnumerable <PropertyInfo> props, Dictionary <string, object> values, CreateCommandOption option)
{
List <PropertyInfo> list = new List <PropertyInfo>();
foreach (var prop in props)
{
if (_Tablename.Equals(prop.Name))
{
continue;
}
var name = GetColumnName(prop);
if (string.IsNullOrEmpty(name))
{
continue;
}
if (!option.HasDesignatedProperties(prop.Name))
{
var isDefaultValue = false;
var val = values[prop.Name];
if (val == null)
{
continue;
}
switch (val.GetType().Name.ToLower())
{
case "int64":
isDefaultValue = Convert.ToInt64(val) == default(long);
break;
case "int32":
isDefaultValue = Convert.ToInt32(val) == default(int);
break;
case "int16":
isDefaultValue = Convert.ToInt16(val) == default(short);
break;
case "datetime":
isDefaultValue = Convert.ToDateTime(val) == default(DateTime);
break;
}
var attr = GetColumnAttribute(prop);
if (attr != null && attr.IsDbGenerated && isDefaultValue)
{
continue;
}
}
list.Add(prop);
}
return(list.ToArray());
}
private string[] GetParameterColumnNamesWithoutPrimaryKey(IEnumerable <PropertyInfo> props, Dictionary <string, object> values, CreateCommandOption option)
{
var columns = new List <string>();
foreach (var prop in props)
{
if (_Tablename.Equals(prop.Name))
{
continue;
}
var name = GetColumnName(prop);
if (string.IsNullOrEmpty(name))
{
continue;
}
var attr = GetColumnAttribute(prop);
if (attr != null && (attr.IsDbGenerated || attr.IsPrimaryKey))
{
continue;
}
if (!option.HasDesignatedProperties(prop.Name))
{
var val = values[prop.Name];
if (val == null)
{
continue;
}
}
if (option != null && option.WhereProperties != null && option.WhereProperties.Any() && option.WhereProperties.Contains(prop.Name))
{
continue;
}
columns.Add(name);
}
return(columns.ToArray());
}
/// <summary>
/// 生成update语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="parameter"></param>
/// <param name="option"></param>
/// <returns></returns>
public IDbCommand CreateUpdateCommand <T>(T parameter, CreateCommandOption option = null)
{
if (option == null)
{
option = new CreateCommandOption();
}
var commd = Connection.CreateCommand();
commd.Transaction = Transaction;
commd.CommandTimeout = DefaultCommandTimeoutBySeconds;
var type = typeof(T);
var props = SplitUpdateOrDeletePropertiesByOption(option, GetColumnProperties(type));
var values = CalculteValues(parameter, props);
string tableName = GetTableName(type);
props = SelectProps(props, values, option);
var columns = GetParameterColumnNamesWithoutPrimaryKey(props, values, option);
var sbSql = new StringBuilder();
sbSql.Append("UPDATE ");
sbSql.Append(tableName);
sbSql.Append(" SET ");
for (int i = 0; i < columns.Length; i++)
{
if (i > 0)
{
sbSql.Append(",");
}
sbSql.Append(columns[i]);
sbSql.Append("=");
sbSql.Append($"@{columns[i]}");
}
sbSql.Append(" WHERE ");
if (option.WhereProperties == null || !option.WhereProperties.Any())
{
string primaryKeyName = GetPrimaryKeyName(props);
sbSql.Append(primaryKeyName);
sbSql.Append("=");
sbSql.Append("@");
sbSql.Append(primaryKeyName);
}
else
{
var whereProps = SplitWherePropertiesByOption(option, GetColumnProperties(type)).ToArray();
var whereValues = CalculteValues(parameter, whereProps);
whereProps = SelectProps(whereProps, whereValues, option);
for (var i = 0; i < whereProps.Length; i++)
{
if (i > 0)
{
sbSql.Append(" AND ");
}
sbSql.AppendFormat("{0}=@{0}", GetColumnName(whereProps[i]));
}
}
commd.CommandText = sbSql.ToString();
sbSql.Clear();
foreach (var prop in props)
{
var val = values[prop.Name];
if (val == null)
{
val = DBNull.Value;
}
commd.Parameters.Add(CreateIDataParameter(TagName + GetColumnName(prop), val, ParameterDirection.Input));
}
return(commd);
}
/// <summary>
/// 生成insert语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="parameter"></param>
/// <param name="option"></param>
/// <returns></returns>
public IDbCommand CreateInsertCommand <T>(T parameter, CreateCommandOption option = null)
{
if (option == null)
{
option = new CreateCommandOption();
}
var commd = Connection.CreateCommand();
commd.Transaction = Transaction;
commd.CommandTimeout = DefaultCommandTimeoutBySeconds;
var type = typeof(T);
var props = SplitInsertPropertiesByOption(option, GetColumnProperties(type));
var values = CalculteValues(parameter, props);
string tableName = GetTableName(type);
var selectedTable = props.Where(a => _Tablename.Equals(a.Name)).FirstOrDefault();
if (selectedTable != null)
{
var oVal = values[selectedTable.Name];
if (oVal != null)
{
tableName = oVal.ToString();
}
}
props = SelectProps(props, values, option);
var columns = GetParameterColumnNames(props, values, option.IgnorePrimaryKey);
var sbSql = new StringBuilder();
sbSql.Append("INSERT INTO ");
sbSql.Append(tableName);
sbSql.Append("(");
for (int i = 0; i < columns.Length; i++)
{
if (i > 0)
{
sbSql.Append(",");
}
sbSql.Append(columns[i]);
}
sbSql.Append(")");
sbSql.Append(" VALUES(");
for (int i = 0; i < columns.Length; i++)
{
if (i > 0)
{
sbSql.Append(",");
}
sbSql.Append(TagName);
sbSql.Append(columns[i]);
}
sbSql.Append(")");
commd.CommandText = sbSql.ToString();
sbSql.Clear();
foreach (var prop in props)
{
var val = values[prop.Name];
if (val == null)
{
val = DBNull.Value;
}
commd.Parameters.Add(CreateIDataParameter(TagName + GetColumnName(prop), val, ParameterDirection.Input));
}
return(commd);
}