ADO.NET

ADO & Its Limitations - ActiveX Data Objects. Connectivity using this architecture is based on the concept of dedicated connection between the front & the back end. We create a Recordset, connect it to a datasource (often a Database) and work. Changes to the data are updated on the database immediately. The ADO RecordSet is limited in flexibility and functionality. For example, most useful data analysis or presentation requires views of your data that span multiple tables or data sources. Using ADO, this cannot be accomplished without performing a SQL JOIN. As you may or may not know, this is a performance drag. It consumes memory and CPU power on the database server—precious resources especially with today’s Internet user demands. Because a RecordSet is essentially a single result table, navigation is sequential, row-by-row. Thus, if you perform a joining query the resulting data is “flattened”; any relations that may have existed are not relationally navigable.
ADO data types are based on COM/COM+ data types. For example, in COM/COM+ programming the BSTR is typically used to represent strings that need to be interoperable across languages. For those of you who do not know, the BSTR type is a null-terminated string whose leading WORD contains the length of the string. Unfortunately, the BSTR type is really a stranger to all languages and only makes sense in the COM context. For C, C++, and other lower level languages you must use special COM run-time functions to create and destroy them, and rapid application development (RAD) environments like Microsoft Visual Basic® need explicit support in the runtime to handle these types.
Sharing data between components in your application or elsewhere is done through COM marshalling. This limits sharing of data to a COM or COM-friendly environment.
There are also problems with marshalling through firewalls, because they tend to restrict the type of data that can pass through them. COM marshalling requires (COM) system services on the “other side” of the firewall (the server), but firewalls are often set up to block such traffic because it could pose a security threat.


ADO.NET - is a data-access technology that enables applications to connect to data stores and manipulate data contained in them in various ways. It is based on the .NET Framework and it is highly integrated with the rest of the Framework class library.
The ADO.NET API is designed so it can be used from all programming languages that target the .NET Framework, such as Visual Basic, C#, J# and Visual C++.
The ADO.NET stack has two major parts: providers and services.
ADO.NET "providers" are the components that know how to talk to specific data stores (for example, there is a provider to talk to SQL Server databases, and another one to talk to Oracle databases). All providers surface a unified API on top of which other layers can be built.
ADO.NET also includes services built on top of the providers that are designed to facilitate writing applications. One such service is support for an in-memory cache that retains the relational shape of the data, and does change-tracking and constraint validation among other things; this service surfaces through the ADO.NET DataSet, and includes components to interact with the provider layer.
ADO.NET is part of the .NET Framework, so in order to use ADO.NET in your applications all you need is the .NET Framework installed in the computers where your application will be used.
ADO.NET Connection - You use the ADO.NET Connection object to create a connection between your program and a database engine. You will normally keep this connection open just long enough to retrieve or update data. By quickly opening, then closing a connection, you use server resources for as little time as possible. This helps you develop scalable, fast applications that are resource-friendly. The fewer resources you use, the more users you can support on your applications at one time.
If you are creating a database application, you will eventually need to open a connection to that database. An ADO.NET Connection object allows you to create that connection. You will also need to retrieve and modify data in the database, and that is where you will need to use the ADO.NET Command object.
When connecting to SQL Server 7.0 or greater, you use the SqlConnection and SqlCommand objects in the System.Data.SqlClient namespace. When connecting to other OLE DB datasources, use the OleDbConnection and OleDbCommand in the System.Data.OleDb namespace.
To modify data within a database -
1. Create a SqlConnection or an OleDbConnection object and give it a connection string.
2. Open the connection to the database.
3. Create a SqlCommand or an OleDbCommand object and assign to it the connection object you opened.
4. Place an SQL statement into the Command object.
5. Execute the SQL statement by invoking the ExecuteNonQuery method on the Command object.
Dim objConn as SqlClient.SqlConnection
Dim strCon as String
Try
'Create a connection object
objConn=New SqlClient.SqlConnection()

'Build the Connection String
strCon &="Data Source=(local);"
strCon &="Initial Catalog=Northwind;"
strConn &= "User ID=sa;"
strConn &= "Password=;"
objConn.ConnectionString = strConn
objConn.Open() 'Open the Connection

