1 / 20

CIS 451: Using ASP.NET Objects with SQL

CIS 451: Using ASP.NET Objects with SQL. Dr. Ralph D. Westfall February, 2009. ASP.NET Database Capabilities. ASP.NET objects offer multiple ways to access data in databases

lark
Download Presentation

CIS 451: Using ASP.NET Objects with SQL

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. CIS 451: Using ASP.NET Objects with SQL Dr. Ralph D. Westfall February, 2009

  2. ASP.NET Database Capabilities • ASP.NET objects offer multiple ways to access data in databases • has a number of objects, with numerous properties, that can be used to open and get data from different types of data sources • ASP.NET uses SQL in different ways with some of these objects

  3. ASP.NET Database Objects • can use the following objects • don't always have to create all of them • some are created when create others • Connection: to database • Command: SQL code or other command • DataAdapter: bridge between the database and the application • DataSet: holds data • DataView: more flexible than DataSet

  4. Set Up ASP.NET for a Database • need the following lines at the top of an .aspx file to make database capabilities accessible • or use Imports statement in aspx.vb file <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.Oledb"%> <%@ Import Namespace="System.Data.SqlClient"%> 'Microsoft Access and/or SQL Server 'Sub Page_Load … … … … … … … … … End Sub

  5. Connection Object • creating in code (prodtestbuild.aspx) • semicolons in the "connection string" separate parameters (driver; path; etc.) Dim strCon as String strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" strCon += "Data Source=" & _ "\example.mdb" assumes database is in project's \bin subdirectory

  6. Connection Object - 2 • opening Dim objCon = New _ OledbConnection(strCon) 'previous pg. objCon.Open() • closing objCon.Close() 'to free memory after database is used

  7. Command Object • has following properties to use with it • Connection – to database or etc. • CommandType can be • Text (default): SQL string, procedure name … • TableDirect: name of a table • StoredProcedure: name inside a database • CommandText • SQL string, SQL stored procedure name, etc.

  8. Creating a Command Object 'for a Microsoft Access database Dim strSQL as String Dim objCommand as OleDbCommand Dim strCity as String objCommand = New OleDbCommand(strSQL, _ objCon) 'slide 6 strSQL = "select * from Customer " 'space 'for a SQLServer database, would use SqlCommand( … instead of OleDbCommand( …

  9. Variables in SQL for Command • can limit output by using variables as conditions strCity = "Colusa" " where City <> '" & strCity & "';" 'note space before where 'also single quotes inside quotes 'single quotes identify string data 'in SQL

  10. DataReader Object • high speed, read only, forward only connection between database and application Dim objDataRead as OleDbDataReader objCon.Open() objDataRead = objCommand.ExecuteReader()

  11. Getting Data from DataReader • reads forward one row at a time • each read also returns a Boolean value • row fields are identified by field names Dim strCity as String 'objDataRead below is from slide 10 Do While objDataRead.Read() ' = True strCity = ObjDataRead("City") Response.Write(strCity & "<br/>") Loop 'until objDataRead.Read() = False

  12. DataAdapter Object • transfers data from database to an application • into a DataSet object that holds the data in the application Dim objDA as OleDbDataAdapter 'strSQL = "select * from Product " 'if not above, Connection object code here objDA = New OleDbDataAdapter(strSQL, objCon)

  13. DataSet Object • holds data retrieved from database • disconnected: changes do not go directly back into database • inflexible e.g., can't sort contents • can be bound to a control e.g., DataGrid Dim objDS as DataSet objDS = New DataSet()

  14. DataSet Object - 2 objDA.SelectCommand = new _ OleDbCommand(strSQL, objCon) objDA.Fill(objDS, "Product")

  15. DataTable & DataRow Objects Dim intProdCount as Integer Dim objTable as DataTable Dim objRow as DataRow objTable = objDS.Tables("Product") objRow = objTable.Rows(0) 'or variable intProdCount = objRow("InStock") 'add .ToString on end of line if errors

  16. CommandBuilder Object • sets up SQL commands so that they run on Microsoft Access or on other sources • avoids problems when trying to use SQL commands directly against specific data sources Dim objBuild as OleDbCommandBuilder objBuild = New OleDbCommandBuilder(objDA)

  17. CommandBuilder Object - 2 • updating (select, insert, delete similar) objDA.UpdateCommand = _ objBuild.GetUpdateCommand() intProdSold = 1 intProdCount = intProdCount - intProdSold objRow("Instock") = intProdCount objDA.Update(objDS, "Product") 'code

  18. DataView Object • can be bound to a control in the output Dim objDV as DataView objDV = New _ DataView(objDS.Tables("Product"))

  19. Binding to Database Data • can hook GridView data to a DataView to show output in the browser • GridView replaced DatGrid in VS.NET 2005 gvProd.DataSource = objDV 'in a Sub gvProd.DataBind() <body> <asp:gridview id="gvProd" runat="server" /> </body> <!– in HTML code-->

  20. Exercise: Use GridView • create some code in Visual Studio and use a DataGrid to output data from a database table onto a web page

More Related