Click here to Skip to main content
15,868,141 members
Articles / Database Development

Using C# to Connect to and Query from a SQL Database

Rate me:
Please Sign up or sign in to vote.
4.71/5 (40 votes)
16 Mar 2015CPOL8 min read 368.5K   65   26
Using C# to get data in- and out of your (relational) database

As a developer, you’ll probably spend a lot of time getting data in and out of a database. Data is important in any organization and your job as a developer is to present that data to a user, have them add or edit that data and store it back to the database.

Yet, I have found that many developers really have no clue how to work with a database! Many developers can get data out of databases, but do so in an unsafe way that may break your code and, worse, give hackers an opportunity to get direct access to your database! Others use an ORM like NHibernate, Entity Framework or LINQ To SQL, but have no idea what’s going on. In this blog post, I will address these issues: how to setup a database connection, query for data in a secure manner and use that data in your code. I’ll also show you how to push data back to a database.

I am assuming you know how to set up a database and you know your way around C# and the .NET Framework. For my example, I have used the Adventure Works 2014 Sample Database on a SQL Server 2014 database.

So let’s start. To create a connection to a database, you’ll first need a database connection object. In our case, we need a specific type of connection object, being the SqlConnection. Using the SqlConnection, you can configure all kinds of settings that are used for your current session to the database. In this blog, we’ll use defaults only. For creating the SqlConnection, we’ll use the constructor that takes a (connection)string as input parameter. Usually, you’d get the connection string from a config file or some such. Alternatively, you can create one using the SqlConnectionStringBuilder, but I won’t go into that here. Notice that I’ve wrapped the SqlConnection in a using block. This ensures that the connection is actually closed once we’re done with it. Make sure you actually open the connection only when needed.

