在.NET项目中链接Oracle使用的驱动是 Oracle.ManagedDataAccess.dll ,这里下载
所遇到的问题
使用存储过程一个参数没有问题，发现两个或两个以上会有参数没传过来的现象。
最后通过排查发现是没有添加参数绑定(问题找了好长时间，刚开始还以为驱动的问题+_+)。
需要设置设置属性 BindByName = true;
下面附上 OracleHelper
public class OracleHelper
{
/// <summary>
/// 测试连接字符串
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
public static bool TestConn(string connectionString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
connection.Open();
return true;
}
catch (OracleException e)
{
connection.Close();
//throw e;
return false;
}
finally
{
connection.Close();
}
}
} /// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="connectionString"></param>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static OracleDataReader ExecuteReader(string connectionString, 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 e;
} } /// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <param name="connectionString"></param>
/// <param name="cmdParms"></param>
/// <returns>SqlDataReader</returns>
public static OracleDataReader ExecuteReader(string connectionString, string SQLString, List<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 e;
}
// finally
// {
// cmd.Dispose();
// connection.Close();
// } } private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, List<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)
{
cmd.BindByName = true;
foreach (OracleParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
} /// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string connectionString, string SQLString, List<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>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string connectionString, 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;
}
} //public static DataTable QueryDataTable(string connectionString, string SQLString)
//{
// return Query(connectionString, SQLString).Tables[0];
//} public static DataTable QueryDataTable(string connectionString, string SQLString, List<JsonParamModel> jsonModels)
{
if (jsonModels.Count == )
{
return Query(connectionString, SQLString).Tables[];
}
var paramsList = new List<OracleParameter>(); foreach (var json in jsonModels)
{
//var sqlParameter = new SqlParameter(string.Format("@{0}", json.paramName), SqlDbType.Variant);
//ParamAssignment(ref sqlParameter, json);
var sqlParameter = new OracleParameter(string.Format(":{0}", json.paramName), json.paramValue);//@号调整成 :号
//ParamAssignment(ref sqlParameter, json);
paramsList.Add(sqlParameter);
} return Query(connectionString, SQLString, paramsList).Tables[];
} private static void ParamAssignment(ref OracleParameter param, JsonParamModel jsonParamModel)
{
var dbType = jsonParamModel.paramDbType;
var value = jsonParamModel.paramValue;
switch (dbType)
{
case "SqlDbType.DateTime":
param.Value = DateTime.Parse(value);
break;
case "SqlDbType.UniqueIdentifier":
param.Value = Guid.Parse(value);
break;
case "SqlDbType.Int":
param.Value = int.Parse(value);
break;
default:
param.Value = value;
break;
}
} /// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string connectionString, 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 e;
}
}
}
} /// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string connectionString, string SQLString, List<OracleParameter> cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
cmd.BindByName = true; PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
} public static int ExecuteSql(string connectionString, string SQLString, List<JsonParamModel> jsonModels)
{
if (jsonModels.Count == )
{
return ExecuteSql(connectionString, SQLString);
} var paramsList = new List<OracleParameter>(); foreach (var json in jsonModels)
{
//var sqlParameter = new SqlParameter(string.Format("@{0}", json.paramName), SqlDbType.Variant);
//ParamAssignment(ref sqlParameter, json);
var sqlParameter = new OracleParameter(string.Format(":{0}", json.paramName), json.paramValue);
//ParamAssignment(ref sqlParameter, json);
paramsList.Add(sqlParameter);
} return ExecuteSql(connectionString, SQLString, paramsList);
}
}