CODEWITHSUNDEEP
c#
Yilmaz Paçariz
Yilmaz Paçariz

Reputation: 186

a few query with one sql connection

private void button5_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True");
    SqlCommand cmd = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='1'", conn);
    conn.Open();
    label1.Text = cmd.ExecuteReader().ToString();
    conn.Close();

    SqlConnection conn1 = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True");
    SqlCommand cmd1 = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='2'", conn1);
    conn1.Open();
    label2.Text = cmd1.ExecuteReader().ToString();
    conn1.Close();

    SqlConnection conn2 = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True");
    SqlCommand cmd2 = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='3'", conn2);
    conn2.Open();
    label3.Text = cmd2.ExecuteReader().ToString();
    conn2.Close();
}

I fetch the label texts from database. But in every fetching operation I open a connection in order to write a query. This is my first project in C#. How can I write a few query without opening many connections ? can anyone help me?

Upvotes: 1

Views: 5839

Answers (5)

Pierre-Alain Vigeant
Pierre-Alain Vigeant

Reputation: 23083

Just showing another method which only require a single connection, a single command and a single data reader.

While Tim Schmelter approach is the most effective in your case, this a demo of the NextResult method of a DataReader.

Notice how the sql query in the SqlCommand included 3 sub queries separated by a semicolon. Whenever you call NextResult, you move to the next query.

using (var connection = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True"))
using (var command = new SqlCommand(
@"select label_sh from label_text where label_form='2' and label_form_labelID='1';
select label_sh from label_text where label_form='2' and label_form_labelID='2';
select label_sh from label_text where label_form='2' and label_form_labelID='3'", connection))
using (var reader = command.ExecuteReader())
{
    var label1 = reader["label_sh"];

    reader.NextResult();

    var label2 = reader["label_sh"];

    reader.NextResult();

    var label3 = reader["label_sh"];
}

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

  1. use using-statement to ensure that a connecion gets closed even in case of exception. You should always use it when a class implements IDisposable.
  2. With Connection-Pooling you're not always opening and closing connections when you call con.Open() or con.Close(). Actually Close just makes the connection reusable, otherwise it would be marked as "in use". So it's good practise to close connections as soon as possible.

You could use a DataAdapter to fill a DataTable with one query. Then you would have all three records and could take what you need:

using (var conn = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True"))
{
    var sql = "select label_sh from label_text where label_form_labelID IN('1','2','3') and label_form='2'";
    using (var da = new SqlDataAdapter(sql, conn))
    {
        da.Fill(table); // you don't need to open a connection when using a DataAdapter
    }
}

label1.Text = table.AsEnumerable()
                   .Single(r => r.Field<int>("label_form_labelID") == 1)
                   .Field<String>("label_sh");
label2.Text = table.AsEnumerable()
                   .Single(r => r.Field<int>("label_form_labelID") == 2)
                   .Field<String>("label_sh");
label3.Text = table.AsEnumerable()
                  .Single(r => r.Field<int>("label_form_labelID") == 3)
                  .Field<String>("label_sh");

Note that you need to add using System.Linq; for Linq-To-DataTable.

Upvotes: 3

Ryblex
Ryblex

Reputation: 11

Well, I suggest you to create only one connection to de DB

 SqlConnection conn = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True");

and then you can use SQL IN Operator to make only one query like this

select label_sh 
from label_text 
where label_form='2' and label_form_labelID IN ('1','2','3')

SQL IN Operator

Upvotes: 1

Erwin
Erwin

Reputation: 4817

You can reuse your SqlConnection for all the SqlCommand objects and after your are finished you can close the SqlConnection:

SqlConnection conn = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True");
conn.Open();

SqlCommand cmd = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='1'", conn);    
label1.Text = cmd.ExecuteReader().ToString();

SqlCommand cmd1 = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='2'", conn);    
label2.Text = cmd1.ExecuteReader().ToString();  


SqlCommand cmd2 = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='3'", conn);   
label3.Text = cmd2.ExecuteReader().ToString();

conn.Close();

But it's even better for the performance to create one SQL query to retrieve your labels.

Upvotes: 1

Joe Doyle
Joe Doyle

Reputation: 6383

There's no need to close the connection each time. You can even reuse the SqlCommand variable in your example.

private void button5_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True");

            SqlCommand cmd = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='1'", conn);
            conn.Open();
            label1.Text = cmd.ExecuteReader().ToString();

            cmd.CommandText ="select label_sh from label_text where label_form='2' and label_form_labelID='2'";
            label2.Text = cmd.ExecuteReader().ToString();

            cmd.CommandText = "select label_sh from label_text where label_form='2' and label_form_labelID='3'"
            label3.Text = cmd.ExecuteReader().ToString();

            conn.Close();
        }

Upvotes: 2

Related Questions