Tutorial showing how Web applications
Can display data from a database
Objectives
- Review DB terminology, review SQL select statements
- ADO .NET Data Providers, ADO classes
- Display (in label of a web form) data that was read from a database.
- 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
This tutorial comes with a sample working application that has all the code samples incorporated into it.
Database Terms (review)
- Database (most of today’s databases are relational databases): Microsoft SQL Server, Oracle, Sybase, DB2, Informix, MySQL, Microsoft Access.
- DBMS – a DataBase Management System is software that provides the only interface to read from or write to the database. This software enforces integrity rules and 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.
- 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 (points to records in a Department table). It shows the Department where a particular Employee works.
- 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”)
Code to Read from Database
(without Classes, i.e., all the code is on the webform)
using System.Data.OleDb; // must have this statement or else you must
// precede all OleDb objects with “System.Data.OleDb.”
// The following code assumes your web form has a button named btnGetData
// and that it has a label named lblDisplayData
// and that there is a database called “payroll.mdb”
// stored in a folder called “a” on the C drive
// and this database has a table named emp with fields FirstName and LastName.
private void btnGetData_Click(object sender, System.EventArgs e) {
this.lblDisplayData.Text = ""; // clear value in the label, 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(); // runtime error could occur here if database is not available.
reader = cmd.ExecuteReader();// runtime error could occur here if SQL is bad
while (reader.Read()) {
// The input to the GetValue method indicates the order of the field
// as specified in the SQL Select statement.
// Because FirstName was listed first in the select statement,
// the value of FirstName is extracted by GetValue(0) and LastName is
// is extracted with GetValue(1). If there were more fields listed, you could
// also extract data with GetValue(2), GetValue(3), etc.
// We use GetValue instead of GetString or GetInt so that we do not abort
// when there is a null value in the database.
this.lblDisplayData.Text += reader.GetValue(0).ToString() + " ";
this.lblDisplayData.Text += reader.GetValue(1).ToString() + "<br>";
// notice that we are inserting HTML code inside the string we build
} // while loop
reader.Close();
con.Close();
} // try block
catch (Exception ex) {
// It’s important to display the error message so you can tell what the problem was!
this.lblDisplayData.Text=ex.Message;
con.Close();
} // catch block
} // button click event
Handling Common Database Access Runtime Errors
As marked in the code above, there are two places where a runtime database access error could occur:
- when you try to connect to the database, and
- when you try to execute the SQL select statement.
That is why you must put this code within a try block. If a runtime error does occur within the try block, the catch block will be executed. In either case (runtime error or not), the connection must be closed. It is not so important to close the reader because the reader will get closed when you close the connection.
There is good news and bad news about catching a database access error. The good news is that (1) your code fails gracefully (does not abort) and (2) the connection is closed, so you can fix and test your program again without having to shut down Visual Studio or rebooting your PC. The bad news is that you now have the responsibility to display the error message, so you can figure out what you need to do to fix the problem.
If there is a problem connecting to the database, it usually is because the database is not named properly or it is not in the right spot. It can also be that the database is locked because your code just aborted and no catch code was executed to close the connection. If this is the case, this is when you may have to shut down and re-launch VS or you may even have to reboot your PC. It’s really best to use the catch code in the first place so that you avoid all this messiness.
If there is a problem with the SQL, you can try debugging your code, paying special attention to the value of the string variable that holds the sql command. You may have a syntax error (missing comma or misspelled SQL command) , or you may have misspelled a table or field name, or unexpected null data might have been encountered in the database. So, carefully check your SQL code for syntax and spelling errors and make sure to keep data in all fields of all records of your test table. Later, in this tutorial, we explain how to handle null values gracefully.
There are two common database accessruntime errors and your code should be resilient (fail gracefully) even if either of these two errors occur.
- Error in the SQL (maybe SQL syntax error, or maybe a table or field name is misspelled, or maybe there is a null value and your code is not testing for that).
- The database is not available.
While you are developing an application, certainly you will have an error in your SQL statements from time to time. If this is true, you will get a runtime error when this statement is executed:
reader = cmd.ExecuteReader();
To handle this type of runtime error gracefully, you need to make sure that the above line of code is inside a try block which closes the reader and then closes the connection. The try block needs to have an accompanying catch block that also closes the connection and displays the exact error on the form (so the programmer/you can know how to fix the problem). If you just write simple code (without try/catch blocks or where a try or catch block does not close the connection), your web form will load (assuming there are no syntax errors in any C# code) but it will abort when you click the button that executes the bad SQL statement. Worse than that, you will get a “file already in use” error the next time you try to run/test your application. Sometimes, to get rid of this “file in use” error, you have to close and rerun Visual Studio or you may even have to reboot your PC (very slow and very frustrating).
To simulate a SQL error (to test/ensure that your code will fail gracefully even if there is a SQL error), try misspelling a SQL keyword and testing. If your try/catch code ALWAYS closes the database connection, you should never get the frustrating error “file already in use” when you run the applicationthe next time after experiencing a SQL syntax error.
Even if your code is pristine and perfect and you roll it out into production, you can always run into a situation where the database is not available. To simulate this error (to test/ensure that your code fails gracefully when presented with this type of error), you can temporarily just misspell the database name or you can temporarily rename the name the database. Again, to achieve resiliency against “database not available”, make sure that both your try AND catch code always close the database connection.
Review of How to Deal with Data Access Runtime Errors
- If there is a syntax error in your SQL statement, your program will abort at runtime (e.g., the Web Form will load then abort only when the button is clicked). Unfortunately, you will not get a compiler error. (A web form will not even load if there is 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).
Selecting Records Where a Field Begins with a Particular Value
(Using LIKE in SQL Select Statement)
Sometimes you may want to display all records that have a field that starts with a particular value. The following code will return all the records where the LastName begins with “W”.
string sql = "SELECT FirstName, LastName FROM emp "
+ "where LastName like 'W%';
NOTE: % is a wild card character
If you want to allow the user to specify the starting characters, your code may look like this:
string sql = "SELECT FirstName, LastName FROM emp where LastName LIKE'"
+ this.lblDisplayData.Text + "'";
How to Prevent Invasive SQL Injection Attacks (and Avoid SQL Syntax Errors)
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 code that is open to SQL injection attack (I have used different colors to make the code easier to understand):
string sql = “select * from Cust where name = ‘” + txtName.Text + “’”;
So, when the average user enters something like Smithinto the textbox, then the SQL expression above evaluates to this:
select * from Cust where name = ‘Smith’
But a malicious computer savvy user could enter something that the programmer did not expect. If this user wanted to see all the data in a table (that’s supposed to be private), this user could enter this into the textbox: Smith’ or ‘1’ = ‘1
Then the statement
string sql = “select * from Cust where name = ‘” + txtName.Text + “’”;
evaluates to this
select * from Cust where name = ‘Smith’ or ‘1’ = ‘1’
and this, of course, would display ALL the data in the Cust table (and this could be private data). This is called an invasive SQL injection attack because the malicious user was able to see data that s/he shouldn’t have been able to see.
Destructive Sql Injection Attacks
Even worse than an Invasive SQL injection attack, is the Destructive SQL injection attack. The malicious user employs the same technique as before, but they enter a value like this into the textbox:
Smith’; delete * from Cust --
The ; in SQL is a way to concatenate one statement with another. The -- in SQL comments out the rest of the line. So, the statement
string sql = “select * from Cust where name = ‘” + txtName.Text + “’”;
evaluates to this
select * from Cust where name = ‘Smith’; delete * from Cust--’
So, this statement would have the effect of showing the smith data, but then deleting ALL the records from the Cust table and this could be very detrimental.
Parameterized SQL Commands
Parameterized SQL commands are good for several reasons:
- Parameterized SQL commandsrun faster than dynamically built SQL strings because the DBMS automatically creates a stored procedure for each unique SQL command that ever ran in the application. (This is only true for substantial DBMSs that support stored procedures, e.g., Oracle or SQL server. MSAccess does not support stored procedures.) Stored procedures run faster than dynamically generated SQL strings because stored procedures are compiled (by the DBMS) just the first time they are used and not afterwards. Whereas, the dynamically generated SQL strings are compiled (by the DBMS) every time it is run. This difference in performance is substantial and noticeable to users (in DBMSs that support stored procedures).
- Parameterized SQL commands are easier to program (less confusion with the single and double quotes)
- Parameterized SQL commands prevent SQL Injection attacks.
- Parameterized SQL commands make the application more portable. For example, as long as the programmer uses nothing but standard SQL (and not Oracle specific or SQL Server specific SQL statements), the application is more easily ported from one DBMS to another (new stored procedures will automatically be generated upon first use in the new DBMS).
Example of Parameterized SQL Command
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’
Tip on Using Parameterized SQL Statements
Always replace parameters in the same order they occur in the SQL statement. For example, call the Add method for @lname BEFORE calling the Add method for @fname because @lname is before @fname in the SQL statement below.
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");
Create Database ConnectionWrapper Class
You can design flexible data access software by creating “wrapper” classes. Instead of “re-inventing the wheel”, you just create your own code that does almost nothing except use the database connection class that is already provided (e.g., the OleDbConnection class). Then, if your database changes its location or type, you only need to modify code in one place (your database connection wrapper class). Whereas, if you had lots of places in your code where you handle database connections, you would probably forget to change at least one place and you’ll waste a lot of time testing your application and finding the error.
Here is a sample code for a Database Connection Wrapper Class (called“DbConn”).
using System.Data.OleDb;
public class DbConn {
// the actual connection
private OleDbConnection conn;
// error message if there was a problem
private string errMsg;
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";
}
}
Create DbConn class (other methods)
public OleDbConnection getConn(){
return conn;
}
public void closeConn(){
conn.Close();
}
Create DbConn class (other methods)
public bool isConnErr(){