using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OracleClient;
using System.Drawing;
using System.IO;
namespace DbClass
{
/// <summary>
/// Db_Class 的摘要说明。
/// </summary>
public class Db_Class
{
private OracleConnection Conn;
private OracleCommand cmd;
//构造函数
public Db_Class()
{
Conn = new OracleConnection(string.Format("data source={0};user id={1};password={2}", "landdb", "landuser", "land")); //连接数据库
}
//打开数据源链接
public OracleConnection Db_Conn()
{
Conn.Open();
return Conn;
}
//关闭数据链接
public void close()
{
Conn.Close();
public OracleDataReader Db_CreateReader(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
OracleDataReader Rs = cmd.ExecuteReader();
return Rs;
}
finally
{
this.close();
}
}
//返回DataReader数据集,下面的SQL是存储过程
public OracleDataReader Db_CommandReader(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleDataReader Rs = cmd.ExecuteReader();
return Rs;
}
finally
{
this.close();
}
}
//返回数据DataSet数据集
public DataSet Db_CreateDataSet(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
OracleDataAdapter Adpt = new OracleDataAdapter(cmd);
DataSet Ds = new DataSet();
Adpt.Fill(Ds, "NewTable");
return Ds;
}
finally
{
this.close();
}
}
//返回数据DataReader数据集,不需要返回数据的修改,删除可以使用本函数
public bool Db_ExecuteNonquery(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
try
{
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
return false;
}
}
finally
{
this.close();
}
}
//返回数据DataReader数据集,返回数据
public String Db_Executequery(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
try
{
return cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
return "";
}
}
finally
{
this.close();
}
}
//返回blob数据
public MemoryStream getBlob(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
cmd.CommandType = CommandType.Text;//是sql
OracleDataReader Rs = cmd.ExecuteReader();
if (Rs.Read()) //循环到下一条记录
{
byte[] image_bytes = (byte[])Rs.GetValue(0);
MemoryStream ms = new MemoryStream(image_bytes);
return ms;
}
else
return null;
}
finally
{
this.close();
}
}
private static byte[] ReadFile(string path)
{
FileStream pFileStream = null;
byte[] pReadByte = new byte[0];
try
{
pFileStream = new FileStream(path, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(pFileStream);
r.BaseStream.Seek(0, SeekOrigin.Begin); //将文件指针设置到文件开
pReadByte = r.ReadBytes((int)r.BaseStream.Length);
return pReadByte;
}
catch
{
return pReadByte;
}
finally
{
if (pFileStream != null)
pFileStream.Close();
}
}
//设置blob
public bool SetBlob(string SQL, string FileName)
{
try
{
Db_Conn();
System.Data.OracleClient.OracleTransaction tran = Conn.BeginTransaction();//启动事务
cmd = new OracleCommand(SQL, Conn);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;//是sql
OracleDataReader Rs = cmd.ExecuteReader();
if (Rs.Read()) //循环到下一条记录
{
System.Data.OracleClient.OracleLob lob = Rs.GetOracleLob(0);//获得CLOB对象
lob.Erase();
byte[] image_bytes = ReadFile(FileName);
lob.Write(image_bytes, 0, image_bytes.Length);//写入新内容
tran.Commit();//提交事务
cmd.Dispose();
return true;
}
else
return false;
}
finally
{
this.close();
}
}
//存储过程返回,
public DataSet getDatasetbyStoredProcedure(string storedProcName, OracleParameter[] parameters)
{
try
{
Db_Conn();
try
{
cmd = new OracleCommand();
cmd.Connection = Conn;
cmd.CommandText = storedProcName;//声明存储过程名
cmd.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();//执行存储过程
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "test");
return ds;
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message);
return null;
}
}
finally
{
this.close();
}
}
public void RunProcedure(string storedProcName, OracleParameter[] parameters)
{
try
{
Db_Conn();
cmd = new OracleCommand();
cmd.Connection = Conn;
cmd.CommandText = storedProcName;//声明存储过程名
cmd.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();//执行存储过程
}
finally
{
this.close();
}
}
}
}
using System.Data;
using System.Windows.Forms;
using System.Data.OracleClient;
using System.Drawing;
using System.IO;
namespace DbClass
{
/// <summary>
/// Db_Class 的摘要说明。
/// </summary>
public class Db_Class
{
private OracleConnection Conn;
private OracleCommand cmd;
//构造函数
public Db_Class()
{
Conn = new OracleConnection(string.Format("data source={0};user id={1};password={2}", "landdb", "landuser", "land")); //连接数据库
}
//打开数据源链接
public OracleConnection Db_Conn()
{
Conn.Open();
return Conn;
}
//关闭数据链接
public void close()
{
Conn.Close();
}
public OracleDataReader Db_CreateReader(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
OracleDataReader Rs = cmd.ExecuteReader();
return Rs;
}
finally
{
this.close();
}
}
//返回DataReader数据集,下面的SQL是存储过程
public OracleDataReader Db_CommandReader(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleDataReader Rs = cmd.ExecuteReader();
return Rs;
}
finally
{
this.close();
}
}
//返回数据DataSet数据集
public DataSet Db_CreateDataSet(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
OracleDataAdapter Adpt = new OracleDataAdapter(cmd);
DataSet Ds = new DataSet();
Adpt.Fill(Ds, "NewTable");
return Ds;
}
finally
{
this.close();
}
}
//返回数据DataReader数据集,不需要返回数据的修改,删除可以使用本函数
public bool Db_ExecuteNonquery(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
try
{
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
return false;
}
}
finally
{
this.close();
}
}
//返回数据DataReader数据集,返回数据
public String Db_Executequery(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
try
{
return cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
return "";
}
}
finally
{
this.close();
}
}
//返回blob数据
public MemoryStream getBlob(string SQL)
{
try
{
Db_Conn();
cmd = new OracleCommand(SQL, Conn);
cmd.CommandType = CommandType.Text;//是sql
OracleDataReader Rs = cmd.ExecuteReader();
if (Rs.Read()) //循环到下一条记录
{
byte[] image_bytes = (byte[])Rs.GetValue(0);
MemoryStream ms = new MemoryStream(image_bytes);
return ms;
}
else
return null;
}
finally
{
this.close();
}
}
private static byte[] ReadFile(string path)
{
FileStream pFileStream = null;
byte[] pReadByte = new byte[0];
try
{
pFileStream = new FileStream(path, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(pFileStream);
r.BaseStream.Seek(0, SeekOrigin.Begin); //将文件指针设置到文件开
pReadByte = r.ReadBytes((int)r.BaseStream.Length);
return pReadByte;
}
catch
{
return pReadByte;
}
finally
{
if (pFileStream != null)
pFileStream.Close();
}
}
//设置blob
public bool SetBlob(string SQL, string FileName)
{
try
{
Db_Conn();
System.Data.OracleClient.OracleTransaction tran = Conn.BeginTransaction();//启动事务
cmd = new OracleCommand(SQL, Conn);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;//是sql
OracleDataReader Rs = cmd.ExecuteReader();
if (Rs.Read()) //循环到下一条记录
{
System.Data.OracleClient.OracleLob lob = Rs.GetOracleLob(0);//获得CLOB对象
lob.Erase();
byte[] image_bytes = ReadFile(FileName);
lob.Write(image_bytes, 0, image_bytes.Length);//写入新内容
tran.Commit();//提交事务
cmd.Dispose();
return true;
}
else
return false;
}
finally
{
this.close();
}
}
//存储过程返回,
public DataSet getDatasetbyStoredProcedure(string storedProcName, OracleParameter[] parameters)
{
try
{
Db_Conn();
try
{
cmd = new OracleCommand();
cmd.Connection = Conn;
cmd.CommandText = storedProcName;//声明存储过程名
cmd.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();//执行存储过程
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "test");
return ds;
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message);
return null;
}
}
finally
{
this.close();
}
}
public void RunProcedure(string storedProcName, OracleParameter[] parameters)
{
try
{
Db_Conn();
cmd = new OracleCommand();
cmd.Connection = Conn;
cmd.CommandText = storedProcName;//声明存储过程名
cmd.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();//执行存储过程
}
finally
{
this.close();
}
}
}
}