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 especially for beginners that when choosing one of these and why.
Nowadays, most of the application is connected with the database. Through the application, we also modify the data, get the data. When we talk about getting the data from the database, it can be single data, a list of data, or only a single value. The data can be any of these. So, basically we use SqlCommand class to pass the query to the database.
There are three methods available in ADP.NET which are 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 to the table.
TestEmployee
For connecting to database, define your database connection in web.config as in the following,
  1. <connectionStrings>
  2. <add name="DefaultConnection" connectionString="Data Source=Mukesh-Pc;Initial Catalog=Test; User Id=sa; Password=******;" providerName="System.Data.SqlClient" />
  3. </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 gets 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.
  1. CREATE PROCEDURE GetEmployeeList
  2. AS
  3. BEGIN
  4. SELECT * FROM dbo.Employees
  5. END
    table
    Here you need to write your code on Employee.aspx.cs for getting the data.
    Example
    1. using System;
    2. using System.Collections.Generic;
    3. using System.Linq;
    4. using System.Web;
    5. using System.Web.UI;
    6. using System.Web.UI.WebControls;
    7. using System.Configuration;
    8. using System.Data.SqlClient;
    9. using System.Data;
    10. namespace EmployeeDemo
    11. {
    12. public partial class Emploee: System.Web.UI.Page
    13. {
    14. string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
    15. protected void Page_Load(object sender, EventArgs e)
    16. {
    17. if (!this.IsPostBack)
    18. {
    19. GetEmployeesList();
    20. }
    21. }
    22. public DataSet GetEmployeesList()
    23. {
    24. DataSet dsEmployee = new DataSet();
    25. using(SqlConnection con = new SqlConnection(connectionString))
    26. {
    27. SqlCommand objSqlCommand = new SqlCommand("GetEmployeeList", con);
    28. objSqlCommand.CommandType = CommandType.StoredProcedure;
    29. SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
    30. try
    31. {
    32. objSqlDataAdapter.Fill(dsEmployee);
    33. dsEmployee.Tables[0].TableName = "Employees";
    34. grvEmployee.DataSource = dsEmployee;
    35. grvEmployee.DataBind();
    36. }
    37. catch (Exception ex)
    38. {
    39. return dsEmployee;
    40. }
    41. }
    42. return dsEmployee;
    43. }
    44. }
    45. }

    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 returns -1, otherwise, if the return value is greater than 0, it means, it affected rows into the database.
    Example
    1. public int AddEmployee(string name, string emailId, string age, string address, int DepartmentId)
    2. {
    3. int i = 0;
    4. using(SqlConnection con = new SqlConnection(connectionString))
    5. {
    6. con.Open();
    7. SqlCommand objSqlCommand = new SqlCommand("Insert into Employees values ('" + name + "','" + emailId + "','" + age + "','" + address + "','" + DepartmentId + "')", con);
    8. try
    9. {
    10. i = objSqlCommand.ExecuteNonQuery();
    11. }
    12. catch (Exception ex)
    13. {
    14. con.Close();
    15. }
    16. }
    17. return i;
    18. }
    ExecuteNonQuery

    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 gives you the 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 a limitation with that, it can only return 2033 characters data. When you think that you need single data from a database like a sum, count, etc. then you should use this.
    Example
    1. public string GetEmployeeName(int id)
    2. {
    3. string employeeName = string.Empty;
    4. using(SqlConnection con = new SqlConnection(connectionString))
    5. {
    6. con.Open();
    7. SqlCommand objSqlCommand = new SqlCommand("select name from employees where id='" + id + "'", con);
    8. try
    9. {
    10. employeeName = Convert.ToString(objSqlCommand.ExecuteScalar());
    11. }
    12. catch (Exception ex)
    13. {
    14. con.Close();
    15. }
    16. }
    17. return employeeName;
    18. }
    article
    Thanks for reading this article, hope you enjoyed it.