accessing relational data using microsoft visual studio net n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Accessing Relational Data Using Microsoft Visual Studio .NET PowerPoint Presentation
Download Presentation
Accessing Relational Data Using Microsoft Visual Studio .NET

Loading in 2 Seconds...

play fullscreen
1 / 19

Accessing Relational Data Using Microsoft Visual Studio .NET - PowerPoint PPT Presentation


  • 106 Views
  • Uploaded on

Accessing Relational Data Using Microsoft Visual Studio .NET. Overview. Overview of ADO.NET Creating a Connection to a Database Displaying a DataSet in a List-Bound Control. What is ADO.NET?. ADO.NET provides a set of classes for working with data. ADO.NET provides:.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Accessing Relational Data Using Microsoft Visual Studio .NET' - marion


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
overview
Overview
  • Overview of ADO.NET
  • Creating a Connection to a Database
  • Displaying a DataSet in a List-Bound Control
what is ado net
What is ADO.NET?

ADO.NET provides a set of classes for working with data. ADO.NET provides:

  • An evolutionary, more flexible successor to ADO
  • A system designed for disconnected environments
    • Disconnected Environment: It means Accessing the data provider source, saving it in a Web application Data source object, and then, closing the connection with Data provider
  • A programming model with advanced XML support
  • A set of classes, interfaces, structures, and enumerations that manage data access from within the .NET Framework
using namespaces
Using Namespaces
  • Use the Imports statement to import namespaces
  • Namespaces used with ADO.NET include:
    • System.Data
    • System.Data.SqlClient

Imports System.Data

Imports System.Data.SqlClient

the ado net object model
The ADO.NET Object Model

DataSet

DataTable

DataTable

SqlDataAdapter

SQL Server .NET

Data Provider

  • DataSet object is a group of related data tables

SqlConnection

SQL Server 7.0

(and later)

what is a dataset
What is a Dataset?

DataSet

DataTable

DataTable

DataTable

SqlDataAdapter

Web server memory

Physical storage

SqlConnection

SQL Server 2000

accessing data with ado net

DataSet

Accessing Data with ADO.NET

Database

1

Client makes request

Create the SqlConnection and SqlDataAdapter objects

2

Fill the DataSet from the DataAdapter and close the connection

SqlConnection

3

Web server

Return the DataSet to the Client

4

SqlDataAdapter

Client manipulates the data

5

Update the DataSet

6

Use the SqlDataAdapter to open the SqlConnection, update the database, and close the connection

7

List-Bound Control

Client

using server explorer to generate a connection
Using Server Explorer to Generate a Connection
  • Create a new data connection using the Data Links dialog box
retrieving data in a disconnected environment
Retrieving Data in a disconnected Environment

To retrieve a DataTable from SQL server Database and store it in a DataSet object:

1. Creating a connection with the required Database

Dim strConn As String = "data source=“ & _ “server\instanceName; initial catalog= DB_name;” & _ “integrated security=true“

Dim con As New SqlConnection(strConn)

2. Creating an SqlCommand that will be executed in the DB:

Dim cmd As New SqlCommand(“Sql_Statement", con)

retrieving data in a disconnected environment1
Retrieving Data in a disconnected Environment

3. Determine the SqlStatement type:

cmd.CommandType = CommandType.Text

4. Creating an SqlDataAdapter object (ad)

  • Dim ad As New SqlDataAdapter(cmd)
  • SqlDataAdapter object is used to :
    • open the connection with DB
    • execute the SQL statement passed to (cmd) object
    • retrieve the resulting table ,
    • storing it in a DataSet
    • and finally close the DB connection
retrieving data in a disconnected environment2
Retrieving Data in a disconnected Environment

5. Creating a DataSet object (ds) to store the retrieved data in it.

Dim ds As New DataSet

6. filling the (ds) with the comming table

ad.Fill(ds, “table_name")

binding a dataset to a list bound control
Binding a DataSet to a List-Bound Control
  • Create the control
  • Bind to a DataSet that contains a table (items)

<asp:Repeater ID="Repeater1" runat="server"></asp:Repeater>

dg.DataSource = ds

dg.DataMember = “items"

dg.DataBind()

handling errors
Handling Errors
  • Connection will not open
    • Connection string is invalid
    • Server or database not found
    • Login failed
  • DataAdapter cannot create a DataSet
    • Invalid SQL syntax
    • Invalid table or field name
inserting a row in a table in a certain db
Inserting a row in a table in a certain DB

ex) assume we have the table (news) in the DB (MyDB) in the Sql instance (server\inst_name)

  • News table schema
  • To insert the news item : “New News item”

Dim con As New SqlConnection("server=server\inst_name;Initial Catalog=MyDB;Integrated security=true")

Dim cmd As New SqlCommand("insert into news(news_headline) values(‘New News item’), con)

con.Open()

cmd.ExecuteNonQuery()

con.close()

what is a datareader
What is a DataReader?
  • read-only
    • You can use it just to read data from DataBase
  • Fast access to data
  • Continuous Connecting to a data source until all the data is retrieved
  • Manage the connection yourself
  • Uses fewer server resources
creating a datareader
Creating a DataReader
  • To use a DataReader:
    • Create and open the database connection
    • Create a Command object
    • Create a DataReader from the Command object
    • Call the ExecuteReader method
    • Use the DataReader object
    • Close the DataReader object
    • Close the Connection object
  • Use Try…Catch…Finally error handling

1

2

3

4

5

6

7

reading data from a datareader
Reading Data from a DataReader
  • Call Read for each record
    • Returns false when there are no more records
  • Access fields
    • Parameter is the ordinal position or name of the field
  • Close the DataReader
  • Close the connection

Do While myReader.Read()

str &= myReader(1)

str &= myReader("field")

str &= myReader.GetDateTime(2)

Loop

example retrieving data from database using sqldatareader
Example ) Retrieving Data from DataBase using SqlDataReader

Dim con As New SqlConnection("server=server\inst_name;Initial Catalog=MyDB;Integrated security=true")

Dim cmdAuthors As New SqlCommand("select * from Authors", con)

con.Open()

Dim dr As SqlDataReader

dr = cmdAuthors.ExecuteReader()

Do While dr.Read()

lstBuiltNames.Items.Add(dr("au_lname") + ", " + dr("au_fname"))

Loop

dr.Close()

conn.Close()

binding a datareader to a list bound control
Binding a DataReader to a List-Bound Control
  • Create the Control
  • Bind to a DataReader

<asp:DataGrid id="dgAuthors" runat="server" />

dgAuthors.DataSource = dr

dgAuthors.DataBind()

dgAuthors.DataSource = dr;

dgAuthors.DataBind();