威尼斯www.9778.com-威尼斯正版官方网站

Oracle 数据库操作类

日期:2019-12-25编辑作者:数据库

代码如下:using System; using System.Data; using System.Configuration; using System.Data.OracleClient; using System.Text; using System.Windows.Forms; using System.Xml; using Transactions; /// summary /// DB 的摘要表明 Written By Luos.Luo ,the creator of SalePlayer.Com /// /summary public class MyOraDB { public MyOraDB(卡塔尔(英语:State of Qatar) { } public int ExcuteSqlWithNoQuery(string vSql卡塔尔(英语:State of Qatar) { int vI = 0; OracleConnection vOracleConn = OpenOracleDBConn(卡塔尔(قطر‎; try { if (vOracleConn.State != ConnectionState.Open) { vOracleConn.Open(); } OracleCommand vOracleCmd = new OracleCommand(); vOracleCmd.Connection = vOracleConn; vOracleCmd.CommandText = vSql; vOracleCmd.CommandType = CommandType.Text; vI = vOracleCmd.ExecuteNonQuery(); } catch (Exception ex) { MyLog vMyLog = new MyLog(); vMyLog.WriteLog("MyOraDB", vSql, ex); } finally { CloseOracleDBConn(vOracleConn); } return vI; } public int ExcuteSqlWithSingleNum(string vSql) { int vI = 0; OracleConnection vOracleConn = OpenOracleDBConn(); try { if (vOracleConn.State != ConnectionState.Open) { vOracleConn.Open(); } OracleDataReader vOracleDataReader = CreateOracleDataReader(vSql); while (vOracleDataReader.Read()) { vI = vOracleDataReader.GetInt32(0); } vOracleDataReader.Close(); } catch (Exception ex) { MyLog vMyLog = new MyLog(); vMyLog.WriteLog("MyOraDB", vSql, ex); } finally { CloseOracleDBConn(vOracleConn); } return vI; } public string ExcuteSqlWithSingleString(string vSql) { StringBuilder vTempStrBld = new StringBuilder(); OracleConnection vOracleConn = OpenOracleDBConn(); try { if (vOracleConn.State != ConnectionState.Open) { vOracleConn.Open(); } OracleDataReader vOracleDataReader = CreateOracleDataReader(vSql); while (vOracleDataReader.Read()) { vTempStrBld.Append(vOracleDataReader.GetString(0)); } vOracleDataReader.Close(); } catch (Exception ex) { MyLog vMyLog = new MyLog(); vMyLog.WriteLog("MyOraDB", vSql, ex); } finally { CloseOracleDBConn(vOracleConn); } return vTempStrBld.ToString(); } public DataTable ExcuteSqlWithDataTable(string vSql) { DataTable vDataTable = new DataTable(); OracleConnection vOracleConn = OpenOracleDBConn(); try { if (vOracleConn.State != ConnectionState.Open) { vOracleConn.Open(); } OracleDataAdapter vOracleDataAdapter = new OracleDataAdapter(vSql, vOracleConn); vOracleDataAdapter.Fill(vDataTable); } catch (Exception ex) { MyLog vMyLog = new MyLog(); vMyLog.WriteLog("MyOraDB", vSql, ex); } finally { CloseOracleDBConn(vOracleConn); } return vDataTable; } public DataSet ExcuteSqlWithDataSet(string vSql) { DataSet vDataSet = new DataSet(); OracleConnection vOracleConn = OpenOracleDBConn(); try { if (vOracleConn.State != ConnectionState.Open) { vOracleConn.Open(); } OracleDataAdapter vOracleDataAdapter = new OracleDataAdapter(vSql, vOracleConn); vOracleDataAdapter.Fill(vDataSet); } catch (Exception ex) { MyLog vMyLog = new MyLog(); vMyLog.WriteLog("MyOraDB", vSql, ex); } finally { CloseOracleDBConn(vOracleConn); } return vDataSet; } public string ExcuteSqlTransactionWithString(string[] vSqlArray) { int vI = vSqlArray.Length; string vSql = string.Empty; OracleConnection vOracleConn = OpenOracleDBConn(); if (vOracleConn.State != ConnectionState.Open) { vOracleConn.Open(); } OracleTransaction vOracleTrans = vOracleConn.BeginTransaction(IsolationLevel.ReadCommitted); OracleCommand vOracleCmd = new OracleCommand(); vOracleCmd.Connection = vOracleConn; vOracleCmd.Transaction = vOracleTrans; try { for (int i = 0; i vI; i++) { if (string.IsNullOrEmpty(vSqlArray[i]) == false) { vSql = vSqlArray[i]; vOracleCmd.CommandText = vSql; vOracleCmd.ExecuteNonQuery(); } } vOracleTrans.Commit(); } catch (Exception ex) { vOracleTrans.Rollback(); CloseOracleDBConn(vOracleConn); MyLog vMyLog = new MyLog(); vMyLog.WriteLog("", vSql, ex); return ex.Message; } CloseOracleDBConn(vOracleConn); return "SUCCESS"; } public void ExcuteProcedureWithNonQuery(string vProcedureName, OracleParameter[] parameters) { OracleConnection vOracleConn = OpenOracleDBConn(); try { if (vOracleConn.State != ConnectionState.Open卡塔尔(英语:State of Qatar) { vOracleConn.Open(卡塔尔(英语:State of Qatar); } OracleCommand vOracleCmd = new OracleCommand(卡塔尔国; vOracleCmd.Connection = vOracleConn; vOracleCmd.CommandText = vProcedureName; vOracleCmd.CommandType = CommandType.StoredProcedure; foreach (OracleParameter parameter in parameters卡塔尔国 { vOracleCmd.Parameters.Add(parameter卡塔尔(قطر‎; } vOracleCmd.ExecuteNonQuery(卡塔尔(英语:State of Qatar); } catch (Exception ex卡塔尔(英语:State of Qatar) { MyLog vMyLog = new MyLog(卡塔尔; WriteLog("非常音信:ExcuteProcedureWithNonQuery:" + ex.Message卡塔尔; } finally { CloseOracleDBConn(vOracleConn卡塔尔; } } public string ExcuteProcedureWithSingleString(string vProcedureName, OracleParameter[] parameters) { string vTempStr = string.Empty; OracleParameter vOutMessage; OracleConnection vOracleConn = OpenOracleDBConn(); try { if (vOracleConn.State != ConnectionState.Open) { vOracleConn.Open(); } OracleCommand vOracleCmd = new OracleCommand(); vOracleCmd.Connection = vOracleConn; vOracleCmd.CommandText = vProcedureName; vOracleCmd.CommandType = CommandType.StoredProcedure; vOutMessage = new OracleParameter("O_FLAG", OracleType.VarChar卡塔尔(قطر‎; vOutMessage.Direction = ParameterDirection.Output; vOutMessage.Size = 100; vOracleCmd.Parameters.Add(vOutMessage卡塔尔; foreach (OracleParameter parameter in parameters卡塔尔国 { vOracleCmd.Parameters.Add(parameter卡塔尔(英语:State of Qatar); } vOracleCmd.ExecuteNonQuery(卡塔尔; vOracleCmd.Dispose(卡塔尔国; vOracleCmd = null; vTempStr = vOutMessage.Value.ToString(卡塔尔(英语:State of Qatar); } catch (Exception ex卡塔尔国 { MyLog vMyLog = new MyLog(卡塔尔(قطر‎; WriteLog("相当消息:ExcuteProcedureWithNonQuery:" + ex.Message卡塔尔国; } finally { CloseOracleDBConn(vOracleConn卡塔尔; } return vTempStr; } public int ExcuteProcedureWithSingleNum(string vProcedureName, OracleParameter[] parameters) { int vI = 0; OracleParameter vOutMessage; OracleConnection vOracleConn = OpenOracleDBConn(); try { if (vOracleConn.State != ConnectionState.Open) { vOracleConn.Open(); } OracleCommand vOracleCmd = new OracleCommand(); vOracleCmd.Connection = vOracleConn; vOracleCmd.CommandText = vProcedureName; vOracleCmd.CommandType = CommandType.StoredProcedure; vOutMessage = new OracleParameter("O_FLAG", OracleType.Int32卡塔尔; vOutMessage.Direction = ParameterDirection.Output; vOutMessage.Size = 100; vOracleCmd.Parameters.Add(vOutMessage卡塔尔(英语:State of Qatar); foreach (OracleParameter parameter in parameters卡塔尔 { vOracleCmd.Parameters.Add(parameter卡塔尔(قطر‎; } vOracleCmd.ExecuteNonQuery(卡塔尔国; vOracleCmd.Dispose(卡塔尔(قطر‎; vOracleCmd = null; vI = System.Convert.ToInt32(vOutMessage.Value卡塔尔国; } catch (Exception ex卡塔尔国 { MyLog vMyLog = new MyLog(卡塔尔(英语:State of Qatar); WriteLog("非凡消息:ExcuteProcedureWithNonQuery:" + ex.Message卡塔尔国; } finally { CloseOracleDBConn(vOracleConn卡塔尔(英语:State of Qatar); } return vI; } /// summary /// Creates the parameter. /// /summary /// param name="name"The name./param /// param name="dbType"Type of the db./param /// param name="size"The value size/param /// param name="direction"The direction./param /// param name="paramValue"The param value./param /// returns/returns public OracleParameter CreateParameter(string vProcedureName, OracleType vOracleType, int vSize, ParameterDirection vDirection, object vParamValue卡塔尔 { OracleParameter vOracleParameter = new OracleParameter(卡塔尔(英语:State of Qatar); vOracleParameter.ParameterName = vProcedureName; vOracleParameter.OracleType = vOracleType; vOracleParameter.Size = vSize; vOracleParameter.Direction = vDirection; if (!(vOracleParameter.Direction == ParameterDirection.Output)) { vOracleParameter.Value = vParamValue; } return vOracleParameter; } private OracleConnection OpenOracleDBConn() { string vConnStr = string.Empty; string vOraDBName = System.Configuration.ConfigurationManager.AppSettings["OraDBName"]; switch (vOraDBName) { case "MESDB_03": vConnStr = "Data Source=SZMESDB;Persist Security Info=True;User ID=MESDB_03;Password=MESDB;Unicode=True;"; break; case "MESDBTEST_03": vConnStr = "Data Source=SZMESDB;Persist Security Info=True;User ID=MESDB_03;Password=MESDB;Unicode=True;"; break; default: vConnStr = "Data Source=SZMESDB;Persist Security Info=True;User ID=MESDBTEST_03;Password=MESDB;Unicode=True;"; break; } OracleConnection vOracleConnection = new OracleConnection(vConnStr); if (vOracleConnection.State != ConnectionState.Open) { vOracleConnection.Open(); } return vOracleConnection; } private void CloseOracleDBConn(OracleConnection vOracleConnection) { if (vOracleConnection.State == ConnectionState.Open) { vOracleConnection.Close(); } } private OracleDataReader CreateOracleDataReader(string vSql) { OracleConnection vOracleConn = OpenOracleDBConn(); OracleCommand vOracleCommand = new OracleCommand(vSql, vOracleConn); OracleDataReader vOracleDataReader = vOracleCommand.ExecuteReader(); return vOracleDataReader; } private OracleDataAdapter CreateOleDbDataAdapter(string vSql) { OracleConnection vOracleConn = OpenOracleDBConn(); OracleDataAdapter vOracleDataAdapter = new OracleDataAdapter(vSql, vOracleConn); CloseOracleDBConn(vOracleConn); return vOracleDataAdapter; } public string GetDateTimeNow() { return System.DateTime.Now.ToString("u").Replace("Z", "").Replace("z", ""); } private void WriteLog(string vMessage) { try { string vTempValue = string.Empty; string vFilePath = Application.StartupPath; string vXmlPath = System.Configuration.ConfigurationManager.AppSettings["LogAddress"]; vXmlPath = vFile帕特h + vXmlPath; XmlDocument xmlDoc = new XmlDocument(卡塔尔(英语:State of Qatar); xmlDoc.Load(vXmlPath卡塔尔(英语:State of Qatar); XmlNode root = xmlDoc.SelectSingleNode("//root"卡塔尔(قطر‎; XmlElement xe = xmlDoc.CreateElement("Node"卡塔尔(قطر‎;//创制贰个节点 XmlElement xesub01 = xmlDoc.CreateElement("RowNum"卡塔尔(قطر‎; xesub01.InnerText = root.ChildNodes.Count.ToString(卡塔尔(英语:State of Qatar); xe.AppendChild(xesub01卡塔尔国;//加多到节点中 XmlElement xesub02 = xmlDoc.CreateElement("Message"卡塔尔; xesub02.InnerText = vMessage; xe.AppendChild(xesub02卡塔尔(قطر‎;//增多到节点中 XmlElement xesub03 = xmlDoc.CreateElement("InserTime"卡塔尔(英语:State of Qatar); xesub03.InnerText = GetDateTimeNow(卡塔尔; xe.AppendChild(xesub03卡塔尔国;//增多到节点中 root.AppendChild(xe卡塔尔;//加多到节点中 xmlDoc.Save(vXmlPath卡塔尔国; root = null; xmlDoc = null; } catch (Exception ex卡塔尔(英语:State of Qatar) { WriteLog(ex.Message卡塔尔国; } } }

