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