Executing Queries and Commands in c#
In the C# programming language, database queries and commands in ADO.NET are reserved statements that are executed directly using SqlCommand or other database provider-specific command objects for SQL Server, such as OleDbCommand, OdbcCommand, and OracleCommand. This completely depends on the data provider software or application the C# user is using to extract and manage database operations. These commands help execute SQL database queries such as SELECT, INSERT, UPDATE, DELETE, command statements, or user-defined database stored procedure operations on the database.
So, let’s learn more about how to execute queries and commands in ADO.NET using C# programming.
Executing Database Queries with SqlCommand.
SELECT Query Concept with SqlDataReader.
To execute a SELECT command statement query that returns rows of data in a database query, C# users can use the SqlCommand statement object with the ExecuteReader() database function method. This method returns a SqlDataReader value in the current database. It provides a forward-only, read-only method or step for retrieving user-defined database data.
Example of executing a SELECT query to fetch data from a database.
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString =
“Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True”;
string query = “SELECT Id, Name, Age, FROM Employees”;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(query, conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
// here it Read the data from the SqlDataReader method
while(reader.Read())
{
Console.WriteLine(
$”Emp Id – {reader[“Id”]}, Employee Age – {reader[“Age”]}, Employee Name – {reader[“Name”]}”
);
}
}
}
}
}
}
SELECT query to fetch data Explanation.
- In this example, the ExecuteReader() method returns a SqlDataReader value to read the file data. The SqlDataReader.Read() function or method is called in a loop to display each database record row-by-row.
- The database connection is opened using a SqlConnection and executed using a SqlCommand query statement.
Insert / Update / Delete ExecuteNonQuery concept.
Here, we establish a database connection in the Employee table and apply insert, update, and delete SQL database command operations. The user can directly create an Employee database connection and insert, update, and delete new database table rows if needed.
Example of Insert/Update/Delete ExecuteNonQuery.
using System.Data.SqlClient;
string connectionString = “your_connection_string”;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string query = “INSERT INTO Employee(Id, Name, Age) VALUES(@id, @name, @age)”;
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue(“@id”, “101”);
cmd.Parameters.AddWithValue(“@name”, “Siddhi”);
cmd.Parameters.AddWithValue(“@age”, 19);
int rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($”New employee database Rows inserted – {rowsAffected}”);
}
}
Executing a Query / ExecuteReader concept.
Here, we can extract and retrieve particular records from the Employee database table using the ADO.NET ExecuteReader method function in the Employee table.
Example of Executing a Query / ExecuteReader.
using System.Data.SqlClient;
string query = “SELECT Id, Name, Age FROM Employees”;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
Console.WriteLine(
$”{reader[“Id”]} – {reader[“Name”]} – {reader[“Age”]}”
);
}
}
}
Getting a Single Database Value with the ExecuteScalar concept.
Here, we can extract a value by counting the number of records in a database table using the ADO.NET ExecuteScalar function method in the Employees table.
Example of a Single Database Value with ExecuteScalar.
string query = “SELECT COUNT(*) FROM Employees”;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
int count = (int)cmd.ExecuteScalar();
Console.WriteLine($”Here it is: Total Count Employees Numbers – {count}”);
}
}