sqlCom.CommandText = sqlStr.ToString(); //为sqlcommand对象钦赐sql语句

/// <summary>
/// OracleConnection 的摘要表达
/// </summary>
public class OracleConn
{
public OracleConn()
{
//
// TODO: 在这里处增加布局函数逻辑
//
}

/// <summary>
/// 删除数据
/// </summary>
/// <param name="intId"></param>
public void DeleteData(int intId)
{
using (OracleConnection con = new OracleConnection(connString))
{
con.Open(卡塔尔(قطر‎; //展开数据库连接

/// <summary>
/// 查询表数据
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static DataSet SelectData(string sql)
{
DataSet ds = new DataSet();
try
{
using (OracleConnection conn = new OracleConnection(connString))
{
//conn.Open();
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter adapter = new OracleDataAdapter;
adapter.Fill;
return ds;
}
}
catch (Exception e)
{
Console.Write;
}
return null;
}

sqlCom.Connection = con; //为sqlcommand对象钦命链接对象
OracleData艾达pter sqlDa = new OracleDataAdapter; //评释数据库适配器
OracleCommandBuilder sqlBuilder = new OracleCommandBuilder;
sqlDa.Fill; //填充表
}

foreach (string item in sqlText)
{
cmd.CommandText = item;

sqlcmd.ExecuteNonQuery(卡塔尔(قطر‎; //钦点更新语句
}
}

finally
{
conn.Close();
}

/// <summary>
/// 增多操作方法
/// </summary>
/// <param name="ht"></param>
public void InsertData(Hashtable ht)
{
//int id = Convert.ToInt32(ht[":ID"].ToString;
OracleConnection conn = new OracleConnection(connString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
string sql = "insert into Testtable (id,name,Address,Tel,Remark)values (Testtable_Id_Seq.NEXTVAL,:Name,:Address,:Tel,:Remark)";
cmd.CommandText = sql;
OracleParameter[] param =
{
//new OracleParameter(":Id",OracleDbType.Int32),
new OracleParameter(":Name",OracleDbType.Varchar2),
new OracleParameter(":Address",OracleDbType.Varchar2),
new OracleParameter(":Tel",OracleDbType.Varchar2),
new OracleParameter(":Remark",OracleDbType.Varchar2)
};
//param[0].Value = id;
param[0].Value = ht[":Name"].ToString();
param[1].Value = ht[":Address"].ToString();
param[2].Value = ht[":Tel"].ToString();
param[3].Value = ht[":Remark"].ToString();
foreach (OracleParameter p in param)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add;
}
}
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Web;

static string connString = DBConnection.connString;

using (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{

}

/// <summary>
/// 查询数据
/// </summary>
/// <returns></returns>
public OracleCommand GetData()
{
//使用using语句举办数据库连接
using (OracleConnection sqlCon = new OracleConnection(connString))
{
sqlCon.Open(卡塔尔; //展开数据库连接

sqlcmd.ExecuteNonQuery(卡塔尔国; //钦点更新语句
con.Close();
}
}

OracleCommand cmd = new OracleCommand();

public static Boolean ExeTransaction(List<string> sqlText)
{

OracleCommand sqlcom = new OracleCommand(卡塔尔; //创制数据库命令对象
sqlcom.CommandText = "select * from testTable"; //为命令对象钦赐施行语句
sqlcom.Connection = sqlCon; //为命令对象钦命连接对象
OracleDataReader reader = sqlcom.ExecuteReader();
while (reader.Read
{
reader.GetInt32;
}
sqlCon.Close();
return sqlcom;
}
}

return dt;
}

cmd.ExecuteNonQuery();
}
tran.Commit();

//创制参数集合,并向sqlcom中加多参数集结
OracleParameter sqlParam = new OracleParameter("@id", intId);
sqlcmd.Parameters.Add;

cmd.Connection = conn;

OracleCommand sqlcmd = new OracleCommand(卡塔尔国; //创立数据库命令对象
sqlcmd.CommandText = "delete from testTable where id=@id"; //为命令对象钦定实行语句
sqlcmd.Connection = con; //为命令对象钦命连接对象

//增改删
/// <summary>
/// 执行sql 语句
/// </summary>
/// <param name="sql"></param>
/// <returns>再次来到影响的行数</returns>
public static Boolean AddUpDelData(string sql)
{
try
{
using (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(sql, conn);
int row = cmd.ExecuteNonQuery();
conn.Close();
if (row > 0)
{
return true;
}
}
}
catch (Exception e)
{
Console.Write;
}
return false;
}

/// <summary>
/// 修改数据
/// </summary>
/// <param name="intId"></param>
/// <param name="strText"></param>
public void UpdateData(int intId, string strText)
{
using (OracleConnection con = new OracleConnection(connString))
{
con.Open(卡塔尔(英语:State of Qatar); //张开数据库连接

/// <summary>
/// 查询数据
/// </summary>
/// <returns></returns>
public DataTable GetTableData()
{
DataTable dt = new DataTable(卡塔尔国; //制造数据库表
using (OracleConnection con = new OracleConnection(connString))
{
con.Open(卡塔尔(英语:State of Qatar); //张开数据库链接
OracleCommand sqlCom = new OracleCommand(卡塔尔; //证明并创设数据库命令集
StringBuilder sqlStr = new StringBuilder(); //声明sql语句
sqlStr.Append("select * from testTable ORDER BY id "); //获取sql语句

//创立参数集合,并向sqlcom中加多参数会集
OracleParameter[] sqlParam = { new OracleParameter(":str", strText), new OracleParameter(":id", intId) };
sqlcmd.Parameters.AddRange;

/// <summary>
/// 加多操作方法
/// </summary>
/// <param name="name"></param>
/// <param name="address"></param>
/// <param name="tel"></param>
/// <param name="remark"></param>
/// <returns></returns>
public int AddData(string name, string address, string tel, string remark)
{
OracleConnection conn = new OracleConnection(connString);
try
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
string sql = "insert into Testtable (id,name,Address,Tel,Remark)values (Testtable_Id_Seq.NEXTVAL,'" + name + "','" + address + "','" + tel + "','" + remark + "')";
cmd.CommandText = sql;
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}

cmd.Transaction = tran;

catch (Exception et)
{
tran.Rollback();
return false;
}

}

OracleCommand sqlcmd = new OracleCommand(卡塔尔; //创制数据库命令对象
sqlcmd.CommandText = "update testTable set name=:str where id=:id"; //为命令对象钦定推行语句
sqlcmd.Connection = con; //为命令对象钦命连接对象

}

}

public int GetConn()
{
OracleConnection conn = new OracleConnection(connString);
int result = 0;
try
{
conn.Open();
result = 1;
}
catch (Exception ex)
{
throw ;
}
finally
{
conn.Close();
}
return result;
}

return true;

本文由威尼斯www.9778.com发布于数据库,转载请注明出处:Oracle 数据库操作类

关键词:

Oracle三种链接方式的区别

代码如下:select a.f_username from ( SELECT/*+parallel(gu,4)*/distinct gu.f_username FROM t_base_succprousergu where gu.f_expectenddate (select tr...

详细>>

新Orcas语言特性

New "Orcas" Language Feature: Query Syntax Saturday, April 21, 2007 2:12   LINQ To SQL 语法及实例大全   上个月我开始了一个贴子系列,...

详细>>

Oracle数据库及应用程序优化开荒者网络Oracle

[NextPage] 2.3优化规划重演日志文件 2.3.1重演日志文件的大小由于数据库在选用重演日志文件时是循环使用它们的,而且...

详细>>

oracle 10g常见问题精选 (2)

安装Oracle9I出现"加载数据库时出错:areasQueries错误",提示停止该组件或所有组件安装,之后安装不能进行。 在penti...

详细>>