//SQL命令 获取删除命令 public static SqlCommand GetDeleteCommand(IBaseEntity info) { if (info == null) { return(null); } Type tp = info.GetType(); string tbName; var attrs = tp.GetCustomAttributes(typeof(EntityTableNameAttribute), false); if (attrs.Length > 0) { tbName = ((EntityTableNameAttribute)attrs[0]).TableName; } else { return(null); } var comm = new SqlCommand { CommandType = CommandType.Text }; var sb = new StringBuilder(); var memberAccessor = new DynamicMethodMemberAccessor(); sb.AppendFormat("DELETE FROM [{0}] WHERE ", tbName); foreach (PropertyInfo prop in tp.GetProperties()) { var eca = Attribute.GetCustomAttribute(prop, typeof(EntityColumnAttribute)) as EntityColumnAttribute; if (eca != null && eca.Key) { string paramName = string.Format("@{0}", prop.Name); var param = new SqlParameter(paramName, eca.DataType) { Value = memberAccessor.GetValue(info, prop.Name) }; comm.Parameters.Add(param); sb.AppendFormat("[{0}] = {1}", eca.Field, paramName); break; } } comm.CommandText = sb.ToString(); return(comm); }
//从数据读取器获取实例 public static void GetEntityFromDataReader(SqlDataReader dr, ref IBaseEntity entity, bool getForeigns, bool getChldren) { var memberAccessor = new DynamicMethodMemberAccessor(); var keyName = "Id"; foreach (PropertyInfo property in entity.GetType().GetProperties()) { var mde = Attribute.GetCustomAttribute(property, typeof(EntityColumnAttribute)) as EntityColumnAttribute; if (mde != null) { if (mde.Key) { keyName = property.Name; } if (getForeigns && mde.Foreign && !string.IsNullOrEmpty(mde.ForeignKey)) { var fEntity = Activator.CreateInstance(property.PropertyType) as IBaseEntity; if (fEntity != null) { GetSingleEntity(memberAccessor.GetValue(entity, mde.ForeignKey), ref fEntity); memberAccessor.SetValue(entity, property.Name, fEntity); } } else if (getChldren && mde.Children && !string.IsNullOrEmpty(mde.ChildKey)) { var children = Activator.CreateInstance(property.PropertyType) as IList; SearchByField(mde.ChildKey, memberAccessor.GetValue(entity, keyName), ref children); memberAccessor.SetValue(entity, property.Name, children); } else { if (!string.IsNullOrEmpty(mde.Field) && !(dr[mde.Field] is DBNull)) { memberAccessor.SetValue(entity, property.Name, dr[mde.Field]); } } } } }
//SQL命令 获取更新命令 public static SqlCommand GetUpdateCommand(IBaseEntity info) { if (info == null) { return(null); } Type tp = info.GetType(); string tbName; var attrs = tp.GetCustomAttributes(typeof(EntityTableNameAttribute), false); if (attrs.Length > 0) { tbName = ((EntityTableNameAttribute)attrs[0]).TableName; } else { return(null); } var comm = new SqlCommand { CommandType = CommandType.Text }; var sb = new StringBuilder(); var sbW = new StringBuilder(); var memberAccessor = new DynamicMethodMemberAccessor(); sb.AppendFormat("UPDATE [{0}] SET ", tbName); sbW.Append(" WHERE 1 = 2"); foreach (PropertyInfo prop in tp.GetProperties()) { var eca = Attribute.GetCustomAttribute(prop, typeof(EntityColumnAttribute)) as EntityColumnAttribute; if (eca != null && !eca.Foreign && !eca.Children) { string paramName = string.Format("@{0}", prop.Name); var param = new SqlParameter(paramName, eca.DataType) { Value = memberAccessor.GetValue(info, prop.Name) }; if (param.Value == null && (eca.DataType == DbType.String || eca.DataType == DbType.AnsiString)) { param.Value = DBNull.Value; } comm.Parameters.Add(param); if (eca.Key || eca.Unique) { sbW.AppendFormat(" OR [{0}] = {1}", eca.Field, paramName); } else { sb.AppendFormat("[{0}] = {1},", eca.Field, paramName); } } } sb.Remove(sb.Length - 1, 1).Append(sbW); comm.CommandText = sb.ToString(); return(comm); }
//SQL命令 获取插入命令 public static SqlCommand GetInsertCommand(IBaseEntity info) { if (info == null) { return(null); } Type tp = info.GetType(); string tbName; var attrs = tp.GetCustomAttributes(typeof(EntityTableNameAttribute), false); if (attrs.Length > 0) { tbName = ((EntityTableNameAttribute)attrs[0]).TableName; } else { return(null); } var comm = new SqlCommand { CommandType = CommandType.Text }; var sb = new StringBuilder(); var sbV = new StringBuilder(); var memberAccessor = new DynamicMethodMemberAccessor(); sb.AppendFormat("INSERT INTO [{0}] (", tbName); sbV.Append(" VALUES ("); foreach (PropertyInfo prop in tp.GetProperties()) { var eca = Attribute.GetCustomAttribute(prop, typeof(EntityColumnAttribute)) as EntityColumnAttribute; if (eca != null && !eca.Key && !eca.Foreign && !eca.Children) { sb.AppendFormat("[{0}],", eca.Field); string paramName = string.Format("@{0}", prop.Name); sbV.AppendFormat("{0},", paramName); var param = new SqlParameter(paramName, eca.DataType); param.Value = memberAccessor.GetValue(info, prop.Name); if (param.Value == null && (eca.DataType == DbType.String || eca.DataType == DbType.AnsiString)) { param.Value = DBNull.Value; } comm.Parameters.Add(param); } } sbV.Remove(sbV.Length - 1, 1).Append(");SELECT @ScopeIdentity = SCOPE_IDENTITY();"); var idParam = new SqlParameter("@ScopeIdentity", SqlDbType.Int) { Direction = ParameterDirection.Output }; comm.Parameters.Add(idParam); sb.Remove(sb.Length - 1, 1).Append(")").Append(sbV); comm.CommandText = sb.ToString(); return(comm); }