Needs for Accessing Database

1. Needs for Accessing Database • To make your web site more dynamic and maintainable, you can display information on your web pages that are retrieved from a database • A number of web applications (e.g. e-commerce, on-line registration) require the manipulation of database information through the web

2. Accessing Database Using ASP • Storing and retrieving information from a relational database can be done with ADO.NET (ActiveX Data Objects). • ADO is a technology that allows connectivity to anyODBC or OLE DB data source. • ODBC (open database connectivity): common API to access any relational database if it has an ODBC driver. • OLE DB: universal data access interface specifications to provide a common layer between data sources and data consumers

3. Accessing Database Application ADO: a component wrapper for OLE DB interface ActiveX Data Object (ADO) OLE DB OLE DB provider OLE DB provider OLE DB provider OLE DB provider Access SQL server oracle ODBC driver

4. ADO.NET • ADO.NET consists of a set of classes defined by the .NET framework • ADO.NET (ActiveX Data Objects.NET) is primary data access API for .NET • Data Source controls: represents different kinds of data stores (e.g. databases and XML files) • No rendering on screen • You have facilities such as (paging, filtering, updating, inserting and deleting)

5. ADO.NET 2.0 • Two types of data providers for relational databases: • SQL server v7: System.Data.SqlClient • OLEDB: System.Data.OleDb • Any provider has 4 main classes to allow access and manipulation of data • Connection -Command • DataReader -DataAdapter

6. ADO.NET 2.0 • Two methods to read data from database • Connected (Use data Reader) only read data as long as there is a connection • Disconnected (Use data Adapter) store results in a memory cash and you can still read and update it despite there is no connection with data source

7. ADO.NET • Reading data from database (General algorithm) • Create a connection object • Create a command object • Set the SQL select command • Execute the command • Read the result using data adaptor OR data reader

8. Data Reader • Read only, forward only object to read data from a database source. • Requires live connection with database • You have to open and close connection

9. Example (Connected method) Dim SQLConn As New OLedbConnection SQLConn.ConnectionString = ConfigurationManager.ConnectionStrings("ProductsConString").ConnectionString SQLConn.Open() Dim SQLComm As New OLedbCommand SQLComm.CommandText = "select * from product" SQLComm.Connection = SQLConn Dim SQLData As OleDbDataReader SQLData = SQLComm.ExecuteReader Label1.text = "SID Sname <br>" While SQLData.Read() Label1.text &= SQLData(0) & " " & SQLData(1) & "<br>" End While SQLConn.Close()

10. ADO.NET Features • Data Can Be Cached in Datasets • When retrieving data from a database, it is more efficient to get the list of records once and during update it is impractical to connect to the database to work on each record • Solution: temporarily store the records retrieved from the database and work with this temporary set (DataSet) • Dataset is a passive container. To actually fetch data from the database and write it back, you use data adapters

11. Data adapter • An object used to communicate between a data source and a dataset • Reading data from a database into a dataset, and then writing changed data from the dataset back to the database • Using an adapter, you can add, update, and delete records in a data source. To specify how each of these operations should occur, an adapter supports the following four properties: • SelectCommand – reference to a command that retrieves rows from the data store. • InsertCommand – reference to a command for inserting rows into the data store. • UpdateCommand – reference to a command for modifying rows in the data store. • DeleteCommand – reference to a command for deleting rows from the data store.

12. Example (Disconnected method) Dim SQLConn As New OleDbConnection SQLConn.ConnectionString = ConfigurationManager.ConnectionStrings("ProductsConString").ConnectionString ' No open connection Dim SQLComm As New OleDbCommand SQLComm.CommandText = "select * from product" SQLComm.Connection = SQLConn Dim SQLData As OleDbDataAdapter Dim DS As DataSet DS = New DataSet SQLData = New OleDbDataAdapter() SQLData.SelectCommand = SQLComm SQLData.Fill(DS) Gv.DataSource = DS Gv.DataBind()

