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