コード例 #1
0
        /// <summary>
        /// 获取更新语句,根据字典.条件为字典格式,有些条件为时间段,无法用实体类表示.关键字:数据库字段名__q(时间起),数据库字段名__z(时间止),数据库字段名__or(条件为or),数据库字段名__like(查询为like)
        /// </summary>
        /// <param name="columnDic">列数据字典,如果列为日期格式,需要增加后缀__date</param>
        /// <param name="conditionDic">条件字典</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public string GetUpdateSql(Dictionary <string, object> columnDic, Dictionary <string, object> conditionDic, string tableName = "")
        {
            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);             //获取表名

                string strSetValue  = "";                                                //更新的值
                string strCondition = InternalBase <T> .GetConditionByDIC(conditionDic); //条件

                //获取更新的值
                foreach (var item in columnDic)
                {
                    string key   = item.Key;
                    object value = item.Value;
                    if (item.Key.ToLower().Contains("__date"))
                    {
                        strSetValue += string.Format(",{0}={1}", key, "to_date('" + value + "','yyyy/mm/dd hh24:mi:ss')");
                    }
                    else
                    {
                        strSetValue += string.Format(",{0}='{1}'", key, value);
                    }
                }
                return(string.Format("UPDATE {0} SET {1} {2}", strTable, strSetValue.Substring(1), strCondition));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return("-1");
            }
        }
コード例 #2
0
        /// <summary>
        /// 根据字典,返回分页需要是sql语句
        /// </summary>
        /// <param name="dataSql">返回查询datatable的sql</param>
        /// <param name="countSql">返回查询数量的sql</param>
        /// <param name="dic">保存条件的字典,key为字段名称,关键字:数据库字段名__q(时间起),数据库字段名__z(时间止),数据库字段名__or(条件为or),数据库字段名__like(查询为like)</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">每页显示的数量</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public void GetSelectPageSql(out string dataSql, out string countSql, Dictionary <string, object> dic, String order, int pageCurrent, int pageSize = 10, string tableName = "", string columnName = "*")
        {
            dataSql  = string.Empty;
            countSql = string.Empty;
            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);

                string strCondition = InternalBase <T> .GetConditionByDIC(dic);

                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段.

                int number;
                InternalBase <T> .GetPageNumber(pageCurrent, pageSize, out number); //获取分页信息

                string tt, ttt;
                InternalBase <T> .GetColumnName(columnName, out tt, out ttt);

                dataSql  = "SELECT " + columnName + " FROM (SELECT " + ttt + ", ROWNUM AS rowno FROM (  SELECT " + tt + " FROM " + strTable + " t  " + strCondition + strOrder + ") tt  WHERE ROWNUM <= " + (number + pageSize) + ") table_alias WHERE table_alias.rowno >= " + (number + 1);
                countSql = "select count (*) from " + strTable + strCondition;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, dataSql + "\r\n" + countSql);
            }
        }
コード例 #3
0
        /// <summary>
        /// 获取插入语句,根据字典.
        /// </summary>
        /// <param name="columnDic">列数据字典,如果列为日期格式,需要增加后缀__date</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public string GetInsertSql(Dictionary <string, object> columnDic, string tableName = "")
        {
            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName); //获取表名

                string strColumn = "";                                       //列
                string strValue  = "";                                       //值
                foreach (var item in columnDic)
                {
                    string key   = item.Key;
                    object value = item.Value;
                    strColumn += "," + key;//列名
                    if (item.Key.ToLower().Contains("__date"))
                    {
                        strValue += ",to_date('" + value + "','yyyy/mm/dd hh24:mi:ss')";
                    }
                    else
                    {
                        strValue += ",'" + value + "'";
                    }
                }
                return(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", strTable, strColumn.Substring(1), strValue.Substring(1)));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return("-1");
            }
        }
