/// <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="obj">Model对象</param>
/// <param name="para">参数命令</param>
/// <returns></returns>
public static object GetProValue(object obj, string para)
{
Type Ts = obj.GetType();
string temp = para.Substring(para.IndexOf("@") + 1); //获得属性名称
string fieldValue = BuildSql.GetProValue(BuildSql.FirstUpper(temp), obj); //获的属性值
object v = Convert.ChangeType(fieldValue, Ts.GetProperty(BuildSql.FirstUpper(temp)).PropertyType); //转化为属性的类型
return(v);
}
/// <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="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="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>
/// 返回指定时间段的总收入
/// </summary>
/// <param name="dt1"></param>
/// <param name="dt2"></param>
/// <returns></returns>
public MODEL.Cost GetCountForPay(DateTime dt1, DateTime dt2)
{
string sql = "SELECT * from " + strTable + " WHERE cDate BETWEEN '" + dt1.ToString("yyyy-MM-dd") + "' AND '" + dt2.ToString("yyyy-MM-dd") + "'";
System.Data.DataTable dt = SqlHelper.GetTable(sql);
MODEL.Cost model = new MODEL.Cost();
if (dt.Rows.Count > 0)
{
BuildSql.LoadModelData(dt.Rows[0], model, strTable);
}
return(model);
}
/// <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="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>
/// <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>
/// <param name="table">数据表</param>
/// <returns>字段名称</returns>
public static string BuildModelField(string table)
{
string _model = "";
string txt1 = "";
string txt2 = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("select * from " + table, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
string sny = dr.GetName(i);
txt1 += "private " + dr.GetFieldType(i).ToString() + " " + sny + ";\r\n";
txt2 += "public " + dr.GetFieldType(i).ToString() + " " + BuildSql.FirstUpper(sny) + "\r\n{\r\n\t get \r\n\t{\r\n\t\t return this." + sny + ";\r\n\t}\r\n\t set \r\n\t{\r\n\t\t this." + sny + "=value;\r\n\t}\r\n}\r\n";
}
txt1 += "\r\n";
_model += txt1 + txt2;
return(_model);
}
/// <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);
}
/// <summary>
/// 执行更新操作
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateWithModify(MODEL.Good model)
{
return(BuildSql.UpdateWithModify(strSql, model));
}
/// <summary>
/// 执行删除操作
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int Del(string ids)
{
return(BuildSql.Del(strSql, ids));
}
/// <summary>
/// 执行增加操作
/// </summary>
/// <param name="model"></param>
/// <param name="newID"></param>
/// <returns></returns>
public int Add(MODEL.Good model, out int newID)
{
return(BuildSql.Add(strSql, model, out newID));
}
/// <summary>
/// 根据ID返回实体对象
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public MODEL.Feedback GetModelByID(string id)
{
return(BuildSql.GetModelById <MODEL.Feedback>(strTable, id));
}
/// <summary>
/// 根据简单分页获取数据
/// </summary>
/// <param name="pageSize"></param>
/// <param name="currentPage"></param>
/// <param name="sqlWhere"></param>
/// <returns></returns>
public List <MODEL.Feedback> GetSimplePageList(string pageSize, string currentPage, string sqlWhere = "")
{
return(BuildSql.SimplePager <MODEL.Feedback>(strTable, pageSize, currentPage, sqlWhere));
}
/// <summary>
/// 根据页容量条件返回总页数
/// </summary>
/// <param name="pageSize"></param>
/// <param name="sqlWhere"></param>
/// <returns></returns>
public int GetPageCount(int pageSize, string sqlWhere = "")
{
return(BuildSql.GetPageCount(strSql, pageSize, sqlWhere));
}
/// <summary>
/// 获取总条数
/// </summary>
/// <returns></returns>
public int GetCount()
{
return(BuildSql.GetCount(strTable));
}
/// <summary>
/// 获取留言的楼数
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int GetCountForWhere(string id)
{
return(BuildSql.GetCount(strTable, " where fID<" + id));
}
/// <summary>
/// 根据ID查询实体对象
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public MODEL.User GetModelByID(string id)
{
return(BuildSql.GetModelById <MODEL.User>(strTable, id));
}
/// <summary>
/// 根据简单分页返回泛型集合
/// </summary>
/// <param name="pageSize">页容量</param>
/// <param name="currentPage">当前页</param>
/// <param name="sqlWhere">条件</param>
/// <returns></returns>
public List <MODEL.User> GetListForSimplePager(string pageSize, string currentPage, string sqlWhere = "")
{
return(BuildSql.SimplePager <MODEL.User>(strTable, pageSize, currentPage, sqlWhere));
}
/// <summary>
/// 根据指定条件返回总记录数
/// </summary>
/// <param name="sqlWhere"></param>
/// <returns></returns>
public int GetCountForWhere(string sqlWhere = "")
{
return(BuildSql.GetCount(strTable, sqlWhere));
}
/// <summary>
/// 根据ID查询实体对象
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public MODEL.Good GetModelByID(string id)
{
return(BuildSql.GetModelById <MODEL.Good>(strSql, id));
}
/// <summary>
/// 根据简单分页获取数据
/// </summary>
/// <param name="pageSize"></param>
/// <param name="currentPage"></param>
/// <param name="sqlWhere"></param>
/// <returns></returns>
public List <MODEL.Good> GetSimplePageList(string pageSize, string currentPage, string sqlWhere = "")
{
return(BuildSql.SimplePager <MODEL.Good>(strSql, pageSize, currentPage, sqlWhere));
}
/// <summary>
/// 执行增加操作
/// </summary>
/// <param name="model"></param>
/// <param name="newID"></param>
/// <returns></returns>
public int Add(MODEL.Feedback model, out int newID)
{
return(BuildSql.Add(strTable, model, out newID));
}
/// <summary>
/// 获取总条数
/// </summary>
/// <returns></returns>
public int GetCount(string sqlWhere = "")
{
return(BuildSql.GetCount(strSql, sqlWhere));
}
/// <summary>
/// 执行更新操作
/// </summary>
/// <param name="model">要修改的实体对象</param>
/// <returns></returns>
public int UpdateWithModify(MODEL.Feedback model)
{
return(BuildSql.UpdateWithModify(strTable, model));
}