C#

ADO.NET

Access to databases in C#

During my first year at the ecole centrale in engineering, I was asked by the computer science association GInfo to give a presentation of the job I was doing at Axa investment manager and to talk about databases, a central concept in software development. So I wrote a course that I presented to twenty students on accessing the Sql server database via C# DotNet.

I – ADO.NET :

ADO.NET is a library of .Net FrameWork classes that allow access to databases.

II– Access modes :

Represents the method used to access a database.

There are 4 modes:

Connected mode.
Disconnected mode.
Linq To SQL (POO).
Entity Framework (POO).

1 – Connected mode :

In connected mode, we connect to the database and execute our SQL queries directly on the database (Selection, insertion, modification and deletion...) using the Connection, Command and DataReader classes.

Connection, Command and DataReader are ADO.NET classes.

Definitions :

A Connection is a path to the database (like a road connecting 2 cities).
A Command is a way to execute SQL queries from C#.
A DataReader is a way to receive the result of the execution of a SQL query in memory.

In connected mode, you stay connected during the whole work.

At the beginning, we open the connection and at the end of the treatments we close it.

2 - Disconnected mode:

In disconnected mode, we manipulate Dataset and Datatable objects which represent the structure of the database in memory. They are modified in disconnected mode and then synchronized with the database with a DataAdapter.

and also existing :

3 – Linq To SQL (POO)

4 – Entity FameWork (POO)

II - How to do in Connected mode ?

To connect to the database, we need an object

This Connection object is created from a connection string (ConnectionString) which is a string that contains all the necessary information to connect to the database : name of the Server, name of the DB . . . etc.

The Connection class of SQLServer is SqlConnection.

Similarly, the Command and DataReader classes of SQLServer are SqlCommand and SqlDataReader.

Imports :

using System.Data.SqlClient;

Declaration and creation of objects:

- Connection chain(ConnectionString):

public static string strcon = @"Data Source =.\SQLExpress; Initial Catalog = Vente; Integrated Security = True";

Or (valid only for SQL):

public static string strcon = @"Server=.\SQLExpress;Database=Vente; Integrated Security = True";

Data Source (or Server) = Serveur name.

Initial Catalog (Database) = DB name.

- SQL query:

public static string Req = "Select * From Client";

- Creation of Object Connection (con) et Command (CmdSelect):

public static SqlConnection con = new SqlConnection (strcon);

public SqlCommand cmd = new SqlCommand(Req, con);

public SqlDataReader dr;

Read access to the database:

To read the data, we need a Command and a DataReader.

The Command represents the SQL query to select the data.

The Datareader stores the result of our query. It allows us to read the rows returned by a command. It only goes forward, once a line is passed it is impossible to go back (no backward).

con.Open();

dr = cmd.ExecuteReader();

while (dr.Read())

{

dr[0] ou dr["CodeCl"] return CodeCl

dr[1] ou dr["Nom"] return Name/Nom

dr[2] ou dr["Ville"] return City/Ville

}

dr.Close();

cmd.Dispose();

Note :

The 1st dr.Read() brings back the 1st Client of the comic and puts it in dr.

The 2nd dr.Read() brings back the 2nd Client from the DB and puts it in dr.

. etc. until the last one.

Example 1 : Displaying information about customers.

We want to consult the data contained in a Customer table in a Sales database:

Customer (CodeCl, Name, City).

Work to be done:

Create a Visual Studio project and the following form to display the names of all the customers in a ListBox :
In the same project add a second form allowing to display in a DataGridView the customers of a city chosen in a Combo:

Example of a command that returns a value: In the same project add a third form to display in a Label the number of customers of a city chosen in a Combo:

Handle exceptions related to a connection.

try { con.Open(); }

catch (Exception ex)

{

..... // error message in ex.Message

}

Note: You can use the following test to ensure that the connection is closed:

if (con.State == ConnectionState.Open)

{

con.Close();

}

Access to the database to add, modify and delete :

To update the data with an insert, update, delete query we use a command as well. But ExecuteNonQuery is used instead of ExecuteReader. ExecuteNonQuery returns the number of affected rows.

Add :

con.Open();

String ReqInsert = "Insert Into Client Values(" + TxtCodeCl.Text + ",' " + TxtNom.Text + " ',' " + TxtVille.Text + " ')";

SqlCommand CmdInsert = new SqlCommand(ReqInsert, con);

int nbLignesAjoutés = CmdInsert.ExecuteNonQuery();

con.Close();

Edit :

con.Open();

String ReqUpdate = "Update Client Set Nom = '" + TxtNom.Text + "', Ville = '" + TxtVille.Text + "' Where CodeCl = '" + TxtCodeCl.Text + "'";

SqlCommand CmdUpdate = new SqlCommand(ReqUpdate, con);

int nbLignesModifiées = CmdUpdate.ExecuteNonQuery();

con.Close();

Delete :

con.Open();

String ReqDelete = "Delete Client Where CodeCl = '" + TxtCodeCl.Text + "'";

SqlCommand CmdDelete = new SqlCommand(ReqDelete, con);

int nbLignesSupprimées = CmdDelete.ExecuteNonQuery();

con.Close();

IV - Disconnected mode :

In disconnected mode, we manipulate Dataset and Datatable objects which represent the structure of the database in memory. We modify them in disconnected mode and then we synchronize with the database with a DataAdapter.

The DataSet class.

This is the generic class that allows us to have a tabular representation of data (one or more tables, an XML file, etc...); this is why it is made up of a tree of collections of classes :

Relation between these classes

V – Linq To SQL (POO) :

Linq To SQL is an access mode introduced by MicroSoft from Visual Studio 2008. It allows you to interact with a SQL database using business object tables mapped to those of the database.

(Mapping means making a correspondence).

These business object tables will be put in a DataContext Object.The DataContext :

The DataContext represents the data source that contains the different tables of the database transformed into object tables (or object collections). It is a kind of object oriented database placed in the RAM.

Linq To Sql works according to the following schemas:

VI – Entity FameWork (POO) :

Page updated

Google Sites

Report abuse