C# Connect to an SQL database – Programming Code Examples
C# C# Console Application

C# Connect to an SQL database3 min read

If you’re looking for a way to connect to a database, I’d rather read this tutorial on linking to a database with Entity Framework, offering the ability to interact without using SQL queries.
I just leave this one online just in case. To learn more, read the conclusion of this tutorial.

Presentation

Today, I discuss database interaction from your .NET application: read, add, modify, and delete data.




This tutorial is valid for most .NET projects (WPF, WCF, ASP.NET, Windows Forms …) having the possibility of communicating directly with a database. (I will not talk about Windows Phone, Windows 8, Silverlight, requiring an additional intermediary like a webservice …)

Prerequisites

Depending on the DBMS you are using, some components may need to be downloaded.

For SQL Server, everything is native.
Here are the component links for MySQL and Oracle:

  • Download MySQL Connector / NET
  • Download Oracle Data Provider (ODP.NET)
  • For others, I’ll let you search the net šŸ˜‰

To beginā€¦
We will integrate references using using statements.

 

SQL ServerĀ 

 

SQL Server Compact

 

MySQL

 

Oracle

 

NOTE :
If the reference is underlined in red, make sure that it is well integrated into your project.
Right-click your project, add a reference and find the DLL you need.

The examples below are valid with SQL Server only.

However, for another DBMS, you only need to take the equivalent according to this table:

SQL Server MySQL Oracle SQL Server Compact Edition
SqlConnection MySqlConnection OracleConnection SqlCeConnection
SqlCommand MySqlCommand OracleCommand SqlCeCommand
SqlDataReader MySqlReader OracleReader SqlCeReader

Connection to the database

We imagine an application that wants to connect to a SQL Server database containing a single table

The connection is established with a SqlConnection object, passing it the connection string.

Run a query

The SqlCommand object is used to execute SQL queries.

We will list all our colors

We use a SqlDataReader to loop and read the data.

Filter results / Pass parameters

To filter the results, we can use the keyword WHERE.
In order to have a clear and secure code, it is recommended to go through parameters rather than generate the query by concatenating strings.
We use AddWithValue() of the Parameters property of our SqlCommand object.

Create, edit, or delete data

We also use SqlCommand. And since we do not need to read any results, we can use the ExecuteNonQuery() method.

The principle remains the same for UPDATE or DELETE requests.

Conclusion

Create a specific class to manage each table in your database with methods to read, add, edit, or delete data. This will allow you to make the job clean enough and you will avoid mixing your brushes.

Finally, there is another way to interact with a database, via an ORM. This type of tool allows you to automatically generate objects corresponding to the elements of your database (tables, fields, columns …).
This allows you to no longer have to write SQL queries and to facilitate the maintenance of your application.

 

Leave a Comment