How to access Access database.. OleDbConnection?

[xucaen]

Hi all, I recently made a test program to access a DB2 database and all I had to do was use an OleDbConnection, OleDbDataAdapter, and a DataSet:

       public System.Data.DataSet ReadMyData(string myConnString) 
        {
            string mySelectQuery = "SELECT field1, field2,field3 FROM table1 ";
            mySelectQuery += "WHERE field1=1234";
            OleDbConnection myConnection = new OleDbConnection(myConnString);
            myConnection.Open();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(mySelectQuery, myConnection);
            System.Data.DataSet DataSet1 = new System.Data.DataSet();
            dataAdapter.Fill(DataSet1);
            return DataSet1;
        }

Which data structures would I use to access an Access database?


Thanks!

Jim

[Satishpp]

The same. Only the connection string will change.

			string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\nwind.mdb;Persist Security Info=False";
			string strSql  = "Select * from employees";
			OleDbConnection oConn = new OleDbConnection(strConn);
			oConn.Open();
			OleDbDataAdapter oData = new OleDbDataAdapter(strSql,strConn);
			DataSet ds = new DataSet();
			oData.Fill(ds,"employees");
			dataGrid1.DataSource = ds.Tables["employees"];

Satish

[xucaen]

This doesn't seem to work for doing inserts and updates. What else should I be using? I've tried using Datasets and Datatables but can't figure out how to connect them to the OleDbConnection or to the OleDbDataAdapter.

here's what I am trying to do:

            string Output = "E:\\Test\\VFileDatabase.mdb";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Output + ";Persist Security Info=False";
            string strSql  = "select * from Program";
            //OleDbConnection oConn = new OleDbConnection(strConn);

            OleDbConnection oConn = new OleDbConnection(strConn);
            oConn.Open();
            OleDbDataAdapter oData = new OleDbDataAdapter(strSql,strConn);
            DataSet ds = new DataSet();
            oData.Fill(ds,"Program");

            DataTable dt = new DataTable("Program");
            DataRow newrow = ds.Tables[0].NewRow();
            newrow["name"] = "test";
            ds.Tables[0].Rows.Add(newrow);
            ds.AcceptChanges();

            oConn.Close();

I'm attaching my table.

any ideas?

Jim

[xucaen]

Thanks to giladasaf's post located at http://www.codeguru.com/forum/showth...leDbConnection I think I am getting closer to some answers.

my code now looks like this:

            string Output = "E:\\Test\\VFileDatabase.mdb";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Output;// + ";Persist Security Info=False";

            OleDbConnection  conn = new OleDbConnection(strConn);            
            conn.Open();            
            String command = "select * from Program";            
            OleDbDataAdapter adapter = new OleDbDataAdapter( command,conn);            
           // OleDbDataAdapter adapter = new OleDbDataAdapter();

            DataSet ds = new DataSet();
            OleDbCommandBuilder cm = new OleDbCommandBuilder(adapter);
            
            adapter.Fill(ds,"Program");
            DataTable dt = new DataTable("Program");
            ds.Tables.Add(dt);

            DataRow newrow = ds.Tables["Program"].NewRow();
            newrow["name"] = "test3";
            ds.Tables[0].Rows.Add(newrow);

            adapter.UpdateCommand = cm.GetUpdateCommand();
            try
            {
                adapter.Update(ds.Tables["Program"]);// ( ds,"students");
            }
            catch
            {
                Console.WriteLine("The adapter failed to update the DB");
            }
            finally
            {            
                conn.Close();
            }

I needed to change my table as well to include a key field. I did not have this before. I had thought that the first field on the table was automatically the key, but that is not the case.

Ok, let me see if I have this straight...

OleDbConnection is the object that connects to a Database.

OleDbDataAdapter opens up a single table within the database. But what if I need acess to more than one table? can I simply change my SQL to include multiple tables or do I need a OleDbDataAdapter per each table I want to access?

DataSet is the object that stores tables in memory.

OleDbCommandBuilder I have no idea why we need this. What purpose does this serve?

adapter.Fill() is how we load up the tables into a dataSet.

