SQl Server database operation using C#

Fetch Data from SQL Server using C#

Introduction :-

Fetching data form SQL Server is programmers daily job . I this article I am going to show how to perform database crud operation in C#. Those who are new in database connection or trying to learn how to connect database using C# , my article may helpful for them.

What is ADO.NET

If you are trying to learning data access mechanism using C# language ,at first you have to learn ADO.NET concept. ADO.NET is nothing but a set of classes that will give you one platform to access and manipulate data with most of databases using C# programming language. If you thinking ADO.NET is nothing but a class library which is given by .NET framework then you are correct.

And we can assume ADO.NET as a layer over database which encapsulate data access mechanism with various databases. I am saying various database means , Yes using ADO.NET you can talk with database like SQLServer, Oracle , Informix etc.

Few Important classes of ADO.NET for SQL Server communication

There are few very important classes are present in ADO.NET which we developer people frequently use. At first you have to include below namespace

using System.Data.SqlClient

to access all those classes. Because in .NET Framework class library those classes are put together within Sysyem.Data.SqlClient namespace. Let’s learn few very important classes.

SqlConnection class

This is one of the most important class of ADO.NET. Object of this class is responsible to connect with database. Below code is to create object of Sqlconnection class

SqlConnection con = new SqlConnection();

SqlCommand class

SqlCommand class is used to create command for data access mechanism. We can create object of SqlCommand class like below.

SqlCommand cmd = new SqlCommand();

SqlDataReader class

Sqldatareader class is used to create data reader object to read data from database.

SqlDataAdapter class

SqlDataAdpater class is very important to developer .  This class perform as a bridge of connected and disconnected architecture. We can create object of SqlDataAdapter object like below.

SqlDataAdapter ad = new SqlDataAdapter();

SqlParameter class

SqlParameter class is very important to developer . When we want to pass parameter to command object we have to use this. Below code will show how to create object of SqlParameter class.

SqlParameter p = new SqlParameter()

Here I am giving one example how to fetch data from Sql server database

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
public partial class sqlserver : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    SqlDataReader rd;
    public string query;
 
    protected void connection()
    {
	con.ConnectionString = "server=.\\SQLEXPRESS; integrated security=SSPI; initial  catalog=sourav;";
	//Initial catalog =sourav  is my database name.
         con.Open();
        cmd.Connection = con;
     }
     protected void Page_Load(object sender, EventArgs e)
    {
        //Calling connection function 
        connection();
        cmd.CommandText = "select * from tablename";
       // Change table name according to your setting
        rd = cmd.ExecuteReader();
        if(rd.Read())
        {
           Response.Write(rd.GetSqlValue(0).ToString());
           Response.Write(rd.GetSqlValue(1).ToString());
        }
        rd.Close();
    }
}

N.B:- don’t forget to change server name , database name and table name according to your setting.

Below example is to insert data in SqlServer database

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 public partial class sqlserver : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    public string query;
     protected void connection()
    {
        con.ConnectionString = "server=.\\SQLEXPRESS; integrated security=SSPI; initial catalog=sourav;";
        con.Open();
        cmd.Connection = con;
 
    }
     protected void Page_Load(object sender, EventArgs e)
    {
        //Calling connection function 
         connection();
         //Change
         below line according to your table  and data
         cmd.CommandText = "insert into loginfo values('abc','xyz')";
        cmd.ExecuteReader();
    }
}

 Below code is to update data in Sql Server database using C#

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 public partial class sqlserver: System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    SqlDataReader rd;
    public string query;
    protected void connection()
    {
        con.ConnectionString = "server=.\\SQLEXPRESS; integrated security=SSPI; initial catalog=sourav;";
        con.Open();
        cmd.Connection = con;
    }
    
    protected void Page_Load(object sender, EventArgs e)
    {
        connection();
        cmd.CommandText = "update loginfo set loginfo.username='aaa' where loginfo.password='xyz'";
        cmd.ExecuteReader();
    }
}

 In below, The C# code to delete data from Database

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
public partial class sqlserver: System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    SqlDataReader rd;
    public string query;
    protected void connection()
    {
        con.ConnectionString = "server=.\\SQLEXPRESS; integrated security=SSPI; initial catalog=sourav;";
        con.Open();
        cmd.Connection = con;
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        //Calling connection function
        connection();
       //Change the tablename here
       cmd.CommandText = "delete from loginfo where username='aaa'";

        cmd.ExecuteReader();
    }
}