Managing connections to databases with ADO.NET

In the previous lesson we saw how to bind a collection of static (also known as in-memory) to various server side controls. Although this type of operation can be useful in certain contexts, however, often comes the need to bind controls to the various collections of items from other sources, often from a database.

In addition to including class libraries for developing desktop applications (Windows Forms) and the management of HTTP requests (ASP.NET), the. NET Framework also includes a library that supports connection to a wide variety of databases and is called ADO.NET (Active Data Objects. NET). This technology is based on three main functions: connecting to a database, querying a database, and results management.

When you want to interact with a database you must connect to it. The connection requires the identification of the location of the database but can also ask for security management and other complex rests. All these elements are managed as part of the process of connecting to a database. The connection information is usually passed as a string, whose content is used for setting various parameters.

While in the past you had to manually write code to access a database through ADO.NET by specifying the type of database you want to connect and automatically set the properties are suitable for that type of connection.

There are several types of databases supported:

• ODBC Data Provider
• OLE DB Data Provider
• OracleClient Data Provider
• SqlClient Data Provider
• SQL Server CE Data Provider

These types defined in the aforementioned gift machine.config file

 <configuration>

 





 <configSections>

 





 <Section name = "System.Data.Odbc"

 





 type = "







 System.Data.Common.DbProviderConfigurationHandler, ..."/>

 





 <Section name = "System.Data.OleDb" type = "







 System.Data.Common.DbProviderConfigurationHandler, ..."/>

 





 <Section name = "System.Data.OracleClient" type = "







 System.Data.Common.DbProviderConfigurationHandler, ..."/>

 





 <Section name = "System.Data.SqlClient" type = "







 System.Data.Common.DbProviderConfigurationHandler, ...

 



 "/>

 





 <configSections />

 





 <system.data>

 





 <DbProviderFactories>

 





 <Add name = "Microsoft SQL Server Compact Data Provider" invariant = "







 System.Data.SqlServerCe.3.5 "

 





 type = "System.Data.SqlServerCe.SqlCeProviderFactory ..." />

 





 </ DbProviderFactories>

 





 </ System.Data> <







 / Configuration>>

To create the database connection must therefore use the right component. With the availability of a connection you can then use to access the database. For example, if our local computer hosting a database of SQL Server called DB_TEST must enter into an eventual our application the following connection string

 <configuration>

 





 <connectionStrings>

 





 <Add name = "TEST"

 





 connectionString = "server = (local);







 Integrated Security = SSPI; DB_TEST database = "/>

 





 </ ConnectionStrings>







 </ Configuration>

At this point you can open the connection and to have the database for the execution of operations that we want.

Most databases support the SQL language (Structured Query Language) to make queries, changes, new insertions and deletions of data. These commands usually have a form similar to the following

 



 SELECT * FROM Customers WHERE Surname = 'Smith'

 

where Customer is a table in a database. This command takes a list of customers whose last name is Smith. To obtain these data within our application of a we write the following code

 TestDBApp class







 {



  



 static void Main ()



  



 {



    



 DbProviderFactory DbProviderFactory DbProviderFactories.GetFactory = ("System.Data.SqlClient");



    



 using (DbConnection conn = dbProviderFactory.CreateConnection ())



    



 {



      



 string s = ConfigurationManager.ConnectionStrings ["Test"]. ConnectionString;



      



 conn.ConnectionString = s;



      



 conn.Open ();



      



 DbCommand cmd = conn.CreateCommand ();



      



 cmd.CommandText = "SELECT * FROM Customers WHERE Surname = 'Smith'";



      



 DbDataReader reader = cmd.ExecuteReader ();



      



 / / Use the reader to access data



    



 }



  



 }







 }

The command (cmd) using the ExecuteReader method causes the sending of our database queries. The results in this case are obtained through an instance of the IDataReader class, but you can also manage them through another class called DataSet.

The IDataReader is indicated to perform iterations on the results of a query. In particular, it exposes the Read method that iterates through the rows one at a time so sequenziale.Lo disadvantage is that when scanning is not possible to return to a previous line and also returned rows are read-only so you can not change the content.

The alternative is the IDataReader to DataSet, which allows management of a set of disconnected data. ADO.NET is designed primarily to support applications very scalable and one of the main problems of scalability is the limit to the number of connections to a database. The database in fact often have a limit on the number of connections simultaneously active at a given time and if all those in use are permitted operations are slowed down. If the number of users of the system is roughly equal to the number of connections available at the same time could cause problems not relevant, but if the number of users has increased system performance may suffer dramatically.

To address these problems ADO.NET provides the DataSet class, designed to provide a sort of copy of the database (or portion thereof) used by an application. The advantages are many, just think that within an instance of the dataset you can enter new data, update or delete existing ones, and then confirm all of these operations that are executed in disconnected mode by connecting to the database for a short period of time.

The DataSet class includes an array of DataTable objects, which are populated through the DataAdapter. These elements are invoked when loading data from the database directly into the DataTable and then you can work on these local data, interacting with the database only when you confirm the changes made to them.

So using the DataSet we can access every element in it so completely random, unlike what we saw for the component DbDataReader.

• <<Previous Lesson
• Help Index
• Next Lesson>>