DataTable I was playing with using a DataTable because I thought I could bypass the OleDbDataAdapter. I understand that DataTable is not needed in this case because I am filling my adapter from a table stored in a .mdb file. But I assume that if I were not filling an adapter then I could create my own tables in memory and fill them with columns and rows. Is this correct? and if so, Can I then create new tables to be added to the .mdb file?

adapter.UpdateCommand()= cm.GetUpdateCommand() I have no idea why this is necessary. Could someone maybe explain the for me?

adapter.Update(ds.Tables["Program"]) updates the table stored in the .mdb file. this line can also be written as
adapter.Update( ds,"Program").

I will go from here and experiment. But my goal ultimately is to loop through a ton of data and update multiple tables. I will post any new information I come up with. If anyone has any information they would like to share to expand on the usage of any of these objects that would be great.

Thanks all!!

Jim

[xucaen]

Here I am with today's lesson.
Today I figured out how to load multiple tables from an .mdb file into a DataSet. First, I have my table names listed in a multidimensional jaged string array. I created an enum to keep track of the index but in this example I don't use it too much. Using a for loop I build the select statement per each table then use the DataAdapter fill() method to fill the DataSet.

            string Output = "E:\\Test\\VFileDatabase.mdb";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Output;// + ";Persist Security Info=False";
            DataSet ds = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter( );
            adapter.SelectCommand = new OleDbCommand();
            adapter.SelectCommand.Connection = new OleDbConnection(strConn);
            string SqlPrefix = "select * from ";
            
            for(int x=0;x<(int)TableNames.NUMBEROFTABLES;++x)
            {
                adapter.SelectCommand.CommandText = SqlPrefix + Table.Tables[x][0];
                adapter.Fill(ds,Table.Tables[x][0]);
            }

then to test that this really works, I spin thru all the rows and display the data:

            DataRow [] rows = new DataRow[(int)TableNames.NUMBEROFTABLES];

            for(int x=0;x<(int)TableNames.NUMBEROFTABLES;++x)
            {
                rows[x] = ds.Tables[ Table.Tables[x][0] ].NewRow();
                Console.WriteLine(ds.Tables[x].TableName);
                for(int y=0;y<ds.Tables[x].Rows.Count;++y)
                {
                    for(int z=0; z<ds.Tables[x].Rows[y].ItemArray.Length;++z)
                    {
                        Console.Write(ds.Tables[x].Rows[y].ItemArray[z].ToString() + "\t"  );
                    }
                    Console.Write("\n");
                }
            }

so far so good! Now I am in a position to start loading new data into the DataSet and then have the DataAdapter update the tables.
I'll post that once I've written it.

Jim

[poochi]

The adapter should be able to fill multiple tables. I am little bit lazy to try it out by myself... Since you already have a sample, can you please try the following query and tell us what you got?

string SqlPrefix = "select * from table1;select * from table2"; // change the table1 and table2 to actual table names
adapter.Fill(ds);      // It should fill both the table rows in ds. 

[xucaen]

Hi Poochi, I did try your idea. Two of my table names are "Program" and "Vfile". Here's how I changed my code:

            string Output = "E:\\Test\\VFileDatabase.mdb";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Output;// + ";Persist Security Info=False";
            DataSet ds = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter( );
            adapter.SelectCommand = new OleDbCommand();
            adapter.SelectCommand.Connection = new OleDbConnection(strConn);
           // string SqlPrefix = "select * from ";

            string SqlPrefix = "select * from Program;select * from Vfile"; // change the table1 and table2 to actual table names
            adapter.SelectCommand.CommandText = SqlPrefix;
            adapter.Fill(ds);      // It should fill both the table rows in ds.

and here is the error:

Unhandled Exception: System.Data.OleDb.OleDbException: Characters found after en
d of SQL statement.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARA
MS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Ob
ject& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behav
ior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startR
ecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior be
havior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at AccessDBTest001.Class1.Main(String[] args) in e:\projects\cs\test\accessdb
test001\class1.cs:line 75 

line 75 is:

  adapter.Fill(ds);      // It should fill both the table rows in ds.

Jim

[poochi]

Thanks Jim. hmm... MSDN document clearly says adapter can fill multiple tables. Let me play with the query and let you know if I find anything....