This blog is subject the DISCLAIMER below.

Thursday, July 10, 2008

Code Snippet :Oracle via C#

Select statement :

OracleCommand myOracleCommand = new OracleCommand();

OracleConnection myOracleConn = new OracleConnection();

myOracleConn.ConnectionString = "user id=userid ; data source=ERPDEV ; password=xxxxx";

myOracleConn.Open();

myOracleCommand.Connection = myOracleConn;

try

{

string strSQLFields = "select QUESTIONANSWER from FCIH where QUESTIONTEXT ='any text' ROWNUM <= 2)";

myOracleCommand.CommandText = strSQLFields;

myOracleCommand.CommandType = CommandType.Text;

OracleDataReader OracleDataReader1 = myOracleCommand.ExecuteReader();

if (OracleDataReader1.Read())

{

if (!(OracleDataReader1.IsDBNull(0)))

{

answer = OracleDataReader1.GetString(0);

}

}

if (!OracleDataReader1.IsClosed)

OracleDataReader1.Close();

if (myOracleConn.State == ConnectionState.Open)

myOracleConn.Close();

}

catch (Exception ex)

{

throw new ArgumentException(ex.Message.ToString());

}


Insert statment :

//new record

OracleCommand myOracleCommand4Insert = new OracleCommand();

OracleConnection myOracleConn4Insert = new OracleConnection();

myOracleConn4Insert.ConnectionString = "user id=ultimus ; data source=ERPDEV ; password=kmbpm263";

myOracleConn4Insert.Open();

myOracleCommand4Insert.Connection = myOracleConn4Insert;

try

{

string strSQLFields4Insert = "INSERT INTO FCIHTABLE( TENDERNAME, TENDERNO )";

string strSQLValues4Insert = ":TENDERNAME,TENDERNO.NEXTVAL";

string strSQL4Insert = strSQLFields4Insert + " VALUES (" + strSQLValues4Insert + ")";

myOracleCommand4Insert.CommandText = strSQL4Insert;

myOracleCommand4Insert.CommandType = CommandType.Text;

myOracleCommand4Insert.Parameters.Add("TENDERNAME", "any tender name");

myOracleCommand4Insert.ExecuteNonQuery();

}

catch (Exception ex)

{

throw new ArgumentException("Error In Insert You Suggestions to DataBase: " + ex.Message.ToString());

}

2 comments:

Ramy Mahrous said...

That's after they have seen ADO.NET, they develop (ODP) to make easier for developers to connect to Oracle DB and looks like ADO.NET classes which meets .net developers mental to work with Oracle like SQL Server

Thanks, Oracle team

Unknown said...

Hi,

the Article is good for the beginners. But the bracing is not possibly opened and closed where it has to be. It might be replaced. Could you check it and correct so that it would be helpful for other who read this article in future.