ADO.NET using SQL Server
Posted by Superadmin on November 15 2023 10:24:00

ADO.NET using SQL Server

I will discuss ADO.NET using SQL Server Database with Examples in this article. Please read our previous article, where we discussed the Introduction of ADO.NET. At the end of this article, you will understand how to connect with the SQL Server database using ADO.NET. I hope you have SQL Server installed on your machine. We are using the SQL Server Management Studio (SSMS) Tool to interact with the SQL Server. 

  

Open SQL Server Management Studio Tool

Once you open SSMS (SQL Server Management Studio), It will prompt you to connect to the server window. Here, you need to provide the server name and authentication details (I am going with the Windows Authentication), select Database Engine as the server type, and finally, click the Connect button, as shown in the image below.

 

Open Microsoft SQL Server Management Tool

Once you click on the Connect button, it will connect to the SQL Server Database, and after a successful connection, it will display the following window.

 

ADO.NET using SQL Server

 

Creating Database in SQL Server

To create a database using GUI, you need to select the database option from Object Explorer and then right-click on it. It pops up an options menu, and here, you need to click on the New Database option, as shown in the below image.

Creating Database in SQL Server

Once you click on the New Database option, it will open the following New Database window. Here, you must provide the database name and click the OK button. Here, I created a database with the name Student. But it is up to you; you can provide any meaningful name you choose. 

   

Connecting to SQL Server using ADO.NET

Once you click on the OK button, it will create a Student database, and you can see the Student database in the object explorer, as shown in the image below.

How to connect to SQL Server using ADO.NET

 

That’s it. Our database part is over. Now, let us move to the ADO.NET part.

 

Establish a Connection to the SQL Server database and create a table using ADO.NET.

Once the Student Database is ready, let’s move and create a table (Student table) using the ADO.NET Provider and C# Code. Open Visual Studio 2017 (you can use any version of Visual Studio), then create a new .NET console application project. Once you create the project, then modify the Program.cs class file as shown below. In this article, I am not going to explain the code. This article will show you how to communicate with an SQL Server database. From our next article onwards, I will explain everything in detail.

using System; 
 using System.Data.SqlClient; 
 namespace AdoNetConsoleApplication
 { 
class Program
{ 
static void Main(string[] args) 
{ 
new Program().CreateTable();
Console.ReadKey();
} 
public void CreateTable() 
{ 
SqlConnection con = null;
try 
{ 
// Creating Connection  
con = new SqlConnection("data source=.; database=student; integrated security=SSPI");
 
// writing sql query  
SqlCommand cm = new SqlCommand("create table student(id int not null, name varchar(100), email varchar(50), join_date date)", con);
 
// Opening Connection  
con.Open();
 
// Executing the SQL query  
cm.ExecuteNonQuery();
 
// Displaying a message  
Console.WriteLine("Table created Successfully");
} 
catch (Exception e) 
{ 
Console.WriteLine("OOPs, something went wrong." + e);
} 
// Closing the connection  
finally 
{ 
con.Close();
} 
} 
}
}

Now, execute the program, and you should see the following message on the console.

Establish connection and create a table using ado.net

We can see the created table in Microsoft SQL Server Management Studio. It shows the created table as shown below.

 

Establish connection to SQL Server and create a table using ado.net

See, we have the Student table within the Student database. As of now, the Student table is empty. Insert one record into the Student table using ADO.NET and C#.

Inserting Record using C# and ADO.NET:

Please modify the Program.cs class file as shown below. Here, we will insert a record into the student table.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().InsertRecord();
Console.ReadKey();
}
public void InsertRecord()
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection("data source=.; database=student; integrated security=SSPI");
// writing sql query
SqlCommand cm = new SqlCommand("insert into student (id, name, email, join_date) values ('101', 'Ronald Trump', 'ronald@example.com', '1/12/2017')", con);
// Opening Connection
con.Open();
// Executing the SQL query
cm.ExecuteNonQuery();
// Displaying a message
Console.WriteLine("Record Inserted Successfully");
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong." + e);
}
// Closing the connection
finally
{
con.Close();
}
}
}
}

Once you run the application, you will get the following output.

 

Inserting Record using ADO.NET

