ARTICLE

Working with the SqlConnection and SqlCommand Classes in ADO.NET

Posted by A K Articles | ADO.NET December 29, 2011
Here, you will learn about the SqlConnection and SqlCommand Classes in ADO.NET with very simple examples.
Reader Level:

Introduction

SqlConnection and SqlCommand are classes of a connected architecture and found in the System.Data.SqlClient namespace. The SqlConnection class makes the connection with the database. Further this connection (database connection) is used by the SqlCommand to work with that database. The SqlCommand class is used to execute the SQL statements. Let's work with the SqlConnection and SqlCommand classes with simple examples.

SqlConnection Class 

Here, we will use two important methods of SqlConnection.

 Open() : The open() method  is used to open the Database connection.
 Close() : The close() method is used to close the Database connection.

Look at the following code:

using
System;
using
System.Data;
using
System.Data.SqlClient;

namespace
sqlconnectionANDsqlcommand
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void btnclick_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=aaaaaaa");
            conn.Open(); 
// Open the connection
            // body 
            // body
            conn.Close();
// Close the connection
        }   
    }
}

In the preceding code, I have added the namespace "System.Data.SqlClient". On the button click event, I have created an
instance as "conn" of SqlConnection and passing a connection string (database) as parameter. Then, I have opened the connection by the open() method and closed to it by the close() method. We can also check whether the connection is open or closed by it's state property.
Add the following code to the application.

         private void btnclick_Click(object sender, EventArgs e)
        {
            // connection is opened. so if you click button then messagebox will be shown with the message "Open"
            SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=aaaaaaa");
            conn.Open();
            MessageBox.Show(conn.State.ToString());
            conn.Close();
        }
         private void btnok_Click(object sender, EventArgs e)
        {
            // connection is not opened. so if you click button then messagebox will be shown with the message "Closed"
            SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=aaaaaaa");
            MessageBox.Show(conn.State.ToString());           
        }
 

SqlCommand Class

The main role of SqlCommand is to execute SQL statements.

Properties

CommandText: The commandText property is used to set the SQL statement.

Connection: This property is used to get connection to the database source which is specified in SqlConnection. 

Method

ExecuteNonQuery() : The ExecuteNonQuery() method does not return any record. Which means we use ExecuteNonQuery() in all operations with databases except retrieving records from a database. It only returns the number of affected rows.

Now we use this method in our application. There is a database "student" and a database "student_detail" which has no record. I am giving a simple example to insert a record into database.

Take 3 labels, 3 textboxes and 1 button and arrange them as in the following figure.

sqlconnection class in .net

Write the following code

using
System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Data;
using
System.Drawing;
using
System.Linq;
using
System.Text;
using
System.Windows.Forms;
using
System.Data.SqlClient;
 

namespace
sqlconnectionANDsqlcommand
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        } 
        private void Form1_Load(object sender, EventArgs e)
        {
        }
 
        private void btnclick_Click(object sender, EventArgs e)
        {
            // Creating instance of SqlConnection
            SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=
aaaaaaa");
            conn.Open();// open the database connection
            SqlCommand cmd = new SqlCommand();// Creating instance of SqlCommand
            cmd.Connection = conn; // set the connection to instance of SqlCommand
            cmd.CommandText = "insert into student_detail values (" + txtrollno.Text + ",'" + txtname.Text + "','" + txtcourse.Text + "')";
// set
           
//the sql command ( Statement )
            cmd.ExecuteNonQuery();
            MessageBox.Show("Record Saved"); // showing messagebox for confirmation message for user
            conn.Close();// Close the connection
        }
 
      }
}
 
Output

Enter the RollNo., Name and Course and click the "save" button. It will save the record into database.

ExecuteScalar() : The ExecuteScalar() returns a single value from the database. Generally it is used with an aggregate function (a function which returns a single value).
Suppose there is a need to check how many records are in the database table. Then we use this function. Add another button to your application and set its text property to "Count" and write the following code on button click. 

        private void btncount_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=
aaaaaaa");
            conn.Open();// open the database connection
            SqlCommand cmd = new SqlCommand();// Creating instance of SqlCommand
            cmd.Connection = conn; // set the connection to instance of SqlCommand
            cmd.CommandText = "select count (*) from student_detail" ; // set the sql command ( Statement )
            string record= cmd.ExecuteScalar().ToString();
            MessageBox.Show("Total records : "+ record );
            conn.Close();// Close the connection
        }
 

Output

When you click the "count" button, a MessageBox will be displayed to show the total number of records in the database table.

ExecuteReader() : The ExecuteReader() method can return a set of records from a database. 

I will explain more about the ExecuteReader() method in my next article....

Here are some related resources.

COMMENT USING