Ado net objects data providers
This presentation is the property of its rightful owner.
Sponsored Links
1 / 37

ADO.NET Objects – Data Providers PowerPoint PPT Presentation


  • 45 Views
  • Uploaded on
  • Presentation posted in: General

ADO.NET Objects – Data Providers. Dr. Ron Eaglin. Requirements. Visual Studio 2005 Microsoft SQL Server 2000 or 2005 Adventure Works Database Installed Database available on local machine. Agenda. Working with the Database Connection Object ConnectionString property

Download Presentation

ADO.NET Objects – Data Providers

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Ado net objects data providers

ADO.NET Objects – Data Providers

Dr. Ron Eaglin


Requirements

Requirements

  • Visual Studio 2005

  • Microsoft SQL Server 2000 or 2005

    • Adventure Works Database Installed

    • Database available on local machine


Agenda

Agenda

  • Working with the Database Connection Object

    • ConnectionString property

    • Visual Creation of Connection

    • Code Creation of Connection

    • Runtime Connections


Database connections

Database Connections

Create a new project

Windows application

Name project:

AdventureWorksDemo1


Using toolbox

Using Toolbox

  • Right click on the data icon in the toolbox

  • Select “Choose items …”

  • This will bring up a dialog allowing you to select all items displayed from the list of available tools.


Data items

Data “items”

Select SQLConnection from

List of available tools

The SQLConection object

Will now be in your Toolbox


Adding sqlconnection to form

Adding SQLConnection to Form

Drag and drop

SQLConnection

Object onto

Form.


Sql connection object

SQL Connection Object

Select the SQLConnection object in the form and look at the

Properties dialog box. Select the ConnectionString property

And select <New Connection>


Add connection

Add Connection

Select your server

Select AdventureWorks database

Test the connection


Add connection1

Add Connection

  • ConnectionString property will fill with the text that allows the connection.

  • ConnectionString has form

    Keyword=value;keyword=value;keyword=value

    Example:

    Data Source=WHITEWATER;Initial Catalog=AdventureWorks;Integrated Security=True


Adding connection string with code

Adding Connection String with Code

Add button to form and

Change text of button

Double click button for

Code window


Button code window

Button Code window

Enter code here


Ado net objects data providers

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

' Create a new connection object of type SQLConnection

Dim RuntimeConnection As New Data.SqlClient.SqlConnection

' Set the connection string

RuntimeConnection.ConnectionString = "Data Source=WHITEWATER;Initial Catalog=AdventureWorks;Integrated Security=True"

Try

RuntimeConnection.Open()

MsgBox("Connection with connection string " + RuntimeConnection.ConnectionString + " opened successfully", MsgBoxStyle.Information)

Catch ex As Exception

MsgBox("Connection with connection string " + RuntimeConnection.ConnectionString + " failed to open", MsgBoxStyle.Information)

End Try

RuntimeConnection.Close()

End Sub


Test application

Test Application


Add code to test sqlconnection1

Add code to test SQLConnection1

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Try

SqlConnection1.Open()

MsgBox("Connection with connection string " + SqlConnection1.ConnectionString + " opened successfully", MsgBoxStyle.Information)

Catch ex As Exception

MsgBox("Connection with connection string " + SqlConnection1.ConnectionString + " failed to open", MsgBoxStyle.Information)

End Try

SqlConnection1.Close()

End Sub


Test sqlconnection1

Test SQLConnection1


Sqlconnection properties

SQLConnection Properties

  • ConnectionString

  • ConnectionTimeout

  • Database

  • DataSource

  • ServerVersion

  • State


Other types of connections

Other types of Connections

  • SQLConnection is specific to SQl Server only

  • ODBCConnection – any ODBC database

  • OLEDBConnection – Any OLE DB datasource

  • OracleConnection


Agenda1

Agenda

  • Creating and using a DataCommand object

  • Issuing Database commands using the DataCommand object.


Datacommand object

DataCommand object

  • DataCommand is a simple ADO.NET wrapper for a SQL Statement.

  • Using DataCommand from design and from run-time.


Design window sqlcommand

Design Window SQLCommand

  • If SQLCommand is not in your toolbox, right click and add it to your toolbox using the Choose Items…

  • Drag and drop the SQLCommand on to your form.


Sqlcommand added

SQLCommand added

SQLCommand object in

form


Sqlcommand

SQLCommand

  • Properties of the SQLCommand

    • Connection

    • CommandType

      • Text

      • StoredProcedure

      • TableDirect

    • Parameters


Sqlcommand1 properties

SQLCommand1 Properties

  • Set Connection = SQLConnection1

  • Set CommandType = Text

  • Click on … dialog on CommandText Property


Ado net objects data providers

Clicking on box

Will bring up query

Builder.


Ado net objects data providers

Once the query is built – it is possible to use the preview

Data link in the properties window.


Runtime sqlcommand

Runtime SQLCommand

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

Dim rtSQLCommand As New Data.SqlClient.SqlCommand

rtSQLCommand.Connection = SqlConnection1

rtSQLCommand.CommandType = CommandType.Text

rtSQLCommand.CommandText = "SELECT Person.Address.* FROM Person.Address"

End Sub


Sqlcommand1

SQLCommand

  • Note that running the code to create the runtime SQLCommand – does not actually do anything.

  • We must execute the Command and also have a place for the results.


Datareader object

DataReader Object

  • A DataReader is a lightweight object meant to hold the results of a SQLCommand

  • DataReaders can be treated as a cursor to go through the DB values.


Datareader

DataReader

SqlConnection1.Open()

Dim rtDataReader1 As Data.SqlClient.SqlDataReader

rtDataReader1 = rtSQLCommand.ExecuteReader

If rtDataReader1.HasRows = True Then

MsgBox("The reader has found rows", MsgBoxStyle.Information)

End If

SqlConnection1.Close()

Add this code to the previous code


Binding the data reader

Binding the Data Reader

Add Listbox

To Form


Add code

Add code

While rtDataReader1.Read

ListBox1.Items.Add(rtDataReader1.GetValue(1))

End While

Add code before closing the connection, after executing the reader.


Results of code

Results of Code

Data from first field of DB

Query is added to the list.


Datareader methods

DataReader Methods

  • Open code window

  • Put cursor on the code

    Dim rtDataReader1 As Data.SqlClient.SqlDataReader

    Hit F1

Put cursor here


Help on datareader

Help on DataReader

Code examples of using Data Reader

More code


Review

Review

  • Working with Database Connection

  • Creating code to use database

  • Using Command Objects

  • Using the Data Reader Object

  • Displaying Database Results

  • Getting More Information on Objects


  • Login