Ejemplo n.º 1
0
        // 用Reader方式
        private DataTable GetDataTableByDataReader(string sql, CommandType type, int pageIndex, int pageSize, ParameterCollection parameters)
        {
            DataTable ret = new DataTable();

            int startIndex = 0;
            int endIndex = 0;
            int rowIndex = 0;
            DataRow row = null;
            OracleDataReader dr = null;
            OracleCommand oleCmd = new OracleCommand();

            try
            {
                startIndex = pageSize * (pageIndex - 1);
                endIndex = startIndex + pageSize - 1;

                //Open();
                oleCmd.Connection = _oleConn;
                oleCmd.CommandText = sql;
                oleCmd.CommandType = type;
                // 设置参数
                for (int i = 0; i < parameters.Count; i++)
                {
                    OracleParameter param = new OracleParameter();
                    param.ParameterName = parameters[i].ParameterName;
                    param.Value = parameters[i].ParameterValue;
                    oleCmd.Parameters.Add(param);
                }
                dr = oleCmd.ExecuteReader();

                for (int i = 0; i < dr.FieldCount; i++)
                {
                    ret.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
                }

                while (dr.Read())
                {
                    rowIndex++;
                    if (rowIndex > startIndex)
                    {
                        row = ret.NewRow();
                        for (int i = 0; i < ret.Columns.Count; i++)
                        {
                            row[i] = dr.GetValue(i);
                        }
                        ret.Rows.Add(row);
                    }
                    if (rowIndex > endIndex)
                    {
                        break;
                    }
                }
            }
            catch (System.Exception ex)
            {
                throw new TechException(ex.Message, sql, ex);
            }
            finally
            {
                //Close();
                dr = null;
                oleCmd = null;                
            }

            return ret;
        }
Ejemplo n.º 2
0
 /// <summary>
 /// 获取数据集
 /// </summary>
 /// <param name="sql">SQL语句</param>
 /// <param name="type">SQL语句类型</param>
 /// <param name="pageIndex">当前页数</param>
 /// <param name="pageSize">当前页显示的记录数</param>
 /// <param name="parameters">条件参数</param>
 /// <returns>数据集</returns>
 public DataTable GetDataTable(string sql, CommandType type, int pageIndex, int pageSize, ParameterCollection parameters)
 {
     if (type == CommandType.Text)
     {
         return GetDataTableByRowNum(sql, type, pageIndex, pageSize, parameters);
     }
     else
     {
         return GetDataTableByDataReader(sql, type, pageIndex, pageSize, parameters);
     }
 }
Ejemplo n.º 3
0
        // 用rownum方式
        private DataTable GetDataTableByRowNum(string sql, CommandType type, int pageIndex, int pageSize, ParameterCollection parameters)
        {
            // 对于Oracle,如果执行的是Sql语句,则采用Oracle数据库分页方式,从而减少DataReader占用的资源
            int startIndex = pageSize * (pageIndex - 1);
            int endIndex = startIndex + pageSize;

            string sqlCommand = @"SELECT * FROM
									(
									SELECT A.*, rownum R
									FROM
										(
											{0}
										) A
									WHERE rownum<={1}
									) B
								 WHERE R>{2}";

            return GetDataTable(string.Format(sqlCommand, sql, endIndex, startIndex));
        }
Ejemplo n.º 4
0
        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="type">SQL语句类型</param>
        /// <param name="parameters">条件参数</param>
        /// <returns>数据集</returns>
        public DataSet GetDataSet(string sql, CommandType type, ParameterCollection parameters)
        {
            DataSet ret = new DataSet();
            OracleDataAdapter da = new OracleDataAdapter();
            OracleCommand oleCmd = new OracleCommand();

            try
            {
                //Open();
                oleCmd.Connection = _oleConn;
                oleCmd.CommandText = sql;
                oleCmd.CommandType = type;

                // 设置参数
                for (int i = 0; i < parameters.Count; i++)
                {
                    OracleParameter param = new OracleParameter();
                    param.ParameterName = parameters[i].ParameterName;
                    param.Value = parameters[i].ParameterValue;
                    oleCmd.Parameters.Add(param);
                }

                da.SelectCommand = oleCmd;
                da.Fill(ret);
            }
            catch (System.Exception ex)
            {
                throw new TechException(ex.Message, sql, ex);
            }
            finally
            {
                //Close();
                oleCmd = null;
                da = null;
            }

            return ret;
        }
Ejemplo n.º 5
0
 /// <summary>
 /// 获取数据集
 /// </summary>
 /// <param name="sql">SQL语句</param>
 /// <param name="type">SQL语句类型</param>
 /// <param name="parameters">条件参数</param>
 /// <returns>数据集</returns>
 public DataTable GetDataTable(string sql, CommandType type, ParameterCollection parameters)
 {
     return GetDataSet(sql, type, parameters).Tables[0];
 }