'The connection is now open
'Write your code here
objConn.Close()
Catch oExcept As Exception
MessageBox.Show(oExcept.Message)
End Try
In this event procedure, you first create a new instance of a SqlConnection class. Then you fill in the ConnectionString property prior to opening the connection.
About Connection Strings - A connection string has a set of semi-colon-separated attributes. Each .Net Data Provider connection string looks different, depending on the type of .NET Data Provider you need to use and which attributes are set for each different type of database system. For example, the connection string below is an example of what you use to connect to a local SQL Server. Note that each parameter is separated by a semicolon.
Data Source=(local);Initial Catalog=Northwind;User ID=sa;Password=;
The connection string shown below is an example of how you would connect to a Microsoft Access 2000 database using the OleDbConnection object in System.Data.OleDb.
Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Northwind.mdb
Parameters in a Connection String - The parameters depends on which data provider is being used.
Server - The name of the SQL Server we wish to access. This is usually the name of the computer that is running SQL server. We may use "local" or "localhost" for local computer. If we are using named instances of SQL server, then the parameter would contain the computer name, followed by a backslash, followed by the named instance of the SQL server.
Database - The name of the database we want to connect to.
User ID - A user ID configured in the SQL Server by the SQL Server administrator.
Password - The password associated with the user ID used.
Note that connection string can also contain the Windows NT account security settings. This is done very simple, by passing the paramater "integrated security=true".
ADO.NET Command Object - The Command object is very similar to the old ADO command object. It is used to store SQL statements that need to be executed against a data source. The Command object can execute SELECT statements, INSERT, UPDATE, or DELETE statements, stored procedures, or any other statement understood by the database. See sample code...

Dim ObjCom as SqlClient.SqlCommand
ObjCom.SqlConnection(strCon)
ObjCom.Connection.Open()
ObjCom.CommandText = "Select * from tblSample"
ObjCom.ExecuteNonQuery()
SqlCommand objects are not used much when we use datasets and data adapters.
Following are some properties of the SqlCommand class...
Connection Property - This property contains data about the connection string. It must be set on the SqlCommand object before it is executed. For the command to execute properly, the connection must be open at the time of execution.
CommandText Property - This property specifies the SQL string or the Stored Procedure.
objCommand.CommandText = "Insert into Employees (empid, empname) values ('EMI0334','Mario Brother')"
Paramaters Collection - If we want to update values in the Employees table above, but we do not know the values at design time, we make use of placeholders. These are variables prefixed with "@" symbol. Our code will look like this...
objCommand.CommandText = "Insert into Employees (empid, empname) values (@empid, @empname)
Next, we have to create parameters that will be used to insert values into the placeholders. For this, we need to add parameters to the parameters collection of the SqlCommand object. This is done so that the values added through the parameters collection & placeholders get included in the SQL statement. Here, parameters mean the parameters to be passed to the SQL statement/Stored Procedures, not the method's parameters.
In order to add parameters to the SqlCommand object, we write the following code...
objCommand.CommandText = "Insert into Employees (empid, empname) values (@empid, @empname)"
objCommand.Parameters.Add("@empID", txtempid.text)
objCommand.Parameters.Add("@empname", txtempname.text)

