Data Access with .NET Platform

• Download source - 314.1 KB

Introduction

Accessing data is very important in most programming languages, that’s why Microsoft provides an important set of classes that enables developers to access several databases and process data. Microsoft developers can use a lot of data access frameworks. Through this article, we will discuss just two scenarios:

1. ADO.NET
   1. Connected Mode
   2. Disconnected mode
2. Entity Framework (database approach)

None of these frameworks are obsolete, and we will explain in detail each of them with a practical workshop.

Background

This article may be useful for intermediate developers who have some basics in C# and .NET.

Using the Code

Through this paragraph, we will explain how to access a SQL Server Database. Tools used in each data access scenario are:

• SQL SERVER 2012
• SQL Management studio 2012
• Visual Studio 2012

I. ADO.NET

ADO.NET appears on the first version of Microsoft.NET Framework, it presents an approach that helps us to learn a programming model and then be able to process nearly any database supported by .NET.

If for example, a developer knows how to access a SQL Server database, then he must be able to access an Oracle database or MySQL database, all that he has to do is to add reference to the appropriate provider because the programming model is the same.

• ADO.NET is the managed version of ADO (Active X data Object)
• OLEDB Access: System.Data.OleDb
• SQL Server Access: System.Data.SqlClient
• Oracle Access: System.Data.OracleClient
• ODBC Access: System.Data.Odbc

ADO.NET has many components:

1. .NET Data Provider:
   • The .NET Framework Data Providers are components that have been explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source and it is used in the connected Mode scenarios.
   • Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data and reconcile changes that were made to the data in the DataSet back to the data source.
2. The Dataset: In simple words, the Dataset is a database in memory and it is used in the (see Disconnected Mode section).

Choosing a dataReader (connectedMode) or a DataSet (Disconnected Mode)?

When you decide whether your application should use a DataReader or a DataSet, consider the type of functionality that your application requires. Use a DataSet to do the following:

• Cache data locally in your application so that you can manipulate it. If you only need to read the results of a query, the DataReader is the better choice.
• Remote data between tiers or from an XML Web service.
• Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources.
• Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.

If you do not require the functionality provided by the DataSet, you can improve the performance of your application by using the DataReader to return your data in a forward-only, read-only manner. Although the DataAdapter uses the DataReader to fill the contents of a DataSet, by using the DataReader, you can boost performance because you will save memory that would be consumed by the DataSet, and avoid the processing that is required to create and fill the contents of the DataSet.

Let’s write some code!

Each data access scenario will be tested against a SQL Server database that contains just two tables:

• user
• command

1. Connected Mode (The Data Reader)

   We need here a valid, open connection object to access a data store. The DbConnection class is an abstract class from which the provider inherits to create provider-specific classes.

   

   Opening and Closing the Connection

   The following code sample shows how first to create the connection and then assign the connection string with a valid connection string, you can open the connection and execute commands. When you are finished working with the connection object, you must close the connection to free up resources.

   C#

   var connection = new SqlConnection();
   connection.ConnectionString = @"Data Source=.;Initial Catalog=codeproject;
   Integrated Security=SSPI";
   connection.Open();
   //Process Data here
   connection.Close();

   Sending Command to the Database

   To process Data into database (display, Create, Remove, Update), we have to use the SqlCommand Class, let’s say for example that we want to display all the commands.

   C#

   SqlCommand myCommand = connection.CreateCommand();
   myCommand.CommandText = "select * from command";

   Reading Data from The Database

   The most important class in the connected Mode scenario is the SqlDataReader, it is a cursor that can read data line by line and display it to the user and this is the unique difference between ConnectedMode and DisconnectedMode.

   C#

   SqlDataReader myReader =myCommand.ExecuteReader();
   while (myReader.Read ())
   //Displaying command names in a ListBox control
      {
         CommandListBox.Items.Add(myReader["Name"]);
    }

   All the sample Code

   C#

   String myConnectionString=@"Data Source=.;Initial Catalog=codeproject;Integrated Security=SSPI";
   SqlConnection myConnection=new SqlConnection (myConnectionString);
   CommandListBox.Items.Clear();  
   myConnection.Open();
   SqlCommand myCommand = myConnection.CreateCommand();
   myCommand.CommandText = "select * from Command";
    SqlDataReader myReader =myCommand.ExecuteReader();
   while (myReader.Read ())
   {
   CommandListBox.Items.Add(myReader["Name"]);
   }
   myConnection.Close();

   Let's make the code cleaner

   In the previous code, we notice that there are many problems:

   • Both SQL Query and C# code exist in the same file.
   • Lack of memory management.
   • Lack of exception handling.
     C#

     String myConnectionString=@"Data Source=.;Initial Catalog=project;Integrated Security=SSPI";
     using (SqlConnection myConnection = new SqlConnection(myConnectionString))
      {
         try
            {
               myConnection.Open();
               SqlCommand myCommand = myConnection.CreateCommand();
               myCommand.CommandType = CommandType.StoredProcedure;
               myCommand.CommandText = "GetListCommands";
               while (myReader.Read ())
                     {
                         CommandListBox.Items.Add(myReader["Name"]);
                      }
              myConnection.Close();
             }
     catch (Exception)
           {
               //Add logging here....
           }
     }

     As you can see here, there are many ways to fix these issues, for example, we can use:

     • Stored procedure (separation of C# and SQL code)
     • The Using bloc (memory management)
     • try catch .... (Exception handling)

When we talk about the C# code level, the unique difference between Connected and Disconnected Mode exists when we want to display Data (SqlDataReader vs SqlDataAdapter). In the Disconnected Mode, we will use the SqlDataAdapter class and we will add also another new class called Dataset which is nothing but a database in memory.

SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
DataSet myDataSet = new DataSet();
myAdapter.Fill(myDataSet);

All the sample Code

String myConnectionString=@"Data Source=.;Initial Catalog=project;Integrated Security=SSPI";
using (SqlConnection myConnection = new SqlConnection(myConnectionString))
{
    try
       {
         myConnection.Open();
         SqlCommand myCommand = myConnection.CreateCommand();
         myCommand.CommandType = CommandType.StoredProcedure;
         myCommand.CommandText = "GetListCommands";
         SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
         DataSet myDataset = new DataSet();
         myAdapter.Fill(myDataset);
         commandDataGrid.DataSource=myDataset.Tables[0];
         myConnection.Close();
        }
catch (Exception)
      {
          //Add logging here....
      }
}

ADO.NET is a powerful framework that enables developers to access different database using the same programming model, but when writing object-oriented applications, you want to think of the problem domain and write objects and code that are domain-centric. Writing data access code and creating data access objects that can talk to the database feel like distractions; they represent noise in your application. However, you need some means of persisting your domain objects to the database, which usually means creating a data model that represents your relational database. The Entity Framework can provide this.

In the next paragraph, we will see how we can use Entity Framework to access a SQL Server database.

II. ADO.NET

Entity Framework (EF) is an open source^] object-relational mapping (ORM) framework for ADO.NET, part of .NET Framework. You can download the source from http://entityframework.codeplex.com/.

