You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1200 lines
46 KiB
1200 lines
46 KiB
using System;
|
|
using System.Collections;
|
|
using System.Collections.Specialized;
|
|
using System.Data;
|
|
using System.Configuration;
|
|
using System.Collections.Generic;
|
|
using Oracle.ManagedDataAccess.Client;
|
|
using Oracle.ManagedDataAccess.Types;
|
|
using System.Text;
|
|
namespace FangYar.Common
|
|
{
|
|
|
|
/// <summary>
|
|
/// Copyright (C) Maticsoft
|
|
/// 数据访问基础类(基于Oracle)
|
|
/// 可以用户可以修改满足自己项目的需要。
|
|
/// </summary>
|
|
public abstract class SqlHelperOra
|
|
{
|
|
|
|
//public static string connectionString = @"Data Source =(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=test;Password=test;Integrated Security=no;Unicode=True;";
|
|
//public static string connectionString = "User Id=test;Password=test;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))";
|
|
//public static string connectionString = System.Configuration.ConfigurationSettings.AppSettings["OraConn"].ToString();
|
|
public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OraConn"].ConnectionString;
|
|
|
|
public SqlHelperOra()
|
|
{
|
|
|
|
}
|
|
|
|
#region 分页查询
|
|
/// <summary>
|
|
/// 分页无排序
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="TableName">表名</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// <returns></returns>
|
|
public static DataTable QueryPage(int PageIndex, int PageSize, string TableName, string strwhere)
|
|
{
|
|
int endnum = PageIndex * PageSize;
|
|
int startnum = (PageIndex - 1) * PageSize;
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("SELECT * FROM (");
|
|
strSql.Append("SELECT t.*, ROWNUM RN FROM " + TableName + " t where ");
|
|
if (strwhere != "")
|
|
{
|
|
strSql.Append(strwhere + " and ");
|
|
}
|
|
strSql.Append(" ROWNUM <= " + endnum + ") tb");
|
|
strSql.Append(" WHERE tb.RN >" + startnum);
|
|
return QueryTable(strSql.ToString());
|
|
}
|
|
/// <summary>
|
|
/// 分页有排序
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="TableName">表名</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// /// <param name="order">排序</param>
|
|
/// <returns></returns>
|
|
public static DataTable QueryPage(int PageIndex, int PageSize, string TableName, string strwhere, string order)
|
|
{
|
|
int endnum = PageIndex * PageSize;
|
|
int startnum = (PageIndex - 1) * PageSize;
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("SELECT * FROM (SELECT tt.*, ROWNUM AS RN FROM ( ");
|
|
strSql.Append("SELECT t.* FROM " + TableName + " t ");
|
|
if (strwhere != null && strwhere != "")
|
|
{
|
|
strSql.Append(" where " + strwhere);
|
|
}
|
|
if (order != null && order != "")
|
|
{
|
|
strSql.Append(" ORDER BY " + order );
|
|
}
|
|
strSql.Append(") tt WHERE ROWNUM <= " + endnum + ") tb ");
|
|
strSql.Append(" WHERE tb.RN >" + startnum);
|
|
return QueryTable(strSql.ToString());
|
|
}
|
|
|
|
|
|
public static DataTable QueryJoinPage(int PageIndex, int PageSize, string strwhere, string order)
|
|
{
|
|
int endnum = PageIndex * PageSize;
|
|
int startnum = (PageIndex - 1) * PageSize;
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("SELECT * FROM (SELECT tt.*, ROWNUM AS RN FROM ( ");
|
|
strSql.Append(@"SELECT t.ID ,t.TITLE,t.CONTENT ,t.SENDER ,t.SENDERNAME,t.SENDTIME ,t.TASKID ,t.TYPE ,t.ATTACHFILE ,t.READTIME ,t.CATEGORY ,
|
|
t.ISPUBLIC ,t.DIGEST ,t.SOURCE ,t.UPDATETIME ,record.ID as RECORDID,record.RECEIVER ,record.RECEIVERNAME ,record.STATUS
|
|
FROM TBL_SYS_NOTICE t
|
|
LEFT JOIN
|
|
TBL_SYS_NOTICESENDRECORD record
|
|
ON t.ID = record.NOTICEID ");
|
|
if (strwhere != null && strwhere != "")
|
|
{
|
|
strSql.Append(" where " + strwhere);
|
|
}
|
|
if (order != null && order != "")
|
|
{
|
|
strSql.Append(" ORDER BY " + order );
|
|
}
|
|
strSql.Append(") tt WHERE ROWNUM <= " + endnum + ") tb ");
|
|
strSql.Append(" WHERE tb.RN >" + startnum);
|
|
return QueryTable(strSql.ToString());
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 公用方法
|
|
|
|
/// <summary>
|
|
/// 获取记录数
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public static int GetCount(string sql)
|
|
{
|
|
|
|
object obj = GetSingle(sql);
|
|
if (obj == null)
|
|
{
|
|
return 0;
|
|
}
|
|
else
|
|
{
|
|
return int.Parse(obj.ToString());
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取记录数
|
|
/// </summary>
|
|
/// <param name="FieldName">查询条件</param>
|
|
/// <param name="TableName">表名</param>
|
|
/// <returns>记录数</returns>
|
|
public static int GetCount(string strwhere, string TableName)
|
|
{
|
|
string strsql = string.Format("select count(*) from {0} {1}", TableName, strwhere);
|
|
object obj = GetSingle(strsql);
|
|
if (obj == null)
|
|
{
|
|
return 1;
|
|
}
|
|
else
|
|
{
|
|
return int.Parse(obj.ToString());
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取字段的最大值
|
|
/// </summary>
|
|
/// <param name="FieldName">字段名</param>
|
|
/// <param name="TableName">表名</param>
|
|
/// <returns>字段最大值</returns>
|
|
public static int GetMaxID(string FieldName, string TableName)
|
|
{
|
|
string strsql = string.Format("select max({0})+1 from {1}", FieldName, TableName);
|
|
object obj = GetSingle(strsql);
|
|
if (obj == null)
|
|
{
|
|
return 1;
|
|
}
|
|
else
|
|
{
|
|
return int.Parse(obj.ToString());
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取字段的最大值
|
|
/// </summary>
|
|
/// <param name="FieldName">字段名</param>
|
|
/// <param name="TableName">表名</param>
|
|
/// <param name="strWhere">where条件</param>
|
|
/// <returns>字段最大值</returns>
|
|
public static long GetMaxID(string FieldName, string TableName, string strWhere)
|
|
{
|
|
string strsql = string.Format("select max({0})+1 from {1} where 1=1 {2}", FieldName, TableName, strWhere);
|
|
object obj = GetSingle(strsql);
|
|
if (obj == null)
|
|
{
|
|
return 1;
|
|
}
|
|
else
|
|
{
|
|
return Convert.ToInt64(obj.ToString()) ;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据指定Sql语句查询是否存在
|
|
/// </summary>
|
|
/// <param name="strSql">要执行的Sql语句</param>
|
|
/// <returns>存在返回true，不存在返回false</returns>
|
|
public static bool Exists(string strSql)
|
|
{
|
|
object obj = GetSingle(strSql);
|
|
int cmdresult;
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|
{
|
|
cmdresult = 0;
|
|
}
|
|
else
|
|
{
|
|
cmdresult = int.Parse(obj.ToString());
|
|
}
|
|
|
|
return cmdresult == 0 ? false : true;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据指定Sql语句及参数查询是否存在
|
|
/// </summary>
|
|
/// <param name="strSql">要执行的Sql语句（包含参数值的标识）</param>
|
|
/// <param name="cmdParms">参数值数组</param>
|
|
/// <returns>存在返回true，不存在返回false</returns>
|
|
public static bool Exists(string strSql, params OracleParameter[] cmdParms)
|
|
{
|
|
object obj = GetSingle(strSql, cmdParms);
|
|
int cmdresult;
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|
{
|
|
cmdresult = 0;
|
|
}
|
|
else
|
|
{
|
|
cmdresult = int.Parse(obj.ToString());
|
|
}
|
|
if (cmdresult == 0)
|
|
{
|
|
return false;
|
|
}
|
|
else
|
|
{
|
|
return true;
|
|
}
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region 执行简单SQL语句
|
|
|
|
/// <summary>
|
|
/// 执行SQL语句，返回影响的记录数
|
|
/// </summary>
|
|
/// <param name="SQLString">SQL语句</param>
|
|
/// <returns>影响的记录数</returns>
|
|
public static int ExecuteSql(string SQLString)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
|
|
{
|
|
try
|
|
{
|
|
connection.Open();
|
|
int rows = cmd.ExecuteNonQuery();
|
|
return rows;
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
connection.Close();
|
|
throw new Exception(E.Message);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 执行多条SQL语句，实现数据库事务。
|
|
/// </summary>
|
|
/// <param name="SQLStringList">多条SQL语句</param>
|
|
public static bool ExecuteSqlTran(List<string> SQLStringList)
|
|
{
|
|
var _result = false;
|
|
using (OracleConnection conn = new OracleConnection(connectionString))
|
|
{
|
|
conn.Open();
|
|
OracleCommand cmd = new OracleCommand();
|
|
cmd.Connection = conn;
|
|
OracleTransaction tx = conn.BeginTransaction();
|
|
cmd.Transaction = tx;
|
|
try
|
|
{
|
|
foreach (var strSql in SQLStringList)
|
|
{
|
|
if (strSql.Trim().Length > 1)
|
|
{
|
|
cmd.CommandText = strSql;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
tx.Commit();
|
|
_result = true;
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
tx.Rollback();
|
|
}
|
|
}
|
|
return _result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行多条SQL语句，实现数据库事务。
|
|
/// </summary>
|
|
/// <param name="SQLStringList">多条SQL语句</param>
|
|
public static void ExecuteSqlTran(ArrayList SQLStringList)
|
|
{
|
|
using (OracleConnection conn = new OracleConnection(connectionString))
|
|
{
|
|
conn.Open();
|
|
OracleCommand cmd = new OracleCommand();
|
|
cmd.Connection = conn;
|
|
OracleTransaction tx = conn.BeginTransaction();
|
|
cmd.Transaction = tx;
|
|
try
|
|
{
|
|
for (int n = 0; n < SQLStringList.Count; n++)
|
|
{
|
|
string strsql = SQLStringList[n].ToString();
|
|
if (strsql.Trim().Length > 1)
|
|
{
|
|
cmd.CommandText = strsql;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
tx.Commit();
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
tx.Rollback();
|
|
throw new Exception(E.Message);
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行带一个存储过程参数的的SQL语句。
|
|
/// </summary>
|
|
/// <param name="SQLString">SQL语句</param>
|
|
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
|
|
/// <returns>影响的记录数</returns>
|
|
public static int ExecuteSql(string SQLString, string content)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
OracleCommand cmd = new OracleCommand(SQLString, connection);
|
|
OracleParameter myParameter = new OracleParameter("@content", OracleDbType.NVarchar2);
|
|
myParameter.Value = content;
|
|
cmd.Parameters.Add(myParameter);
|
|
try
|
|
{
|
|
connection.Open();
|
|
int rows = cmd.ExecuteNonQuery();
|
|
return rows;
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
throw new Exception(E.Message);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
|
|
/// </summary>
|
|
/// <param name="strSQL">SQL语句</param>
|
|
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
|
|
/// <returns>影响的记录数</returns>
|
|
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
OracleCommand cmd = new OracleCommand(strSQL, connection);
|
|
OracleParameter myParameter = new OracleParameter("@fs", OracleDbType.LongRaw);
|
|
myParameter.Value = fs;
|
|
cmd.Parameters.Add(myParameter);
|
|
try
|
|
{
|
|
connection.Open();
|
|
int rows = cmd.ExecuteNonQuery();
|
|
return rows;
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
throw new Exception(E.Message);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <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 (OracleException e)
|
|
{
|
|
connection.Close();
|
|
throw new Exception(e.Message);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
|
|
/// </summary>
|
|
/// <param name="strSQL">查询语句</param>
|
|
/// <returns>OracleDataReader</returns>
|
|
public static OracleDataReader ExecuteReader(string strSQL)
|
|
{
|
|
OracleConnection connection = new OracleConnection(connectionString);
|
|
OracleCommand cmd = new OracleCommand(strSQL, connection);
|
|
try
|
|
{
|
|
connection.Open();
|
|
OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|
return myReader;
|
|
}
|
|
catch (OracleException e)
|
|
{
|
|
throw new Exception(e.Message);
|
|
}
|
|
finally
|
|
{
|
|
|
|
connection = null;
|
|
connection.Close();
|
|
}
|
|
|
|
}
|
|
/// <summary>
|
|
/// 执行查询语句，返回DataSet
|
|
/// </summary>
|
|
/// <param name="SQLString">查询语句</param>
|
|
/// <returns>DataSet</returns>
|
|
public static DataSet Query(string SQLString)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
connection.Open();
|
|
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
|
|
command.Fill(ds, "ds");
|
|
}
|
|
catch (OracleException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return ds;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行查询语句，返回DataSet
|
|
/// </summary>
|
|
/// <param name="SQLString">查询语句</param>
|
|
/// <returns>DataSet</returns>
|
|
public static DataSet Query(string SQLString, DataSet ds, string TableName)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
try
|
|
{
|
|
connection.Open();
|
|
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
|
|
command.Fill(ds, TableName);
|
|
}
|
|
catch (OracleException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return ds;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行查询语句，返回DataTable
|
|
/// </summary>
|
|
/// <param name="SQLString">查询语句</param>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable QueryTable(string SQLString)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
var dt = new DataTable();
|
|
try
|
|
{
|
|
connection.Open();
|
|
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
|
|
command.Fill(dt);
|
|
}
|
|
catch (OracleException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return dt;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 执行带参数的SQL语句
|
|
|
|
/// <summary>
|
|
/// 执行SQL语句，返回影响的记录数
|
|
/// </summary>
|
|
/// <param name="SQLString">SQL语句</param>
|
|
/// <returns>影响的记录数</returns>
|
|
public static int ExecuteSql(string SQLString, params OracleParameter[] cmdParms)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
using (OracleCommand cmd = new OracleCommand())
|
|
{
|
|
try
|
|
{
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|
int rows = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
return rows;
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
throw new Exception(E.Message);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行SQL语句，返回影响的记录数
|
|
/// </summary>
|
|
/// <param name="SQLString">SQL语句</param>
|
|
/// <returns>影响的记录数</returns>
|
|
public static DataSet ExecuteProc(string produce, params OracleParameter[] cmdParms)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
using (OracleCommand cmd = new OracleCommand())
|
|
{
|
|
try
|
|
{
|
|
PrepareCommand(cmd, connection, null,CommandType.StoredProcedure, produce, cmdParms);
|
|
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
|
|
{
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
da.Fill(ds);
|
|
cmd.Parameters.Clear();
|
|
}
|
|
catch (OracleException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return ds;
|
|
}
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
throw new Exception(E.Message);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
//执行(带参)存储过程返回DataTable
|
|
public static DataTable ExecuteProcToDataTable(string strProcName, params OracleParameter[] parameters)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
using (OracleConnection conn = new OracleConnection(connectionString))
|
|
{
|
|
try
|
|
{
|
|
OracleCommand cmd = new OracleCommand();
|
|
cmd.CommandText = strProcName;
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.Connection = conn;
|
|
conn.Open();
|
|
if (parameters != null)
|
|
{
|
|
// 添加参数
|
|
cmd.Parameters.AddRange(parameters);
|
|
}
|
|
|
|
// 取数据
|
|
using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
|
|
{
|
|
adapter.Fill(dt);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception("错误:" + ex.Message + "/r/n跟踪:" + ex.StackTrace);
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
return dt;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 执行SQL语句，返回影响的记录数
|
|
/// </summary>
|
|
/// <param name="SQLString">SQL语句</param>
|
|
/// <returns>影响的记录数</returns>
|
|
public static OracleCommand ExecuteProcToCommand(string produce, params OracleParameter[] cmdParms)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
//connection.Open();
|
|
using (OracleCommand cmd = new OracleCommand())
|
|
{
|
|
try
|
|
{
|
|
PrepareCommand(cmd, connection, null, CommandType.StoredProcedure, produce, cmdParms);
|
|
cmd.ExecuteNonQuery();
|
|
return cmd;
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
throw new Exception(E.Message);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public static void ExecuteProcTest()
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
try
|
|
{
|
|
string str_Sql = @"call proc_test(:p1,:p2)";
|
|
/*不能用：call proc_test(?,?)或者call proc_test(@p1,@p2)，这样会报ORA-01036:非法的变量名/编号错误 */
|
|
OracleCommand cmd = new OracleCommand(str_Sql, connection);
|
|
/*cmd.CommandType = CommandType.StoredProcedure;-注意这种方式调用存储过程，不能指定CommandType为StoredProcedure */
|
|
|
|
OracleParameter pram1 = new OracleParameter("p1", OracleDbType.Varchar2, 10);
|
|
pram1.Value = "test";
|
|
|
|
cmd.Parameters.Add(pram1);
|
|
|
|
OracleParameter pram2 = new OracleParameter("p2", OracleDbType.Varchar2, 10);
|
|
pram2.Direction = ParameterDirection.Output;
|
|
|
|
cmd.Parameters.Add(pram2);
|
|
|
|
|
|
connection.Open();
|
|
|
|
cmd.ExecuteNonQuery();
|
|
string text = cmd.Parameters[1].Value.ToString();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
}
|
|
}
|
|
|
|
public static void ExecuteProcTest2(string produce, params OracleParameter[] cmdParms)
|
|
{
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
//connection.Open();
|
|
using (OracleCommand cmd = new OracleCommand())
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandText = produce;
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.Connection = connection;
|
|
connection.Open();
|
|
cmd.Parameters.Add(cmdParms[0]);
|
|
cmd.Parameters.Add(cmdParms[1]);
|
|
|
|
//PrepareCommand(cmd, connection, null, CommandType.StoredProcedure, produce, cmdParms);
|
|
cmd.ExecuteNonQuery();
|
|
string p1 = cmd.Parameters["p1"].Value.ToString();////执行完成后得到回传的结果
|
|
string p2 = cmd.Parameters["p2"].Value.ToString();////执行完成后得到回传的结果
|
|
}
|
|
catch (OracleException E)
|
|
{
|
|
throw new Exception(E.Message);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行多条SQL语句，实现数据库事务。
|
|
/// </summary>
|
|
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
|
|
public static void ExecuteSqlTran(Hashtable SQLStringList)
|
|
{
|
|
using (OracleConnection conn = new OracleConnection(connectionString))
|
|
{
|
|
conn.Open();
|
|
using (OracleTransaction trans = conn.BeginTransaction())
|
|
{
|
|
OracleCommand cmd = new OracleCommand();
|
|
try
|
|
{
|
|
//循环
|
|
foreach (DictionaryEntry myDE in SQLStringList)
|
|
{
|
|
string cmdText = myDE.Key.ToString();
|
|
OracleParameter[] cmdParms = (OracleParameter[])myDE.Value;
|
|
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
|
|
int val = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
|
|
trans.Commit();
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
trans.Rollback();
|
|
throw;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 执行多条SQL语句，实现数据库事务。
|
|
/// </summary>
|
|
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
|
|
public static bool ExecuteSqlTranBool(List<FangYar.Model.OA.CommonSql> SQLStringList)
|
|
{
|
|
var _result = false;
|
|
using (OracleConnection conn = new OracleConnection(connectionString))
|
|
{
|
|
conn.Open();
|
|
using (OracleTransaction trans = conn.BeginTransaction())
|
|
{
|
|
OracleCommand cmd = new OracleCommand();
|
|
try
|
|
{
|
|
//循环
|
|
foreach (FangYar.Model.OA.CommonSql myDE in SQLStringList)
|
|
{
|
|
string cmdText = myDE.sql;
|
|
OracleParameter[] cmdParms = myDE.param;
|
|
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
|
|
int val = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
|
|
}
|
|
trans.Commit();
|
|
_result = true;
|
|
}
|
|
catch(Exception e)
|
|
{
|
|
trans.Rollback();
|
|
throw;
|
|
}
|
|
}
|
|
}
|
|
return _result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 使用oracle参数化批量插入、更新，实现数据库事务。--做文件上传，批量导入，批量更新
|
|
/// </summary>
|
|
/// <param name="SQLStringList">SQL语句的dictionary集合（key为OracleParameter[]语句，value是该语句的sql语句）</param>
|
|
public static int ExecuteSqlListParameterTran(Dictionary<OracleParameter[], string> list)
|
|
{
|
|
int val = 0;
|
|
using (OracleConnection conn = new OracleConnection(connectionString))
|
|
{
|
|
conn.Open();
|
|
using (OracleTransaction trans = conn.BeginTransaction())
|
|
{
|
|
OracleCommand cmd = new OracleCommand();
|
|
cmd.Connection = conn;
|
|
try
|
|
{
|
|
//循环
|
|
foreach (KeyValuePair<OracleParameter[], string> myDE in list)
|
|
{
|
|
if (myDE.Key == null)
|
|
{
|
|
cmd.CommandText = myDE.Value.ToString();
|
|
if (trans != null)
|
|
cmd.Transaction = trans;
|
|
cmd.CommandType = CommandType.Text;
|
|
val += cmd.ExecuteNonQuery();
|
|
}
|
|
else
|
|
{
|
|
cmd.CommandText = myDE.Value.ToString();
|
|
if (trans != null)
|
|
cmd.Transaction = trans;
|
|
cmd.CommandType = CommandType.Text;
|
|
OracleParameter[] cmdParms = (OracleParameter[])myDE.Key;
|
|
if (cmdParms != null)
|
|
{
|
|
foreach (OracleParameter item in cmdParms)
|
|
{
|
|
cmd.Parameters.Add(item);
|
|
}
|
|
}
|
|
val += cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
}
|
|
}
|
|
trans.Commit();
|
|
}
|
|
catch
|
|
{
|
|
trans.Rollback();
|
|
throw;
|
|
}
|
|
}
|
|
}
|
|
return val;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行一条计算查询结果语句，返回查询结果（object）。
|
|
/// </summary>
|
|
/// <param name="SQLString">计算查询结果语句</param>
|
|
/// <returns>查询结果（object）</returns>
|
|
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 (OracleException e)
|
|
{
|
|
throw new Exception(e.Message);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
|
|
/// </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(CommandBehavior.CloseConnection);
|
|
cmd.Parameters.Clear();
|
|
return myReader;
|
|
}
|
|
catch (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 (OracleException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return ds;
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行查询语句，返回DataTable
|
|
/// </summary>
|
|
/// <param name="SQLString">查询语句</param>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable QueryTable(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))
|
|
{
|
|
var dt = new DataTable();
|
|
try
|
|
{
|
|
da.Fill(dt);
|
|
cmd.Parameters.Clear();
|
|
}
|
|
catch (OracleException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
|
|
}
|
|
return dt;
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 替换sql语句中的参数占位为真实的参数值
|
|
/// </summary>
|
|
/// <param name="cmd">OracleCommand对象</param>
|
|
/// <param name="conn">数据库连接对象</param>
|
|
/// <param name="trans">事务对象</param>
|
|
/// <param name="cmdText">sql语句</param>
|
|
/// <param name="cmdParms">参数值数组</param>
|
|
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>
|
|
/// 替换sql语句中的参数占位为真实的参数值
|
|
/// </summary>
|
|
/// <param name="cmd">OracleCommand对象</param>
|
|
/// <param name="conn">数据库连接对象</param>
|
|
/// <param name="trans">事务对象</param>
|
|
/// <param name="cmdText">sql语句</param>
|
|
/// <param name="cmdParms">参数值数组</param>
|
|
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdtype, 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 = cmdtype;//cmdType;
|
|
if (cmdParms != null)
|
|
{
|
|
foreach (OracleParameter parm in cmdParms)
|
|
cmd.Parameters.Add(parm);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 统计某表记录总数
|
|
/// <summary>
|
|
/// 统计某表记录总数
|
|
/// </summary>
|
|
/// <param name="KeyField">主键/索引键</param>
|
|
/// <param name="TableName">数据库.用户名.表名</param>
|
|
/// <param name="Condition">查询条件</param>
|
|
/// <returns>返回记录总数</returns>
|
|
public static int GetRecordCount(string keyField, string tableName, string condition)
|
|
{
|
|
int RecordCount = 0;
|
|
string sql = "select count(" + keyField + ") as count from " + tableName + " " + condition;
|
|
DataSet ds = Query(sql);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
|
|
}
|
|
ds.Clear();
|
|
ds.Dispose();
|
|
return RecordCount;
|
|
}
|
|
#endregion
|
|
#region 统计某表记录总数
|
|
/// <summary>
|
|
/// 统计某表记录总数
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public static int GetRecordCount(string sql)
|
|
{
|
|
int RecordCount = 0;
|
|
|
|
DataSet ds = Query(sql);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
|
|
}
|
|
ds.Clear();
|
|
ds.Dispose();
|
|
return RecordCount;
|
|
}
|
|
/// <summary>
|
|
/// 统计某表记录总数
|
|
/// </summary>
|
|
/// <param name="Field">可重复的字段</param>
|
|
/// <param name="tableName">数据库.用户名.表名</param>
|
|
/// <param name="condition">查询条件</param>
|
|
/// <param name="flag">字段是否主键</param>
|
|
/// <returns>返回记录总数</returns>
|
|
public static int GetRecordCount(string Field, string tableName, string condition, bool flag)
|
|
{
|
|
int RecordCount = 0;
|
|
if (flag)
|
|
{
|
|
RecordCount = GetRecordCount(Field, tableName, condition);
|
|
}
|
|
else
|
|
{
|
|
string sql = "select count(distinct(" + Field + ")) as count from " + tableName + " " + condition;
|
|
DataSet ds = Query(sql);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
|
|
}
|
|
ds.Clear();
|
|
ds.Dispose();
|
|
}
|
|
return RecordCount;
|
|
}
|
|
#endregion
|
|
#region 统计某表分页总数
|
|
/// <summary>
|
|
/// 统计某表分页总数
|
|
/// </summary>
|
|
/// <param name="keyField">主键/索引键</param>
|
|
/// <param name="tableName">表名</param>
|
|
/// <param name="condition">查询条件</param>
|
|
/// <param name="pageSize">页宽</param>
|
|
/// <param name="RecordCount">记录总数</param>
|
|
/// <returns>返回分页总数</returns>
|
|
public int GetPageCount(string keyField, string tableName, string condition, int pageSize, int RecordCount)
|
|
{
|
|
int PageCount = 0;
|
|
PageCount = (RecordCount % pageSize) > 0 ? (RecordCount / pageSize) + 1 : RecordCount / pageSize;
|
|
if (PageCount < 1) PageCount = 1;
|
|
return PageCount;
|
|
}
|
|
/// <summary>
|
|
/// 统计某表分页总数
|
|
/// </summary>
|
|
/// <param name="keyField">主键/索引键</param>
|
|
/// <param name="tableName">表名</param>
|
|
/// <param name="condition">查询条件</param>
|
|
/// <param name="pageSize">页宽</param>
|
|
/// <returns>返回页面总数</returns>
|
|
public int GetPageCount(string keyField, string tableName, string condition, int pageSize, ref int RecordCount)
|
|
{
|
|
RecordCount = GetRecordCount(keyField, tableName, condition);
|
|
return GetPageCount(keyField, tableName, condition, pageSize, RecordCount);
|
|
}
|
|
/// <summary>
|
|
/// 统计某表分页总数
|
|
/// </summary>
|
|
/// <param name="Field">可重复的字段</param>
|
|
/// <param name="tableName">表名</param>
|
|
/// <param name="condition">查询条件</param>
|
|
/// <param name="pageSize">页宽</param>
|
|
/// <param name="flag">是否主键</param>
|
|
/// <returns>返回页页总数</returns>
|
|
public int GetPageCount(string Field, string tableName, string condition, ref int RecordCount, int pageSize, bool flag)
|
|
{
|
|
RecordCount = GetRecordCount(Field, tableName, condition, flag);
|
|
return GetPageCount(Field, tableName, condition, pageSize, ref RecordCount);
|
|
}
|
|
#endregion
|
|
#region Sql分页函数
|
|
/// <summary>
|
|
/// 构造分页查询SQL语句
|
|
/// </summary>
|
|
/// <param name="KeyField">主键</param>
|
|
/// <param name="FieldStr">所有需要查询的字段(field1,field2...)</param>
|
|
/// <param name="TableName">库名.拥有者.表名</param>
|
|
/// <param name="where">查询条件1(where ...)</param>
|
|
/// <param name="order">排序条件2(order by ...)</param>
|
|
/// <param name="CurrentPage">当前页号</param>
|
|
/// <param name="PageSize">页宽</param>
|
|
/// <returns>SQL语句</returns>
|
|
public static string JoinPageSQL(string KeyField, string FieldStr, string TableName, string Where, string Order, int CurrentPage, int PageSize)
|
|
{
|
|
string sql = null;
|
|
//if (CurrentPage == 1)
|
|
//{
|
|
// sql = "select " + CurrentPage * PageSize + " " + FieldStr + " from " + TableName + " " + Where + " " + Order + " ";
|
|
//}
|
|
//else
|
|
//{
|
|
// sql = "select * from (";
|
|
// sql += "select " + CurrentPage * PageSize + " " + FieldStr + " from " + TableName + " " + Where + " " + Order + ") a ";
|
|
// sql += "where " + KeyField + " not in (";
|
|
// sql += "select " + (CurrentPage - 1) * PageSize + " " + KeyField + " from " + TableName + " " + Where + " " + Order + ")";
|
|
//}
|
|
sql = "SELECT * FROM (Select ROWNUM AS ROWNO, T.* from " + TableName + " T " + Where + " AND ROWNUM <= " + (PageSize * CurrentPage) + ") TABLE_ALIAS WHERE TABLE_ALIAS.ROWNO >" + ((CurrentPage - 1) * PageSize);
|
|
|
|
|
|
return sql;
|
|
}
|
|
/// <summary>
|
|
/// 构造分页查询SQL语句
|
|
/// </summary>
|
|
/// <param name="Field">字段名(非主键)</param>
|
|
/// <param name="TableName">库名.拥有者.表名</param>
|
|
/// <param name="where">查询条件1(where ...)</param>
|
|
/// <param name="order">排序条件2(order by ...)</param>
|
|
/// <param name="CurrentPage">当前页号</param>
|
|
/// <param name="PageSize">页宽</param>
|
|
/// <returns>SQL语句</returns>
|
|
public static string JoinPageSQL(string Field, string TableName, string Where, string Order, int CurrentPage, int PageSize)
|
|
{
|
|
string sql = null;
|
|
if (CurrentPage == 1)
|
|
{
|
|
sql = "select rownum " + CurrentPage * PageSize + " " + Field + " from " + TableName + " " + Where + " " + Order + " group by " + Field;
|
|
}
|
|
else
|
|
{
|
|
sql = "select * from (";
|
|
sql += "select rownum " + CurrentPage * PageSize + " " + Field + " from " + TableName + " " + Where + " " + Order + " group by " + Field + " ) a ";
|
|
sql += "where " + Field + " not in (";
|
|
sql += "select rownum " + (CurrentPage - 1) * PageSize + " " + Field + " from " + TableName + " " + Where + " " + Order + " group by " + Field + ")";
|
|
}
|
|
return sql;
|
|
}
|
|
#endregion
|
|
}
|
|
}
|
|
|