/// <summary> /// 执行更新修改操作 /// </summary> /// <param name="table">要修改的表</param> /// <param name="obj">实体对象</param> /// <returns>受影响行数</returns> public static int UpdateWithModify(string table, object obj) { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "UPDATE " + table + " SET "; string[] pas = columnParas.Split(','); string[] names = columnNames.Split(','); List <SqlParameter> paras = new List <SqlParameter>();//参数集合 for (int i = 0; i < pas.Length; i++) { if (i != 0) { strSql += names[i] + "=" + pas[i] + ","; } object v = BuildSql.GetProValue(obj, pas[i]); //返回属性类型的值 SqlParameter pa = new SqlParameter(pas[i], v); //创建集合对象元素 paras.Add(pa); //加入集合 } strSql = strSql.Substring(0, strSql.Length - 1); strSql += " where " + names[0] + "=" + pas[0]; return(SqlHelper.ExcuteNonQuery(strSql, paras.ToArray())); }
/// <summary> /// 执行指定列值更新操作 /// </summary> /// <param name="table">数据库表</param> /// <param name="ids">操作的ID</param> /// <param name="sqlField">数据库字段</param> /// <param name="sqlValue">设置的值</param> /// <returns>返回受影响的行数</returns> public static int UpdateFieldValue(string table, string ids, string sqlField, string sqlValue) { string strSql = string.Empty; string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string[] names = columnNames.Split(','); strSql = "update " + table + " set " + sqlField + "=" + sqlValue + " where " + names[0] + " in(" + ids + ") "; return(SqlHelper.ExcuteNonQuery(strSql)); }
/// <summary> /// 执行硬删除操作,此操作将不可恢复 /// </summary> /// <param name="table">数据库表</param> /// <param name="ids">要删除的id</param> /// <returns>返回受影响的行数</returns> public static int Del(string table, string ids) { string strSql = string.Empty; string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string[] names = columnNames.Split(','); strSql = "DELETE FROM " + table + " WHERE " + names[0] + " IN (" + ids + ")"; return(SqlHelper.ExcuteNonQuery(strSql)); }
/// <summary> /// 加载实体对象 /// </summary> /// <param name="dr">行数据</param> /// <param name="user">对象</param> /// <param name="table">表</param> public static void LoadModelData(DataRow dr, object obj, string table) { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string[] names = columnNames.Split(','); for (int i = 0; i < names.Length; i++) { SetProValue(FirstUpper(names[i]), dr[names[i]].ToString(), obj); } }
/// <summary> /// 返回分页返回数据表(基于AspPager控件) /// </summary> /// <param name="lastRowIndex">上一页最后一行下标</param> /// <param name="pageSize">页容量</param> /// <param name="table">数据表</param> /// <param name="value">条件值</param> /// <param name="sqlID">对应值的数据列名</param> /// <param name="sort">排序方式</param> /// <param name="sortID">排序列名</param> /// <returns></returns> public static DataTable GetDataTableForSamplePager(int lastRowIndex, int pageSize, string table, string value, string sqlID, string sort = "desc", string sortID = "") { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = string.Empty; if (sortID == "") { sortID = sqlID; } strSql = "select * from " + table + " where " + sqlID + "=" + value + " order by " + sortID + " " + sort; return(SqlHelper.GetTable(strSql, lastRowIndex, pageSize, table)); }
/// <summary> /// 根据指定条件(指定列或值)返回实体对象 /// </summary> /// <param name="table">数据表</param> /// <param name="name">名称</param> /// <param name="obj">Mode对象</param> /// <param name="sqlName">数据库字段名称</param> /// <returns></returns> public static object GetModelByWhere(string table, object obj, string sqlName, string value) { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "SELECT " + columnNames + " FROM " + table + " WHERE " + sqlName + "='" + value + "'"; DataTable dt = SqlHelper.GetTable(strSql); if (dt.Rows.Count > 0) { LoadModelData(dt.Rows[0], obj, table); } return(obj); }
/// <summary> /// 根据ID返回实体对象 /// </summary> /// <param name="table">数据表</param> /// <param name="id">ID</param> /// <param name="obj">Mode对象</param> /// <returns></returns> public static object GetModelById(string table, string id, object obj) { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "SELECT " + columnNames + " FROM " + table + " WHERE " + columnNames.Split(',')[0] + "=" + id; DataTable dt = SqlHelper.GetTable(strSql); if (dt.Rows.Count > 0) { LoadModelData(dt.Rows[0], obj, table); } return(obj); }
/// <summary> /// /// </summary> /// <typeparam name="T">返回数据的类型</typeparam> /// <param name="table">数据表对象</param> /// <param name="paseSize">页容量</param> /// <param name="currentPage">当前页数</param> /// <param name="sqlWhere">分页条件</param> /// <returns></returns> public static List <T> SimplePager <T>(string table, string paseSize, string currentPage, string sqlWhere = "") where T : new() { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "select top " + paseSize + " * from ( select ROW_NUMBER() OVER (order by " + columnNames.Split(',')[0] + " desc) as RowNumber,* from " + table + " " + sqlWhere + " " + ") A where RowNumber>" + paseSize + "*(" + currentPage + "-1) "; DataTable dt = SqlHelper.GetTable(strSql); List <T> list = new List <T>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { T obs = new T(); BuildSql.LoadModelData(dr, obs, table); list.Add(obs); } } return(list); }
/// <summary> /// 返回泛型集合对象 /// </summary> /// <typeparam name="T">返回数据的类型</typeparam> /// <param name="table">数据表</param> /// <param name="desc">排序方法</param> /// <param name="sqlWhere">条件,默认为空</param> /// <returns></returns> public static List <T> GetListByWhere <T>(string table, string desc = "desc", string sqlWhere = "") where T : new() { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "SELECT " + columnNames + " FROM " + table + " " + sqlWhere + " " + " order by " + columnNames.Split(',')[0] + " " + desc; DataTable dt = SqlHelper.GetTable(strSql); List <T> list = new List <T>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { T obs = new T(); BuildSql.LoadModelData(dr, obs, table); list.Add(obs); } } return(list); }
/// <summary> /// 返回泛型集合对象 /// </summary> /// <param name="table">数据表</param> /// <param name="obj">实体对象</param> /// <param name="desc">排序方式</param> /// <param name="sqlWhere">条件,默认为空 </param> /// <returns>List<object>集合</object></objct></returns> // public List<MODEL.User> GetAllList() //{ // MODEL.User model = new MODEL.User(); // List<object> objs = BuildSql.GetListByWhere("dbo.[User]", model); // List<MODEL.User> list = new List<MODEL.User>(); // foreach (object item in objs) // { // if (item is MODEL.User) // { // MODEL.User mol = (MODEL.User)item; // list.Add(mol); // } // } // return list; //} public static List <object> GetListByWhere(string table, object obj, string desc = "desc", string sqlWhere = "") { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "SELECT " + columnNames + " FROM " + table + " " + sqlWhere + " " + " order by " + columnNames.Split(',')[0] + " " + desc; DataTable dt = SqlHelper.GetTable(strSql); Type t = obj.GetType(); List <object> list = new List <object>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { object obs = Activator.CreateInstance(t); BuildSql.LoadModelData(dr, obs, table); list.Add(obs); } } return(list); }
/// <summary> /// 执行插入操作 /// </summary> /// <param name="table">要插入的表</param> /// <param name="obj">实体对象</param> /// <param name="newID">返回插入新的ID</param> /// <returns>受影响的行数</returns> public static int Add(string table, object obj, out int newID) { newID = 0; int res; string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, false); string strSql = "INSERT " + table + "(" + columnNames + ") values(" + columnParas + ");select @@IDENTITY"; string[] pas = columnParas.Split(','); List <SqlParameter> paras = new List <SqlParameter>();//参数集合 for (int i = 0; i < pas.Length; i++) { object v = BuildSql.GetProValue(obj, pas[i]); //返回属性类型的值 SqlParameter pa = new SqlParameter(pas[i], v); //创建集合对象元素 paras.Add(pa); //加入集合 } res = SqlHelper.ExcuteScalar(strSql, paras.ToArray()); newID = res; return(res); }