C# 数据库的基本操作（Oracle）

2020年8月21日 23:24:52Linux大全评论977 views阅读模式

配置文件如下：

<appSettings>
<add key="ServiceName" value="lev2" />
<add key="UserID" value="DBNAME" />
<add key="UserPsw" value="DBPASSWORD" />
</appSettings>

基本操作类如下：

using System;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using System.Collections;
using System.Text;
namespace Common
{
/// <summary>
/// CommonClass 的摘要说明
/// </summary>
public class OracleServerDAL
{
OracleConnection connect = null;
OracleCommand command = null;
OracleDataReader reader = null;
OracleDataAdapter adapter = null;
DataSet ds = null;
public OracleServerDAL()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public string GetConnStr()
{
string[] configStrings = ConfigHelper.ReadDBConfig();
string serviceName = "";
string userID = "";
string userPSW = "";
if (configStrings[0] == null || configStrings[1] == null || configStrings[2] == null)
{
return "";
}
serviceName = ConfigHelper.GetAppConfig("ServiceName");
userID = configStrings[1];
userPSW = configStrings[2];
string constr = string.Format("DATA SOURCE={0};user={1};password={2};", serviceName, userID, userPSW);
return constr;
}
/// <summary>
/// 建立与数据库的连接
/// </summary>
public void connectionData()
{
try
{
if (connect == null)
{
string constr = GetConnStr();
if (string.IsNullOrEmpty(constr))
{
connect = new OracleConnection(DefineConst.constr);
}
else
{
connect = new OracleConnection(constr);
}
}
if (connect.State == ConnectionState.Closed)
{
connect.Open();
}
}
catch (Exception e)
{
Console.Write(e.Message);
}
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public static OracleConnection GetConnection()
{
return new OracleConnection(DefineConst.constr);
}
/// <summary>
/// 获取数据库连接字符串
/// </summary>
/// <returns></returns>
public static string GetConnectionStr()
{
string[] configStrings = ConfigHelper.ReadDBConfig();
string serviceName = "";
string userID = "";
string userPSW = "";
if (configStrings[0] == null || configStrings[1] == null || configStrings[2] == null)
{
return "";
}
serviceName = ConfigHelper.GetAppConfig("ServiceName");
userID = configStrings[1];
userPSW = configStrings[2];
string constr = string.Format("DATA SOURCE={0};user={1};password={2};", serviceName, userID, userPSW);
return constr;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void closeConnect()
{
try
{
if (connect != null)
{
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
connect.Dispose();
connect = null;
}
}
catch (Exception e)
{
Console.Write(e.Message);
}
}
/// <summary>
/// 返回查询记录集DataSet
/// </summary>
/// <returns>System.Data.OracleClient.OracleDataReader</returns>
public DataSet returnRecordSet(string sql)
{
connectionData();
ds = new DataSet();
adapter = new OracleDataAdapter(sql, connect);
adapter.Fill(ds);
closeConnect();
return ds;
}
/// <summary>
/// 返回查询记录数
/// </summary>
/// <returns>int</returns>
public int returnRecordCount(string sql)
{
int rowCount = 0;
connectionData();
command = new OracleCommand(sql, connect);
try
{
rowCount = command.ExecuteNonQuery();
}
catch
{
//throw;
}
closeConnect();
return rowCount;
}
/// <summary>
/// 返回第一行第一列的值
/// </summary>
/// <returns>int</returns>
public object returnRCValue(string sql)
{
object obj = null;
connectionData();
command = new OracleCommand(sql, connect);
try
{
obj = command.ExecuteScalar();
}
catch
{
//throw;
}
closeConnect();
return obj;
}
/// <summary>
/// sql形式 select COUNT(*) from 表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int callRecordCount(string sql)
{
object obj = null;
connectionData();
command = new OracleCommand(sql, connect);
try
{
obj = command.ExecuteScalar();
}
catch
{
//throw;
}
closeConnect();
return Convert.ToInt16(obj);
}
/// <summary>
/// 返回结果状态1为成功0为失败
/// </summary>
///<param name="myArr">存储SQL语句的集合</param>
/// <returns>int</returns>
public int ExucteTransaction(ArrayList myArr)
{
int returnValue = 0;
connectionData();
command = new OracleCommand();
command.Connection = connect;
OracleTransaction myTran = connect.BeginTransaction();
command.Transaction = myTran;
try
{
for (int i = 0; i < myArr.Count; i++)
{
command.CommandText = myArr[i].ToString();
command.ExecuteNonQuery();
}
myTran.Commit();
returnValue = 1;
}
catch
{
myTran.Rollback();
returnValue = 0;
}
finally
{
connect.Close();
}
return returnValue;
}
/// <summary>
/// 返回DataReader,非安全代码，必须手动关闭连接!
/// </summary>
/// <param name="sql">SQL查询语句</param>
/// <returns>SqlDataReader</returns>
public OracleDataReader retrunDataReader(string sql)
{
command = new OracleCommand(sql, connect);
try
{
reader = command.ExecuteReader();
}
catch
{
//throw;
}
return reader;
}
//　返回Command
public OracleCommand returnCommand(string sql, OracleParameter[] parms)
{
command = new OracleCommand(sql, connect);
foreach (OracleParameter parm in parms)
{
command.Parameters.Add(parm);
}
return command;
}
/// <summary>
/// 调用存储过程,非安全代码，必须手动关闭连接!
/// </summary>
/// <param name="proceName">存储过程名</param>
/// <param name="parms">存储过程参数</param>
/// <param name="sdr">返回SqlDataReader对象</param>
public void RunProce(string proceName, OracleParameter[] parms, out OracleDataReader sdr)
{
command = CreateCommand(proceName, parms);
sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 调用存储过程,非安全代码，必须手动关闭连接!
/// </summary>
/// <param name="proceName"></param>
/// <param name="parms"></param>
/// <param name="result"></param>
public void RunProce(string proceName,OracleParameter[] parms, out int result)
{
command = CreateCommand(proceName, parms);
result = command.ExecuteNonQuery();
}
/// <summary>
/// 调用存储过程,非安全代码，必须手动关闭连接!
/// </summary>
/// <param name="proceName"></param>
/// <param name="parms"></param>
/// <param name="ds"></param>
public void RunProce(string proceName, OracleParameter[] parms, out DataSet ds)
{
command = CreateCommand(proceName, parms);
adapter = new OracleDataAdapter(command);
ds = new DataSet();
try
{
adapter.Fill(ds, "result");
}
catch (Exception e)
{
}
}
/// <summary>
/// 调用存储过程,返回多个游标
/// </summary>
/// <param name="proceName"></param>
/// <param name="parms"></param>
/// <param name="ds"></param>
public void RunMultiCurProce(string proceName, OracleParameter[] parms, out DataSet ds)
{
command = CreateCommand(proceName, parms);
adapter = new OracleDataAdapter(command);
ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception e)
{
}
}
/// <summary>
/// 调用存储过程,非安全代码，必须手动关闭连接!
/// </summary>
/// <param name="proceName"></param>
/// <param name="parms"></param>
/// <param name="result"></param>
public void RunProce(string proceName, OracleParameter[] parms)
{
command = CreateCommand(proceName, parms);
command.CommandType = CommandType.StoredProcedure;
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write(e.Message);
}
}
/// <summary>
/// 调用存储过程,非安全代码，必须手动关闭连接!
/// </summary>
/// <param name="proceName"></param>
/// <param name="parms"></param>
/// <param name="ds"></param>
public void RunProce(string proceName, string tableName, OracleParameter[] parms, out DataSet ds)
{
command = CreateCommand(proceName, parms);
adapter = new OracleDataAdapter(command);
ds = new DataSet();
try
{
adapter.Fill(ds, tableName);
}
catch(Exception e)
{
};
}
/// <summary>
/// 创建SqlComand对象执行存储过程
/// </summary>
/// <param name="proceName">存储过程名</param>
/// <param name="parms">存储过程参数</param>
/// <returns>返回SqlCommand对象</returns>
private OracleCommand CreateCommand(string proceName, OracleParameter[] parms)
{
command = new OracleCommand(proceName, connect);
command.CommandType = CommandType.StoredProcedure;
if (parms != null)
{
foreach (OracleParameter parm in parms)
{
command.Parameters.Add(parm);
}
}
return command;
}
/// <summary>
/// 初始化页面Table数据
/// </summary>
/// <param name="field"></param>
/// <param name="table"></param>
/// <param name="condition"></param>
public void getInitTable(string field, string table, string condition)
{
StringBuilder SQL = new StringBuilder();
SQL.Append("select " + field + " from " + table + " where " + condition);
retrunDataReader(SQL.ToString());
}
/// <summary>
/// 仅执行数据库操作
/// </summary>
/// <param name="sql"></param>
public void SqlOpt(string sql)
{
connectionData();
command = new OracleCommand(sql);
command.Connection = connect;
try
{
command.ExecuteNonQuery();
}
catch (OracleException se)
{
throw se;
}
finally
{
}
connect.Close();
}
}
}

public string GS_Info(string parm1, string parm2, string parm3)
{
OracleParameter[] parm = null;
parm = new OracleParameter[4] { new OracleParameter("parm1", OracleType.VarChar, 10),
new OracleParameter("parm2", OracleType.VarChar, 10),
new OracleParameter("parm3", OracleType.VarChar, 10),
new OracleParameter("Re_CURSOR", OracleType.Cursor, 100000) };
parm[0].Direction = System.Data.ParameterDirection.Input;
parm[1].Direction = ParameterDirection.Input;
parm[2].Direction = ParameterDirection.Input;
parm[3].Direction = ParameterDirection.Output;
parm[0].Value = parm1;
parm[1].Value = parm2;
parm[2].Value = parm3;
DataSet ds = null;
OracleServerDAL OSD = new OracleServerDAL();
string result = "";
try
{
OSD.connectionData();
OSD.RunProce("ProceName", "GS_Table", parm, out ds);
result = CommonFormOpt.SerializeDataTableXml(ds.Tables[0]);
}
catch (Exception)
{
}
finally
{
OSD.closeConnect();
}
return result;
}