Accessing database fields .NET

[niggz]

Hello masters!

I have a project and need a bit of your help.
I would like to access/get values from certain fields from a database and assing that value to a variable.

Database is included into my project along with DataSet.

Is there any simple way to access certain field?

Roughly, something like:

String myString = "";
Database db = "C:\myDatabase.accdb";

myString = db.Tables["Table1"].Row[0].Cells[1];

[thines01]

Have you looked into the IDataReader types, such as OdbcDataReader?

[niggz]

Have you looked into the IDataReader types, such as OdbcDataReader?

Okay, I wrote some code, like this:

private string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; data source=" + "Data Source='D:\\MyDatabase.accdb'";

        private void readDatabase() {
            // Define the Select statement.
            string selectSQL;
            selectSQL = "SELECT name, surname FROM Players";

            // Define the ADO.NET objects.
            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(selectSQL, con);
            OleDbDataReader reader;

            // Try to open database and read information.
            try
            {
                con.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    player.Name = reader["name"].ToString();
                    uc.PlayersBL.Add(player);
                    MessageBox.Show("Players name is " + uc.PlayersBL[0]);
                }
                reader.Close();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message,"An error occured! Description:\n\n" + err.Message);
            }
            finally
            {
                con.Close();
            }
        }

But now I get this error:
Format of the initialization string does not conform to specification starting at index 34.

[thines01]

You have the words "Data Source" twice in your connection string.

You could also change your function so it can also be used outside of a WinForms app sorta like this:

private static bool readDatabase(ref string strError)
      {
         bool blnRetVal = true;
         // Define the Select statement.
         string selectSQL = "SELECT name, surname FROM Players";

         try
         {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
               conn.Open();
               using (OleDbDataReader rdr = (new OleDbCommand(selectSQL, conn)).ExecuteReader())
               {
                  while (rdr.Read())
                  {
                     // Populate player
                  }
                  rdr.Close();
               }
               conn.Close();
            }
         }
         catch (Exception exc)
         {
            blnRetVal = false;
            strError = exc.Message;
         }

         return blnRetVal;
      }

...then it can be called like this:

static void FunctionThatUsesTheData()
      {
         string strError = "";

         if (!readDatabase(ref strError))
         {
            // Display ErrorMessage Here
            // MessageBox.Show or Console.WriteLine, etc...
            return; // <-- RETURN!
         }

         // more code goes here
      }

Another benefit is that the variables have a more limited scope and you won't have to figure out if the database is open or closed.
I "smothered" that OleDbCommand because it only has one purpose (to bring you the OleDbDataReader).

Of course, if you were adding parameters (or doing something else with it), it should have its own variable.

[niggz]

You have the words "Data Source" twice in your connection string.

You could also change your function so it can also be used outside of a WinForms app sorta like this:.........

Thanks, this is perfect.
I know I'm a pain in the a$$, but please tell me one more thing.

How to get a value from a certain field in the table?

[thines01]

Inside the while(rdr.Read()), you can use:

string strMyField = rdr["FIELDNAME"].ToString().Trim();

So, depending on the structure of the object "Player", you can query the values like this directly into the fields of Player;

[niggz]

Inside the while(rdr.Read()), you can use:

string strMyField = rdr["FIELDNAME"].ToString().Trim();

So, depending on the structure of the object "Player", you can query the values like this directly into the fields of Player;

Okay. Thank you very much!

[thines01]

Another technique I use is to let the constructor of the object take the entire IDataReader and parse into its own fields.
Check out this posting.