예제 #1
0
        /// <summary>
        /// 执行SQL语句,并返加影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public static DataResult ExecuteNonQuery(string sql, params IDbDataParameter[] cmdParms)
        {
            sql = DataBaseFactory.GetSQL(sql);
            DataResult result = new DataResult();

            result.Sql = sql + ";";
            result.IDbDataParameter = cmdParms;
            using (IDbConn = DataBaseFactory.GetIDbConnection())
            {
                IDbConn.Open();
                using (IDbComm = DataBaseFactory.GetIDbCommand())
                {
                    try
                    {
                        PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms);
                        int rows = IDbComm.ExecuteNonQuery();
                        IDbConn.Close();
                        IDbComm.Parameters.Clear();
                        result.RecordCount = rows;
                        return(result);
                    }
                    catch (Exception E)
                    {
                        IDbConn.Close();
                        result.RecordCount = 0;
                        result.Error       = E.Message;
                        return(result);
                    }
                    finally
                    {
                        IDbConn.Close();
                    }
                }
            }
        }
예제 #2
0
        /// <summary>
        /// 执行SQL语句返回结果集IDataReader
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public static DataResult GetDataReader(string sql, params IDbDataParameter[] cmdParms)
        {
            sql = DataBaseFactory.GetSQL(sql);
            DataResult result = new DataResult();

            result.Sql = sql + ";";
            result.IDbDataParameter = cmdParms;
            try
            {
                if (IDbConn == null || IDbConn.State == ConnectionState.Closed)
                {
                    IDbConn = DataBaseFactory.GetIDbConnection();
                    IDbConn.Open();
                    IDbComm = DataBaseFactory.GetIDbCommand();
                    PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms);
                    // = IDbcmd.ExecuteReader();
                    result.IDataReader = IDbComm.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
                    return(result);
                    // IDbComm.Parameters.Clear();
                }
                else
                {
                    return(result);
                }
            }
            catch (Exception e)
            {
                IDbConn.Close();
                result.Error = e.Message;
                return(result);
            }
        }
예제 #3
0
        /// <summary>
        /// 执行SQL语句返回结果集object
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params IDbDataParameter[] cmdParms)
        {
            sql = DataBaseFactory.GetSQL(sql);
            DataResult result = new DataResult();

            result.Sql = sql + ";";
            using (IDbConn = DataBaseFactory.GetIDbConnection())
            {
                IDbConn.Open();
                using (IDbComm = DataBaseFactory.GetIDbCommand())
                {
                    try
                    {
                        PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms);
                        object rows = IDbComm.ExecuteScalar();
                        IDbConn.Close();
                        IDbComm.Parameters.Clear();
                        return(rows);
                    }
                    catch (Exception E)
                    {
                        IDbConn.Close();
                        return(null);

                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        IDbConn.Close();
                    }
                }
            }
        }
예제 #4
0
        /// <summary>
        /// 执行SQL语句返回结果集DataSet
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataResult GetDataSet(string sql)
        {
            sql = DataBaseFactory.GetSQL(sql);
            DataResult result = new DataResult();

            result.Sql = sql + ";";
            using (IDbConn = DataBaseFactory.GetIDbConnection())
            {
                IDbConn.Open();
                using (IDbComm = DataBaseFactory.GetIDbCommand())
                {
                    try
                    {
                        PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, IDbParams);
                        IDbDataAdapter dataAdapter = DataBaseFactory.GetIDbDataAdapter();
                        dataAdapter.SelectCommand = IDbComm;
                        DataSet dataSet = new DataSet();
                        dataAdapter.Fill(dataSet);
                        IDbComm.Parameters.Clear();
                        result.DataSet = dataSet;
                    }
                    catch (Exception E)
                    {
                        IDbConn.Close();
                        result.Error = E.Message;
                        return(result);
                    }
                    finally
                    {
                        IDbConn.Close();
                    }
                }
            }
            return(result);
            //using (IDbConn = DataBaseFactory.GetIDbConnection())
            //{
            //    IDbConn.Open();
            //    DataSet dataset = new DataSet();
            //    try
            //    {
            //        IDbComm = DataBaseFactory.GetIDbCommand(sql);
            //        //IDbComm.CommandText = sql;
            //        //IDbComm.CommandType = CommandType.Text;
            //        IDbAdpter = DataBaseFactory.GetIDbDataAdapter(IDbComm);
            //        //IDbAdpter.SelectCommand = IDbComm;
            //        IDbAdpter.Fill(dataset);
            //    }
            //    catch (Exception e)
            //    {
            //        IDbConn.Close();
            //        throw new Exception(e.Message);
            //    }
            //    finally
            //    {
            //        IDbConn.Close();
            //    }
            //    return dataset;
            //}
        }
