Archives of the TeradataForum
Message Posted: Sun, 02 Jun 2002 @ 12:07:06 GMT
| Subj: | | Re: dotNET & Teradata |
| |
| From: | | ielosta |
Hello..... You may be interested in this ....
Imad El-Osta
---------------------------------------
Connecting to Databases the ASP.NET Way
[Ed. Note: Updated to .NET v1.0 (RTM) by Donny Mack on /03/10/2002]
It seems with every new release of Visual Studio we get a new data access method. DAO, RDO, ADO...and now ADO.NET. Well, this version of
Visual Studio not only introduces ADO.NET, but also the SQL .NET Data Provider. ADO.NET and the SQL .NET Data Provider introduce many new
features that we cover in other tutorials. There are quite a few samples out there demonstrating how to connect your ASP.NET application to
a Microsoft SQL Server database...but lets face it, not everybody uses SQL. So how can you connect your application to an ODBC or OLEDB data
store?
The answer is with the ODBC and OleDb .NET Data Providers. In this tutorial we will cover all three types of connections.
The OLEDB and ODBC .NET Data Providers
ADO.NET introduces the OleDbConnection, found in the System.Data.OleDb namespace. This is the ADO.NET version of the ADO Connection
Object. Like the "classic" Connection object, the OleDbConnection has a family of properties and methods, such as ConnectionString, Open(),
Close(), etc. To create an ADO.NET connection to a data store, we simply provide the necessary properties, and invoke the Open() method.
Before we do this, however; we must import the System.Data.OleDb namespace (see "What's In a Namepsace" for more detail on namespaces).
Connecting to an ODBC Data Source Name
The ODBC .NET Data Provider,(not installed by default,download here: ODBC .NET Download) enables connecting to ODBC compliant data stores
(see ODBC download page for a list of tested drivers). We'll go ahead and connect to a System-level Data Source Name (DSN) that points to
our Northwind data base (sample database installed with Access 97/2000/XP or SQL Server). We are going to create the connection in our
ASP.NET Page_Load event, and we'll set up an ASP.NET Label control to display the connections properties after connected.
The first step is to declare our variables. Since ASP.NET enables early binding, we can declare our variables as their specific data
types. This is all done within the Page_Load event handler.
'ODBC .NET Data Provider
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim _ODBCConnection As Microsoft.Data.Odbc.OdbcConnection
Dim _SBDBProperties As New System.Text.StringBuilder()
Once we have a space in memory to create the OdbcConnection object, we can use the New keyword to instantiate the object. When
instatiating a connectoin object, you can pass in the ConnectionString as a parameter for the constructor (for a list of all constructor
overloads see the constructor information in the help docs). We'll go ahead and pass in our DSN as the ConnectionString parameter. Once the
object is instantiated, and the ConnectionString has been set, we can call the Open() method to make the connection.
_ODBCConnection = New
Microsoft.Data.Odbc.OdbcConnection("DSN=NWind;UID=sa;PWD=")
_ODBCConnection.Open()
Lastly, we want to show some information about the connection. The following code illustrates some of the information you can retrieve
from the connection object. We will do this using an ASP.NET Label control and a StringBuilder object.
_SBDBProperties.Append("Connection String: ")
_SBDBProperties.Append(_ODBCConnection.ConnectionString)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Connection Timeout: ")
_SBDBProperties.Append(_ODBCConnection.ConnectionTimeout)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Database: ")
_SBDBProperties.Append(_ODBCConnection.Database)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Data Source: ")
_SBDBProperties.Append(_ODBCConnection.DataSource)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Driver: ")
_SBDBProperties.Append(_ODBCConnection.Driver)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Server Version: ")
_SBDBProperties.Append(_ODBCConnection.ServerVersion)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Connection State: ")
_SBDBProperties.Append(_ODBCConnection.State)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Invoke _ODBCConnection.Close Method...")
_ODBCConnection.Close()
_SBDBProperties.Append("
")
_SBDBProperties.Append("Connection State: ")
_SBDBProperties.Append(_ODBCConnection.State)
Me.lblReturnCode.Text = _SBDBProperties.ToString()
End Sub
The OleDb .NET Data Provider We can reuse much of the same code to connect to an OLEDB data source wth a few difference, for instance
instead of a Driver property the OleDb Data Provider has a Provider property. All three examples are including in the download. Just like
the ASP applications you are currently creating, we will make use of the OLEDB Provider to connect to our data store. In this example we'll
connect to the Access 2000 version of the Northwind data base.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim _OleDbConnection As System.Data.OleDb.OleDbConnection
Dim _SBDBProperties As New System.Text.StringBuilder()
_OleDbConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb;")
_OleDbConnection.Open()
_SBDBProperties.Append("Connection String: ")
_SBDBProperties.Append(_OleDbConnection.ConnectionString)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Connection Timeout: ")
_SBDBProperties.Append(_OleDbConnection.ConnectionTimeout)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Database: ")
_SBDBProperties.Append(_OleDbConnection.Database)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Data Source: ")
_SBDBProperties.Append(_OleDbConnection.DataSource)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Provider: ")
_SBDBProperties.Append(_OleDbConnection.Provider)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Server Version: ")
_SBDBProperties.Append(_OleDbConnection.ServerVersion)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Connection State: ")
_SBDBProperties.Append(_OleDbConnection.State)
_SBDBProperties.Append("
")
_SBDBProperties.Append("Invoke _OleDbConnection.Close Method...")
_OleDbConnection.Close()
_SBDBProperties.Append("
")
_SBDBProperties.Append("Connection State: ")
_SBDBProperties.Append(_OleDbConnection.State)
Me.lblReturnCode.Text = _SBDBProperties.ToString()
End Sub
Here we simply wrote a connection string the way we have been doing for years with ADO. We used the Microsoft Jet 4.0 Provider to
connect to Access 2000 (use the Jet 3.51 Provider to connect to Access 97). The SQL .NET Data Provider
One of the reasons the SQL .NET Data Provider has gotten so much hype (which is why all the samples use it), is how it has been
optimized. The SQL Managed Provider talks directly to SQL Server without using OLEDB (the benefits of using products from the same
company). Microsoft claims that the speed of moving data between SQL Server and your ASP.NET application can increase as much as 300% or
more using the SQL Managed Provider because of this direct communication.
The following example illustrates how to use the SQL .NET Data Provider
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim _SQLConnection As System.Data.SqlClient.SqlConnection
Dim _SBDBProperties As New System.Text.StringBuilder()
_SQLConnection = New System.Data.SqlClient.SqlConnection("SERVER=LocalHost; " & _
"Database=Northwind; UID=SA; PWD=Mack")
_SQLConnection.Open()
_SBDBProperties.Append("Connection String: ")
_SBDBProperties.Append(_SQLConnection.ConnectionString)
_SBDBProperties.Append("
The SQL .NET Data Provider uses a ConnectionString identical to what you have been using with ADO and OLEDB for years, only there is no
need to specify an OLEDB Provider, since we are not using one..
You'll notice a new property available with this provider - PacketSize - This read-only property gets the size in bytes of network
packets used to communicate with an instance of SQL Server.
The Difference "So, is all the hype true?" you ask. A simple way to check performance is with Page Tracing. We'll cover this in depth
in another tutorial, but for now, change your first directive (the <%@...%> at the top of the page) to:
<%@ Page Language="vb" Trace=True%>
This will render a table at the bottom of the page detailing the page performance and stats. Here are the results running the .NET
Framework on a Pentium III 700 MHz with 192 MB RAM (a laptop). This computer is using Access 2000 and SQL 2000.
Data Access Type Total Time to Render (in seconds)
ODBC .NET Data Provider: 0.068359
OLEDB .NET Data Provider: 0.068122
SQL .NET Data Provider: 0.067821
Looks like the SQL Managed Provider wins! Although, the numbers may seem close you must realize that the test was run on a single machine
with multiple apps open, and with very little data being returned or processing being done. If you would like to see some real performance
tests using the SQL .NET Data Provider check out:
www.gotdotnet.com....
It's really truely amazing!!!!!!!!!
Here is the full page code for the comparison - The download also contains this page!
[Comparison.aspx] <%@ Page Trace="true" Language="vb" AutoEventWireup="false" Codebehind="comparisons.aspx.vb"
Inherits="HowTosVB.comparisons"%>
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
< HTML >
< HEAD >
< title >comparisons< /title >
< meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0" >
< meta name="CODE_LANGUAGE" content="Visual Basic 7.0" >
< meta name="vs_defaultClientScript" content="JavaScript" >
< meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5" >
< /HEAD >
< body >
< form id="Form1" method="post" runat="server" >
< P >.NET Data Provider Comparison< /P >
< P >
< asp:DropDownList id="DropDownList1" runat="server" AutoPostBack="True" >
< asp:ListItem Selected="True" Value="" Text="Please Choose .NET Data Provider" / >
< asp:ListItem Value="0" Text="SQL .NET Data Provider" / >
< asp:ListItem Value="1" Text="OleDb .NET Data Provider" / >
< asp:ListItem Value="2" Text="ODBC .NET Data Provider" / >
< /asp:DropDownList >
< /P >
< P >
< asp:DataGrid id="DataGrid1" runat="server" Font-Size="8" >< /asp:DataGrid >
< /P >
< /form >
< /body >
< /HTML >
[Comparison.aspx.vb]
Namespace HowTosVB
Public Class comparisons : Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As System.Web.UI.WebControls.DropDownList
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
#Region "User Defined Code"
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If (Me.IsPostBack) Then
Select Case DropDownList1.SelectedItem.Value
Case 0 'SQL .NET Data Provider
Dim _SqlConnection As New System.Data.SqlClient.SqlConnection()
Dim _Command As New System.Data.SqlClient.SqlCommand()
_SqlConnection.ConnectionString = "Server=LocalHost; Database=Northwind; UID=Sa; PWD=Mack"
_Command.CommandText = "SELECT * FROM Products"
_Command.Connection = _SqlConnection
_SqlConnection.Open()
Me.DataGrid1.DataSource = _Command.ExecuteReader()
Me.DataGrid1.DataBind()
_Command.Connection.Close()
Case 1 'OleDb .NET Data Provider
Dim _OleDbConnection As New System.Data.OleDb.OleDbConnection()
Dim _Command As New System.Data.OleDb.OleDbCommand()
_OleDbConnection.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb;"
_Command.CommandText = "SELECT * FROM Products"
_Command.Connection = _OleDbConnection
_OleDbConnection.Open()
Me.DataGrid1.DataSource = _Command.ExecuteReader()
Me.DataGrid1.DataBind()
_Command.Connection.Close()
Case 2 'ODBC .NET Data Provider
Dim _ODBCConnection As New Microsoft.Data.Odbc.OdbcConnection()
Dim _Command As New Microsoft.Data.Odbc.OdbcCommand()
_ODBCConnection.ConnectionString = "DSN=NWind;UID=sa;PWD=mack"
_Command.CommandText = "SELECT * FROM Products"
_Command.Connection = _ODBCConnection
_ODBCConnection.Open()
Me.DataGrid1.DataSource = _Command.ExecuteReader()
Me.DataGrid1.DataBind()
_Command.Connection.Close()
End Select
End If
End Sub
#End Region
End Class
End Namespace
| 
