C# Connect to an SQL database

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.

Table of Contents

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


using System.Data.SqlServer;

using System.Data.SqlServer;

SQL Server Compact


using System.Data.SqlServerCe;

using System.Data.SqlServerCe;

MySQL


using System.Data.MySqlClient;

using System.Data.MySqlClient;

Oracle


using Oracle.DataAccess.Client;

using Oracle.DataAccess.Client;

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


color {id, name}

color {id, name}

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


            // Prepare the connection to the database
            string connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                // Login to the database
                connection.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                
            }

// Prepare the connection to the database

string connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

SqlConnection connection = new SqlConnection(connectionString);

try

{

// Login to the database

connection.Open();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

Run a query

The SqlCommand object is used to execute SQL queries.


// Prepare the SQL query to execute
 SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT id, name FROM color";

// Prepare the SQL query to execute

SqlCommand command = connection.CreateCommand();

command.CommandText = "SELECT id, name FROM color";

We will list all our colors

We use a SqlDataReader to loop and read the data.


            SqlDataReader dataReader = command.ExecuteReader();

            while (dataReader.Read())
            {
                Console.WriteLine(dataReader["name"]);
            }

SqlDataReader dataReader = command.ExecuteReader();

while (dataReader.Read())

{

Console.WriteLine(dataReader["name"]);

}

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.


command.CommandText = "SELECT * FROM color WHERE id = @id";
command.Parameters.AddWithValue("@id", 3);

command.CommandText = "SELECT * FROM color WHERE id = @id";

command.Parameters.AddWithValue("@id", 3);

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.


            command.CommandText = "INSERT INTO color (id, name) VALUES (@id, @name)";
            command.Parameters.AddWithValue("@id", 1);
            command.Parameters.AddWithValue("@name", "RED");

            
            command.ExecuteNonQuery();

command.CommandText = "INSERT INTO color (id, name) VALUES (@id, @name)";

command.Parameters.AddWithValue("@id", 1);

command.Parameters.AddWithValue("@name", "RED");

command.ExecuteNonQuery();

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.

C# Connect to an SQL database3 min read

Presentation

Prerequisites

Connection to the database

Run a query

We will list all our colors

Filter results / Pass parameters

Create, edit, or delete data

Conclusion

Leave a Comment X

Presentation

Prerequisites

Connection to the database

Run a query

We will list all our colors

Filter results / Pass parameters

Create, edit, or delete data

Conclusion

You may also like

Leave a Comment X