Can we write multiple select statements to pull data from different tables

Question

0.00/5 (No votes)

See more:

I have multiple tables none of them is related to each other.

I want to select data from database and display it on the form when form loads for the first
time. using ms access as backend

There are different parameters, text , listboxes ,tooltips, Language selection, which are filled from database when form loads.
I am using background thread for this so no GUI issues.

But Is it right to write the select statement in one function as below.

C#

void LoadDatafromDatabase()
{

try{

 OleDbConnection AccessConnection = new OleDbConnection(@"Provider = Microsoft.JET.OLEDB.4.0;data source =" + Application.StartupPath + "\\" + "FileDatabase.mdb;Jet OLEDB:Database password =;);

               AccessConnection.Open();

 //select query 1
    OledbCommand AccessCommand = new OleDbCommand("select Value from Parameters ",AccessConnection);
               DataReader = AccessCommand.ExecuteReader();
               
               int j=0;
               while (DataReader.Read())
               {
                   FormValue = DataReader.GetValue(1).ToString();
                   LoadFormValues[j] = FormValue;
                   j++;
               }        
//select query 2
 OledbCommand AccessCommand = new OleDbCommand("select Value from Tooltip",AccessConnection);
               DataReader = AccessCommand.ExecuteReader();
               
               int j=0;
               while (DataReader.Read())
               {
                   FormValue = DataReader.GetValue(1).ToString();
                   FormTooltip[j] = FormValue;
                   j++;
               }        

////select query 3...n
         .........      like this I have used different select queries for different 
   tables.

}

catch (Exception ex)
            {
               //catch exp
            }
            finally
            {
           //dispose open connenction ,datareader
            }
}

Is it a good coding practice

Posted 19-May-14 21:23pm

krishpraj123

Add a Solution

2 solutions

Solution 1

While it is possible to get back multiple datatables from 1 query into a dataset, the only time I tested this it was dismally slow, it was some years ago and I have never repeated the experiment.

I was attempting to pull in a number (6) of very small tables to support the lookup function in an application.

Posted 19-May-14 21:33pm

Mycroft Holmes

Comments

krishpraj123 20-May-14 4:04am

My tables are totally different. Only ID and Values column in tables.How I am going to join them and if yes, on which basis.
Can u please suggest me how i can solve this.
can you give me example

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2014-05-19T22:26:00

Your approach should look like following

Step 1: merge all sql queries as one. For example

C#

sql query = "select Value from Parameters;select Value from Tooltip;";

Step 2: Execute the query and read multiple resultset

C#

using (OledbCommand command = new OledbCommand
       ("select Value from Parameters;select Value from Tooltip;", AccessConnection ))
{
    using (DataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            MessageBox.Show(reader.GetString(0), "Table1.Column1");
        }

        if(reader.NextResult())
        {
           while (reader.Read())
          {
            MessageBox.Show(reader.GetString(0), "Table2.Column2");
          }
        }
    }
}