|
|
|
|
|
Home
»
VB.NET
»
.NET Data Providers : A Basic Tutorial
|
|
|
|
Page Views :
|
34869
|
|
Downloads :
|
0
|
|
Rating :
|
Rate it
|
|
Level :
|
Beginner
|
|
The .NET data provider is the layer between the application and the database that takes care of the all the nitty gritties of the database interaction. They are designed for fast, efficient means of data retrieval and reconciliation. In this article I'll deal with the classes the .NET data provider offers and some common functions in each of the classes.
Now, the .NET Data provider can be manifested as SQL Server data provider, OLEDB data provider or the ODBC Data Provider. For the time being I'll be taking you through this article in the context of the first two Data providers.
SQL Server Data provider is the most efficient way to connect to an SQL Server Database(version 7.0 onwards). It uses a proprietary protocol for connecting to the Database which results in optimized usage of the SQL Server database along with faster data transactions.
The System.Data.Sqlclient namespace contains classes for the SQL Server data provider.
OLEDB Data Provider is used with databases that support the OLE DB interfaces. ADO.NET supports the following OLE DB Providers
SQLOLEDB - Microsoft OLE DB Provider for SQL Server. MSDAORA - Microsoft OLE DB Provider for Oracle. Microsoft.Jet.OLEDB.4.0 - OLE DB Provider for Microsoft Jet.
However, OLE DB Data Provider does not support OLE DB 2.5 Interfaces like the ones used for MS Exchange Server and Internet publishing. This data provider is the recommended one for Oracle, SQL Server 6.5.
The System.Data.Oledbclient namespace contains classes for the OLE DB Data provider.
The .NET data providers have 4 key classes which are derived from the IDbConnection, IDbCommand, IDataReader, IDbDataAdapter interfaces found in the System.Data namespace.
These four classes are manifested in their SQL Server or OLE DB providers as SqlConnection,OledbConnection for the Connection class; SqlCommand, OledbCommand for the Command class; SqlDataReader,OledbDataReader for the DataReader class; SqlDataAdapter, OledbDataAdapter for the DataAdapter class.
Lets look at each of these four classes in brief ...
The Connection class
This class is inherited from the IDbConnection interface and used to establish connection to the database. The commands to the database will be executed using this connection. The connection has to be closed explicitly after use especially if its part of a connection pool to make it available, for eg.
if the Provider is SQL Server then
Dim con As New _ SqlDbConnection("server=myserver;database=MLPierre;uid=sa;pwd=;")
or
if the Provider is an OLE DB provider then
Dim con As New _ OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0.;" & _ "Data Source=C:\samples\dotnet\Testdb.mdb").
The Command class
Inherited from the IDbCommand interface and used to execute Transact SQL commands or stored procedure against the database. The command class exposes the following methods.
ExecuteReader: Return a stream of data in the form of an object that inherits from the IDataReader interface.
ExecuteScalar: A single value is returned from the query/procedure execution. ExecuteNonQuery: Returns the status of a query for intance, number of rows effected from the query. for eg.
Public Sub readData(ByVal con As IDbConnection) Dim cmd As IDbCommand = con.CreateCommand() cmd.CommandText = "SELECT * FROM ProductMaster" Dim reader As IDataReader = cmd.ExecuteReader() While reader.Read() Console.WriteLine("{0} :: {1}", _ reader.GetString(0), _ reader.GetString(1)) End While End Sub
The DataReader class
Inherited from the IDataReader interface and used to read data from the database as a read-only, forward only stream. It is an efficient means for data retrieval and scarcely used memory resources because it reads one record at a time from the database. This class exposes the following methods.
Read: To iterate throught the rows in the returned stream of data. for eg.
While reader.Read() Console.WriteLine("{0} :: {1}", _ reader.GetString(0), _ reader.GetString(1)) End While
another eg.
While reader.Read() Console.WriteLine("{0} :: {1}", _ reader.Item(0).ToString(), _ reader.Item(1).ToString()) End While
NextResult: If there are more than 1 result sets, use this method to jump to the next. for eg.
Public Sub readData(ByVal con As IDbConnection) Dim cmd As IDbCommand = con.CreateCommand() cmd.CommandText = "SELECT * FROM ProductMaster; SELECT * From OrderMaster" Dim reader As IDataReader = cmd.ExecuteReader() Dim nextResult As Boolean = True Do Until Not nextResult While reader.Read() Console.WriteLine("{0} :: {1}", _ reader.Item(0).ToString(), _ reader.Item(1).ToString()) End While nextResult = reader.NextResult() Loop End Sub
The DataAdapter class
Inherited from the IDbDataAdapter interface and used to reconcile data to and from the dataset and the database. This class is basically used to interact with the dataset which is a disconnected source of data. The Dataset is mapped with the corresponding table's schema. More about Datasets in my forthcoming article. This class exposes the following methods.
Fill: Fills the dataset with data from the database and then the connection is released making the DataSet available for perusal. For eg.
Imports System Imports System.Data Imports System.Data.SqlClient Public Class DAdaptTest Public Shared Sub Main() Dim ds As New DataSet Dim sda As New SqlDataAdapter( _"SELECT * FROM ProductMaster", _ "server=myserver;database=MLPierre;uid=sa;pwd=;") sda.Fill(ds, "Products") Dim ctr As Integer = 0 While i < ds.Tables("Products").Rows.Count - 1 Console.WriteLine("{0} :: {1}", _ ds.Tables("Products").Rows(i).Item(0).ToString(), _ ds.Tables("Products").Rows(i).Item(1).ToString()) ctr += 1 End While End Sub End Class
From this article, we can see that the .NET Data Provider encapsulates the database interaction part from the actual data making it versatile and simplifying development.
|
|
Comment Request!
Thank you for reading this post. Please post your feedback, question, or comments about this post
Here.
|
|
|
|
|
Login
to add your contents and source code to this article
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional
consulting company, our consultants are well-known experts in .NET and many of them
are MVPs, authors, and trainers. We specialize in Microsoft .NET development and
utilize Agile Development and Extreme Programming practices to provide fast pace
quick turnaround results. Our software development model is a mix of Agile Development,
traditional SDLC, and Waterfall models.
|
|
Click here to learn more about C# Consulting. |
|
|
|
|
|
|
|
Introducing MaxV - one click. infinite control. Hyper-V Hosting from MaximumASP.
Finally – a virtual platform that delivers next-generation Windows Server 2008 Hyper-V virtualization technology from a managed hosting partner you can truly depend on. Visit www.maximumasp.com/max for a FREE 30 day trial. Hurry offer ends soon.
Climb aboard the MaxV platform and take advantage of High Availability, Intelligent Monitoring, Recurrent Backups, and Scalability – with no hassle or hidden fees.
As a managed hosting partner focused solely on Microsoft technologies since 2000, MaximumASP is uniquely qualified to provide the superior support that our business is built on. Unparalleled expertise with Microsoft technologies lead to working directly with Microsoft as first to offer IIS 7 and SQL 2008 betas in a hosted environment; partnering in the Go Live Program for Hyper-V; and product co-launches built on WS 2008 with Hyper-V technology.
|
Dynamic PDF
ceTE software specializes in components for dynamic PDF generation and manipulation. The DynamicPDF™ product line allows you to dynamically generate PDF documents, merge PDF documents and new content to existing PDF documents from within your applications.
|
Nevron Chart for .NET 2010.1 Now Available
The leading .NET charting control now features PDF, Flash and Silverlight export, visualization of large datasets and more. Deliver true charting functionality to your BI, Scorecard, Presentation or Scientific apps. Download evaluation now.
|
ASP.NET 4 Hosting
Get 2 Months Free of ASP.NET Hosting for Only $4.95/month! Receive FREE MS SQL and MySQL Databases Including ASP.NET 4/3.5, MVC 3.0, Silverlight 4, Windows 2008/IIS 7.0 Plus FREE IIS 7 Modules. Host UNLIMITED ASP.NET Web Sites – Click Here!
|
|
|
|
|
|
|
|
|
|
|
|
|