.net执行SQL语句

 

    /// <summary>

    /// 构造函数，初始化时连接数据库
    /// </summary>
    public DB_Common()
    {
        string connStr;
        connStr = System.Configuration.ConfigurationSettings.AppSettings[0].ToString();
        sqlcon = new OracleConnection(connStr);
    }

    #endregion

    #region 打开数据库
    /// <summary>
    /// 打开数据库
    /// </summary>
    public void OpenConn()
    {
        try
        {
            if (this.sqlcon.State != ConnectionState.Open)
                this.sqlcon.Open();
        }
        catch (OracleException ex)
        {
            throw new Exception(ex.Message);
        }
    }
    #endregion
    #region 关闭数据库联接
    /// <summary>
    /// 关闭数据库联接
    /// </summary>
    public void CloseConn()
    {
        try
        {
            if (sqlcon.State == ConnectionState.Open)
                sqlcon.Close();
        }
        catch (OracleException ex)
        {
            throw new Exception(ex.Message);
        }
    }
    #endregion

 

 #region  执行SQL语句
    /// <summary>

    /// 此方法用来执行SQL语句
    /// </summary>
    /// <param name="SqlCom">要执行的SQL语句</param>
    /// <returns></returns>
    public bool ExceSQL(string strSqlCom)
    {
        sqlcom = new OracleCommand(strSqlCom, sqlcon);
        try
        {
            OpenConn();
            sqlcom.ExecuteNonQuery();
            return true;
        }
        catch (OracleException ex)
        {
            throw new Exception(ex.Message);
            return false;
        }
        finally
        {
            CloseConn();
            //sqlcon.Close();
        }
    }
    #endregion

    #region 执行SQL语句，返回记录总数
    /// <summary>
    /// 执行SQL语句，返回记录总数
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>返回记录总条数</returns>
    ///
    public int Execute_Record(string sql)
    {
        int recordCount = 0;
        OpenConn();//打开数据连接
        OracleCommand command = new OracleCommand(sql, sqlcon);
        OracleDataReader dataReader = command.ExecuteReader();
        while (dataReader.Read())
        {
            recordCount++;
        }
        dataReader.Close();
        CloseConn();//关闭数据库连接
        return recordCount;
    }

    #endregion

 

 

 

 

 

 

 #region  返回DataSet类型数据
    /// <summary>
    /// 此方法返回一个DataSet类型
    /// </summary>
    /// <param name="SqlCom">要执行的SQL语句</param>
    /// <returns></returns>
    public DataSet ExceDS(string SqlCom)
    {
        try
        {
            OpenConn();
            sqlcom = new OracleCommand(SqlCom, sqlcon);
            sqldata = new OracleDataAdapter();
            sqldata.SelectCommand = sqlcom;
            DataSet ds = new DataSet();
            sqldata.Fill(ds);
            return ds;
        }
        catch (OracleException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            // sqlcon.Close();
            CloseConn();
        }
    }
    #endregion

 

 

  #region 执行SQ DML语句,返回所影响的行数
    /// <summary>
    /// 执行SQL语句,返回所影响的行数
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int ExecuteDML(string strSqlCom)
    {
        int Cmd = 0;
        OpenConn();
        OracleCommand command = new OracleCommand(strSqlCom, sqlcon);
        OracleTransaction OraTrans = sqlcon.BeginTransaction();
        command.Transaction = OraTrans;
        try
        {
            Cmd = command.ExecuteNonQuery();
            OraTrans.Commit();
        }
        catch (OracleException ex)
        {
            throw new Exception(ex.Message);
            OraTrans.Rollback();
        }
        finally
        {
            CloseConn();
        }

        return Cmd;
    }
    #endregion