是否可以在单个连接中执行多个查询? [英] Is any possible Multiple query executing in single connection?

问题描述

朋友，

在我的表单中，有很多组合框.我想将不同的表数据加载到组合框中.我正在努力做到这一点.但是代码进行的时间很长.在单个连接中是否可以执行多个查询?

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();

已更新-

经过数字人和Original Griff的帮助之后，我将代码更新为:-

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();

在这种情况下，当我尝试在一个连接中执行此操作时.如果我删除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();

推荐答案

首先，为什么不重用连接对象而不是每次都创建一个新对象?

无论如何，一种快速的方法可能是合并所有查询(假设返回了相等数量的列或为了平衡而添加了空列)，添加了一个包含数字的列，以便您可以区分每个集合然后遍历结果，其中该数字=您的循环计数器并填充控件.

另外，仅从数据库返回所需的列.

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.

是的，您可以重新使用连接:您无需创建新的连接.您还可以重新使用阅读器和命令.

更重要的是，不要先执行"SELECT *"，然后再依赖字段的顺序位置:它们可能不会保持这种状态. Reader [2]有效，但是如果您更改表定义，则可能不会.而是使用字符串形式，或者最好还是只选择您实际要使用的那些字段:这还将在与数据库服务器通信时节省带宽.

您甚至可以只使用DataAdapter并将组合框的DataSource属性直接设置为所选信息:

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;
      }
   }

这篇关于是否可以在单个连接中执行多个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！