コード例 #4
0
        /// <summary>
        /// 根据标识列的值返回整条数据
        /// </summary>
        /// <param name="IdentityValue">标识列的值</param>
        /// <returns></returns>
        internal DataTable SelectByIdentity(string IdentityValue)
        {
            string strTable     = "";
            string strCondition = "";

            try
            {
                //获取表名
                strTable = InternalBase <T> .GetTableName();

                //获取条件
                PropertyInfo[] pi = typeof(T).GetProperties();
                foreach (var item in pi)
                {
                    var temp = InternalBase <T> .GetColumnAttribute(item);

                    if (temp.Identity)
                    {
                        strCondition = item.Name + "='" + IdentityValue + "'";
                        break;
                    }
                }
                string sql = string.Format("select * from {0} where {1}", strTable, strCondition);
                return(dbl.ExecuteQuery(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return(null);
            }
        }
コード例 #5
0
        /// <summary>
        /// 查询显示分页后的列表数据.根据字典生成条件,用于时间条件为时间段时.
        /// </summary>
        /// <param name="dic">保存条件的字典,key为字段名称,关键字:数据库字段名__q(时间起),数据库字段名__z(时间止),数据库字段名__or(条件为or),数据库字段名__like(查询为like)</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">每页显示的数量</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public DataTable SelectPage(Dictionary <string, object> dic, String order, int pageCurrent, int pageSize = 10, string tableName = "", string columnName = "*")
        {
            string sql = string.Empty;

            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);

                string strCondition = InternalBase <T> .GetConditionByDIC(dic);

                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段.

                int number;
                InternalBase <T> .GetPageNumber(pageCurrent, pageSize, out number); //获取分页信息

                string tt, ttt;
                InternalBase <T> .GetColumnName(columnName, out tt, out ttt);

                sql = "SELECT " + columnName + " FROM (SELECT " + ttt + ", ROWNUM AS rowno FROM (  SELECT " + tt + " FROM " + strTable + " t  " + strCondition + strOrder + ") tt  WHERE ROWNUM <= " + (number + pageSize) + ") table_alias WHERE table_alias.rowno >= " + (number + 1);
                return(dbl.ExecuteQuery(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql);
                return(null);
            }
        }
コード例 #6
0
        /// <summary>
        /// 获取唯一值
        /// </summary>
        /// <param name="myCode">实体类的条件</param>
        /// <param name="columnName">获取唯一值的列名,默认codevalue</param>
        /// <returns></returns>
        public object GetValue(CODES myCode, string columnName = "codevalue")
        {
            string strCondition = InternalBase <CODES> .GetParamForSelectAndDelete(myCode);

            string    sql = "select " + columnName + " from CODES " + strCondition;
            DataTable dt  = this.SelectBySQL(sql);

            if (dt != null && dt.Rows.Count > 0)
            {
                return(dt.Rows[0][columnName]);
            }
            return("");
        }
コード例 #7
0
        /// <summary>
        /// 获取delete语句.根据条件字典,用于有时间段的条件
        /// </summary>
        /// <param name="dic">条件字典</param>
        /// <param name="tableName">表名 </param>
        /// <returns></returns>
        public string GetDeleteSql(Dictionary <string, object> dic, string tableName = "")
        {
            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);    //获取表名

                string strCondition = InternalBase <T> .GetConditionByDIC(dic); //获取条件

                return(string.Format("delete from {0} {1}", strTable, strCondition));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return("-1");
            }
        }
