ADO.NET is a data access technology created by Microsoft. It helps .NET developers to connect to the database from C# code. This technology is a set of software components that developers use to access data and data services from a database.
ADO.NET stands for ActiveX Data Objects. It acts as a layer that connects the database and C# code.
Before you start this tutorial, make sure you are comfortable with the basics of C# and SQL. If you aren’t, check out my tutorials on C# and MS SQL Server Basics.
In this ADO.NET tutorial, let’s see how we can implement ADO.NET and connect the C# code to the MS SQL Server database using Visual Studio.
Let’s jump right in.
Steps to Create a Database and Table in ADO.NET
Open Visual Studio and create a new C# console-based project.
Navigate to the following options:
View -> SQL Server Object Explorer -> SQLServer -> LocalDB -> Right Click -> New Query
You can write SQL queries here. Write a query to create a database.
create database adodotnet;
Now, let’s create a table in this database.
create table Student (id int, name varchar(30), phone_number int);
Now, we have a table and a database. We can connect C# to this database and table using ADO.NET.
Let’s start writing the C# code.
Steps to Write ADO.NET Code
Let’s look at a rough template to see how we are connecting to the database and performing CRUD operations. These are the steps that we will follow for writing ADO.NET code in C#.
The first thing you need to do is to import the System.Data.SqlClient namespace.
using System.Data.SqlClient;
Now, create a connection object. You need to mention the data source, initial catalog, user id, and password for the database.
You can find the data source name in the following section in SQL Server Object Explorer.
The Initial Catalog is the name of the database you want to connect to. In our example, the database name is adodotnet.
The User id and Password are optional fields.
string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;
Initial Catalog=adodotnet;
User id= UserName;
Password = Secret;
";
SqlConnection connectionObject = new SqlConnection(connectionString);Once you create a connection object, you need to open it.
connectionObject.Open();Now you can write an SQL query as a string and create an object of SqlCommand.
string sql = "insert into Student values(@id,'Bob',12345)";
SqlCommand command = new SqlCommand(sql, connectionObject);Now, if your query is dynamic, you can pass your parameters to the query. In the above example, I have kept the variable @id for accepting the id value dynamically. Let’s add a parameter value to this variable.
command.Parameters.AddWithValue("@id", 101);It is time to execute the query. We can execute an SQL query using three methods. We will see when to use which method.
Let’s execute the SQL query using ExecuteNonQuery(). We use this method to execute any SQL statements if we don’t want any result set to be returned.
command.ExecuteNonQuery();If you want the result set to be returned as an array of DataSet, then use the ExecuteReader() command and store the array in an SqlDataReader object. You can loop through this array and get values one by one.
SqlDataReader r = command.ExecuteReader();
while (r.Read())
{
Console.WriteLine(r["id"]);
Console.WriteLine(r["name"]);
}
r.Close();If you want the SQL query to return a single value, then use the ExecuteScalar() method.
var maxId = command.ExecuteScalar();You can use any of the three methods mentioned above based on your requirement to execute the query.
Once the query is executed, you can print the success message or perform the necessary operations.
Console.WriteLine("Success message!");Once all the database-related tasks are done, you can close the connection object.
connectionObject.Close();Note: I would suggest using try-catch blocks when you write ADO.NET code so that you can handle all the exceptions properly.
Complete Code for ADO.NET CRUD Operations
Given below is the code to perform basic CRUD operations using ADO.NET in Visual Studio with MS SQL Server database.
using System;
using System.Data.SqlClient;
namespace AdoDotNetApp
{
public class AdoDotNetBasics
{
static void Main(string[] args)
{
SqlConnection conn = createConnection();
conn.Open();
Student bob = new Student(101, "Bob", 198765);
Student ann = new Student(102, "Ann", 112211);
insertData(conn, bob);
insertData(conn, ann);
updateData(conn,101);
deleteData(conn, 101);
deleteData(conn,102);
getData(conn);
conn.Close();
Console.WriteLine("Connection closed.");
}
static SqlConnection createConnection()
{
try
{
string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;
Initial Catalog=adodotnet;";
SqlConnection connectionObject = new SqlConnection(connectionString);
Console.WriteLine("Connection established.");
return connectionObject;
}
catch (Exception e)
{
Console.WriteLine(e);
return null;
}
}
static void insertData(SqlConnection conn, Student s)
{
try
{
string sql = "insert into Student values(@id,@name,@phone);";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@id", s.id);
command.Parameters.AddWithValue("@name", s.name);
command.Parameters.AddWithValue("@phone", s.phone);
command.ExecuteNonQuery();
Console.WriteLine("Data inserted successfully.");
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
static void updateData(SqlConnection conn, int id)
{
try
{
string sql = "update Student set name='John' where id=@id;";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
Console.WriteLine("Data updated successfully.");
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
static void getData(SqlConnection conn)
{
try
{
string sql = "select * from Student;";
SqlCommand command = new SqlCommand(sql, conn);
SqlDataReader r = command.ExecuteReader();
while (r.Read())
{
Console.WriteLine(r["id"]);
Console.WriteLine(r["name"]);
}
// for retrieving a single value, use:
// var maxId = command.ExecuteScalar();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
static void deleteData(SqlConnection conn, int id)
{
try
{
string sql = "delete from Student where id=@id;";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
Console.WriteLine("Data deleted successfully.");
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
class Student
{
public int id;
public string name;
public int phone;
public Student(int id, string name, int phone)
{
this.id = id;
this.name = name;
this.phone = phone;
}
}
}
Output:
