ServerSide Scripting Examples Acme new user registration Acme

Server-Side Scripting • Examples: – Acme new user registration – Acme products table • Technologies: –. Net Framework – VB. Net – ASP. Net – ADO. Net (http: //www. w 3 schools. com/ngws) (http: //msdn. microsoft. com/vbasic/) (http: //www. w 3 schools. com/aspnet) (http: //www. w 3 schools. com/ado) © Keith Vander Linden, 2003

Database Programming • The world of information technology revolves around databases. • Most database work is done through database application programs. • Approaches to database programming: © Keith Vander Linden, 2003

Impedance Mismatch Relational databases General-purpose programming languages “pi” 3. 14159 • fields • records • tables • standard data types • classes The problem is to bind: • relational fields, records and tables • native 4 GL variables, arrays and classes © Keith Vander Linden, 2003

ADO. Net • Active. X Data Objects • Microsoft’s generic database programming API. • The typical ADO. Net interaction sequence: 1. Establish a connection to a database 2. Interact with the database 3. Close the connection © Keith Vander Linden, 2003

ADO. Net Example 1. Establish a connection to the Acme database on pella. calvin. edu. 2. Create objects to bridge the gap to SQL Server: – Data Adapter – SQL Command – Data Set 3. Interact with the database. 4. Close the connection. © Keith Vander Linden, 2003

ADO. Net Connections Me. Sql. Connection 1 = New System. Data. Sql. Client. Sql. Connection Me. Sql. Connection 1. Connection. String = "workstation id=PELLA; packet " & _ "size=4096; data source=PELLA; persist security info=True; " & _ "initial catalog=acme; user id=account. ID; password=password" • All interactions between the VB program and the database will be done through this object. © Keith Vander Linden, 2003

ADO. Net Commands Me. Sql. Select. Command 1 = New System. Data. Sql. Client. Sql. Command Me. Sql. Data. Adapter 1. Select. Command = Me. Sql. Select. Command 1. Connection = Me. Sql. Connection 1 Me. Sql. Select. Command 1. Command. Text = & _ "SELECT name, unit. Price, color, size, image. File. Name “ & _ “FROM Products” • Commands are VB objects that represent standard SQL command as strings. • These strings are passed to the database to be executed. © Keith Vander Linden, 2003

ADO. Net Data. Adaptors Me. Sql. Data. Adapter 1 = New System. Data. Sql. Client. Sql. Data. Adapter Me. Sql. Data. Adapter 1. Fill(Data. Set 1, “some. Resultname") • Data Adaptors mediate the communication between the DBMS and the VB program. © Keith Vander Linden, 2003

ADO. Net Data. Sets Dim Data. Set 1 As New Data. Set Dim a. Table As Data. Table Dim a. Row As Data. Row a. Table = Data. Set 1. Tables(0) For Each a. Row In a. Table. Rows …access row values with: a. Row("image. File. Name") … Next • Data. Sets make the data returned by the query available to the VB program. © Keith Vander Linden, 2003

ADO. Net Code Public Class Products Inherits System. Web. UI. Page ‘Code created by Visual Studio <System. Diagnostics. Debugger. Step. Through()> Private Sub Initialize. Component() Me. Sql. Data. Adapter 1 = New System. Data. Sql. Client. Sql. Data. Adapter Me. Sql. Select. Command 1 = New System. Data. Sql. Client. Sql. Command Me. Sql. Connection 1 = New System. Data. Sql. Client. Sql. Connection Me. Sql. Data. Adapter 1. Select. Command = Me. Sql. Select. Command 1. Connection = Me. Sql. Connection 1. Connection. String = "workstation id=PELLA; packet " & _ "size=4096; data source=PELLA; persist security info=True; " & _ "initial catalog=acme; user id=account. ID; password=password" End Sub …other stuff (including the page_init subroutine)… End Class © Keith Vander Linden, 2003

ADO. Net Code (2) Private Sub Page_Load(By. Val sender As System. Object, By. Val e As System. Event. Args)_ Handles My. Base. Load …more stuff here… Dim Data. Set 1 As New Data. Set Dim a. Table As Data. Table Dim a. Row As Data. Row Me. Sql. Select. Command 1. Command. Text = "SELECT name, unit. Price, color, size, ” & _ “image. File. Name FROM Products" Me. Sql. Data. Adapter 1. Fill(Data. Set 1, "ds. Products") a. Table = Data. Set 1. Tables(0) Label 3. Text="<table border=1 cellpadding=5 width=100%><tr><th>Product</th>”& _ “<th>Name</th><th>Size</th><th>Color</th><th>Price</th></tr>" For Each a. Row In a. Table. Rows Label 3. Text += "<tr>" Label 3. Text += "<td align=center><img src=""Images/" + _ a. Row("image. File. Name") + """ height=75 px></td>" Label 3. Text += "<td align=center>" + table. Value(a. Row("name")) + "</td>" Label 3. Text += "<td align=center>" + table. Value(a. Row("size")) + "</td>" Label 3. Text += "<td align=center>" + table. Value(a. Row("color")) + "</td>" Label 3. Text += "<td align=right>" + & _ table. Value(Format(a. Row("unit. Price"), "$###, ###. ##")) + "</td>" Label 3. Text += "</tr>" Next …more stuff here… End Sub © Keith Vander Linden, 2003

ADO. Net Code (3) Function table. Value(By. Val item As Object) If (item. To. String() = "") Then Return "  " Else Return item End If End Function © Keith Vander Linden, 2003