CIS 407A Web Application Development

1. CIS 407AWeb Application Development Assistant Professor Kyvernitis Module 3: ADO .NET (viewing data from DB)

2. Outline • Web Application Architecture: How do we create a stateful database applications on top of stateless HTTP? • How Visual Studio makes it easy for programmers – reduces the complexity introduced by html and http. Programmers can pretend they are writing a client server application. • Review DB terminology, review SQL select statements • ADO .NET Data Providers, ADO classes

3. Outline (Continued) • Read data from a database, display in label of webform. • Use Parameterized SQL select statement (prevent SQL injection attack). • Use Like in SQL select statement (gives nice functionality). • Move data read code to a Class. • Use String Builder instead of string – more efficient for appending. • Build a html table to display data (not just text with newlines). • Format date and currency data for display. • Create reusable methods to prevent null value exceptions

4. Business Applications: • Read from a database • Summarize and present information extracted from a database • Write (inserts, updates) to a database. • Access restricted based on secure logon and user role.

5. What is challenging about trying to put business applications on the web?

6. Web Architecture • HTTP is the protocol used by web browsers and web servers. It is a “stateless” (“connectionless”) protocol in which: • the client (browser) asks for a page, • that request is directed to the right server (by domain name), and • that server gives the requested (HTML) page back to the client. • HTML is pretty limited (layout, pictures, links, javascript that can give a message box & move things around on the page, user input can be posted back to a web server). • The client PC/browser cannot communicate directly with any database. Why???

7. Why Client PCs Don’t Talk to Databases • HTTP (the protocol that governs the communication between client PC and web server) is connectionless- it gets a request from anyone, responds to that request and that’s it ! • However, methods have been devised that enable web servers to keep session information about it’s users – remember login information per user, what they’ve done, and what they are trying to do. • HTML (the language of web pages) has no commands for accessing a database. • Those commands don’t exist because the purpose of HTML was to display information and links, not run database applications. • No one has added this functionality to HTML because companies don’t want their database server to be visible and accessible to all the PCs (and hackers) on the internet

8. So, how did they put business applications on the internet??

9. Web Application System Architecture

10. Web Application System Architecture • The rightmost server is the database server (e.g., oracle or sql server database stored on it, dbms installed on it). This is the only place where database queries (select statements) and updates are run. The sql that is run may be saved in stored procedures (in the database) or it may be dynamically created by the web application (and presented to the dbms to be run). • The server in the lower left represents 1 or more application servers that typically all access the same database server. Your application logic (Business logic and presentation logic) runs here. We always try to separate business logic (what it is, how we can manipulate it) from presentation logic (how we want it to look). • The database server and the application servers are located “inside the firewall” of a company.

11. Web Application System Architecture • Between the company’s application servers and the client’s PCs running browsers, there is the internet. • The application server can only hand the client PCs simple HTML possibly with some javascript in it.

12. .NET converts our webforms into plain HTML for us ! • So, our middle tier code (business logic): • accesses the database “in between” the generation of html pages (remember that a button click event causes the page to be regenerated, just like a new page request causes a page to be generated) • The database information can be incorporated into the generated page, e.g., a list of products pulled from the database, in response to user entered data. • In addition to this, .NET: • stores the state of the client (what they entered previously) in server side session variables.

13. Web Applications • If something changes on the client’s page (e.g., after a user clicked on button), this change was either caused by: • javascript executing (e.g., an alert messagebox or javascript is writing to the document and changing the way the document looks). • or it is a new page (that looks a lot like the old page).  This is what is happening with the basic .NET code we are writing…

14. Web Controls • Examples: buttons, textboxes, radio buttons. • In design mode (to programmers) they look/act like windows application forms – except you avoid using events other than button click & perhaps dropdown list selections. • Also called ASP .NET server controls because they actually reside on the server (even tho they look like the reside in the client’s browser). And they only run on the server “in between” pages.

15. Database Terms (review) • Database (most of today’s databases are relational databases): Microsoft SQL Server, Oracle, Sybase, DB2, Informix, MySQL, Microsoft Access. • DBMS – software that provides the only interface to read from or write to the database. This SW can enforce integrity rules, database constraints. • SQL (Structured Query Language)- the language we use (either thru SQL GUI interface or via programmatic control) to communicate with the DBMS – telling it what we want to read or write.

