Basic Database Operations for C# Developers

As a C# developer, it’s important to have a solid understanding of basic database operations to effectively interact with databases in your applications. In this article, we’ll cover some of the most common database operations and how to perform them using C#.

Connecting to a Database

Before you can perform any database operations, you need to connect to the database. There are several ways to connect to a database in C#, including:

Using ADO.NET

ADO.NET is a set of classes and interfaces that provide a way to interact with databases from C#. To connect to a database using ADO.NET, you’ll need to create an instance of the SqlConnection class and pass in the connection string:

using System.Data.SqlClient;

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword";
SqlConnection connection = new SqlConnection(connectionString);

Using Entity Framework

Entity Framework is an ORM (Object-Relational Mapping) framework that allows you to interact with databases using C#. To connect to a database using Entity Framework, you’ll need to create an instance of the DbContext class and pass in the connection string:

using EntityFramework;

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword";
DbContext context = new DbContext(connectionString);

Using Dapper

Dapper is a lightweight ORM framework that allows you to interact with databases using C#. To connect to a database using Dapper, you’ll need to create an instance of the IDbConnection interface and pass in the connection string:

using Dapper;

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword";
IDbConnection connection = new DbConnection(connectionString);

Once you have connected to the database, you can perform various operations such as executing queries, inserting data, updating data, and selecting data.

Executing Queries

To execute a query in a C# application, you’ll need to use a database connection object. There are several ways to execute queries in C#, including:

Using ADO.NET

ADO.NET provides several methods for executing queries, including ExecuteNonQuery, ExecuteReader, and ExecuteScalar. Here’s an example of how to execute a query using ExecuteNonQuery:

using System.Data.SqlClient;

string query = "SELECT * FROM myTable";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

int rowsAffected = connection.ExecuteNonQuery(query);

Using Entity Framework

Entity Framework provides several methods for executing queries, including DbContext.Database.ExecuteSqlCommand, DbContext.Database.SqlQuery, and DbContext.Database.RawQuery. Here’s an example of how to execute a query using DbContext.Database.ExecuteSqlCommand:

using EntityFramework;

string query = "SELECT * FROM myTable";
DbContext context = new DbContext(connectionString);
context.Database.ExecuteSqlCommand(query);

Using Dapper

Dapper provides several methods for executing queries, including Connection.Execute, Connection.Query, and Connection.RawQuery. Here’s an example of how to execute a query using Connection.Execute:

using Dapper;

string query = "SELECT * FROM myTable";
IDbConnection connection = new DbConnection(connectionString);
connection.Open();

int rowsAffected = connection.Execute(query);

Inserting Data

To insert data into a database using C#, you’ll need to use a database connection object and the Insert method. Here’s an example of how to insert data into a table using ADO.NET:

using System.Data.SqlClient;

string query = "INSERT INTO myTable (column1, column2) VALUES (@p1, @p2)";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@p1", "value1");
command.Parameters.AddWithValue("@p2", "value2");

int rowsAffected = command.ExecuteNonQuery();

Updating Data

To update data in a database using C#, you’ll need to use a database connection object and the Update method. Here’s an example of how to update data in a table using ADO.NET:

using System.Data.SqlClient;

string query = "UPDATE myTable SET column1 = @p1, column2 = @p2 WHERE id = @p3";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@p1", "new value1");
command.Parameters.AddWithValue("@p2", "new value2");
command.Parameters.AddWithValue("@p3", 123);

int rowsAffected = command.ExecuteNonQuery();

Selecting Data

To select data from a database using C#, you’ll need to use a database connection object and the SqlCommand or DbContext class. Here’s an example of how to select data from a table using ADO.NET:

using System.Data.SqlClient;

string query = "SELECT * FROM myTable";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    // read data from the database
}

Using Entity Framework

To select data from a database using Entity Framework, you’ll need to use the DbContext class and the SqlQuery method. Here’s an example of how to select data from a table using Entity Framework:

using EntityFramework;

string query = "SELECT * FROM myTable";
DbContext context = new DbContext(connectionString);

IQueryable<myTable> results = context.Database.SqlQuery(query);

foreach (myTable item in results)
{
    // read data from the database
}

Using Dapper

To select data from a database using Dapper, you’ll need to use the Connection class and the Query method. Here’s an example of how to select data from a table using Dapper:

using Dapper;

string query = "SELECT * FROM myTable";
IDbConnection connection = new DbConnection(connectionString);

IEnumerable<myTable> results = connection.Query(query);

foreach (myTable item in results)
{
    // read data from the database
}

Conclusion

In this article, we’ve covered some of the most common database operations in C#, including connecting to a database, executing queries, inserting data, updating data, and selecting data. By understanding these basic operations, you’ll be well-equipped to build robust C# applications that interact with databases.