예제 #5
0
파일: SMTPaging.cs 프로젝트: jjg0519/OA
        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="index">页索引</param>
        /// <param name="pageSize">每面显示记录数</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="rowCount">总记录数</param>
        /// <returns></returns>
        public static DataResult BindPaging(string sql, int index, int pageSize, out int pageCount, out int rowCount)
        {
            if (index > 0)
            {//索引从0开始
                index = index - 1;
            }
            sql = DataBaseFactory.GetSQL(sql);
            DataResult result = new DataResult();

            result.Sql = sql + ";";
            #region ORACLE

            // int rowCount = 0;
            //   DataTable dt = new DataTable();
            try
            {
                OracleParameter[] param = new OracleParameter[] {
                    new OracleParameter("pindex", OracleDbType.Int32),
                    new OracleParameter("psql", OracleDbType.Varchar2, 2000),
                    new OracleParameter("psize", OracleDbType.Int32),
                    new OracleParameter("pcount", OracleDbType.Int32),
                    new OracleParameter("prowcount", OracleDbType.Int32),
                    new OracleParameter("v_cur", OracleDbType.RefCursor)
                };
                param[0].Value = index;
                param[1].Value = sql;
                param[2].Value = pageSize;

                param[0].Direction = ParameterDirection.Input;
                param[1].Direction = ParameterDirection.Input;
                param[2].Direction = ParameterDirection.Input;
                param[3].Direction = ParameterDirection.Output;
                param[4].Direction = ParameterDirection.Output;
                param[5].Direction = ParameterDirection.Output;
                result.DataTable   = SMT.OracleDataProvider.ExecuteDataTableByPaging("SMT_P_PAGE.SMT_Paging", param);

                pageCount = int.Parse(param[3].Value.ToString());
                rowCount  = int.Parse(param[4].Value.ToString());
            }
            catch (OracleException on)
            {
                pageCount    = 0;
                rowCount     = 0;
                result.Error = on.Message;
                //throw new Exception(on.Message);
            }
            return(result);

            #endregion
        }
예제 #6
0
        /// <summary>
        /// 通过事务提执行SQL语句(必须放在BeginTransaction()方法与CommitTransaction()方法中间)
        /// </summary>
        /// <param name="idbconn">IDbConnection</param>
        ///  <param name="sql">SQL语句</param>
        public static DataResult ExecuteSQLByTransaction(IDbCommand iDbComm, string sql)
        {
            DataResult result = new DataResult();

            sql        = DataBaseFactory.GetSQL(sql);
            result.Sql = sql + ";";
            try
            {
                PrepareCommand(iDbComm, iDbComm.Connection, iDbComm.Transaction, CommandType.Text, sql, IDbParams);
                int rows = iDbComm.ExecuteNonQuery();
                iDbComm.Parameters.Clear();
            }
            catch (Exception E)
            {
                iDbComm.Connection.Close();
                result.Error = E.Message;
            }
            return(result);
        }
예제 #7
0
        /// <summary>
        /// 执行SQL语句返回结果集IDataReader
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataResult GetDataReader(string sql)
        {
            sql = DataBaseFactory.GetSQL(sql);
            DataResult result = new DataResult();

            result.Sql = sql + ";";
            IDbConn    = DataBaseFactory.GetIDbConnection();
            IDbConn.Open();
            IDbComm = DataBaseFactory.GetIDbCommand(IDbConn);
            try
            {
                PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, IDbParams);
                result.IDataReader = IDbComm.ExecuteReader();
            }
            catch (Exception E)
            {
                result.Error = E.Message;
                return(result);
            }
            return(result);
            //  IDataReader ireader = null;
            //  try
            //  {
            //      if (IDbConn == null || IDbConn.State == ConnectionState.Closed)
            //      {
            //          IDbConn = DataBaseFactory.GetIDbConnection();
            //          IDbConn.Open();
            //          IDbComm = DataBaseFactory.GetIDbCommand(sql);
            //          //IDbComm.CommandText = sql;
            //          //IDbComm.CommandType = CommandType.Text;
            //          // = IDbcmd.ExecuteReader();
            //          ireader = IDbComm.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
            //      }
            //      return ireader;
            //  }
            //catch (Exception e)
            //  {
            //       IDbConn.Close();
            //      return null;
            //      throw new Exception(e.Message);
            //  }
        }
예제 #8
0
        /// <summary>
        /// 执行SQL语句返回结果集DataSet
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public static DataResult GetDataSet(string sql, params IDbDataParameter[] cmdParms)
        {
            sql = DataBaseFactory.GetSQL(sql);
            DataResult result = new DataResult();

            result.Sql = sql + ";";
            result.IDbDataParameter = cmdParms;
            using (IDbConn = DataBaseFactory.GetIDbConnection())
            {
                IDbConn.Open();
                using (IDbComm = DataBaseFactory.GetIDbCommand())
                {
                    try
                    {
                        PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms);
                        IDbDataAdapter dataAdapter = DataBaseFactory.GetIDbDataAdapter();
                        dataAdapter.SelectCommand = IDbComm;
                        DataSet dataSet = new DataSet();
                        dataAdapter.Fill(dataSet);
                        IDbComm.Parameters.Clear();
                        result.DataSet = dataSet;
                    }
                    catch (Exception E)
                    {
                        IDbConn.Close();
                        result.Error = E.Message;
                        return(result);
                    }
                    finally
                    {
                        IDbConn.Close();
                    }
                }
            }
            return(result);
        }