16. More Database Terms (review) • Table – data that represents a set of objects, such as a group of employees, payments, departments. • Row = Record, e.g. a specific employee or payment or department (stored in a table). • Column = Field, e.g., employee’s name. Columns define a table’s layout. • Primary key – a field or fields that uniquely identify a row in a table, e.g., SS number, Department Code. • Foreign key – a field or fields that show a relationship from one record to another. For example, Employee.Dept_Code is a foreign key in the Employee table. It shows the Department where a particular Employee works.

17. Basic SQL Select Statements • Select * from Titles; • Select Title, ISBN from Titles; • Select * from Titles where ISBN = ‘12345’; • Select * from Titles where Title like ‘Cat%’ order by Title ASC; • Select * from Titles, AuthorISBN, Author where Title.ISBN = AuthorISBN.ISBN and AuthorISBN.authorID = Authors.AuthorID and Author.lastName = ‘Poe’; (this called “inner join”)

18. ADO .NET ADO.NET stands for ActiveX Data Objects. It consists of two primary parts: • Data provider -- classes that provide access to a data source. Each type of database (e.g., sql server, oracle) has its own set of providers, but they all provide similar functionality including: • Connection object: connection to the database. • Command object: such as select, insert, update, delete. • DataReader object: holds result sets from select statements. • DataAdapter object: A bridge used to transfer data between a data source and a DataSet object (see below). • DataSet – classes that represent a simple in-memory relational database (e.g., tables, relationships, constraints).

19. ADO .NET Data Providers

20. ADO .NET Data Providers • A data provider is a set of ADO .NET classes that let you access (read from, write to) a specific database. ADO .NET version 1.1 comes with these providers: • ODBC (Open DataBase Connectivity): accesses any data source using db specific ODBC driver. • OLE DB (Object Linking & Embedding-DataBase): accesses any data source w/OLE DB driver, includes SQL Server earlier than v 7.0. This access method is a little more efficient than ODBC. • SQL Server: accesses SQL Svr DBs v 7.0 or later. This is the most efficient for SQL server databases. • Oracle: accesses Oracle DB (v 8i or later). This is the most efficient for oracle databases.

21. Selecting a Data Provider • 1st choice is a data provider that is optimized for your database. So, if SQL server 7.0 or later, use SQL server. If Oracle 8i or later, use Oracle. • Else, you’ll have to use OLE DB or ODBC. • For simplicity, we’ll use OLE DB with an MSAccess database in this class.

22. ADO .NET Ole Db Data Provider Classes • OleDbConnection – manages a connection to a datasource (a particular database). • OleDbCommand – a specific SQL command to be executed. • OleDbDataReader – provides fast, read-only, forward-only access to query. • OleDbDataAdapter – connects to a datasource, runs an SQL command, and populates a DataSet (which can be modified then updated back to DB).

23. Add Using Statement to WebForm • Add this at the top of WebForm1.aspx.cs using System.Data.OleDb; • The “using” statement is like the java import statement. It enables you to have code like this: OleDbDataReader reader; • Instead of having to use code like this: System.Data.OleDb.OleDbDataReader reader; • Type in the code from the next slide into the button click event – or you can copy this code from the zipped project you just downloaded/extracted.