13. DataSet • A dataset is a cache of records retrieved from a data source. • Works like a virtual data store: A dataset includes one or more tables based on the tables in the actual database, and it can include information about the relationships between those tables • Similar structure as a relational database (tables, rows, and columns) • Used if you want to work with a set of tables and rows while disconnected from the data source.

14. ADO.NET classes Memory Web Page Data table • Data adaptor Fill method: opens connection, executes command, reads data and closes connection DataBind() Rows Relations Grid View Data Adaptor Data Reader Command ADO.NET Connection Data Source

15. Data Access models • You can either use available controls to perform and access data • OR, you can write code

16. Data Access Model (1) • Steps to include database access to your ASP.NET application • Design and implement your database • Store the database file on the server • Create a dataSource control on your web page • Specify the type of database provider • Locate database file and access information • Optionally, store connection string on web.config file • Create queries (select, update, insert, delete) • Connect the datasource to any control on your web page by binding the right fields

17. Update, Delete from ViewGrid • Reconfigure datasource object to generate Insert, Delete and update queries • Check the queries generated in properties window • Add update, delete command buttons in the Grid view • Make sure that the DataKeyName= Primary key field in your database • Run application

18. Form View Control • Used to display a single item (row) from the data source • Allows update, delete, insert and paging

19. Display Images from data source • Add form View control • Click “Edit template” • Delete the image template (image file name) • Drag an image control in place • Bind the ImageURL property to the appropriate data source field • (Make sure images are in the correct path)

20. Data Access Model (2) • Performing database operations in code • Add a connection object to the form and set its connection string • Open the connection using the “open” method • Create a command object and set its “connection” property • Set the “commandtext” property of the command to the SQL statement that you want to perform • Execute the command • Close the connection using the “close” method

21. Connection Object • To move data between a data store and the application, you must first have a connection to the data store. • In ADO.NET you can create and manage a connection using one of two connection objects: • SqlConnection - an object that manages a connection to a SQL Server version 7.0 or later. (optimized because it bypasses the OLE DB layer) • OleDbConnection - an object that manages a connection to any data store accessible via OLE DB. • ConnectionString property, consists of a string with information required to log on to a database

22. Updating data Dim SQLConn As New OleDbConnection SQLConn.ConnectionString = ConfigurationManager.ConnectionStrings("ProductsConString").ConnectionString SQLConn.Open() Dim SQLComm As New OleDbCommand SQLComm.CommandText = "update [product] set [productName]='" & TextBox4.Text & "' , [Price]=" & Val(TextBox2.Text) & " , [image] = '" & TextBox3.Text & "' where [ProductID]=" & Val(TextBox1.Text) SQLComm.Connection = SQLConn Response.Write(SQLComm.CommandText) SQLComm.ExecuteNonQuery() SQLConn.Close()

23. Inserting Data Dim SQLConn As New OleDbConnection SQLConn.ConnectionString = ConfigurationManager.ConnectionStrings("ProductsConString").ConnectionString SQLConn.Open() Dim SQLComm As New OleDbCommand SQLComm.CommandText = "Insert into product values ('" & TextBox1.Text & "'," & TextBox2.Text & ")" SQLComm.Connection = SQLConn SQLComm.ExecuteNonQuery() SQLConn.Close()

24. Grid View Object • Access data in a selected row of a Grid View Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged Response.Write("Values " & GridView1.SelectedRow.Cells(1).Text & "<BR>") Response.Write(GridView1.SelectedRow.Cells(2).Text) End Sub

25. Deleting data • Using GridView object to select a row Dim SQLConn As New OleDbConnection SQLConn.ConnectionString = ConfigurationManager.ConnectionStrings("ProductsConString").ConnectionString SQLConn.Open() Dim SQLComm As New OleDbCommand SQLComm.CommandText = "Delete from product where ProductID=" & GridView1.SelectedRow.Cells(2).Text SQLComm.Connection = SQLConn Response.Write(SQLComm.CommandText) SQLComm.ExecuteNonQuery() SQLConn.Close()