OleDBCommand String question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a question about the OleDBCommand.

I am making a program that needs to load 6 tabels from a Access DataBase.
At the moment I got everything working by making 6 Adapters,
CommandBuilders and using 6 times the Adapter.fill() command. I would like to
know if there is a way to bring it back to 1 Adapter command.

Right know the code looks like this. (I only put 2 Adapter Commands cause
the rest is the same)

tabel1Adapter = new OleDbDataAdapter("SELECT * FROM tabel1", myDB1Connection);
tabel2Adapter = new OleDbDataAdapter("SELECT * FROM tabel2", myDB1Connection);

tabel1Builder = new OleDbCommandBuilder(tabel1Adapter);
tabel2Builder = new OleDbCommandBuilder(tabel2Adapter);

tabel1Adapter.Fill(myDataSET, "tabel1");
tabel2Adapter.Fill(myDataSET, "tabel2");

I tried with "Select * From tabel1 tabel2");

but then I get a error while adding rows.

I hope someone can clarify this for me.

Thanks

Bardo Versteeg.
Beginner C# programmer.
 
bardo,

From your post I can't tell exactly what you would like to return. I will
assume that you are looking for a way to do a batch query with Access like
you can with SQL Server (e.g. SELECT * FROM TABLE1; SELECT * FROM TABLE2).
Unfortunately MS Access does not support multiple queries in one statement.

On the other hand if you do have access to SQL Server this will work. If
you fill your dataset with the above query you will be able to access the
different DataTables (e.g. ds.Tables[0] and ds.Tables[1]).

I hope this helps.
 
Brian Brown said:
bardo,

From your post I can't tell exactly what you would like to return. I will
assume that you are looking for a way to do a batch query with Access like
you can with SQL Server (e.g. SELECT * FROM TABLE1; SELECT * FROM TABLE2).
Unfortunately MS Access does not support multiple queries in one statement.

On the other hand if you do have access to SQL Server this will work. If
you fill your dataset with the above query you will be able to access the
different DataTables (e.g. ds.Tables[0] and ds.Tables[1]).

I hope this helps.
Click to expand...


Ahmed thanks for the link, plenty of reading material there.

Brain,

What I want to do is load a 4 to 6 tabels that are in a Access Database
file (*.MDB) into a DataSet then I want to add rows to the different tabels.
(The tabels are linked to each other with primary keys. The structure looks a
lot like the Northwind database).
After the rows are added in the different tabels, not all tabels will be
effected, it depends on the users input. Then I update the Access DataBase.

At the moment I have everything working nicely but I use 6 different
OleDbDataAdapter statements and then also 6 times the Fill statement. Plus at
the end when I update the DataBase I use 6 times the Update statement.
What I would like to know if I reduce programming code so that I only need
1 statement to load all 6 tabels in 1 go from the DataBase into the DataSet.
And back offcourse.

Looking in the MSDN files I see that its possible to use the following
statement
myOleDbAdapter.Update(myDataSet);

At the moment I am using "
myOleDbAdapter.Update(myDataSet, "tabel1"); (then same statement for
tabel2 and so on).

noot: At the moment I just load everything from the database into the
dataSet. The database is pretty small so thats not a problem. But later on I
will change the code so that I will only load in the last few entries.

I hope this is a bit clearer , and your able to help me out with this.

Thanks

Bardo
 
Bardo,

Since you are using a relational database in MS Access you cannot pull all
of your tables in with one statement that uses one call to the Fill() method
of the dataadapter. As I said in my previous post if you were using Sql
Server you could but this would not necessarily be the best thing to do.
Especially if you wanted to implement typed datasets.

What you can do to reduce clutter and implement a best practice strategy is
to create a data access logic component for your application. In this class
you can encapsulate all of the method calls to fill the dataadapter, other
methods to return the datatables or datarows that you wish to work with, and
the methods to update the database. I have posted a link below that details
the methodology of implementing this pattern. In particular reference to
this posting look at the sections 2-4 and the section in the appendix titled
“How to Define a Data Access Logic Component Classâ€.

You can look upon this as a benefit. You seeking a solution to reduce code
have led you into the world of patterns and best practices, which will only
help the users of your application. The users will have a better overall
experience and you (or anyone developing for that matter) will have a better
experience developing the application.

I hope this helps.
 
Back
Top