C#
using (SqlConnection connection = new SqlConnection("Data Source=(local);
Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
{
    connection.Open();
}

Unfortunately, this doesn’t do anything yet. We’ll need a SqlCommand which takes the query we want to send to the database. In this case, I’m going to select all persons from the table Person.Person. We can create a command object in different ways, but I’m going to create one using the constructor that takes the query and our just created connection. Once we have created the command, we must open the connection (which we already did) and have it execute our query. There are a few ways to have the command actually execute your query.
The first is ExecuteNonQuery, which seems odd because we are going to execute a query, right? Well, actually you use this method when you don’t expect a result (perhaps an update statement, or a call to a Stored Procedure that returns no resultset).

The second method, and the one we’ll need in this example, is ExecuteReader. This method returns a SqlDataReader which represents a forward-only stream of rows from the database. The columns of each row can be accessed by index or name. We’ll see how to use the SqlDataReader in the next example.

The third method, and last I will discuss, is ExecuteScalar. You can use this method when you expect exactly one result from a query.

There’s also an ExecuteXmlReader method which I will not discuss here. Additionally, every method has its async versions. For older versions of .NET, these are the BeginExecute and EndExecute methods and for later versions of .NET, these are the ExecuteAsync methods. I will not discuss them here.

So let’s look at our example. We’re going to create a command to fetch some data from the Person.Person table and use ExecuteReader to get our results.

C#
List persons = new List();
using (SqlConnection connection = new SqlConnection("Data Source=(local);
Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, 
FirstName, MiddleName, LastName FROM Person.Person", connection))
{
    connection.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // Check is the reader has any rows at all before starting to read.
        if (reader.HasRows)
        {
            // Read advances to the next row.
            while (reader.Read())
            {
                Person p = new Person();
                // To avoid unexpected bugs access columns by name.
                p.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                p.FirstName = reader.GetString(reader.GetOrdinal("FirstName"));
                int middleNameIndex = reader.GetOrdinal("MiddleName");
                // If a column is nullable always check for DBNull...
                if (!reader.IsDBNull(middleNameIndex))
                {
                    p.MiddleName = reader.GetString(middleNameIndex);
                }
                p.LastName = reader.GetString(reader.GetOrdinal("LastName"));
                persons.Add(p);
            }
        }
    }
}
// Use persons here...

You may have noticed that getting a value from a SqlDataReader isn’t easy! There are methods like GetString, GetInt32, GetBoolean, etc. to convert values from their database representation to their CLR type equivalents. Unfortunately, they throw on DBNull values. So in case of MiddleName, which is a NULLABLE column in the database, we need to check for DBNull before setting the MiddleName value. In case of integer or booleans (or any non-nullable type), we would use the nullable equivalents of those types like int? or bool? (which is short for Nullable<T>).

Another method to get data from the database is by using a SqlDataAdapter. This results in a DataTable or DataSet (for multiple resultsets) containing the database data. I won’t go into the use of DataTables and DataSets, but they are like in-memory GridViews. They even track if a row was changed and can automatically generate update, insert or delete commands when used with a SqlCommandBuilder.

The next code snippet shows how to fill a DataTable (that’s a lot less code than the SqlDataReader example, but keep in mind that the result is also very different).

C#
DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);
Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, 
FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(table);
}
// Use table here...

For the next example, we are going to select a subset of persons by first name. That means we’ll have to change our query. Let’s look at an example.

C#
string firstName = "John";
using (SqlConnection connection = new SqlConnection("Data Source=(local);
Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, 
FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = '" + firstName + "'", connection))
{
    // ...
}

Looking good, right? NO! THIS IS REALLY VERY WRONG! For John, this works great (I’ll tell you in a moment why it works, but still isn’t great), but for D’Artagnan (a musketeer), this won’t work at all! While the apostrophe is all good in C#, it ends a string in SQL. So the query you’ll be sending to SQL is SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = D'Artgnan. Go to SQL server Management Studio, open a new query window and try to run that exact query. You’ll get an error message saying something about an unclosed quotation mark. What it should’ve been was D”Artagnan. But even replacing every apostrophe with double apostrophe won’t work.

Whenever you send a query to SQL Server, a query plan is made and the fastest way to get your data is calculated. For our query, SQL Server might decide it will use an index we placed on FirstName. Once the plan is decided, it’s cached and re-used when the exact same query is called. In our example, that would mean a plan is made and cached for each name we look for! That’s not very efficient since every plan will probably be the same anyway…

What’s even worse and THIS IS VERY IMPORTANT is that by concatenating strings to form a query like that is a HUGE SAFETY RISK! Maybe you’ve heard of SQL Injection Attacks. Let me demonstrate this. Let’s assume for a moment that the user gets a textbox to enter a name and that name is concatenated to your query like above. Now the user enters John'; USE master; DROP DATABASE AdventureWorks2014 -- and BAM! There goes your database… Really, it’s gone. I hope you have a backup. This technique is used to get personal information of users like email addresses and passwords.

Here is a mandatory xkcd on the subject:

xkcd: Exploits of a Mom

So how are we going to solve these problems? Parameterisation! By creating parameterized queries, the query plan can be re-used for different values and SQL injection belongs to the past! So how does this look?

C#
string firstName = "John";
using (SqlConnection connection = new SqlConnection("Data Source=(local);
Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, 
FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = @FirstName", connection))
{
    cmd.Parameters.AddWithValue("FirstName", firstName);
    connection.Open();
    using (var reader = cmd.ExecuteReader())
    {
        // ...
    }
}

And that’s how easy it is! Notice that by adding a parameter, we also improved the readability of our code. Wow, that’s a win-win-win situation!

There is one caveat though, when you want to pass a NULL to the database, you’ll have to use the DBNull.Value object instead of simply null. So when fetching data, we converted DBNull to null and now we’ll have to convert null to DBNull. We’ll see this happening in the next example.

Now what if we want to update, insert or delete a record in the database? We can go about it in much the same way, but use ExecuteNonQuery (which returns the number of affected rows only).

C#
int businessEntityID = 1;
string firstName = "Sander";
string middleName = null;
string lastName = "Rossel";
using (SqlConnection connection = new SqlConnection("Data Source=(local);
Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("UPDATE Person.Person SET FirstName = @FirstName, 
MiddleName = @MiddleName, LastName = @LastName WHERE BusinessEntityID = @BusinessEntityID", connection))
{
    cmd.Parameters.AddWithValue("FirstName", firstName);
    if (middleName == null)
    {
        cmd.Parameters.AddWithValue("MiddleName", DBNull.Value);
    }
    else
    {
        cmd.Parameters.AddWithValue("MiddleName", middleName);
    }
    cmd.Parameters.AddWithValue("LastName", lastName);
    cmd.Parameters.AddWithValue("BusinessEntityID", businessEntityID);
    connection.Open();
    cmd.ExecuteNonQuery();
}

I have to add that it’s generally a good idea to check for null for ALL your parameters. You can make a helper function to prevent your code from cluttering up to much.

And in case you want your original Person back, here are his first-, middle- and last name: Ken J Sánchez.

So far, we have only worked with plain text queries. Many times, you’ll want to execute a stored procedure. This works in much the same way as sending your query to the database. You simply have to set the CommandType of your command to StoredProcedure and pass in the parameters.

C#
int businessEntityID = 1;
string nationalIDNumber = "295847284";
DateTime birthDate = new DateTime(1987, 11, 8);
char maritalStatus = 'S';
char gender = 'M';
using (SqlConnection connection = new SqlConnection("Data Source=(local);
Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("HumanResources.uspUpdateEmployeePersonalInfo", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("BusinessEntityID", businessEntityID);
    cmd.Parameters.AddWithValue("NationalIDNumber", nationalIDNumber);
    cmd.Parameters.AddWithValue("BirthDate", birthDate);
    cmd.Parameters.AddWithValue("MaritalStatus", maritalStatus);
    cmd.Parameters.AddWithValue("Gender", gender);
    connection.Open();
    cmd.ExecuteNonQuery();
}

In case you want your original employee back, here is his original birthdate: 1969-01-29.

Perhaps you have noticed that the SqlConnection inherits from DbConnection which implements IDbConnection. We have also used other classes like the SqlCommand and SqlDataReader which inherit from DbCommand and DbDataReader in the same manner. The only thing you need to know right now is that many database providers have these classes as a common base class which means that if you know how to connect to SQL Server, you (more or less) know how to connect to most SQL databases like Oracle, MySQL, PostgreSQL, Firebird, etc. In theory (and probably in practice too, although I’ve never tried), you can create a flexible data layer that can switch seamlessly between (SQL) databases because of these common base classes and interfaces.

Well, there you have it. We have successfully and correctly selected data, updated data and executed a stored procedure using C#. I assume you can now guess how to use ExecuteScalar, which I mentioned, but haven’t discussed further. Things don’t stop here though. There’s many more like queries that return multiple result sets, stored procedures that return output parameters, BLOBs, bulk operations, transactions… Way too much to discuss here. Luckily, there are many books, articles and blogs on the subject.

Happy coding!

The post Using C# to connect to and query from a SQL database appeared first on Sander's bits.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
CEO JUUN Software
Netherlands Netherlands
Sander Rossel is a Microsoft certified professional developer with experience and expertise in .NET and .NET Core (C#, ASP.NET, and Entity Framework), SQL Server, Azure, Azure DevOps, JavaScript, MongoDB, and other technologies.

He is the owner of JUUN Software, a company specializing in custom software. JUUN Software uses modern, but proven technologies, such as .NET Core, Azure and Azure DevOps.

You can't miss his books on Amazon and his free e-books on Syncfusion!

He wrote a JavaScript LINQ library, arrgh.js (works in IE8+, Edge, Firefox, Chrome, and probably everything else).

Check out his prize-winning articles on CodeProject as well!

Comments and Discussions

 
PraiseThank You! Pin
Member 126778768-Mar-17 4:20
Member 126778768-Mar-17 4:20 
GeneralRe: Thank You! Pin
Sander Rossel20-Mar-17 1:18
professionalSander Rossel20-Mar-17 1:18 
QuestionThanks Sander Pin
Member 1203057417-Nov-15 6:40
Member 1203057417-Nov-15 6:40 
AnswerRe: Thanks Sander Pin
Sander Rossel17-Nov-15 7:56
professionalSander Rossel17-Nov-15 7:56 
QuestionMy Vote 5 Pin
Muhammad Raheel Yousuf11-Nov-15 1:24
professionalMuhammad Raheel Yousuf11-Nov-15 1:24 
AnswerRe: My Vote 5 Pin
Sander Rossel11-Nov-15 5:57
professionalSander Rossel11-Nov-15 5:57 
GeneralMy vote of 5 Pin
Will Z K21-Sep-15 14:59
Will Z K21-Sep-15 14:59 
GeneralRe: My vote of 5 Pin
Sander Rossel21-Sep-15 21:02
professionalSander Rossel21-Sep-15 21:02 
QuestionHi Pin
Member 1170374819-May-15 13:08
Member 1170374819-May-15 13:08 
AnswerRe: Hi Pin
Sander Rossel19-May-15 20:29
professionalSander Rossel19-May-15 20:29 
GeneralMy vote of 4 Pin
Member 188040317-Mar-15 3:11
Member 188040317-Mar-15 3:11 
GeneralRe: My vote of 4 Pin
Sander Rossel17-Mar-15 4:05
professionalSander Rossel17-Mar-15 4:05 
GeneralRe: My vote of 4 Pin
Member 188040317-Mar-15 4:28
Member 188040317-Mar-15 4:28 
GeneralRe: My vote of 4 Pin
Sander Rossel17-Mar-15 8:08
professionalSander Rossel17-Mar-15 8:08 
QuestionA handy TIP Pin
Herman<T>.Instance15-Mar-15 22:47
Herman<T>.Instance15-Mar-15 22:47 
AnswerRe: A handy TIP Pin
Sander Rossel16-Mar-15 0:46
professionalSander Rossel16-Mar-15 0:46 
GeneralMy vote of 5 Pin
Sheepings9-Nov-14 13:05
professionalSheepings9-Nov-14 13:05 
GeneralRe: My vote of 5 Pin
Sander Rossel9-Nov-14 20:40
professionalSander Rossel9-Nov-14 20:40 
Generalgreat! Pin
Member 111198164-Nov-14 13:32
Member 111198164-Nov-14 13:32 
GeneralRe: great! Pin
Sander Rossel4-Nov-14 20:44
professionalSander Rossel4-Nov-14 20:44 
QuestionVote of 5 Pin
Yasskier4-Nov-14 8:39
Yasskier4-Nov-14 8:39 
AnswerRe: Vote of 5 Pin
Sander Rossel4-Nov-14 12:16
professionalSander Rossel4-Nov-14 12:16 
AnswerRe: Vote of 5 Pin
Sander Rossel6-Nov-14 20:30
professionalSander Rossel6-Nov-14 20:30 
GeneralMy vote of 5 Pin
Toni Fasth3-Nov-14 8:47
professionalToni Fasth3-Nov-14 8:47 
GeneralRe: My vote of 5 Pin
Sander Rossel3-Nov-14 9:27
professionalSander Rossel3-Nov-14 9:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.