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); }