ExecuteNonQuery Method - Once the connection is open, we run the query in the SqlCommand object using the ExecuteNonQuery method. This is very simple as shown below...
objConnection.Open()
objCommand.ExecuteNonQuery()
objConnection.Close()
DataReader - In ADO.NET, you no longer have a Recordset. Instead, you have new objects such as DataSets, DataTables, and DataReaders that will be used to retrieve records from data sources.
The DataReader object is a forward-only type of cursor that provides the fastest way to retrieve records from a data source. Because its direction is limited to forward-only, it provides great performance for programmatically processing results or loading list boxes, combo boxes, etc.
Below is code to load a listbox using a DataReader...
Private Sub frmProduct_Load( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
ListLoad()
End Sub

Within the Load Event, we are making a call to ListLoad() Procedure. Note that ListLoad does
not have any constructor, hence we are using MyBase.Load to invoke the constructor of the
class System.Object.

Private Sub ListLoad()
Dim oCmd As SqlClient.SqlCommand
Dim oDR As SqlClient.SqlDataReader
Dim strSQL As String
Dim strConn As String

strConn = ConnectStringBuild()

strSQL = "SELECT ProductName "
strSQL &= "FROM Products"

Try
oCmd = New SqlClient.SqlCommand()
With oCmd
.Connection = _
New SqlClient.SqlConnection(strConn)
.Connection.Open()
.CommandText = strSQL
oDR = .ExecuteReader()
End With

lstProducts.Items.Clear()
Do While oDR.Read()
lstProducts.Items.Add(oDR.Item("ProductName"))
Loop

Catch oExcept As Exception
MessageBox.Show(oExcept.Message)

End Try
End Sub
The code above declares two objects, one for the Command and one for the DataReader.
The Command object holds and executes the SELECT statement to send to the data source. The DataReader is the object that retrieves the data from the result set that comes back from the SELECT statement. Set the Connection property on the Command object to a new instance of a Connection object. You pass a connection string to this new Connection object. After the proper connection string is set, you can open the connection on the command object.
Place the SELECT statement into the CommandText property. When you invoke the ExecuteReader method, the command object submits the SELECT statement to the back end data source. The result is returned and the DataReader object is given a pointer to this result set. The cursor is set just before the first record in this result set.
You will loop through each of the rows in the DataReader by invoking the Read method. The Read method moves the cursor from one row to the next. After the Read method has executed, you can pass the name of the column you wish to retrieve to the Item property on the DataReader. This returns the actual data from that column.
The data returned from the Item property comes back as an Object data type. Because the items you add to the list box are of type Object, no conversion is required when using the Add method of the Items collection on the list box. In this manner, you continue looping until the Read method returns a False. This means you have hit the end of the rows of data that were returned from the SELECT statement.
Here goes the ConnectStringBuild function...
Private Function ConnectStringBuild() As String
Dim strConn As String

strConn &= "Data Source=(local);"
strConn &= "Initial Catalog=Northwind;"
strConn &= "User ID=sa"

Return strConn
End Function

SqlDataAdapter - It is a Data Adapter class for SQL Server databases. It can be used with SQL 2k, SQL 7, MSDE.
Now whats a Data Adapter? -> It is a class that acts as a bridge between the Data Source (means the database)
and the in-memory data objects such as a Dataset. To access a database, a command object is used. We
normally pass a command string and a connection string to the constructor of a Data Adapter. This way, we dont
need to use the SqlCommand Object while retrieving data from the database. We pass the data (using Fill method)
retrieved from the data source through the data adapter to a dataset (or any other in-memory object like a datatable).



Sample Code of filling a textbox with data from a datasource using a data adapter...

Dim con as SqlConnection = New SqlConnection("Server=;Database=;User ID=;Password=;")
Dim strcom as string = "Select * from employees where empid = 334"
Dim da as SqlDataAdapter=New SqlDataAdapter(strcom,con)
Dim ds as dataset
da.fill(ds)
textbox1.datasource = ds
textbox1.databind

Similarly, we can fill a datagrid with data...

Dim con as SqlConnection = New SqlConnection("Server=;Database=;User ID=;Password=;")
Dim strcom as string = "Select * from employees "
Dim da as SqlDataAdapter=New SqlDataAdapter(strcom,con)
Dim ds as dataset
da.fill(ds,"employees")
datagrid1.datasource = ds
datagrid1.databind

NOTE: In the code above, we have passed the name of the table in the Fill method. This is actually not really necessary
until we have more than one table in the data adapter.

Select Command Property (of the Data Adapter)- This property is used to hold the SQL command that is used to
retrieve data from the data source. In the figure above, we can see that the CommandText and Connection are
properties of the Select Command propety. CommandType is also a property of Select Command. See example
below...

Dim da as new SqlDataAdapter
da.SelectCommand = New SqlCommand( )
With da.SelectCommand
.Connection = objConnection
.CommandText = "select * from employees"
End With

Dataset - DataSet object is like an in-memory database. This object holds a collection of DataTable objects. Each
DataTable object is a representation of the data that was retrieved via a SELECT statement or stored procedure
execution. The data in a DataSet can be written out or read in as XML. DataSets also store schema information,
constraints, and relationships between multiple DataTable objects. Through a DataSet you can add, edit, and delete data.