Example #1
0
        /// <summary>
        /// 计算查询结果数
        /// </summary>
        /// <param name="eaconn"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <param name="pageInfo"></param>
        /// <returns></returns>
        public long Count(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
        {
            using (IDbConnection conn = eaconn.Open())
            using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
            {
                AddParameters(eaconn.DatabaseType, cmd, parameters);

                cmd.CommandText = string.Format("select count(*) from ({0})T ", cmd.CommandText);

                return cmd.ExecuteScalar().ToLong();
            }
        }
Example #2
0
        public void CreateTable(IEAConnection eaconn, string tableName, string cmdText)
        {
            string sqlStr = string.Empty;
            if (eaconn.DatabaseType != DatabaseType.Oracle)
            {
                sqlStr = string.Format("SELECT top 1 table_name as [Name] FROM INFORMATION_SCHEMA.TABLES where table_name=@table_name");
            }
            else
            {
                tableName = tableName.ToUpper();
                sqlStr = string.Format("select table_name as Name from user_tables where table_name=:table_name");
            }

            IDictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("table_name", tableName);

            if (ExecuteScalar(eaconn, sqlStr, parameters) == null)
            {
                ExecuteNonQuery(eaconn, cmdText);
            }
        }
Example #3
0
        public DataSet ExecuteDataSet(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
        {
            using (IDbConnection conn = eaconn.Open())
            using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
            {
                AddParameters(eaconn.DatabaseType, cmd, parameters);

                return new ADODataAdapter().FillDataSet(cmd);
            }
        }
Example #4
0
        public void Insert(IEAConnection eaconn, DataTable dt, IDictionary<string, string> mappings)
        {
            if (eaconn.DatabaseType != DatabaseType.Oracle)
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(eaconn.ConnectionString))
                {
                    foreach (KeyValuePair<string, string> item in mappings)
                    {
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(item.Key, item.Value));
                    }
                    bulkCopy.DestinationTableName = dt.TableName;
                    bulkCopy.WriteToServer(dt);
                }

                return;
            }

            if (!string.IsNullOrEmpty(dt.TableName))
                ExecuteNonQuery(eaconn, string.Format("alter   table   {0}   NOLOGGING", dt.TableName.ToUpper()));

            foreach (DataRow dr in dt.Rows)
            {
                IDictionary<string, object> parameters = mappings.Values.ToDictionary(field => field, field => dr[field]);

                ExecuteNonQuery(eaconn, @"INSERT INTO DD_BatchValue
                                               (ID
                                               ,BatchDataID
                                               ,Value
                                               ,CreateTime
                                               ,Creator)
                                         VALUES
                                               (:ID,
                                                :BatchDataID,
                                                :Value,
                                                to_date(:CreateTime, 'yyyy-mm-dd hh24:mi:ss'),
                                                :Creator)", parameters);
            }

            if (!string.IsNullOrEmpty(dt.TableName))
                ExecuteNonQuery(eaconn, string.Format("alter   table   {0}   LOGGING", dt.TableName.ToUpper()));
        }
Example #5
0
        public object ExecuteScalarWithTrans(IEAConnection eaconn, string cmdText)
        {
            object value = null;

            using (IDbConnection conn = eaconn.Open())
            {
                using (System.Data.IDbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
                    {
                        try
                        {
                            cmd.Transaction = trans;
                            value = cmd.ExecuteScalar();
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            GlobalLogger.Error<DataContext>(string.Format("执行sql={0}出错", cmdText), ex);
                            trans.Rollback();
                            throw;
                        }
                    }
                }
            }

            return value.ToSafeString();
        }
Example #6
0
        public DataTable ExecuteTop1(IEAConnection eaconn, string cmdText)
        {
            if (eaconn.DatabaseType == DatabaseType.Oracle)
            {
                return ExecuteDataTable(eaconn, string.Format("select * from ({0}) T where rownum=1", cmdText));
            }

            return ExecuteDataTable(eaconn, string.Format("select top 1 * from ({0}) T ", cmdText));
        }
