1 / 31

Introduction to Database Processing with ADO.NET

Introduction to Database Processing with ADO.NET. The ASP.NET / ADO.NET Conundrum. The tools and wizards are wonderful for simple applications As applications become more complex and “real” You need to write code between the controls and the data providers

quana
Download Presentation

Introduction to Database Processing with ADO.NET

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. Introduction to Database Processing with ADO.NET

  2. The ASP.NET / ADO.NET Conundrum • The tools and wizards are wonderful for simple applications • As applications become more complex and “real” • You need to write code between the controls and the data providers • Nested controls and one-to-many views become complex • At some point, the visual designers become useless

  3. Lecture Overview • Introduce ASP.NET data sources • Introduce the ASP.NET data bound controls • Show a few SIMPLE examples to get us started

  4. History of ADO (1) • Open Database Connectivity (ODBC) was created to provide a uniform API to access SQL database servers • OLE DB evolved from ODBC and provided a COM based interface • Next came the first versions of ADO, which encapsulated OLE DB • ADO.NET is the current generation of ADO and uses .NET Managed Providers

  5. History of ADO (2) • While not a problem Microsoft has stepped away from all MSAccess support. • The data controls are gone • Necessary Jet versions are not usually installed

  6. ADO / ASP.NET NOTE • The capabilities of ASP and ADO are vast • There are many ways to accomplish the same task • DataReaders vs. DataAdapters • DataSource controls vs. manual binding • Bound control configuration is sophisticated

  7. ADO.NET -The Role of Managed Providers • Managedproviders interact with a data source and return data to an application • Data sources are typically (almost always) disconnected • After data is returned to the application, connections are closed

  8. ADO.NET Namespaces / Classes (Intro) • There are many of these • Some you don’t need to work with directly • There are many ways to accomplish the same task • Mastery of the topic requires that you understand what the controls do and how they interact with the underlying classes

  9. ADO.NET Namespaces (1) • Here’s the fabric • System.Data • System.Data.SqlClient • System.Data.OleDb • System.Data.SqlTypes

  10. ADO.NET Classes (1) • Connection – creates the connection with the data source (database) • Support is provided for ORACLE, SQL Server, Access, etc. • Transaction – allows for transactional processing • Command – typically an SQL statement executed against a database server • Parameter – a parameter passed to a command

  11. ADO.NET Classes (2) • DataAdapter – a database object that returns a disconnected set of records (DataSet and DataTable) • DataReader – a forward-only reader to get database records

  12. Introduction to Connections • Executing a database command is generally a two-step process • Create a connection • Prepare and execute a command • The ASP.NET controls help automate much of the process • There are specific connection classes for OLE DB, SQL Server and others • They all work the same way

  13. Connection Strings • There are tools to build these for you based on the database provider • Connection strings are typically stored in the web.config file • They store all information needed to establish a database connection • More later

  14. Connection Strings (Example) • Connection strings are usually stored in web.config • The following reads the connection string from web.config

  15. Connection Strings (Example) • The SqlConnectionobject represents the connection itself. It’s properties depict the connection status

  16. Executing Commands(Introduction) • Use the SqlCommand object to • Return one record one field (ExecuteNonQuery) • Execute SQL INSERT, UPDATE, DELETE statements • Return records in the form of a DataTable • Return records one row at a time (DataReader)

  17. SqlCommand Object (Using 1 row) • The OleDbCommand or SqlCommandobjects work the same way • Set the CommandText property to the SQL statement to execute • Set the CommandType property to CommandType.Text • Set the Connection property to an existing Connection object

  18. SqlCommand Object (Using 1 row) • To return one field from one row, call ExecutScalar() on the SqlCommandobject

  19. SqlCommand Object (Returning Nothing) • Use the ExecuteNonQuerymethod to execute SQL statements that do not return values • INSERT / UPDATE / DELETE • As before, the CommandText property stores the SQL statement • Two ways to do INSERT / UPDATE /DELETE • With parameters • Roll the statement by hand

  20. SqlCommand Object (Returning Nothing - Manual) • Use create the CommandText, CommandType and Connection as before • The INSERT / UPDATE / DELETE statements get contorted

  21. SqlCommand Object (Parameters 1 ) • Parameters provide the preferred way to pass data to INSERT / UPDATE / DELETE and other SQL statements • Parameters begin with an @ in the SQL statement

  22. SqlCommand Object (Parameters 1 ) • Then we create the parameters as part of the command object • We are adding items to the parameters collection

  23. Using the DataReader • This is straight out of the book • There are much better ways to read data but here is the first cut • It’s a forward only reader • Call Read to read the first and subsequent records • Create an SqlCommand object as before • Call ExecuteReader to initialize • Call Read to read each record • Index contains the field name

  24. Using the DataReader (example)

  25. Reading Multiple Records • Here, we use the DataSet and DataTableobjects along with the DataAdapter • Steps • Create SqlCommand with the SELECT statement as before • Create a new SqlDataAdapter • Create a DataSet • Call Fill on the DataAdapter to populate the DataSet

  26. Reading Multiple Records

  27. Introduction to Data Sources • Data sources establish the connection between an ASP.NET application and a database • These are not the same controls as the desktop controls

  28. Data Source Controls(Categories) • Tabular • AccessDataSource – For Access databases • SqlDataSource – For SQL databases • ObjectDataSource – For custom business objects • Hierarchical • SiteMapDataSource – For menus • XmlDataSource – To bind XML documents

  29. Data Sources (Core Concepts) • The ASP.NET data source controls are bound to a page rather than the application • They belong to System.Web rather than System.Data so they are again unique to ASP.NET • Underneath the hood, there is a DataSet • You can use the System.Data classes from an ASP.NET application • Visual Studio does a great job configuring the data source controls

  30. Data Sources (Under the Hood 1) • The Wizards configure properties to • Write SELECT statements and customize them • Write INSERT, UPDATE, and DELETE statements that are executed by other bound controls

  31. Data Sources(Under the Hood 2) • The Properties window tells thetale

More Related