/// <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));
        }
Пример #8
0
        /// <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);
        }
Пример #9
0
        /// <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);
        }
Пример #12
0
        /// <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);
        }
Пример #13
0
        /// <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));
 }