C# SQL: Using Queries in Your C# Code

Understanding front-end GUI code is the first step in development, but you need to know how to retrieve and edit data in your database. Old web forms in C# use SQL stored procedures, but you can use LINQ with MVC.NET and ADO.NET. Knowing SQL helps you understand how to work with LINQ.

Learn SQL from scratch with a class at Udemy.com.

How C# Works with Queries

LINQ makes it easier to query your database from the front-end app, but how does it work? Think of the process as a step-by-step application. You have a database or data source at the bottom and your LINQ statements at the top. In between the top and the bottom are libraries meant to translate your SQL LINQ statement into a language the database understand.

The data source must support LINQ, which most do. LINQ incorporates ADO.NET, which you can’t avoid if you use the .NET framework. ADO.NET lets you connect to several databases (provided you have the software) and other data objects such as XML.

Creating a Data Model

Visual Studio and C# MVC.NET work with object relational modeling (ORM). Visual Studio has a wizard that guides you through the process of creating a data model. These data models represent your tables in classes. Classes are basic OOP concepts, so you’ll need to understand how to instantiate, build and call class methods. The advantage is you no longer need to write complex SQL queries in C#.

Right-click your front-end GUI project in Visual Studio and select “Add” then “New.” Choose “Generate from Database” and follow the instructions to create the data model. Take note of the name, because you’ll need it to call your database classes.

After you finish, Visual Studio auto-generates the tables. For instance, suppose you created a data model called userdb, the following code instantiates the database model, so you can use LINQ on its tables:

Userdb userdb = new Userdb();

Now that you have your data model, you can retrieve data. Instead of retrieving data in a record set like other languages, you search your database and return objects. For instance, if you want to search for all users who signed up to your site within the last week, you return an enumerated “Users” object. You identify these objects in the same way you would identify them in a database. The data is stored in the class in its properties.

The following code is an example of a LINQ statement that retrieves data:

DateTime dt = DateTime.Now.AddDays(-7);
var users = (from u in Users where u.SignupDate >= dt select u);

The first C# statement gets the current date and subtracts 7 days from that date. The “Now” property gets the current system date and time, so this code will return a date that’s 7 days prior to the system’s time. This makes your code totally dynamic, so you don’t hard code any values into your program.

Learn how to work with C# as a beginner with a course at Udemy.com.

The “dt” variable is then used in the LINQ statements. There is only one statement in the example code. Notice “var” is used instead of defining a data type. The “var” phrase makes it easier to retrieve and store data in a variable without getting data type errors. The statement will automatically convert to an enumerated Users variable.

The LINQ statement looks similar to SQL statements, if you are familiar with them. This statement selects all users who signed up within the last week. It’s assumed that the user’s signup date is stored in the “SignupDate” column. The “u” acts like an alias for your tables. Notice you use it as a prefix for the column you used to filter the data. Finally, the “select u” statement returns the record set in the form of a Users object.

After you retrieve the data, you can run through the data using the “foreach” statement. The following code is an example of iterating through a list of users:

foreach (var user in users)
{
string fname = u.first_name;
Console.WriteLine(fname);
}

In the above code, each record is set to “user.” Notice the difference between “user” and “users.” The “user” variable represents one value and the “users” variable represents the enumerated variable such as “IEnumerable<User>”.

The first statement in the foreach loop sets the “first_name” column to a string variable. Each time you loop through the statement, a new string is created (with the same name) with the corresponding string first name stored in the SQL database “first_name” column.

What you do with your data is up to you. In this example, the name is displayed in the console. Typically, in a C# web application, you display the user name in the browser in a label, text box or in a drop-down list.

Joining Tables

Just like regular SQL statements, you can also join tables. For instance, you probably need to get a list of customers who also have orders. The following code is an example of joining a customer table (user table) with the orders table:

DateTime dt = DateTime.Now.AddDays(-7);
var users = (from u in Users join o in Orders on u.userid equals o.userid where u.SignupDate >= dt select u);
foreach (var user in users)
{
string fname = u.first_name;
Console.WriteLine(fname);
}

In the above example, the only difference is the LINQ statement. It joins users with Orders on the “userid” column. In your SQL database, this column should be set as a foreign and primary key pair. In the LINQ statement, the same structure is still returned (a User enumeration). You can also return certain columns or both the User and the Orders tables.  Since you’ve defined your variable as a “var,” the C# system will automatically convert the returned data type as the right structure.

These are very basic LINQ queries you’ll need to know with C#. Knowing both C# and LINQ will help you create powerful web or desktop applications using the .NET framework. Knowing C#, LINQ and MVC will help you make money and find a good career in web development.

Learn more LINQ and C# with a class at Udemy.com.