24. Code to Read From Database(without using Classes) private void btnGetData_Click(object sender, System.EventArgs e) { this.lblDisplayData.Text = ""; // clear value if there is any string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\a\\payroll.mdb"; OleDbConnection con = new OleDbConnection(strCon); OleDbDataReader reader; try { string sql = "SELECT FirstName, LastName FROM emp"; OleDbCommand cmd = new OleDbCommand(sql,con); con.Open(); reader = cmd.ExecuteReader(); while (reader.Read()) { this.lblDisplayData.Text += reader.GetValue(0).ToString() + " "; this.lblDisplayData.Text += reader.GetValue(1).ToString() + "<br>"; } reader.Close(); con.Close(); } // continued on next slide

25. Code to Read From Database(without using Classes -- continued) // .. Continued from previous slide catch (Exception ex) { this.lblDisplayData.Text=ex.Message; con.Close(); } }

26. Using ADO

27. The GetValue input parameter is based on the order of cols in the SQL select statement (not the order of cols as designed in the DB table). // … string sql = "SELECT FirstName, LastName FROM emp"; //… while (reader.Read()) { this.lblDisplayData.Text += reader.GetValue(0).ToString() + " "; this.lblDisplayData.Text += reader.GetValue(1).ToString() + “<br>"; } • GetValue(0) gets the first column as specified in the SQL statement. • GetValue(1) gets the next column as specified in the SQL statement. • GetValue(2) would get the next column after that, and so on. • The GetValue parameter has nothing to do with the order of the columns when looking at the table in design mode in MSAccess. • Use GetValue instead of GetString or GetInt so we don’t abort on say a null value in the database.

28. How to Deal with Runtime errors caused by bad SQL • If there is a syntax error in your SQL statement, your program will abort at runtime. Unfortunately, you will not get a compiler error. • All database access should be put in a try block. Of course, the try block should close the connection when the connection is no longer needed. The catch block should also close the connection – or else you’ll get a “file in use” error the next time you try to run after you’ve encountered an error. • At any rate, if you ever run into a message like “file already in use” – you may have to close Visual Studio, reopen and retry. If this doesn’t fix it, you can try rebooting. • Make sure that you ALWAYS close every connection you open (for every code path, including error code paths).

29. Handling Errors • There are two common errors: • The database is not available. • There is a error in the SQL (maybe syntax or maybe a table or field misspelled). • We can easily simulate both errors: • Misspell database name (acts like db not available). • Misspell a SQL keyword (SQL syntax error). • Make sure your program is resilient to both potential problems. You want your code to close any open connection to the database (even if there IS an error) or you’ll have trouble running/testing your program next time. Do this by closing the connection in the try code as well as in the catch code. Otherwise, you’ll have to restart VS and/or reboot everytime you test and this is a frustrating way to code.

30. Invasive SQL Injection Attacks • Suppose you have a webform where the user enters in a customer name into a textbox and then presses a button to see that customer’s record. • If the program incorporates the user’s data directly into the sql select statement (without using a parameterized sql statement), we open ourselves up to a SQL injection attack. • Here is an example of a sql statement that is open to SQL injection attack: string sql = “select * from Cust where name = ‘” + txtName.Text + “’”; • So, if the user enters “Smith” into the textbox, then the expression above evaluates to this: select * from Cust where name = ‘Smith’

31. Invasive SQL Injection Attacks … string sql = “select * from Cust where name = ‘” + txtName.Text + “’”; • A malicious (technically savvy) user could enter this into the textbox: Smith’ or ‘1’=‘1 • Then the expression evaluates to: select * from Cust where name = ‘Smith’ or ‘1’=‘1’ • Then the user would see all of the (possibly sensitive) data in the customer file.

32. Destructive Sql Injection Attacks • Or (assuming same web form) the user enters this into the textbox: Smith’; delete * from Cust - - • The expression then evaluates to this (much more malicious than the previous example): select * from Cust where name = ‘Smith’; delete * from Cust-- ’ ;ends the first sql statement and lets you start another statement. - -comments out the rest of the statement

33. Parameterized SQL Commands • Parameterized SQL commands prevent SQL injection attacks. • Parameterized SQL commands automatically create stored procedures within the database (assuming the db provides stored procedures – MSAccess does not). • When the DBMS (Database Mgt System, e.g. oracle or Sql) sees a new parameterized SQL command, it compiles it, uses it, and saves it for the next time. • Stored also procedures run much more quickly than plain text sql statements because they are pre-compiled (by the DBMS).

34. Example of Parameterized SQL Command(not open to Sql Injection Attack) string sql = "SELECT FirstName, LastName FROM emp " + "where LastName = @lname"; OleDbCommand cmd = new OleDbCommand(sql,con); cmd.Parameters.Add("@lname","Willis"); con.Open(); System.Data.OleDb.OleDbDataReader reader; reader = cmd.ExecuteReader(); … // In this example, you’ll see all the first and last name of all emp records where the last name = ‘Willis’

35. Tip on Using Parameterized SQL • Always replace parameters in the same order they occur in the SQL statement. For example, call the Add method for @lname THEN call the Add method for @fname. string sql = "SELECT FirstName, LastName FROM emp " + "where LastName = @lname or FirstName = @fname"; OleDbCommand cmd = new OleDbCommand(sql,con); cmd.Parameters.Add("@lname","Willis"); cmd.Parameters.Add("@lname",“Bruce");

36. Using LIKE in SQL Select Statement string sql = "SELECT FirstName, LastName FROM emp " + "where LastName like @lname"; OleDbCommand cmd = new OleDbCommand(sql,con); cmd.Parameters.Add("@lname","Will%"); con.Open(); System.Data.OleDb.OleDbDataReader reader; reader = cmd.ExecuteReader(); … % is a wild card character This gets all the records where last name Starts With ‘Will’

37. Create Database ConnectionWrapper Class • You can design flexible data access software by “putting a wrapper around” the data provider classes (e.g., Connection, Command, Reader). • For example, when you first start designing a web application, you use a simple Access database (cheaper, easier to obtain) and plan to switch to a more robust database later. • So, rather that directly using OleDbConnection, etc, you can create your own Connection class that is just a “wrapper” around OleDbConnection. When you upgrade to different DB, just modify that Connection class (e.g., to be a SQL server Connection class).

38. A Closer Look at our Connection Wrapper Class DbConn – its data members using System.Data.OleDb; public class DbConn { // the actual connection private OleDbConnection conn; // error message if there was a problem private string errMsg;

39. DbConn class (Constructor) // Constructor creates then opens a db connection. public DbConn() { try { string myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data "+ "Source=C:\\a\\payroll.mdb"; conn = new OleDbConnection(myConnectionString); conn.Open(); } catch (Exception ex) { this.errMsg = "Could not access database. Error: " + ex.Message + "\n"; } }

40. Create DbConn class (other methods) public OleDbConnection getConn(){ return conn; } public void closeConn(){ conn.Close(); }

41. Create DbConn class (other methods) public bool isConnErr(){ string state = conn.State.ToString(); if (state.Equals("Open")) return false; else { this.errMsg=“DB connection is not open.”; return true; } } public string getErrMsg(){ return this.errMsg; }

42. DB Connection Management • Waiting to get a db connection can be slow (one computer requests of another, over a network). • I timed it once (oracle) at about 2-3 seconds per connection. • If one page request (either new page or button click on same page) asks for 5 database connections, this can add 10-15 seconds onto the time that a user has to wait for the page.

43. DB Connection Management:Just get one per page – pass it around. • One simple but effective way of managing database connections is to get one in the page (e.g., button click event, or page load event) and pass that single one around to whoever needs it. Then, make sure to close it (every code path). • Don’t get more than one database connection per page since this is an unnecessary waste. If a particular page is heavily hit, you’ll require twice or three times as many database connections. • The general limit of database connections (that I’ve seen in industry) is about 90 or so. It depends on various factors.

44. DB Connection Management:Close every connection that’s opened • EVERY time a connection is opened it MUST be closed. • If there’s even one code path where a db connection is opened but not closed, this creates a db connection leak. If this code path is heavily used, then the database will run out of connections, the application will fail -- until the database server is rebooted. User work will be lost. • If this code path is not heavily used, then its possible that the database server will not need to be rebooted (each connection will “time out” if not used over a certain time period, as determined by the database administrator).

45. DB Connection Management –DB Connection Pooling • To combat this potential area of slowness, database servers (e.g., oracle software) and application servers (e.g., .NET software) employ connection pooling. That is to say, they request connections ahead of time and have a limited number of them already available. • In order to take advantage of this connection pooling, however, you must specify the connection exactly the same way each time to try to access a particular database. This can best be accomplished by funneling all db connection requests through a single class – your database wrapper class.

46. Closer look at EmpRead class’s data members & constructor private DbConn dbc; // db connection private string errMsg; // error msg private bool err; // is there an error? public EmpRead(DbConn dbc) { this.dbc=dbc; }

47. Put all SQL access into Same Class • In our architecture, we create a SQL class that holds all the access to a particular table. • That way, if changes are made to the database, we know where we’ll have to change the code (in the SQL classes). • In our Sample code, we put the SQL into a class called StockRead.

48. Examine EmpRead.read method (1/3) public string read(string lastNameLike) { this.err=false; this.errMsg=""; string tmp = ""; OleDbConnection con = dbc.getConn(); if (dbc.isConnErr()) { this.err=true; this.errMsg=dbc.getErrMsg(); return "*** Error Connecting to Database ***"; } // continued on next slide

49. Examine EmpRead.read method (2/3) // continued from … public string read(string lastNameLike) { try { string sql = "SELECT FirstName, LastName FROM emp "+ "where LastName LIKE @nameParm"; OleDbCommand cmd = new OleDbCommand(sql,con); cmd.Parameters.Add("@nameParm",lastNameLike+"%"); System.Data.OleDb.OleDbDataReader reader; reader = cmd.ExecuteReader(); while (reader.Read()) { tmp += reader.GetValue(0).ToString() + " "; tmp += reader.GetValue(1).ToString() + "<br>"; } reader.Close(); }

50. Examine EmpRead.read method (3/3) // continued from … public string read(string lastNameLike) { catch (Exception e){ this.err=true; this.errMsg=e.Message; } return tmp; }