Ejemplo n.º 6
0
        /// <summary>
        /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="type">SQL语句的类型</param>
        /// <param name="parameters">相关参数</param>
        /// <returns>第一行第一列的值</returns>
        public object ExecuteScalar(string sql, CommandType type, ParameterCollection parameters)
        {
            object ret = null;
            OracleCommand oleCmd = new OracleCommand();

            try
            {
                //Open();
                oleCmd.Connection = _oleConn;
                oleCmd.CommandText = sql;
                oleCmd.CommandType = type;

                // 设置参数
                for (int i = 0; i < parameters.Count; i++)
                {
                    OracleParameter param = new OracleParameter();
                    param.ParameterName = parameters[i].ParameterName;
                    param.Value = parameters[i].ParameterValue;
                    oleCmd.Parameters.Add(param);
                }

                ret = oleCmd.ExecuteScalar();

                //// 获取参数返回值
                //for (int i = 0; i < parameters.Count; i++)
                //{
                //    parameters[i].ParameterValue = oleCmd.Parameters[parameters[i].ParameterName].Value;
                //}
            }
            catch (System.Exception ex)
            {
                throw new TechException(ex.Message, sql, ex);
            }
            finally
            {
                //Close();
                oleCmd = null;
            }

            return ret;
        }
Ejemplo n.º 7
0
        /// <summary>
        /// 执行数据库操作(Insert,Update,Delete)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="type">SQL语句的类型</param>
        /// <param name="parameters">相关参数</param>
        /// <returns>SQL语句所影响的记录数</returns>
        public int ExecuteNonQuery(string sql, CommandType type, ParameterCollection parameters)
        {
            int ret = 0;
            OracleCommand oleCmd = new OracleCommand();

            try
            {
                //Open();
                oleCmd.Connection = _oleConn;
                if (_oleTrans != null) oleCmd.Transaction = _oleTrans;
                
                oleCmd.CommandText = sql;
                oleCmd.CommandType = type;

                // 设置参数
                for (int i = 0; i < parameters.Count; i++)
                {
                    OracleParameter param = new OracleParameter();
                    param.ParameterName = parameters[i].ParameterName;
                    param.Value = parameters[i].ParameterValue;
                    oleCmd.Parameters.Add(param);
                }

                ret = oleCmd.ExecuteNonQuery();

                // 获取参数返回值
                for (int i = 0; i < parameters.Count; i++)
                {
                    parameters[i].ParameterValue = oleCmd.Parameters[parameters[i].ParameterName].Value;
                }
            }
            catch (System.Exception ex)
            {
                throw new TechException(ex.Message, sql, ex);
            }
            finally
            {
                oleCmd.Dispose();
                oleCmd = null;
                //Close();
            }
            return ret;
        }
Ejemplo n.º 8
0
        // 用rownum方式
        private DataTable GetDataTableByRowNum(string sql, CommandType type, int pageIndex, int pageSize, ParameterCollection parameters)
        {
            //SELECT * 
            //FROM (select *,ROW_NUMBER() Over(order by id) as rowNum from table_info )as myTable
            //where rowNum between 50 and 60;

            //注:SQL Server 的ROW_NUMBER()函数性能,通用性都不够,暂不使用

            return null;

        }
Ejemplo n.º 9
0
        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="type">SQL语句类型</param>
        /// <param name="parameters">条件参数</param>
        /// <returns>数据集</returns>
        public DataSet GetDataSet(string sql, CommandType type, ParameterCollection parameters)
        {
            DataSet ret = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand oleCmd = new SqlCommand();

            try
            {
                oleCmd.Connection = _oleConn;
                oleCmd.CommandText = sql;
                oleCmd.CommandType = CommandType.Text;

                // 设置参数
                for (int i = 0; i < parameters.Count; i++)
                {
                    SqlParameter param = new SqlParameter();
                    param.ParameterName = parameters[i].ParameterName;
                    param.Value = parameters[i].ParameterValue;
                    if (parameters[i].ParameterValue is Guid)
                    {
                        param.DbType = DbType.Guid;
                    }
                    oleCmd.Parameters.Add(param);
                }

                da.SelectCommand = oleCmd;
                da.Fill(ret);
            }
            catch (System.Exception ex)
            {
                throw new TechException(ex.Message, sql, ex);
            }
            finally
            {
                oleCmd.Dispose();
                oleCmd = null;
                da.Dispose();
                da = null;
            }

            return ret;
        }
Ejemplo n.º 10
0
        /// <summary>
        /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="type">SQL语句的类型</param>
        /// <param name="parameters">相关参数</param>
        /// <returns>第一行第一列的值</returns>
        public object ExecuteScalar(string sql, CommandType type, ParameterCollection parameters)
        {
            object ret = null;
            SqlCommand oleCmd = new SqlCommand();

            try
            {
                oleCmd.Connection = _oleConn;
                oleCmd.CommandText = sql;
                oleCmd.CommandType = CommandType.Text;

                // 设置参数
                for (int i = 0; i < parameters.Count; i++)
                {
                    SqlParameter param = new SqlParameter();
                    param.ParameterName = parameters[i].ParameterName;
                    param.Value = parameters[i].ParameterValue;
                    if (parameters[i].ParameterValue is Guid)
                    {
                        param.DbType = DbType.Guid;
                    }
                    oleCmd.Parameters.Add(param);
                }

                ret = oleCmd.ExecuteScalar();

                //// 获取参数返回值
                //for (int i = 0; i < parameters.Count; i++)
                //{
                //    parameters[i].ParameterValue = oleCmd.Parameters[parameters[i].ParameterName].Value;
                //}
            }
            catch (System.Exception ex)
            {
                throw new TechException(ex.Message, sql, ex);
            }
            finally
            {
                oleCmd.Dispose();
                oleCmd = null;               
            }

            return ret;
        }