using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
namespace cl
{
class Class_DB
{
private OracleConnection con;
private string is_ConString;
public Class_DB()
{
is_ConString = "Data Source = 服务名; User ID = 用户名; Password = 密码";
con = new OracleConnection();
con.ConnectionString = is_ConString;
}
public void Open()
{
if (con.State == System.Data.ConnectionState.Open)
{
return;
}
if (con == null)
{
con = new OracleConnection(is_ConString);
}
con.Open();
}
public void Close()
{
if (con != null)
{
con.Close();
}
else
{
con = new OracleConnection(is_ConString);
con.Close();
}
}
public string uf_TestDBConn()
{
string ls_Rtn = "";
if (con.State == System.Data.ConnectionState.Open)
{
ls_Rtn = "数据库自身已打开;";
}
else
{
if (con == null)
{
con = new OracleConnection(is_ConString);
}
try
{
con.Open();
ls_Rtn = "数据库打开成功;";
}
catch (Exception e)
{
return e.Message;
}
}
/*
string ls_Sql = "Select To_Char(SysDate, 'yyyy-mm-dd hh24:mi:ss') From Dual";
OracleCommand cmd;
try
{
cmd = new OracleCommand(ls_Sql, con);
ls_Rtn += cmd.ExecuteOracleScalar().ToString() + ";";
}
catch
{
ls_Rtn += "执行SQL报错;";
}
*/
try
{
con.Close();
ls_Rtn += "关闭数据库成功;";
}
catch
{
ls_Rtn += "关闭数据库错误;";
}
return ls_Rtn;
}
//获取1个值
public string f_Get_DataOne(string as_Sql, string as_Type, out string as_Err)
{
string ls_Rtn = "";
as_Err = "";
try
{
Open();
if (as_Type.ToUpper() == "SP")
{
ls_Rtn = uf_Get_DataOne_Sp(as_Sql, out as_Err);
}
else
{
ls_Rtn = uf_Get_DataOne_Sel(as_Sql, out as_Err);
}
}
catch (Exception ex)
{
as_Err = ex.Message;
ls_Rtn = "";
}
Close();
return ls_Rtn;
}
//获取1个值——存储过程方式
private string uf_Get_DataOne_Sp(string as_Sql, out string as_Err)
{
OracleCommand cmd;
string ls_Rtn = "";
try
{
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "sp_Xml_GetDataOne"; //声明存储过程名
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parameters1 = new OracleParameter("as_Sql", OracleType.VarChar, 1000);
OracleParameter parameters2 = new OracleParameter("as_Rtn", OracleType.VarChar, 200);
parameters1.Value = as_Sql;
parameters1.Direction = ParameterDirection.Input;
parameters2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parameters1);
cmd.Parameters.Add(parameters2);
cmd.ExecuteNonQuery(); //执行存储过程
ls_Rtn = parameters2.Value.ToString();
as_Err = "";
return ls_Rtn;
}
catch (Exception ex)
{
as_Err = ex.Message;
return "";
}
}
//获取1个值——直接执行SQL语句方式
private string uf_Get_DataOne_Sel(string as_Sql, out string as_Err)
{
OracleCommand cmd;
string ls_Rtn = "";
try
{
cmd = new OracleCommand(as_Sql, con);
ls_Rtn = cmd.ExecuteOracleScalar().ToString();
as_Err = "";
}
catch (Exception ex)
{
if (ls_Rtn == "")
{
as_Err = "";
}
else
{
as_Err = ex.Message;
}
ls_Rtn = "";
}
return ls_Rtn;
}
//获取数据集
public DataSet f_Get_DataSet(string as_Sql, out string as_Err)
{
DataSet lds_Rtn = null;
as_Err = "";
try
{
Open();
OracleDataAdapter loda;
DataSet lds_Temp = new DataSet();
loda = new OracleDataAdapter(as_Sql, con);
loda.Fill(lds_Temp);
lds_Rtn = lds_Temp;
if (lds_Temp != null && lds_Temp.Tables[0].Rows.Count > 0)
{
lds_Rtn = lds_Temp;
}
else
{
lds_Rtn = null;
}
}
catch (Exception ex)
{
lds_Rtn = null;
as_Err = ex.Message;
}
Close();
return lds_Rtn;
}
//直接执行Sql语句
public string f_Exec_Sql(string as_Sql)
{
string ls_Rtn = "未知错误";
try
{
Open();
try
{
OracleCommand cmd;
cmd = new OracleCommand(as_Sql, con);
int li_Rtn = cmd.ExecuteNonQuery();
if (li_Rtn >= 0)
{
ls_Rtn = "";
}
else
{
ls_Rtn = li_Rtn.ToString();
}
}
catch (Exception ex1)
{
ls_Rtn = ex1.Message;
}
}
catch (Exception ex)
{
ls_Rtn = ex.Message;
}
Close();
return ls_Rtn;
}
//直接执行Sql语句
public string f_Exec_Sql_Scalar(string as_Sql)
{
string ls_Rtn = "未知错误";
try
{
Open();
try
{
OracleCommand cmd;
cmd = new OracleCommand(as_Sql, con);
ls_Rtn = cmd.ExecuteScalar().ToString();
}
catch (Exception ex1)
{
ls_Rtn = ex1.Message;
}
}
catch (Exception ex)
{
ls_Rtn = ex.Message;
}
Close();
return ls_Rtn;
}
public bool f_IsNotNull(DataSet ads)
{
if (ads == null)
return false;
if (ads.Tables.Count == 0)
return false;
if (ads.Tables.Count == 1 && ads.Tables[0].Rows.Count == 0)
return false;
return true;
}
public string sp_Interface(string as_Sp_Name, string as_UserName, string as_parm1, string as_parm2, string as_parm3, string as_parm4, string as_parm5, string as_parm6, string as_parm7, string as_parm8, string as_parm9, string as_parm10, string as_parm11)
{
string ls_Rtn = "";
try
{
Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "sp_Interface"; //声明存储过程名
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parameters1 = new OracleParameter("as_Sp_Name", OracleType.VarChar, 200);
OracleParameter parameters2 = new OracleParameter("as_UserName", OracleType.VarChar, 200);
OracleParameter parameters3 = new OracleParameter("as_parm1", OracleType.VarChar, 4000);
OracleParameter parameters4 = new OracleParameter("as_parm2", OracleType.VarChar, 4000);
OracleParameter parameters5 = new OracleParameter("as_parm3", OracleType.VarChar, 4000);
OracleParameter parameters6 = new OracleParameter("as_parm4", OracleType.VarChar, 4000);
OracleParameter parameters7 = new OracleParameter("as_parm5", OracleType.VarChar, 4000);
OracleParameter parameters8 = new OracleParameter("as_parm6", OracleType.VarChar, 4000);
OracleParameter parameters9 = new OracleParameter("as_parm7", OracleType.VarChar, 4000);
OracleParameter parameters10 = new OracleParameter("as_parm8", OracleType.VarChar, 4000);
OracleParameter parameters11 = new OracleParameter("as_parm9", OracleType.VarChar, 4000);
OracleParameter parameters12 = new OracleParameter("as_parm10", OracleType.VarChar, 4000);
OracleParameter parameters13 = new OracleParameter("as_parm11", OracleType.VarChar, 4000);
OracleParameter parameters14 = new OracleParameter("aso_rtn", OracleType.VarChar, 4000);
parameters1.Direction = ParameterDirection.Input;
parameters2.Direction = ParameterDirection.Input;
parameters3.Direction = ParameterDirection.Input;
parameters4.Direction = ParameterDirection.Input;
parameters5.Direction = ParameterDirection.Input;
parameters6.Direction = ParameterDirection.Input;
parameters7.Direction = ParameterDirection.Input;
parameters8.Direction = ParameterDirection.Input;
parameters9.Direction = ParameterDirection.Input;
parameters10.Direction = ParameterDirection.Input;
parameters11.Direction = ParameterDirection.Input;
parameters12.Direction = ParameterDirection.Input;
parameters13.Direction = ParameterDirection.Input;
parameters14.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parameters1);
cmd.Parameters.Add(parameters2);
cmd.Parameters.Add(parameters3);
cmd.Parameters.Add(parameters4);
cmd.Parameters.Add(parameters5);
cmd.Parameters.Add(parameters6);
cmd.Parameters.Add(parameters7);
cmd.Parameters.Add(parameters8);
cmd.Parameters.Add(parameters9);
cmd.Parameters.Add(parameters10);
cmd.Parameters.Add(parameters11);
cmd.Parameters.Add(parameters12);
cmd.Parameters.Add(parameters13);
cmd.Parameters.Add(parameters14);
parameters1.Value = as_Sp_Name;
parameters2.Value = as_UserName;
parameters3.Value = as_parm1;
parameters4.Value = as_parm2;
parameters5.Value = as_parm3;
parameters6.Value = as_parm4;
parameters7.Value = as_parm5;
parameters8.Value = as_parm6;
parameters9.Value = as_parm7;
parameters10.Value = as_parm8;
parameters11.Value = as_parm9;
parameters12.Value = as_parm10;
parameters13.Value = as_parm11;
cmd.ExecuteNonQuery(); //执行存储过程
ls_Rtn = parameters14.Value.ToString();
}
catch (Exception ex)
{
ls_Rtn = "程序调用 sp_Interface 异常:" + ex.Message;
}
Close();
return ls_Rtn;
}
}
}
07-18
05-04