ADO.NET and SQL Server

1
ADO.NET and SQL Server

• Tuc Goodwin

2
Introduction

• This is the Ninth of 15 planned presentations
• Upcoming presentations

Date Title
6/11/05 Using ADO.NET
7/9/05 TBD (Tuc is on Vacation)
8/13/05 Deploying Applications
9/10/05 Comparisons C VB.NET part 1
10/8/05 Comparisons C VB.NET part 2
3
This Months Objective

• Our overall objective is to access a SQL Server
  Database
• This Presentation explains how to use ADO.NET
  with a Windows Forms application to create, read,
  update, and delete records in Access and SQL
  Server databases..

4
Demonstration Code

• We will build a short demonstration application
  as we go through this lecture.
• All code samples can be downloaded from
• http//groups.msn.com/NTPCUGDevToolsSIG
• And soon Beginning VB.NET SharePoint Site
• http//69.41.237.216879/BEGVBNET/default.aspx
• End With

5
Agenda

• ADO.NET Objects
• Data Providers
• Data Connection
• Data Command
• Data Adapter
• DataReader
• DataSet
• Data Access Building Blocks

6
ADO.NET Objects
7
Data Providers

• A data provider in the .NET Framework serves as a
  bridge between an application and a data source.
  A data provider is used to retrieve data from a
  data source and to reconcile changes to that data
  back to the data source.
• The following table lists the .NET Framework data
  providers that are included in the .NET
  Framework.

.NET Framework data provider Description
.NET Framework Data Provider for SQL Server For Microsoft SQL Server version 7.0 or later.
.NET Framework Data Provider for OLE DB For data sources exposed using OLE DB.
.NET Framework Data Provider for ODBC For data sources exposed using ODBC.
.NET Framework Data Provider for Oracle For Oracle data sources. The .NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later.
8
Data Connection

• This object establishes a connection to a
  specific data source.
• Properties ConnectionString
• Methods Open()
• Close()

Dim nwindConn As SqlConnection _ New
SqlConnection("Data SourcelocalhostIntegrated
SecuritySSPI" _
"Initial Catalognorthwind") nwindConn.Open()
9
Data Command

• This object uses the connection to create a
  command object. Then the program can implement
  several Execute methods to perform the action
  which may return data in a variety of formats.

Dim catCMD As SqlCommand New SqlCommand("SELECT
CategoryID, _ CategoryName FROM Categories",
nwindConn) Dim myReader As SqlDataReader
myCommand.ExecuteReader(CommandBehavior.CloseConn
ection)
10
Execute Methods

• ExecuteReader Sends the CommandText to the
  Connection and builds a SqlDataReader.
• ExecuteScalar Executes the query, and returns
  the first column of the first row in the result
  set returned by the query. Extra columns or rows
  are ignored.
• ExecuteXMLReader - Sends the CommandText to the
  Connection and builds an XmlReader object.

11
Data Adapter

• Represents a set of data commands and a database
  connection that are used to fill the DataSet and
  update a SQL Server database. This class cannot
  be inherited.

Public Function SelectSqlSrvRows(dataSet As
DataSet, connection As String, query As String)
As DataSet Dim conn As New SqlConnection(conne
ction) Dim adapter As New SqlDataAdapter()
adapter.SelectCommand new SqlCommand(query,
conn) adapter.Fill(dataset) Return
dataset End Function
12
DataReader

• Provides a means of reading a forward-only stream
  of rows from a SQL Server database.

Dim myReader As SqlDataReader
myCommand.ExecuteReader() If myReader.HasRows
Then Do While myReader.Read()
Console.WriteLine(vbTab "0" vbTab "1",
myReader.GetInt32(0), myReader.GetString(1))
Loop Else Console.WriteLine("No rows
returned.") End If myReader.Close()
13
DataSet

• Represents an in-memory cache of data.

14
Data Access Building Blocks

• These are helper objects from Microsoft that
  encapsulates all the objects necessary to
  interact with the database.
• http//www.microsoft.com/downloads/details.aspx?Fa
  milyIdF63D1F0A-9877-4A7B-88EC-0426B48DF275displa
  ylangen

15
Summary

• We started on ADO.NET
• and we wrote some more code
• Well continue to build from here

16
Next Time

• Deploying Applications (August)
• Comparisons VB.NET and C (Part I) Sept.
• Comparisons VB.NET and C (Part II) Oct.
• A new Series

17
Questions?