Post
Ask Question

Working with the SqlConnection and SqlCommand Classes in ADO.NET

Introduction

 
SqlConnection and SqlCommand are classes of a connected architecture and found in the System.Data.SqlClient namespace. The SqlConnection class makes a 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
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4.   
  5. namespace sqlconnectionANDsqlcommand  
  6. {  
  7.     public partial class Form1 : Form  
  8.     {  
  9.         public Form1()  
  10.         {  
  11.             InitializeComponent();  
  12.         }  
  13.         private void btnclick_Click(object sender, EventArgs e)  
  14.         {  
  15.             SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=aaaaaaa");  
  16.             conn.Open();  // Open the connection  
  17.             // body    
  18.             // body   
  19.             conn.Close(); // Close the connection  
  20.         }      
  21.     }  
  22. }  
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 its state property.

Add the following code to the application.
  1. private void btnclick_Click(object sender, EventArgs e)  
  2. {  
  3.     // connection is opened. so if you click button then messagebox will be shown with the message "Open"  
  4.     SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=aaaaaaa");  
  5.     conn.Open();  
  6.     MessageBox.Show(conn.State.ToString());  
  7.     conn.Close();  
  8. }  
  9. private void btnok_Click(object sender, EventArgs e)  
  10. {  
  11.     // connection is not opened. so if you click button then messagebox will be shown with the message "Closed"  
  12.     SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=aaaaaaa");  
  13.     MessageBox.Show(conn.State.ToString());              
  14. }   

SqlCommand Class

 
The main role of SqlCommand is to execute SQL statements.
 

SqlCommand 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. 
 

SqlCommand 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
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Drawing;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Windows.Forms;  
  9. using System.Data.SqlClient;  
  10.    
  11. namespace sqlconnectionANDsqlcommand  
  12. {  
  13.     public partial class Form1 : Form  
  14.     {  
  15.         public Form1()  
  16.         {  
  17.             InitializeComponent();  
  18.         }   
  19.         private void Form1_Load(object sender, EventArgs e)  
  20.         {  
  21.         }  
  22.    
  23.         private void btnclick_Click(object sender, EventArgs e)  
  24.         {  
  25.             // Creating instance of SqlConnection  
  26.             SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=aaaaaaa");  
  27.             conn.Open();// open the database connection  
  28.             SqlCommand cmd = new SqlCommand();// Creating instance of SqlCommand  
  29.             cmd.Connection = conn; // set the connection to instance of SqlCommand  
  30.             cmd.CommandText = "insert into student_detail values (" + txtrollno.Text + ",'" + txtname.Text + "','" + txtcourse.Text + "')"; // set  
  31.             //the sql command ( Statement )  
  32.             cmd.ExecuteNonQuery();  
  33.             MessageBox.Show("Record Saved"); // showing messagebox for confirmation message for user  
  34.             conn.Close();// Close the connection  
  35.         }  
  36.    
  37.       }  
  38. }  
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.
  1. private void btncount_Click(object sender, EventArgs e)  
  2. {  
  3.     SqlConnection conn = new SqlConnection("Database=student;Server=.;user=sa;password=aaaaaaa");  
  4.     conn.Open();// open the database connection  
  5.     SqlCommand cmd = new SqlCommand();// Creating instance of SqlCommand  
  6.     cmd.Connection = conn; // set the connection to instance of SqlCommand  
  7.     cmd.CommandText = "select count (*) from student_detail" ; // set the sql command ( Statement )  
  8.     string record= cmd.ExecuteScalar().ToString();  
  9.     MessageBox.Show("Total records : "+ record );   
  10.     conn.Close();// Close the connection  
  11. }  
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.


Similar Articles