Is any possible Multiple query executing in single connection?

Question

0.00/5 (No votes)

See more:

hi friends,

In my form, lot of combo box is there. I want to load different table datas to combo box. I am trying to do that. but code is going on very long. Is any possible Multiple query executing in single connection?

string MyConString = ConfigurationManager.ConnectionStrings["College_Management_System.Properties.Settings.cmsConnectionString"].ConnectionString;
           MySqlConnection connection = new MySqlConnection(MyConString);
           MySqlCommand command = connection.CreateCommand();
           MySqlDataReader Reader;
           command.CommandText = "select * from course_master";
           connection.Open();
           Reader = command.ExecuteReader();
           while (Reader.Read())
           {
               cmbo_course.Items.Add(Reader[2].ToString());
           }
           connection.Close();
           MySqlConnection connection1 = new MySqlConnection(MyConString);
           MySqlCommand command1 = connection1.CreateCommand();
           MySqlDataReader Reader1;
           command1.CommandText = "select * from country_master";
           connection1.Open();
           Reader1 = command1.ExecuteReader();
           while (Reader1.Read())
           {
               cmbo_perCountry.Items.Add(Reader1[2].ToString());
               cmbo_country.Items.Add(Reader1[2].ToString());
           }
           connection1.Close();

UPDATED -

After help from digital man and Original Griff,.. I update My code to:-

string MyConString = ConfigurationManager.ConnectionStrings["College_Management_System.Properties.Settings.cmsConnectionString"].ConnectionString;
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = "select name from course_master";
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                cmbo_course.Items.Add(Reader[0].ToString());
            }
            connection.Close();
            connection.Open();
            command.CommandText = "select name from country_master";
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                cmbo_perCountry.Items.Add(Reader[0].ToString());
                cmbo_country.Items.Add(Reader[0].ToString());
            }
            connection.Close();

In that when i try to do this in one connection. if i remove the connection.close(), it give runtime error ,

<br />
There is already an open DataReader associated with this Connection which must be closed first.

string MyConString = ConfigurationManager.ConnectionStrings["College_Management_System.Properties.Settings.cmsConnectionString"].ConnectionString;
 MySqlConnection connection = new MySqlConnection(MyConString);
 MySqlCommand command = connection.CreateCommand();
 MySqlDataReader Reader;
 command.CommandText = "select name from course_master";
 connection.Open();
 Reader = command.ExecuteReader();
 while (Reader.Read())
 {
     cmbo_course.Items.Add(Reader[0].ToString());
 }

 command.CommandText = "select name from country_master";
 Reader = command.ExecuteReader();
 while (Reader.Read())
 {
     cmbo_perCountry.Items.Add(Reader[0].ToString());
     cmbo_country.Items.Add(Reader[0].ToString());
 }
 connection.Close();

Posted 25-May-11 1:21am

Sagotharan Jagadeeswaran

Updated 25-May-11 2:20am

v4

Add a Solution

2 solutions

Solution 2

Yes, you can re-use the connection: you do not need to create a new one. You can also re-use the Reader, and the command.

More importantly, do not do "SELECT *" and then rely on the ordinal position of fields: they may not stay that way. Reader[2] works, but if you alter your table definition, it may not. Instead use the string form, or better still select only those fields you are actually going to use: this will also save bandwith in communicating with the database server.

You could even just use a DataAdapter and set the DataSource property of the combo box to the selected information directly:

using (MySqlConnection connection = new MySqlConnection(MyConString))
   {
   connection.Open();
   using (MySqlCommand command = new MySqlCommand("SELECT course FROM course_master", connection))
      {
      MySqlDataAdapter da = new MySqlDataAdapter(command);
      DataTable dt = new DataTable();
      da.Fill(dt);
      cmbo_course.DataSource = dt;
      }
   }

Posted 25-May-11 1:43am

OriginalGriff

Comments

Sagotharan Jagadeeswaran 25-May-11 7:57am

hi friend, if try to do this format,..

string MyConString = ConfigurationManager.ConnectionStrings["College_Management_System.Properties.Settings.cmsConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(MyConString))
{
connection.Open();
using (MySqlCommand command = new MySqlCommand("SELECT name FROM course_master", connection))
{
MySqlDataAdapter da = new MySqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
cmbo_course.DataSource = dt;
}
}

i got System.Data.DataRowView in combobox. What i do for that?.

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)

**R. Giskard Reventlov** · Accepted Answer · 2011-05-25T01:31:00

Solution 1

First of all, why don't you re-use the connection object instead of creating a new one each time?

Anyway, a quick way might be to union all of the queries (assumes that an equal number of columns are returned or null columns added for balance) adding a column which will contain a number so that you can tell each set apart then loop through the results where that number = your loop counter and populate the control.

Also, only return the columns you need from the database.

Posted 25-May-11 1:31am

R. Giskard Reventlov

Comments

Sagotharan Jagadeeswaran 25-May-11 7:42am

thank u for the idea. i overwrite the code,..
string MyConString = ConfigurationManager.ConnectionStrings["College_Management_System.Properties.Settings.cmsConnectionString"].ConnectionString;
MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "select * from course_master";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
cmbo_course.Items.Add(Reader[2].ToString());
}
connection.Close();

command.CommandText = "select * from country_master";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
cmbo_perCountry.Items.Add(Reader[2].ToString());
cmbo_country.Items.Add(Reader[2].ToString());
}
connection.Close();

is it correct?. and try to do union.

And another thing,. is dal implementation possible in windows form?

Sagotharan Jagadeeswaran 25-May-11 7:50am

sorry friend,

string MyConString = ConfigurationManager.ConnectionStrings["College_Management_System.Properties.Settings.cmsConnectionString"].ConnectionString;
MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "(select name from course_master) UNION (select name from country_master)";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
MessageBox.Show(Reader[0].ToString());
//cmbo_course.Items.Add(Reader[0].ToString());
//cmbo_perCountry.Items.Add(Reader[1].ToString());
//cmbo_country.Items.Add(Reader[1].ToString());
}
connection.Close();

if i do the union the country and course data's are combine. but i want separately.

R. Giskard Reventlov 25-May-11 7:54am

select 1 [Type], Course from course_master
union
select 2, country from country_master
etc, etc.
Only one call to the database.
Loop through the reader picking off the data you want as stated in my answer.

Sagotharan Jagadeeswaran 25-May-11 8:07am

ya friend, what you mention in type?. i don't understand, that one.

i accept the only one call to the database. but it give county combo box item include course combo box item. How to separate that.

For example course table have B.Sc, B.Com. And Country table have India, US.

I want to load that Course table data's in Course Combo box as soon as the Country. But that query give Course and Country data's combine. What i do for that?.

R. Giskard Reventlov 25-May-11 9:10am

That is what the Type column is for: it allows you to filter on the data from each table. try it and you'll see what I mean.

Sagotharan Jagadeeswaran 26-May-11 2:37am

string MyConString = ConfigurationManager.ConnectionStrings["College_Management_System.Properties.Settings.cmsConnectionString"].ConnectionString;
MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "select 1,name from course_master union select 2,name from country_master";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
cmbo_course.Items.Add(Reader[0].ToString());
}
connection.Close();

i try this code. but combo box items were get 1,1,2,2. In both table columnname was name only. Wheather its a problem?.

R. Giskard Reventlov 26-May-11 3:10am

Probably should be: cmbo_course.Items.Add(Reader[1].ToString());

You filter on Reader[0].

Sagotharan Jagadeeswaran 27-May-11 1:47am

Wow i got it!.

Thank you friend!,...