コード例 #8
0
        /// <summary>
        /// 获取最小值
        /// </summary>
        /// <param name="columnName">最大值的列名</param>
        /// <param name="t">实体类,传入的值都将当做查询条件参数</param>
        /// <returns></returns>
        internal int GetMin(string columnName, T t = null)
        {
            try
            {
                string strTable = InternalBase <T> .GetTableName();

                string strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);

                string sql = "select min(" + columnName + ") from " + strTable + strCondition;
                return(SelectScalar(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return(-1);
            }
        }
コード例 #9
0
        /// <summary>
        /// 根据列以及列的值,返回对应的一条数据
        /// </summary>
        /// <param name="ColumnName">列名</param>
        /// <param name="ColumnValue">列值</param>
        /// <returns></returns>
        internal DataTable SelectByColumn(string ColumnName, string ColumnValue)
        {
            string strTable = InternalBase <T> .GetTableName();

            string strCondition = "";

            try
            {
                strCondition = ColumnName + "='" + ColumnValue + "'";
                string sql = string.Format("select * from {0} where {1}", strTable, strCondition); //$"select * from {strTable} where {strCondition}";
                return(dbl.ExecuteQuery(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return(null);
            }
        }
コード例 #10
0
        /// <summary>
        /// 获取delete语句
        /// </summary>
        /// <param name="t">实体类型</param>
        /// <returns></returns>
        public string GetDeleteSql(T t)
        {
            try
            {
                string strTable     = "";
                string strCondition = "";
                //获取表名
                strTable = InternalBase <T> .GetTableName();

                //获取条件
                strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);

                return(string.Format("delete from {0} {1}", strTable, strCondition));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return("-1");
            }
        }
コード例 #11
0
        /// <summary>
        /// 查询返回所有数据,根据实体对象生成条件.
        /// </summary>
        /// <param name="t">实体类,将查询条件赋给实体类的属性</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public DataTable Select(T t, string order = "", string columnName = "*")
        {
            string sql = string.Empty;

            try
            {
                string strTable = InternalBase <T> .GetTableName();

                string strCondition = InternalBase <T> .GetParamForSelectAndDelete(t);

                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段

                sql = string.Format("select {0} from {1} {2} {3}", columnName, strTable, strCondition, strOrder);
                return(dbl.ExecuteQuery(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql);
                return(null);
            }
        }
コード例 #12
0
        /// <summary>
        /// 获取查询语句
        /// </summary>
        /// <param name="strTable">表名</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">每页显示的数量</param>
        /// <param name="strOrder">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="strCondition">条件,需要填写where</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        internal string GetSelectSql(string strTable, int pageCurrent, int pageSize, string strOrder, string strCondition, string columnName)
        {
            string sql = string.Empty;

            try
            {
                int number;
                InternalBase <T> .GetPageNumber(pageCurrent, pageSize, out number);//获取分页信息

                string tt, ttt;
                InternalBase <T> .GetColumnName(columnName, out tt, out ttt);

                sql = "SELECT " + columnName + " FROM (SELECT " + ttt + ", ROWNUM AS rowno FROM (  SELECT " + tt + " FROM " + strTable + " t  " + strCondition + strOrder + ") tt  WHERE ROWNUM <= " + (number + pageSize) + ") table_alias WHERE table_alias.rowno >= " + (number + 1);
                return(sql);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex + "sql:" + sql);
                throw;
            }
        }
コード例 #13
0
        /// <summary>
        /// 查询返回所有数据,根据字典生成条件,用于时间条件为时间段时.
        /// </summary>
        /// <param name="dic">保存条件的字典,key为字段名称,关键字:数据库字段名__q(时间起),数据库字段名__z(时间止),数据库字段名__or(条件为or),数据库字段名__like(查询为like)</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public DataTable Select(Dictionary <string, object> dic, string order = "", string tableName = "", string columnName = "*")
        {
            string sql = string.Empty;

            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);

                string strCondition = InternalBase <T> .GetConditionByDIC(dic);

                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段

                sql = string.Format("select {0} from {1} {2} {3}", columnName, strTable, strCondition, strOrder);
                return(dbl.ExecuteQuery(sql));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql);
                return(null);
            }
        }
コード例 #14
0
        /// <summary>
        /// 获取insert语句
        /// </summary>
        /// <param name="t">实体类型</param>
        /// <returns></returns>
        public string GetInsertSql(T t)
        {
            string strTable  = ""; //表名
            string strColumn = ""; //列
            string strValue  = ""; //值

            try
            {
                //获取表名
                strTable = InternalBase <T> .GetTableName();

                //获取列名和值
                PropertyInfo[] pi = t.GetType().GetProperties();
                foreach (var item in pi)
                {
                    string key   = item.Name;
                    object value = item.GetValue(t, null);
                    if (value == null)
                    {
                        continue;
                    }
                    strColumn += "," + key;//列名
                    //值
                    if (value.GetType() == typeof(DateTime))
                    {
                        strValue += ",to_date('" + value + "','yyyy/mm/dd hh24:mi:ss')";
                    }
                    else
                    {
                        strValue += ",'" + value + "'";
                    }
                }
                return(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", strTable, strColumn.Substring(1), strValue.Substring(1)));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return("-1");
            }
        }
コード例 #15
0
        /// <summary>
        /// 查询显示分页数据.返回json字符串(包含列表数据和总数据量数据,配合easyui使用).根据字典生成条件,用于时间条件为时间段时.
        /// </summary>
        /// <param name="dic">保存条件的字典,key为字段名称,关键字:数据库字段名__q(时间起),数据库字段名__z(时间止),数据库字段名__or(条件为or),数据库字段名__like(查询为like)</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">每页显示的数量</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public string SelectPageToJSON(Dictionary <string, object> dic, String order, int pageCurrent, int pageSize = 10, string tableName = "", string columnName = "*")
        {
            string sql      = string.Empty;
            string countSql = string.Empty;

            try
            {
                string strTable = InternalBase <T> .GetTableName(tableName);

                string strCondition = InternalBase <T> .GetConditionByDIC(dic);

                DataTable dt = SelectPage(dic, order, pageCurrent, pageSize, tableName, columnName);// dbl.ExecuteQuery(sql);
                countSql = "select count (*) from " + strTable + strCondition;
                var pageCount = dbl.GetSingle(countSql);
                return(JSONHelper.ObjectToJson(new { total = pageCount, rows = dt }));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql + "\r\n" + countSql);
                return("");
            }
        }