The following diagram illustrates the Entity Framework architecture for accessing data:

The object services is a component that enables the developer to query the database (insert, select, update, remove) using CLR object that are instances of entity types. The object services support Linq (language integrated query).

Besides, ADO.NET (the first paragraph of this article) is always used to query the database with the ADO.NET Data provider who receive the queries from the entity Client Data provider and returns a collection of object to the caller.

The Entity Framework supports two scenarios:

• Database First
• Code First

Code First Model vs. Database First Model

What is the Code First model? This is when you create your conceptual model before you create the database. Using the Code First model, you can generate the database from the conceptual model, but first you must create your conceptual model manually. The Database First model enables you to generate the conceptual model from the database schema, but first you must create the database schema manually.

Which model should you use? If the database or the conceptual model already exists, you will surely use the associated model. If nothing exists, simply take the path of least resistance and start working on the end with which you are most comfortable. It’s that simple.

In the next paragraph, we will use the Database first approach.

Steps

• Open Visual Studio (I am using VS 2013).
• Choose Visual CSharp and create a Windows Form application.
• Visual Studio will generate a windows Forms project for you.
• Add a DataGrid and a button.
• Right click on solution explorer => Add => new element =>Data => ADO.NET Entity Data Model
• Visual Studio assistant will suggest many options (Database first, code first, etc.), in our case we will choose Database First
• The assistant will connect to Our Database and extract data (tables, stored procedures, etc.)

Click OK and the assistant will generate for us all the entities needed to access the database.

Now we have everything to access the database, so let’s write some C# code to display data from Command table in the database.

 codeprojectEntities db = new codeprojectEntities();
 var commands = from cmd in db.Command
 select new
 {
 Name = cmd.Name
 };
commandGridView.DataSource = commands.ToList();

//Adding new user
codeprojectEntities db=new codeprojectEntities ();
User user =new User();
User.Nom="Bill G";
Db.User.Add(user);
db.SaveChanges();

As you can see now, we can do everything based on OOP (Object oriented Programming), there is no SQL Code, everything is based on the conceptual entities and this is the principal goal of ORM like Entity framework, it creates a level of abstraction and makes the application independent from the Database. If we want to work with Oracle for example, the programming model will never change and all that we have to do is to install the Entity Framework provider for Oracle.

Summary

Through this article, we have seen some scenarios explaining how .NET can access database, these scenarios are presented using two majors .NET Core technologies:

• ADO.NET which is the managed version of ADO
• Entity Framework

For EF, it requires a book to explain all advantages and functionalities of this data framework, I hope that in the next articles, I will explain deeply EF and the two approaches (Database First and Code First).

Thank you for reading, try to download the database (the backup of the DB) and do not hesitate to leave your questions, comments and thanks if you want to.