Example #7
0
 public object ExecuteScalar(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
 {
     using (IDbConnection conn = eaconn.Open())
     using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
     {
         AddParameters(eaconn.DatabaseType, cmd, parameters);
         return cmd.ExecuteScalar();
     }
 }
Example #8
0
 public object ExecuteScalar(IEAConnection eaconn, string cmdText)
 {
     using (IDbConnection conn = eaconn.Open())
     using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
     {
         return cmd.ExecuteScalar();
     }
 }
Example #9
0
        public DataTable ExecuteProcDataTable(IEAConnection eaconn, string procName, IDictionary<string, object> parameters)
        {
            DataSet ds = ExecuteProcDataSet(eaconn, procName, parameters);

            if (ds == null || ds.Tables.Count == 0)
                return null;

            return ds.Tables[0];
        }
Example #10
0
        public object ExecuteProcScalar(IEAConnection eaconn, string procName, IDictionary<string, object> parameters)
        {
            using (IDbConnection conn = eaconn.Open())
            using (IDbCommand cmd = PrepareCommand(procName, conn, CommandType.StoredProcedure))
            {
                AddParameters(eaconn.DatabaseType, cmd, parameters);

                return cmd.ExecuteScalar();
            }
        }
Example #11
0
        public DataSet ExecuteProcDataSet(IEAConnection eaconn, string procName, IDictionary<string, object> parameters)
        {
            using (IDbConnection conn = eaconn.Open())
            {
                using (IDbCommand cmd = PrepareCommand(procName, conn, CommandType.StoredProcedure))
                {
                    AddParameters(eaconn.DatabaseType, cmd, parameters);

                    return new ADODataAdapter().FillDataSet(cmd);
                }
            }
        }
Example #12
0
 public void ExecuteNonQuery(IEAConnection eaconn, string cmdText)
 {
     using (IDbConnection conn = eaconn.Open())
     using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
     {
         cmd.ExecuteNonQuery();
     }
 }
Example #13
0
        public DataTable ExecuteDataTable(IEAConnection eaconn, string cmdText)
        {
            DataSet ds = ExecuteDataSet(eaconn, cmdText);

            if (ds == null || ds.Tables.Count == 0)
                return null;

            return ds.Tables[0];
        }
Example #14
0
        /// <summary>
        /// 执行sql命令,返回DataTable
        /// </summary>
        /// <param name="cmdText">sql命令</param>
        /// <param name="parameters">条件参数</param>
        /// <param name="pageInfo">分页信息</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters, string orderBy, PageInfo pageInfo)
        {
            pageInfo.ItemCount = Count(eaconn, cmdText, parameters);

            if (!string.IsNullOrEmpty(orderBy) && !orderBy.Replace(" ", "").ToLower().StartsWith("orderby"))
            {
                orderBy = string.Format("order by {0} ", orderBy);
            }

            using (IDbConnection conn = eaconn.Open())
            using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
            {
                //AddParameters(eaconn.DatabaseType, cmd, parameters);
                //cmd.CommandText = string.Format("select count(*) from ({0})T ", cmd.CommandText);

                AddParameters(eaconn.DatabaseType, cmd, parameters);

                string paramChar = DataUtil.GetParamChar(eaconn.DatabaseType);
                parameters.SafeAdd(paramChar + "PageIndex", pageInfo.PageIndex);
                parameters.SafeAdd(paramChar + "PageSize", pageInfo.PageSize);

                if (eaconn.DatabaseType == DatabaseType.MySQL)
                {
                    cmd.CommandText = string.Format("{0} {1} limit {2}, {3} ", cmd.CommandText, orderBy, (pageInfo.PageIndex - 1) * pageInfo.PageSize, pageInfo.PageSize);
                }
                else if (eaconn.DatabaseType == DatabaseType.Oracle)
                {
                    cmd.CommandText = string.Format("SELECT * FROM(SELECT ROWNUM RN,T.* FROM ({0} {2})T WHERE ROWNUM <= {1}PageIndex*{1}PageSize) WHERE RN >=({1}PageIndex-1)*{1}PageSize+1", cmd.CommandText, paramChar, orderBy);
                }
                else
                {
                    cmd.CommandText = string.Format("select Top ({0}PageSize) * from (select  ROW_NUMBER() OVER( {2} ) AS row_number, * from ({1}) P ) T Where row_number>({0}PageIndex-1)*{0}PageSize", paramChar, cmd.CommandText, string.IsNullOrEmpty(orderBy) ? "ORDER BY CURRENT_TIMESTAMP" : orderBy);
                }

                AddParameter(cmd, "PageIndex", pageInfo.PageIndex);
                AddParameter(cmd, "PageSize", pageInfo.PageSize);
                DataSet ds = new ADODataAdapter().FillDataSet(cmd);

                return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;
            }
        }
Example #15
0
        public DataSet ExecuteDataSet(IEAConnection eaconn, string cmdText)
        {
            using (IDbConnection conn = eaconn.Open())
            using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
            {
                DataSet ds = new DataSet();

                return new ADODataAdapter().FillDataSet(cmd);
            }
        }