Connecting to Oracle db using ASP.NET

[atgcpaul]

It's been more than 8 months since I took a C#/ASP.NET course. At the time
I could connect to a SQL Server db on my local machine but we never
covered connecting to an Oracle db.

I'm trying to create a simple ASP.NET page that will connect to an Oracle db,
query the db, and fill a GridView/DataList/etc. Once I know how to do this,
I should be set.

I've got Visual Studio 2005. Can someone point me in the right direction?

Thank you,
Paul

[Alex Franke]

Add a reference to System.Data.Oracle (right click project in solution explorer, "Add reference..."), add a grid view called myGridView and then do something like this... (It's not PHP -- it just says it is)

DataTable table = new DataTable();
using (OracleConnection con = new OracleConnection("my connection string"))
{
    string select = "select * from Customer";
    OracleCommand cmd = new OracleCommand(select, con);
    con.Open();
    table.Load( cmd.ExecuteReader() ); 
}

myGridView.DataSource = table;
myGridView.DataBind(); 

[phi1l]

.. What Alex said...

[atgcpaul]

Add a reference to System.Data.Oracle (right click project in solution explorer, "Add reference..."), add a grid view called myGridView and then do something like this... (It's not PHP -- it just says it is)

DataTable table = new DataTable();
using (OracleConnection con = new OracleConnection("my connection string"))
{
    string select = "select * from Customer";
    OracleCommand cmd = new OracleCommand(select, con);
    con.Open();
    table.Load( cmd.ExecuteReader() ); 
}

myGridView.DataSource = table;
myGridView.DataBind(); 

Excellent! Thank you, thank you, thank you! You can't imagine how many
times I tried different itterations of this code from what I saw posted online,
but this is the only one that worked and was WAY easier to implement.

Thanks again,
Paul

[crokett]

Alex, how long did it take you to write that, and what references did you consult, if any? I've done enough programming to be able to read that syntax and understand at least the basic steps I would need to do, but it would probably have taken at least an hour and a lot of research for me to write those few lines of code.

[Kristofor]

I've done enough programming to be able to read that syntax and understand at least the basic steps I would need to do, but it would probably have taken at least an hour and a lot of research for me to write those few lines of code.

The first time.... Then (because you keep your code, right?) even if you don't recall the syntax/method exactly it's a whole lot faster the second time...

[BrazosJake]

First off, Oracle is a different beast, it has a much bigger client footprint than SQL Server. So first question is, do you have an Oracle client installed on the computer your app will be hosted on? Next, do you have a tnsnames.ora file for the Server/DB you want to connect to? Tnsnames defines the Oracle instances and their network addresses, and what ports the listener is on. This normally lives in the Network\Admin folder under the Oracle home directory.

You can create an ODBC DSN on the computer, or use a DSN-less connection string.
Either way, put the connection string in your web.config, that way if it changes (and it will), you don't have to rebuild the solution. An IIS-hosted app will restart whenever the web.config is updated. The one below uses an ODBC Data Set Name named MyOracleDSN and uses the Oracle ODP.Net provider, which has to be installed with the Oracle client:
<configuration>

<appSettings/>
<connectionStrings>
<clear/>
<add name="MyDBConnectionString" connectionString="User Id=myuserid;Password=mypassword;Data Source=MyOracleDSN" providerName="Oracle.DataAccess.Client" />

Same connection string, Microsoft provider:
<add name="MyDBConnectionString" connectionString="User Id=myuserid;Password=mypassword;Data Source=MyOracleDSN" providerName="System.Data.OracleClient" />


To get the connection string in code:

using (OracleConnection oc = new OracleConnection(ConfigurationManager.ConnectionSt rings["MyDBConnectionString"].ConnectionString))
{
OracleCommand cmd = oc.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT stuff from table"
OracleDataAdapter od = new OracleDataAdapter(cmd);
DataSet ds = new DataSet("stuff");
od.Fill(ds, "mystuff");
}

You can also open a connection without the Using statement so:
OracleConnection oc = new OracleConnection(ConfigurationManager.ConnectionSt rings["MyDBConnectionString"].ConnectionString);
oc.Open();


As for the rest of the code, it's just like a woodworking project: the first time is toughest, build the same project again, and you have jigs and templates that can be reused. Most shops build base classes/assemblies to handle the common data access plumbing. As for the first time, experienced developers don't write it, they copy it from someone else:-)

Keep the SQL as simple and ANSI-compliant as possible: Oracle PL/SQL is an entirely different world from Microsoft T-SQL.

[Alex Franke]

No problem, Paul. Anytime! I'm glad it's working for you.

Alex, how long did it take you to write that, and what references did you consult, if any?

It wasn't long, but Kristofor is right -- the more you do it the easier it is. I did check to see if the Oracle namespace was referenced by default.

I think my favorite reference is the MSDN library: From http://msdn.microsoft.com/en-us/libr...stem.data.aspx you can get to all the .Net database stuff. Lots of examples in there, too. A lot of times you can just start with one of their examples and build it up from there.

Edit: BrazosJake has good advice about keeping the connection string in the web.config. It's a little less important when the asp files compile on the fly, but it's still the best place to keep that kind of stuff. Also, the "using statement" is a good habit to get into for disposable and limited resources like connections, file handles, etc.

[Hoakie]

Looks like you guys have this one covered....I'll stay on the bench this series