How are you generating your DataAdapter? Are you doing it through the IDE,
or manually through code?
The simplest way to create a DataAdapter is to drag a single table in from
Server Explorer, then rename it to whatever you need it to be. The IDE will
automatically create all the necessary CRUD SQL commands necessary to work
with Oracle.
Here's a brief rundown on how I do it (this is based on a multi-tier
architecture).
<---- Schema ---->
1. Create a new User Control project in your solution. Give it a name
like "mySchema".
2. Drag all the tables you require out of Server Explorer, and rename
each new OleDbDataAdapter to something meaningful.
3. Right click on each DataAdapter and choose "Generate DataSet". The
first time you will create a new DataSet, but each subsequent time, select
the existing DataSet. Make sure you uncheck the box to "Add to designer"
otherwise the IDE will make a mess. Also, if a table schema should change
in your database. All you need to do is delete the DataAdapter, drag that
table in again, rename the new DataAdapter to the old name, and run the
"Generate DataSet" command on the new adapter.
3. Switch to code view and add a single method for each DataAdapter
that does nothing but return a reference to it.
public System.Data.OleDb.OleDbDataAdapter
getMyTableAdapter()
{
return this.daMyTable ;
}
<---- Data Access ---->
1. Create a new class project in your solution. Give it a name like
"myDataAccess
2. Create a reference to the Schema project.
3. Create a single method for each DataAdapter which is responsible for
modifying the command text, filling the DataAdapter, and returning a typed
dataset.
public myDataSet getMyTable( string where )
{
myDataSet ds = new myDataSet() ;
mySchema schema = new mySchema() ;
OleDbDataAdapter daMyTable = schema.getMyTableAdapter() ;
string oldSelect = daMyTable.SelectCommand.CommandText ;
daMyTable.SelectCommand.CommandText += where ;
try
{
daMyTable.SelectCommand.Connection.Open() ;
daMyTable.Fill( ds ) ;
}
catch( Exception exc )
{
throw exc ;
}
finally
{
daMyTable.SelectCommand.Connection.Close() ;
daMyTable.SelectCommand.CommandText = oldSelect ;
}
return ds ;
}
4. You will create one final method on this class which will commit changes
back to the database. The example below shows the update of a single table,
but this method can easily be modified to handle the update of all tables in
your project, simply by adding the rest of the DataAdapters.
public void Commit( myDataSet ds )
{
mySchema schema = new mySchema() ;
if ( ds.HasChanges() == true )
{
OleDbDataAdapter daMyTable =
schema.getMyTableAdapter() ;
OleDbTransaction trans = null ;
try
{
daMyTable.SelectCommand.Connection.Open() ;
trans =
daMyTable.SelectCommand.Connection.BeginTransaction() ;
daMyTable.InsertCommand.Transaction =
trans ;
daMyTable.UpdateCommand.Transaction =
trans ;
daMyTable.DeleteCommand.Transaction =
trans ;
daMyTable.Update ( ds ) ;
trans.Commit() ;
}
catch( Exception exc )
{
if ( trans != null )
{
trans.Rollback() ;
}
throw exc ;
}
finally
{
daMyTable.SelectCommand.Connection.Close() ;
daMyTable.InsertCommand.Transaction
= null ;
daMyTable.UpdateCommand.Transaction
= null ;
daMyTable.DeleteCommand.Transaction
= null ;
}
}
}
<---- Business Logic / Presentation layer---->
1. This is where the rubber meets the road. Create a reference to both
the DataAccess and Schema projects.
2. To get all your data out of the table, do this :
myDataAccess da = new myDataAccess();
myDataSet ds = da.getMyTable(null);
3. To filter or sort your data at the database level, do this :
myDataAccess da = new myDataAccess();
myDataSet ds = da.getMyTable("WHERE MY_FIELD = '" + myValue + "'");
4. When your done, this is how you commit.
da.Commit( ds );
All that is missing from this, is the remoting between layers, but that's a
whole other story. As long as the DataAccess layer does not inherit from
MarshalByRef object, this will function without remoting.
I know it's a little long, but I hope it helps.
William Alexander
Software Developer
-----------------------------------------------
DATATRAK International, Inc.
-----Original Message-----
From: Noam Arbel [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 04, 2002 8:37 PM
To: [EMAIL PROTECTED]
Subject: [ADVANCED-DOTNET] Using a Data Builder and Data Adapter with Oracle
Hi,
I am trying to use an OleDbDataAdapter and DataBuilder with a recordset to
update and insert rows into a table in Oracle. All the documentation and
examples I can find are all for SQL Server(using the SqlDataAdapter
variant). I can get my code to work on Access (with the OleDBData*
classes), but when I go to Oracle it all breaks down.
What I get is that I can use DataAdapter.Fill() to hidrate the record set,
but if I make any changes and call DAtaAdapter.Update(), it throws the
following exception:
System.InvalidOperationException: Dynamic SQL generation for the
UpdateCommand is not supported against a SelectCommand that does not
return any key column information.
I have the latest drivers from Oracle (I hope).
Any ideas would be greatly appreciated.
Noam
You can read messages from the Advanced DOTNET archive, unsubscribe from
Advanced DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced
DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
