Пример #1
0
        }//Execute(string sql)

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">数据库连接对象</param>
        /// <param name="param">参数</param>
        /// <returns>返回受影响行数</returns>
        static public int Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param)
        {
            if (Conn == null)
            {
                DBCoreHelper.ErrLog("DBCoreHelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):连接对象为空!");
                return(0);
            }
            if (Conn.State == System.Data.ConnectionState.Closed)
            {
                Conn.Open();
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            cmd.CommandTimeout = 180;
            cmd.CommandText    = sql;
            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }
            try
            {
                return(cmd.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                DBCoreHelper.ErrLog("DBCoreHelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):" + ex.Message + "/nsql=" + sql);
                return(0);
            }
        }//Execute(string sql,System.Data.IDataParameter[] param)
Пример #2
0
        }//ExecuteQueryToDataTable(string sql)

        /// <summary>
        /// 执行查询返回DataReader
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">连接对象</param>
        /// <returns>成功时返回Reader对象,失败时返回null</returns>
        static public System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn)
        {
            System.Data.IDataReader reader = null;
            if (Conn == null)
            {
                return(null);
            }
            try
            {
                if (Conn.State == System.Data.ConnectionState.Closed)
                {
                    Conn.Open();
                }
                System.Data.IDbCommand cmd = Conn.CreateCommand();
                cmd.CommandText = sql;
                reader          = cmd.ExecuteReader();
                return(reader);
            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("死锁"))
                {
                    WriteLog(ex.Message + " 再做列锁循环!ExecuteQuery");
                    System.Threading.Thread.Sleep(200);
                    return(ExecuteQuery(sql, Conn));
                }
                else
                {
                    DBCoreHelper.ErrLog("DBCoreHelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + ";  \n sql =" + sql);
                }
                return(null);
            }
        }//ExecuteQuery(string sql)
Пример #3
0
        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="filters">过滤条件filter对象</param>
        /// <returns>FilterQueryResult结果对象</returns>
        internal virtual FilterQueryResult GetQueryResult(QueryPageFilter filters)
        {
            FilterQueryResult result        = new FilterQueryResult();
            string            whereStr      = GetWhereString(filters);
            string            orderBy       = GetOrderByString(filters);
            string            groupByFields = GetGroupByFields(filters);
            var    fields = GetQueryFields(filters);
            string sql    = "select count(*) from " + filters.TableName + " where " + whereStr;
            string strSql = string.Empty;

            if (filters.IsPage == true)
            {
                var index = filters.PageSize * (filters.PageIndex - 1);
                if (index < 0)
                {
                    index = 0;
                }

                strSql = "select top " + filters.PageSize + fields + " from " + filters.TableName +
                         " where Id Not In ( select top " + index + " Id from " + filters.TableName + " where " + whereStr + orderBy + ") and  ( " + whereStr + " ) " + orderBy;
                if (CurrPrividerType == SqlPrividerType.Sqlite)
                {
                    strSql = "select " + fields + " from " + filters.TableName +
                             " where Id Not In ( select Id from " + filters.TableName + " where " + whereStr + orderBy + " limit 0," + index + " ) and  ( " + whereStr + " ) " + orderBy + " limit 0," + filters.PageSize;
                }
            }
            else
            {
                var groupBy = string.Empty;
                if (!string.IsNullOrEmpty(groupByFields))
                {
                    fields  = groupByFields;
                    groupBy = "group by " + groupByFields;
                }

                strSql = "select  " + fields + " from " + filters.TableName + " where " + whereStr + groupBy + orderBy;
            }
            var conn       = DBCoreHelper.OpenConnect(CurrConnectionString, DBHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
            var tableCount = DBCoreHelper.ExecuteQueryToDataTable(sql, conn);
            int count      = int.Parse(tableCount.Rows[0][0].ToString());

            if (filters.IsReturnCount == false)
            {
                var table = DBCoreHelper.ExecuteQueryToDataTable(strSql, conn);
                result.Result = table;
            }
            result.TotalCount = count;
            conn.Close();
            return(result);
        }
Пример #4
0
        }//Execute(string sql)

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">数据库连接对象</param>
        /// <returns>返回受影响行数</returns>
        static public int ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbTransaction tran)
        {
            if (Conn == null)
            {
                DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
                return(0);
            }
            if (Conn.State == System.Data.ConnectionState.Closed)
            {
                Conn.Open();
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            cmd.CommandTimeout = 180;
            cmd.Transaction    = tran;
            cmd.CommandText    = sql;
            try
            {
                var count = cmd.ExecuteNonQuery();
                cmd.Dispose();
                return(count);
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                if (ex.Message.Contains("死锁"))
                {
                    WriteLog(ex.Message + " 再做列锁循环!ExecuteWithTrans");
                    System.Threading.Thread.Sleep(200);
                    return(Execute(sql, Conn));
                }
                else
                {
                    DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
                    return(0);
                }
            }
        }//Execute(string sql)
Пример #5
0
        }//Execute(string sql,System.Data.IDataParameter[] param)

        /// <summary>
        /// 执行一个事务
        /// </summary>
        /// <param name="sqls">Sql语句组</param>
        /// <returns>成功时返回true</returns>
        static public bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
        {
            System.Data.IDbTransaction myTrans;
            if (Conn == null)
            {
                DBCoreHelper.ErrLog("DBCoreHelper.ExecuteTrans(string[] sqls):连接对象为空!");
                return(false);
            }
            if (Conn.State == System.Data.ConnectionState.Closed)
            {
                Conn.Open();
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            cmd.CommandTimeout = 180;
            myTrans            = Conn.BeginTransaction();
            cmd.Transaction    = myTrans;
            try
            {
                foreach (string sql in sqls)
                {
                    if (sql != null)
                    {
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                }
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                DBCoreHelper.ErrLog("DBCoreHelper.ExecuteTrans(string[] sqls):" + ex.Message);
                return(false);
            }
            return(true);
        }//Execute(string sql)