您现在的位置是:门户> 编程语言> ASP.NET

asp.net Oracle数据库访问操作类
2021-02-23 4人围观 0条评论
简介asp.net Oracle数据库访问操作类,需要的朋友可以参考一下

    复制代码 代码如下:

    using System;
    using System.Collections;
    using System.Collections.Specialized;
    using System.Data;
    using System.Data.OracleClient;
    using System.Configuration;
    using System.Data.Common;
    using System.Collections.Generic;

        ///
        /// 数据访问抽象基础类
        ///
        ///
    public class DBBase
    {


        //数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.       
        public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"].ToString();
        public DBBase()
        {
        }

        #region 检查用户名是否存在
        ///
        /// 检查用户名是否存在,存在返回true,不存在返回false
        ///
        ///
        ///
        public static bool Exists(string strSql)
        {

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand myCmd = new OracleCommand(strSql, connection);
                try
                { 
                    object obj = myCmd.ExecuteScalar(); //返回结果的第一行一列
                    myCmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }          
                }
                catch (Exception ex)
                 {
                     throw ex;
                 }
           }
        }

        #endregion

        #region  执行简单SQL语句 返回影响的记录数

        ///
        /// 执行SQL语句,返回影响的记录数
        ///
        /// SQL语句
        /// 影响的记录数
        public static int ExecuteSql(string SQLString)
        {

            OracleConnection connection = null;
            OracleCommand cmd = null;
            try
            {
                connection = new OracleConnection(connectionString);
                cmd = new OracleCommand(SQLString, connection);
                connection.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }
           #endregion

     

        #region   执行查询语句,返回SqlDataReader
        ///
        /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
        ///
        /// 查询语句
        /// SqlDataReader
        public static OracleDataReader ExecuteReader(string strSQL)
        {
            OracleConnection connection = new OracleConnection(connectionString);
            OracleCommand cmd = new OracleCommand(strSQL, connection);
            try
            {
                connection.Open();
                OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.OracleClient.OracleException e)
            {
                throw e;
            }
            finally
            {
                connection.Close();

            }
        }
        #endregion

        #region  执行SQL查询语句,返回DataTable数据表
        ///
        /// 执行SQL查询语句
        ///
        ///
        /// 返回DataTable数据表
        public static DataTable GetDataTable(string sqlStr)
        {
            OracleConnection mycon = new OracleConnection(connectionString);
            OracleCommand mycmd = new OracleCommand(sqlStr, mycon);
            DataTable dt = new DataTable();
            OracleDataAdapter da = null;
            try
            {
                mycon.Open();
                da = new OracleDataAdapter(sqlStr, mycon);
                da.Fill(dt);


            }
            catch (Exception ex)
            {

                throw new Exception(ex.ToString());
            }
            finally
            {
                mycon.Close();
            }
            return dt;
        }
        #endregion

        #region 存储过程操作
        ///
        ///  运行存储过程,返回datatable;
        ///
        /// 存储过程名称
        /// 参数
        ///
        public static DataTable RunProcedureDatatable(string storedProcName, IDataParameter[] parameters)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                DataSet ds = new DataSet();
                connection.Open();
                OracleDataAdapter sqlDA = new OracleDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(ds);
                connection.Close();
                return ds.Tables[0];
            }
        }
        ///
        /// 执行存储过程
        ///
        /// 存储过程名称
        /// 参数
        ///
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    OracleCommand command = new OracleCommand(storedProcName, connection);
                    command.CommandType = CommandType.StoredProcedure;
                    foreach (OracleParameter parameter in parameters)
                    {
                        if (parameter != null)
                        {
                            // 检查未分配值的输出参数,将其分配以DBNull.Value.
                            if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                                (parameter.Value == null))
                            {
                                parameter.Value = DBNull.Value;
                            }
                            command.Parameters.Add(parameter);
                        }
                    }
                   int rows = command.ExecuteNonQuery();
                   return rows;
                }

                finally
                {
                    connection.Close();
                }
            }
        }

        ///
        /// 构建 OracleCommand 对象(用来返回一个结果集,而不是一个整数值)
        ///
        /// 数据库连接
        /// 存储过程名
        /// 存储过程参数
        /// OracleCommand
        private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = new OracleCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }


        #endregion

        #region 事务处理

        ///
        /// 执行多条SQL语句(list的形式),实现数据库事务。
        ///
        /// 多条SQL语句   
        /// 调用 Transaction 对象的 Commit 方法来完成事务,或调用 Rollback 方法来取消事务。
        public static int ExecuteSqlTran(List SQLStringList)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                // 为事务创建一个命令
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connection;
                OracleTransaction tx = connection.BeginTransaction();// 启动一个事务
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();//用Commit方法来完成事务
                    return count;//
                }
                catch
                {
                    tx.Rollback();//出现错误,事务回滚!
                    return 0;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();//关闭连接
                }
            }
        }
        #endregion
        #region 事务处理

        ///
        /// 执行多条SQL语句(字符串数组形式),实现数据库事务。
        ///
        /// 多条SQL语句   
        /// 调用 Transaction 对象的 Commit 方法来完成事务,或调用 Rollback 方法来取消事务。
        public static int ExecuteTransaction(string[] SQLStringList,int p)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                // 为事务创建一个命令
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connection;
                OracleTransaction tx = connection.BeginTransaction();// 启动一个事务
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < p; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();//用Commit方法来完成事务
                    return count;//
                }
                catch
                {
                    tx.Rollback();//出现错误,事务回滚!
                    return 0;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();//关闭连接
                }
            }
        }

        #endregion
        ///
        /// 执行存储过程获取所需编号(各表主键)
        ///
        /// 存储过程参数
        /// 存储过程参数(默认为1)
        /// 编号(各表主键)
        public static string Get_FlowNum(string FlowName, int StepLen = 1)
        {
            OracleConnection mycon = new OracleConnection(connectionString);
            try
            {
                mycon.Open();
                OracleCommand MyCommand = new OracleCommand("ALARM_GET_FLOWNUMBER", mycon);
                MyCommand.CommandType = CommandType.StoredProcedure;
                MyCommand.Parameters.Add(new OracleParameter("I_FlowName", OracleType.VarChar, 50));
                MyCommand.Parameters["I_FlowName"].Value = FlowName;
                MyCommand.Parameters.Add(new OracleParameter("I_SeriesNum", OracleType.Number));
                MyCommand.Parameters["I_SeriesNum"].Value = StepLen;
                MyCommand.Parameters.Add(new OracleParameter("O_FlowValue", OracleType.Number));
                MyCommand.Parameters["O_FlowValue"].Direction = ParameterDirection.Output;
                MyCommand.ExecuteNonQuery();
                return MyCommand.Parameters["O_FlowValue"].Value.ToString();
            }
            catch
            {
                return "";
            }
            finally
            {
                mycon.Close();
            }
        }

    }

分享:

文章评论

    • wxpython 学习笔记 第一天
    • python文本数据处理学习笔记详解