コード例 #16
0
        /// <summary>
        /// 查询显示分页数据.返回json字符串(包含列表数据和总数据量数据,配合easyui使用),根据实体对象生成条件.
        /// </summary>
        /// <param name="strTable">表名</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">每页显示的数量</param>
        /// <param name="order">排序字段:如果不需要排序可不传入数据,也可以传入null或者"";如果需要排序则写入order by之后的内容</param>
        /// <param name="strCondition">条件,需要填写where</param>
        /// <param name="columnName">列名,默认为*,查询所有</param>
        /// <returns></returns>
        public string SelectPageToJSONBySQL(string strTable, int pageCurrent, int pageSize = 10, string order = "", String strCondition = "", string columnName = "*")
        {
            string sql      = "";
            string countSql = "";

            try
            {
                string strOrder = InternalBase <T> .AddOrder(order);//获取排序字段.

                sql = this.GetSelectSql(strTable, pageCurrent, pageSize, strOrder, strCondition, columnName);
                int       number = (pageCurrent - 1) * pageSize;
                DataTable dt     = this.SelectBySQL(sql);
                countSql = "select count (*) from " + strTable + strCondition;
                var pageCount = dbl.GetSingle(countSql);
                return(JSONHelper.ObjectToJson(new { total = pageCount, rows = dt }));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex, sql + "\r\n" + countSql);
                return("");
            }
        }
コード例 #17
0
        /// <summary>
        /// 获取update语句
        /// </summary>
        /// <param name="t">实体类型</param>
        /// <param name="conditionName">条件字段,默认为ID,如果有多个条件,可以用","隔开</param>
        /// <returns></returns>
        public string GetUpdateSql(T t, string conditionName = "ID")
        {
            try
            {
                string strTable     = ""; //表名
                string strSetValue  = ""; //更新的值
                string strCondition = ""; //条件

                //获取表名
                strTable = InternalBase <T> .GetTableName();

                PropertyInfo[] pi = t.GetType().GetProperties();
                bool           isCondition;//此属性是否为条件
                foreach (var item in pi)
                {
                    //todo:想使用特性,来判断是否把属性赋空值,但是没好用.目前的问题就是,无法把非string类型的值修改为空
                    //string columnType = string.Empty;
                    //object[] propertyAttrs = item.GetCustomAttributes(false);
                    //for (int i = 0; i < propertyAttrs.Length; i++)
                    //{
                    //    object propertyAttr = propertyAttrs[i];
                    //    //获取Column自定义属性中配置的type值(表的列名)
                    //    columnType = GetColumnType(propertyAttr);
                    //}

                    isCondition = false;
                    string key   = item.Name;
                    object value = item.GetValue(t, null);
                    if (value == null)//&& string.IsNullOrEmpty(columnType)
                    {
                        continue;
                    }
                    string[] arrayCondition = conditionName.Split(',');
                    foreach (var condition in arrayCondition)
                    {
                        if (key.ToUpper().Equals(condition.ToUpper()))
                        {
                            strCondition += string.Format(" and {0}='{1}'", key, value);
                            isCondition   = true;
                            break;
                        }
                    }
                    if (isCondition)
                    {
                        continue;
                    }
                    else
                    {
                        if (value.GetType() == typeof(DateTime))
                        {
                            strSetValue += string.Format(",{0}={1}", key, "to_date('" + value + "','yyyy/mm/dd hh24:mi:ss')");
                        }
                        else
                        {
                            strSetValue += string.Format(",{0}='{1}'", key, value);
                        }
                    }
                }
                if (strCondition.Length > 0)
                {
                    strCondition = InternalBase <T> .IsKeepAndWhere(strCondition, false, true);
                }
                return(string.Format("UPDATE {0} SET {1} {2}", strTable, strSetValue.Substring(1), strCondition));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return("-1");
            }
        }