1 / 76

Chapter 5

Chapter 5. Configuring Connections and Connecting to Data. Overview of ADO.NET with the .NET Framework. Introdution to ADO.NET. Universal Data Access (UDA). Microsoft’s strategy for accessing data for multiple providers

hamlet
Download Presentation

Chapter 5

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5 Configuring Connections and Connecting to Data

  2. Overview of ADO.NETwith the .NET Framework Introdution to ADO.NET

  3. Universal Data Access (UDA) • Microsoft’s strategy for accessing data for multiple providers • Goal is to access any type of data from any application on any type of computer Introdution to ADO.NET

  4. OLEDB • Technology designed to implement the UDA concept • Provides an standardized object-oriented interface • Allows access data from any source • Library functions • Hard to use Introdution to ADO.NET

  5. ADO.NET ActiveX Data Objects  ADO • Important Core of the .NET Framework • Microsoft’s latest database object model • Data access solution in .NET • Allows VB programmers to use a standard set of objects to refer to data from any source: SQL Server, Oracle, OLE DB & ODBC (UDA…)

  6. .NET Data Provider Connection Command Data Adapter SelectCommand InsertCommand UpdateCommand DeleteCommand DataReader .NET Data Provider Objects

  7. Generic classes • Connection • Command. • DataReader • DataAdapter • DataSet • Core ADO.NET Namespaces System.Data System.Data.OleDb System.Data.SqlClient

  8. Importing the ADO.NET Namespaces Needed to build a data access application • For OLE DB: (MS Access) Imports System.Data Imports System.Data.OleDB • For SQL Server: Imports System.Data Imports System.Data.SQLClient

  9. Lesson 1: Creating and Configuring Connection Objects • What Is a Connection Object?

  10. What Is a Connection Object? • Representation of an open connection to a data source. • Does not fetch, update data, , execute queries • the pipeline that commands and queries use to send

  11. How to create connection? • Creating Connections in Server Explorer • P206 • Creating Connections Using Data Wizards • P207 • Creating Connection Objects Programmatically • SQL Server • SQL / Integrated Security • MS Access

  12. Creating Connection Objects Programmatically • MS Access • SQL Server

  13. Connection Objects • Connection namespace

  14. Connection properties

  15. Connection methods

  16. Chapter 6 Working with Data in a Connected Environment

  17. Data Source Connection Data Adapter DataSet Web Form Windows Form Lesson 0: Connected & Disconnectd Environment in ADO.NET • 2 ways using with ADO.NET Disconnectd Connected Web Form Data Source Connection Command DataReader Windows Form

  18. Disconnected Data Access Advantages Disadvantages • Single database server can support many users • reduced server's resources • Data using more flexible • Data not 'tied' to a connection • easy to pass between tiers or persist to file • Highly suited to Web and n-tier Apps • Expensive when open, close connections • Retrieving large result sets can be very slow • Places demand on client memory and CPU

  19. System.Data Architecture Program DataReader DataSet DataProvider DataAdapter Database

  20. Program Action SQL DataReader Maintain data DataSet Get data Maintain data Command Object Get data Connection Object DataAdapter Provider Database Details: System.Data Architecture

  21. Main Difference:DataSet-DataReader DataSet • Data structure to store schema and data in a disconnected fashion • Useful for editing data offline and later update to data source • DataReader • Like Phone connection. • Doesn’t need to store data in memory • Object to access data in a connected, forward-only, read-only fashion • When performance is your chief concern, especially with large amounts of data, use a DataReader class

  22. Differences DataSet and DataReader

  23. Lesson 1: Creating and Executing Command Objects • 1.What Are Command Objects? • 2.Creating and Configuring Command Objects • 3.Creating SQL Commands (SQL Statements) with the Query Designer

  24. Lesson 1: Creating and Executing Command Objects • 1.What Are Command Objects?

  25. Lesson 1: Creating and Executing Command Objects • 1.What Are Command Objects? • To execute SQL statements,stored procedures • Contain the necessary information to execute SQL statements

  26. Lesson 1: Creating and Executing Command Objects • 1.What Are Command Objects? • Depend on Data Providers

  27. Lesson 1: Creating and Executing Command Objects • 1.What Are Command Objects? • Common properties (p.254) • CommandText (SQL statement or the name of any valid stored procedure) • CommandType (Text, TableDirect, StoredProceduce) • CommandTimeout(The time in seconds before terminating the attempt to execute a command. 30s) • Connection

  28. Lesson 1: Creating and Executing Command Objects

  29. Lesson 1: Creating and Executing Command Objects • 1.What Are Command Objects? • Common Command Object Methods (p.255) • ExecuteNonQuery (Executes SQL statements or stored procedures that return excuted number) • ExecuteReader (Executes commands that return tabular (or rows) of data.) • ExecuteScalar (return object, often use to get value of a column or total of record ) • ExecuteXmlReader (Returns XML formatted data)

  30. Lesson 1: Creating and Executing Command Objects • 2.Creating and Configuring Command Objects • Creating a Command Object That Executes a SQL Statement • Creating a Command Object That Executes a Stored Procedure • Creating a Command Object That Performs Catalog Operations • Creating a Command Object That Returns a Single Value

  31. Lesson 1: Creating and Executing Command Objects • 2.Creating and Configuring Command Objects • Creating a Command Object That Executes a SQL Statement (p.256)

  32. Lesson 1: Creating and Executing Command Objects • 2.Creating and Configuring Command Objects • Creating a Command Object That Executes a Stored Procedure (p.257)

  33. Lesson 1: Creating and Executing Command Objects • 2.Creating and Configuring Command Objects • Creating a Command Object That Performs Catalog Operations (p.257)

  34. Lesson 1: Creating and Executing Command Objects • 2.Creating and Configuring Command Objects • Creating a Command Object That Returns a Single Value (p.258)

  35. Lesson 1: Creating and Executing Command Objects • 2.Creating and Configuring Command Objects • Creating a Command Object That Returns a Single Value (p.258)

  36. Lesson 1: Creating and Executing Command Objects • 3.Creating SQL Commands (SQL Statements) with the Query Designer • Creating SQL Commands (SQL Statements) with the Query Designer • Performing Database Operations Using Command Objects

  37. Lesson 1: Creating and Executing Command Objects • 3.Creating SQL Commands (SQL Statements) with the Query Designer • Creating SQL Commands (SQL Statements) with the Query Designer • We can use the Query Designer to assist in creating SQL for Command objects • Select database in Server Explorer-> select New Query from the Data menu.

  38. Lesson 1: Creating and Executing Command Objects • 3.Creating SQL Commands (SQL Statements) with the Query Designer • Performing Database Operations Using Command Objects (p 260)

  39. How to receive DataReader Connectionn Open Commandn Which data? DataReadern Result

  40. DataReader Class • Datareader and MS Access • Store the information obtained by the command • In stateless stream type object • Very efficient • Forward-only cursor • Read-only cursor

  41. DataReaders • You can’t access anything until you call Read()the first time

  42. Core DataReader method/property • Read:Reads, and set pointer to the next record. • Close • IsClosed • HasRows:Returns true if DataReader contains rows • FiledCount: Number of columns • GetName(i): returns the label of the ith column in the current row • GetString(i) :returns the value of the ith column as the specified type

  43. Note: DataReader • ADO.NET does not provide all the server-side cursor • Don’t keep DataReaders open longer than necessary • For flexible updates & client-side manipulation… • Use DataSets and DataAdapters • Only one DataReader use at a time • Tie to Connection=> cannot used other DataReader. • To reuse connection=>call DataReader.Close. • Don’t depend on the garbage collector-> explicitly close. • Tie the connection ‘ life to DataReader • CommandBehavior.CloseConnection in ExecuteReader.

  44. Note about DataReader • The first row of data is not available until you call the Read method. • Using with stored procedure uses a return or output parameter, must close DataReader before get parameter • DataReader cannot be used for data binding • System.DBNull.value

  45. Lesson 1: Creating and Executing Command Objects • 3.Creating SQL Commands (SQL Statements) with the Query Designer • Lab: P265

  46. ExecuteSql

  47. Ex: _Module8_2

  48. Load Data from Access

More Related