CRUD and Reusable Code
Create a reusable datalayer that can handle all CRUD actions and centralize data access.
SQL injection attacks
Data Access Layer DAL
Data Access Layer save us from repeating code and recreating objects that we use often http://iam.colum.edu/dd/gbrowser.php?file=/App_code/CheeseSqlConnection.cs
An example of a DataReader using the DAL. http://iam.colum.edu/dd/classsource/ado/datareaderDAL.aspx -source
This exmaples uses the DataReader and It also uses the ExecuteNonQuery from the new class.
without DAL reminder also show client events created on the fly
Simple ADO using DAL http://iam.colum.edu/dd/classsource/ado/ADODal.aspx -source
Command object can
Connection and Commands
NonQuery vs Query
There are two basic type of SQL Commands. Ones that return Data and one sthat don't.
INSERT, UPDATE and DELETE are NONQueries and don't return data from the database. They do return an int the represents the number of rows that were added/modified in the Database;
SELECT will always return results and is known as a QUERY.
Command object demo
It's much better to user SQL pararmeters than raw SQL
<sql> SqlCommand dc = new SqlCommand("Select RegionName, RegionID from Region WHERE RegionID = @RegionID");
Microsoft Enterprise Framework
Microsoft provides an open reference library that includes a very extensible DAL
Data Access Application Block http://msdn.microsoft.com/en-us/library/ff664408%28v=PandP.50%29.aspx
Create Cheese browse as discussed above using SQLDataSource
Using ADO create a page that INSERTS, UPDATES, DELETES rows in a single table.
3. SQL Quiz next week