oracleaccess.cs

封装了oracleclient访问接口,可以方便访问数据库

    /// 执行查询语句，返回OracleDataReader
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>OracleDataReader</returns>
    public static OracleDataReader ExecuteReader(string SQLString, params OracleParameter[] cmdParms)
    {
        OracleConnection connection = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand();
        try
        {
            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
            OracleDataReader myReader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            return myReader;
        }
        catch (System.Data.OracleClient.OracleException e)
        {
            throw new Exception(e.Message);
        }

    }

    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(string SQLString, params OracleParameter[] cmdParms)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
            using (OracleDataAdapter da = new OracleDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (System.Data.OracleClient.OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
    }


    private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        if (trans != null)
            cmd.Transaction = trans;
        cmd.CommandType = CommandType.Text;//cmdType;
        if (cmdParms != null)
        {
            foreach (OracleParameter parm in cmdParms)
                cmd.Parameters.Add(parm);
        }
    }
    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string SQLString)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand cmd = new OracleCommand(SQLString, connection))
            {
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    connection.Close();
                    throw new Exception(e.Message);
                }
            }
        }
    }


    public static object GetSingle(string SQLString, params OracleParameter[] cmdParms)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand cmd = new OracleCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    throw new Exception(e.Message);
                }
            }
        }
    }
#endregion	


    #region blob类型的数据文件读写
    
    /// <summary> 
    /// 文件写入到 Oracle Blob 字段中(新增)。 
    /// </summary> 
    /// <param name="keyData">主键列值,tableName表中新增BLOB类型数据的主键值</param> 
    /// <param name="fileName">文件名</param> 
    /// <param name="key">主键列名,tableName表中新增BLOB类型数据的主键</param> 
    /// <param name="blobColName">blobColName列名</param> 
    /// <param name="tableName">表名</param> 
    public void WriteBlob(string fileName, string tableName, string blobColName, string key, int keyData)
    {
        string strSql = "select * from " + tableName;
        using (OracleConnection connection = new OracleConnection(connectionString))
        {

            OracleDataAdapter da = new OracleDataAdapter(strSql, connection);
            OracleCommandBuilder CB = new OracleCommandBuilder(da);
            DataSet ds = new DataSet(tableName);
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read);
            byte[] BlobData = new byte[fs.Length];
            fs.Read(BlobData, 0, System.Convert.ToInt32(fs.Length));
            fs.Close();
            da.Fill(ds, tableName);

            DataRow newRow;
            newRow = ds.Tables[tableName].NewRow();
            newRow[key] = keyData;
            newRow[blobColName] = BlobData;
            ds.Tables[tableName].Rows.Add(newRow);
            da.Update(ds, tableName);
        }

    } 


    /// <summary>
    /// 读取 Oracle Blob 到文件中。
    /// </summary>
    /// <param name="idData">id 值</param>
    /// <param name="fileName">文件名</param>
    /// <param name="id">id 键</param>
    /// <param name="blob">blob 键</param>
    /// <param name="tableName">表名</param>
    public void ReadBlob(int idData, string fileName, string id, string blob, string tableName)
    {
        using (OracleConnection connection = new OracleConnection(connectionString)) 
        {
            try
            {
                connection.Open();
                OracleCommand cmd = connection.CreateCommand();

                // 利用事务处理（必须）
                OracleTransaction transaction = cmd.Connection.BeginTransaction();
                cmd.Transaction = transaction;

                // 获得 OracleLob 指针
                string sql = "select " + blob + " from " + tableName + " where " + id + " = " + idData;
                cmd.CommandText = sql;
                OracleDataReader dr = cmd.ExecuteReader();
                dr.Read();
                OracleLob tempLob = dr.GetOracleLob(0);
                dr.Close();

                // 读取 BLOB 中数据，写入到文件中
                FileStream fs = new FileStream(fileName, FileMode.Create);
                int length = 1048576;
                byte[] Buffer = new byte[length];
                int i;
                while ((i = tempLob.Read(Buffer, 0, length)) > 0)
                {
                    fs.Write(Buffer, 0, i);
                }
                fs.Close();
                tempLob.Clone();
                cmd.Parameters.Clear();

                // 提交事务
                transaction.Commit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        
        }
    }
   
    #endregion


}