Retrieve Record using C# and ADO.NET.

Here, we will retrieve the inserted data from the Student table of the student database. Please modify the Program.cs class file as shown below.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().DisplayData();
Console.ReadKey();
}
public void DisplayData()
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection("data source=.; database=student; integrated security=SSPI");
// writing sql query
SqlCommand cm = new SqlCommand("Select * from student", con);
// Opening Connection
con.Open();
// Executing the SQL query
SqlDataReader sdr = cm.ExecuteReader();
// Iterating Data
while (sdr.Read())
{
// Displaying Record
Console.WriteLine(sdr["id"] + " " + sdr["name"] + " " + sdr["email"]);
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong." + e);
}
// Closing the connection
finally
{
con.Close();
}
}
}
}

You will get the following output when you run the above program.

Retrieve Record using ADO.NET

Deleting Record from SQL Server database using C# and ADO.NET

As of now, the student table contains one record. Let us delete that record using ADO.NET and C#. Please modify the Program.cs class file code as shown below which will delete the record from the Student table.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().DeleteData();
Console.ReadKey();
}
public void DeleteData()
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection("data source=.; database=student; integrated security=SSPI");
// writing sql query
SqlCommand cm = new SqlCommand("delete from student where id = '101'", con);
// Opening Connection
con.Open();
// Executing the SQL query
cm.ExecuteNonQuery();
Console.WriteLine("Record Deleted Successfully");
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong." + e);
}
// Closing the connection
finally
{
con.Close();
}
}
}
}

It will display the following output once you execute the program.

Deleting Record from SQL Server database using ADO.NET

If you verify the student table, you will see that the record is deleted. In this article, I didn’t explain a single line of code intentionally. I will explain everything in detail in our next article.

Summary of ADO.NET using SQL Server:

Using ADO.NET with SQL Server involves establishing a connection to the SQL Server database, executing queries, retrieving and manipulating data, and managing transactions. Here’s an overview of how you can use ADO.NET with SQL Server:

 

Import Required Namespaces:
using System;
using System.Data;
using System.Data.SqlClient;
Establish a Connection:

Create a connection string with the necessary details to connect to your SQL Server database, such as the server name, database name, and authentication credentials.

string connectionString = "Server=MyDatabaseServerAddress;Database=MyDataBase;User Id=MyUserName;Password=MyPassword;";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
Execute Queries:

Use the SqlCommand class to create and execute SQL queries against the database. You can perform various types of queries, such as SELECT, INSERT, UPDATE, DELETE, and stored procedures.

string sqlQuery = "SELECT * FROM Employees";
SqlCommand command = new SqlCommand(sqlQuery, connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// Process the data from the reader
}
reader.Close();
Retrieve Data:

Use the SqlDataReader to retrieve and process data returned by SELECT queries. Iterate through the rows using the Read() method and access column values using indexer or GetString(), GetInt32(), etc., methods.

Update Data:

For data modification (UPDATE, INSERT, DELETE), you can use the ExecuteNonQuery() method of SqlCommand.

 

string updateQuery = "UPDATE Employees SET Salary = 50000 WHERE Department = 'IT'";
SqlCommand updateCommand = new SqlCommand(updateQuery, connection);
int rowsAffected = updateCommand.ExecuteNonQuery();
Use Transactions:

You can manage transactions using the SqlTransaction class. Transactions ensure that a group of operations is completed entirely or rolled back if an error occurs.

SqlTransaction transaction = connection.BeginTransaction();
try
{
// Perform data operations within the transaction
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
Close Connection:

Always close the connection when you’re done using it to free up resources.

connection.Close();

Remember that ADO.NET involves more manual handling than higher-level ORMs like Entity Framework. While ADO.NET provides more control, it also requires writing more code. Depending on your project’s needs and your familiarity with ADO.NET, you might use ADO.NET directly or explore alternatives like Entity Framework for more abstraction and ease of use.

In the next article, I will discuss ADO.NET SqlConnection Class in detail. Here, I explain ADO.NET using SQL Server, i.e., how to connect to SQL server using ADO.NET. I hope you enjoy this article. I would like to have your feedback. Please post your feedback, questions, or comments about this article.