盼望得到一些asp.net连接oracle数据库的有用代码

[lijianlee]

本人手中有一套c#.net+sql2000的框架,由于工作需要改成oracle数据库,一时不知道如何下手希望得到有价值的建议和代码,谢谢大家.

[lijianlee]

求助!

[llainn]

我一直用这个

Data Source=Oracle8i;Integrated Security=yes

[lijianlee]

大哥,详细点,先说说需要注意和要改哪些方面,然后把配置文件的写法具体写给俺看看bei.

[jimu8130]

public static OracleParameter[] SpExecute(OracleTransaction myTrans,string spName,params object[] parameterValues)
{

try
{
OracleConnection oracon;
oracon =myTrans.Connection;
OracleCommand ocmd=new OracleCommand(spName,oracon);
//oracon.Open();
ocmd.Transaction = myTrans;
ocmd.CommandType = CommandType.StoredProcedure;
OracleCommandBuilder.DeriveParameters(ocmd);

int InputParamCount=0;

for(int i=0;i<ocmd.Parameters.Count;i++)
{
if ((ocmd.Parameters[i].Direction == ParameterDirection.Input) ||
(ocmd.Parameters[i].Direction == ParameterDirection.InputOutput))
{
InputParamCount++;
}
}

if (InputParamCount != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}

int k=0;
for (int i = 0, j = ocmd.Parameters.Count; i < j; i++)
{
if ((ocmd.Parameters[i].Direction == ParameterDirection.Input) ||
(ocmd.Parameters[i].Direction == ParameterDirection.InputOutput))
{
ocmd.Parameters[i].Value = parameterValues[k];
k++;
}
}

int retval = ocmd.ExecuteNonQuery();

OracleParameter[] RetParameter = new OracleParameter[ocmd.Parameters.Count];
ocmd.Parameters.CopyTo (RetParameter,0);

ocmd.Parameters.Clear();
return RetParameter;
}
catch(Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");
return null;

}

}
public static OracleParameter[] SpExecute(string spName,params object[] parameterValues)
{
string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
using(OracleConnection oracon=new OracleConnection(connectionString))
{
try
{
OracleCommand ocmd=new OracleCommand(spName,oracon);
oracon.Open();
ocmd.CommandType = CommandType.StoredProcedure;
OracleCommandBuilder.DeriveParameters(ocmd);

int InputParamCount=0;

for(int i=0;i<ocmd.Parameters.Count;i++)
{
if ((ocmd.Parameters[i].Direction == ParameterDirection.Input) ||
(ocmd.Parameters[i].Direction == ParameterDirection.InputOutput))
{
InputParamCount++;
}
}

if (InputParamCount != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}

int k=0;
for (int i = 0, j = ocmd.Parameters.Count; i < j; i++)
{
if ((ocmd.Parameters[i].Direction == ParameterDirection.Input) ||
(ocmd.Parameters[i].Direction == ParameterDirection.InputOutput))
{
ocmd.Parameters[i].Value = parameterValues[k];
k++;
}
}

int retval = ocmd.ExecuteNonQuery();

OracleParameter[] RetParameter = new OracleParameter[ocmd.Parameters.Count];
ocmd.Parameters.CopyTo (RetParameter,0);

ocmd.Parameters.Clear();
return RetParameter;
}
catch(Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");
return null;

}
}
}

[jimu8130]

public static DataSet SqlExecuteDataSet(string sSql,string TableName)
{
string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
using(OracleConnection oracon=new OracleConnection(connectionString))
{
try
{
OracleDataAdapter oraAdp = new OracleDataAdapter(sSql,oracon);
DataSet ds = new DataSet();
oracon.Open();
oraAdp.Fill(ds,TableName);

return ds;
}
catch(Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");
oracon.Close();
return new DataSet();
}
}
}

public static DataSet SqlExecuteDataSet(string cmdText)
{
string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
using(OracleConnection oracon=new OracleConnection(connectionString))
{
try
{
OracleDataAdapter oraAdp = new OracleDataAdapter(cmdText,oracon);
DataSet ds = new DataSet();
oracon.Open();
oraAdp.Fill(ds);
return ds;
}
catch(Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");
oracon.Close();
return new DataSet();
}
}
}
public static DataView SqlExecuteDataView(string cmdText)
{
string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
using(OracleConnection oracon=new OracleConnection(connectionString))
{
try
{
OracleDataAdapter oraAdp = new OracleDataAdapter(cmdText,oracon);
DataSet ds = new DataSet();
oracon.Open();
oraAdp.Fill(ds);
return ds.Tables[0].DefaultView;
}
catch (Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");
oracon.Close();
return new DataView();
}
}
}

public static OracleDataReader SqlDataReader (string sSql)
{
string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
OracleConnection oracon=new OracleConnection(connectionString);
try
{
oracon.Open();

OracleCommand cmd = new OracleCommand(sSql,oracon);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");
oracon.Close();
return null;
}

}

public static object SqlExecuteScalar(string sSql)
{
string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
using(OracleConnection oracon=new OracleConnection(connectionString))
{
try
{
OracleCommand cmd = new OracleCommand(sSql,oracon);
oracon.Open();

//执行OracleCommand的ExecuteScalar,得到结果集中第一行的第一列
object retval = cmd.ExecuteScalar();

return retval;
}
catch (Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");
oracon.Close();
return null;
}
}
}

public static int SqlExecuteNonQuery(string sSql)
{
string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
using(OracleConnection oracon=new OracleConnection(connectionString))
{
try
{
OracleCommand cmd = new OracleCommand(sSql,oracon);
oracon.Open();

int retval = cmd.ExecuteNonQuery();

return retval;
}
catch (Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");

oracon.Close();
return -1;
}
}
}

public static int SqlExecuteNonQuery(OracleTransaction myTrans,string sSql)
{
try
{
OracleConnection oracon = myTrans.Connection;
OracleCommand cmd = new OracleCommand(sSql,oracon);
cmd.Transaction = myTrans;

int retval = cmd.ExecuteNonQuery();

return retval;
}
catch (Exception ex)
{
ErrMsg = ex.Message.Replace("\n","");
return -1;
}
}

[jimu8130]

webconfig文件中加入
<configuration>
<appSettings>
<add key="ConnectionString" value="user id=uid;data source=dbname;password=password" />－－－－这个
</appSettings>
<system.web>

[lijianlee]

我还需要注意些什么?