ADO.NET

Web page by Kevin Harris of Homer IL

Please contact Kevin Harris of Homer IL concerning this web site

ADO.NET

ADO.NET provides access to a variety of data sources and supports 'connected' and 'disconnected' models with a highly efficient connection pooling system.


ADO.NET is a set of classes that provides data access services for .NET. It provides access to many different relational database management systems (RDBMS) as well as other types of data such as XML. ADO.NET was designed to handle large data loads and to be secure, flexible, and dependable. It is the oldest data access technology in .NET and is widely used. ADO.NET supports both a connected and disconnected models with a highly efficient connection pooling system.

The System.Data namespace contains many of the ADO.NET methods which are used for all database management systems. There are also several vendor specific libraries available such as System.Data.OracleClient. Additionally their are generic libraries such as System.Data.Odbc which provide access to ODBC compliant systems. The generic libraries generally do not perform as well as the vendor specific libraries.

ADO.NET is vulnerable to SQL Injection and Cross-Site Scripting attacks. It is essential to use parameterized queries, with typed parameters, when using any untrusted data. That includes data retrieved from the database as well as data input by the user. Typed parameters treat the values as typed data and not executable code. When query strings use data that is concatenated to the query string, it allows the data to be treated as executable code (SQL, JavaScript) which may have been entered by a malicious users. Also do not depend on .NET's "Request Validation" to trap all potentially malicious code going to the browser. Be sure any data sent to the browser is properly HTML encoded. HTML encoding changes the string "<script>" to "&lt;script&gt;" to prevent Javascript stored in data from being executed by the browser.


Connection Strings

Connection Strings contain initialization information that is passed as a parameter from a data provider to a data source. The syntax depends on the data provider, and the connection string is parsed during the attempt to open a connection. Once the connection string syntax is validated, the data source applies the options specified in the connection string and opens the connection to the data source. Below is a comparison of an ADO.NET connection string (line #1) and and Entity Framework connection string (line#3). Note, by default, the Entity Framework looks for a connection string named the same as the object context class.

1
2
3
<add name="MyADOConnection" connectionString="server=myServer;database=myDatabase;User Id=myUserID;password=myPassword;Persist Security Info=True; Convert Zero Datetime=true; Allow Zero Datetime=true" providerName="MySql.Data.MySqlClient" />

<add name="MyEntities" connectionString="metadata=res://*/MyModels.csdl|res://*/My.ssdl|res://*/MyModels.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=myServer;user id=myUserID;password=myPasswrod;persistsecurityinfo=True;database=myDatabase&quot;" providerName="System.Data.EntityClient" />

ADO on Line #1 and Entity Framework on Line #3

Connections can hold locks on data causing concurrency issues. In ADO.NET this can be alleviated by using the disconnected model and keeping connections closed as much as possible. By default, ADO.NET uses connection pooling which reduces the work of establishing and cleaning up connections. The connections in the pool are open database connections. When the program requests a connection, .NET assign a connection from the pool. When a connection is closed, or disposed, .NET returns the connection to the pool. ADO.NET 4.5 accessing SQL Server 2012 defaults to a maximum of 100 simultaneous connections and will adjust the actual number of connections in the pool according to the application's needs.


Data Providers

The connection string is one of the core objects in the set of .NET data providers. Other core objects allow for the execution of SQL commands and for retrieving results from the database. The results are either processed directly or are stored in an in-memory cache of data called a dataset to use the disconnected model. DataSets consist of a collection of related DataTables. The DataTables can be copies of database tables or can be populated from other sources.

The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables.



Database Queries

xxx

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Dim strConn As String = ConfigurationManager.ConnectionStrings("myConncectionString").ConnectionString
Dim objConn As New MySqlConnection(strConn)

objConn.Open()
Dim cmd As New MySqlCommand()
cmd.CommandText = "UPDATE mytable Set MyField = null where MyFieldInUse=?username"
cmd.Connection = objConn
cmd.Parameters.AddWithValue("username", Session("userName"))
cmd.ExecuteNonQuery()
objConn.Close()

ExecuteNonQuery()



xxx

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
Dim strConn As String = ConfigurationManager.ConnectionStrings("myConncectionString").ConnectionString
Dim objConn As New MySqlConnection(strConn)

Dim strSQL As String
Dim objDataSet As New DataSet()
strSQL = "SELECT * FROM myTable WHERE myField = 'myValue';"
Dim objAdapter As New MySqlDataAdapter(strSQL, objConn)
objAdapter.Fill(objDataSet, "myDataSet")
Dim dt As DataTable = objDataSet.Tables(0)
Dim dr As DataRow = dt.Rows(0)

lblLabel1.Text = dr.Item("myField").ToString()

DataAdapter