1.c#连接oracle11g的连接方式
1.1 利用ado.net 连接oracle。
连接字符串可以为:Data source=数据库名;user id=用户名;password=密码;
注意:此方法连接oracle时,以sys,system账户登陆时,是回报“必须以“sysdba或者sysoper”,是连接不成功的。
1.2 以orace 自带的odp.net去连接数据库,以此方式登陆时,必须要到下载oracle的odp.net,导入Oracle.ManagedDataAccess.dll。此文件的使用方法可以在网上找找方法。
连接字符串如下代码:
public static string oracleConnect = @"Data Source=(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =数据库的ip)(PORT = 1521))
)
(CONNECT_DATA =
(SID = 数据库名)
)
);user id =登陆账号;password=密码;DBA Privilege=用户等级(sysdba);";
此字符串连接oracle是可以用sys身份登陆的,而且还可以数据库进行操作的。
2.c#操作oracle的存储过程(代码其实跟操作sqlserver是一样的,没有多大区别。)如下代码:
<pre name="code" class="html"> OracleParameter[] param;
param = SetInsertParameterData(1, row); //生成存储过程的,以及其值。其代码在另外一段代码里面。
OracleCommand command = new OracleCommand();
command.Connection = oracle;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "p_d_o_tra";
command.Parameters.AddRange(param);
int i = command.ExecuteNonQuery();
/// <summary>
/// 设置存储过程的入参以及
/// </summary>
/// <param name="doType"></param>
/// <param name="oneRow"></param>
private OracleParameter[] SetInsertParameterData(DataRow oneRow)
{
OracleParameter[] pars = CreateOracleParameter(4); <span style="font-family: Arial, Helvetica, sans-serif;">//设置存储过程的入参个数,其代码在另外一段代码/里面。</span><pre name="code" class="html" style="font-size:18px;"> <span style="font-family: Arial, Helvetica, sans-serif;"> pars[0].ParameterName = "actionType";</span>
pars[0].OracleDbType = OracleDbType.Decimal; pars[0].Value = doType; pars[1].ParameterName = "number"; pars[1].OracleDbType = OracleDbType.Varchar2; if (oneRow["TranNo"].ToString() == null) { pars[1].Value = DBNull.Value; } else { pars[1].Value = oneRow["number"].ToString(); } pars[2].ParameterName = "id
";
pars[2].OracleDbType = OracleDbType.Varchar2;
if (oneRow["devId"].ToString() == null) { pars[2].Value = DBNull.Value; }
else { pars[2].Value =oneRow["Id"].ToString(); }
pars[3].ParameterName = "Time";
pars[3].OracleDbType=OracleDbType.Varchar2;
if (oneRow["tranTime"].ToString() == null) { pars[3].Value = DBNull.Value; }
else {
string stringDate=oneRow["Time"].ToString().Split('.')[0];
pars[3].Value = stringDate; //DateTime.Parse(stringDate);
}
pars[4].ParameterName = "Date";
pars[4].OracleDbType = OracleDbType.Varchar2;
if (oneRow["tranDate"].ToString() == null) { pars[4].Value = DBNull.Value; }
else {
string stringDate = oneRow["Date"].ToString().Split('.')[0];
pars[4].Value = stringDate ;
}
return pars;
}
/// <summary>
/// 生成Oracle参数
/// </summary>
/// <param name="paramCount"></param>
/// <param name="dataBase"></param>
/// <returns></returns>
public static OracleParameter[] CreateOracleParameter(int paramCount)
{
OracleParameter[] param = new OracleParameter[paramCount];
for (int i = 0; i < paramCount; i++)
{
param[i] = new OracleParameter();
}
return param;
}