Different Ways To Access DataBase In ADO.NET

For accessing the database, for getting the data or inserting the data we basically use SQLDataAdapter.Fill(), ExecuteNonQuery() and ExecuteScalar(), I have seen when I was a fresher, many developers were confused between them and don’t know which one is suitable for which condition.

Therefore, today I will explain specially for beginners that when to choose one of these and why.

Now a days, most of the application is connected with database. Through the application we also modify the data, get the data. When we talk about getting the data from database, it can be single data, list of data or only single value. The data can be any of these. So, basically we use SqlCommand class to pass the query to database.

There are three methods available in ADP.NETwhich is used to access your database. And these are as follows.

1. SqlDataAdapter.Fill()
2. ExecuteNonQuery()
3. ExecuteScalar()

Create a Test database with Employee Table

To demonstrate this article, you need to access the database. So, it is required to create a database with a table. So, create a table “TestEmployee” with a table “Employees”. After adding the table, add some data into table.



For connecting to database, define your database connection in web.config as in the following,

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=Mukesh-Pc;Initial Catalog=Test; User Id=sa; Password=******;" providerName="System.Data.SqlClient" />
 </connectionStrings>

SqlDataAdapter.Fill()

It is used to retrieve the data from the database. Whenever you think that you need to get the data from any data source in ADO.NET and fill this data into DataTable or DataSet, then you need to use objSqlDataAdapter.Fill(objDataSet);. When you pass multiple select statement query to get the data, then it executes and get one by one data and pass it to corresponding tables.

So, first you need to create a Stored Procedure to get the data from the database.

CREATE PROCEDURE GetEmployeeList
AS
BEGIN
SELECT * FROM dbo.Employees
END

Here you need to write your code on Employee.aspx.cs for getting the data.

Example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace EmployeeDemo
{
    public partial class Emploee: System.Web.UI.Page
    {
        string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                GetEmployeesList();
            }
        }
        public DataSet GetEmployeesList()
        {
            DataSet dsEmployee = new DataSet();
            using(SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand objSqlCommand = new SqlCommand("GetEmployeeList", con);
                objSqlCommand.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                try
                {
                    objSqlDataAdapter.Fill(dsEmployee);
                    dsEmployee.Tables[0].TableName = "Employees";
                    grvEmployee.DataSource = dsEmployee;
                    grvEmployee.DataBind();
                }
                catch (Exception ex)
                {
                    return dsEmployee;
                }
            }
            return dsEmployee;
        }
    }
}

ExecuteNonQuery()

This is used when you think that multiple rows will be affected. You can use ExecuteNonQuery() for Inserting, Updating and Deleting the data. So, basically ExecuteNonQuery() does, it returns the number of rows effecting when performing DML [Data Manipulation Language] operation into the database. If no rows affected then it return -1, otherwise if the return value is greater than 0, it means, it affected rows into the database.

Example

public int AddEmployee(string name, string emailId, string age, string address, int DepartmentId)
{
    int i = 0;
    using(SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        SqlCommand objSqlCommand = new SqlCommand("Insert into Employees values ('" + name + "','" + emailId + "','" + age + "','" + address + "','" + DepartmentId + "')", con);
        try
        {
            i = objSqlCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            con.Close();
        }
    }
    return i;
}

ExecuteScalar()

It is used to get the single row, first value from the database. If the data is more than one row then it always give you first row, first column value and additional data ignore. It is very faster than others.

It will return null if the result data is empty and there is limitation with that, it can only return 2033 characters data. When you think that you need single data from database like sum, count, etc. then you should use this.

Example

public string GetEmployeeName(int id)
{
    string employeeName = string.Empty;
    using(SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        SqlCommand objSqlCommand = new SqlCommand("select name from employees where id='" + id + "'", con);
        try
        {
            employeeName = Convert.ToString(objSqlCommand.ExecuteScalar());
        }
        catch (Exception ex)
        {
            con.Close();
        }
    }
    return employeeName;
}

Thanks for reading this article, hope you enjoyed it.