DD Class7
Contents
CRUD and Reusable Code
Create a reusable datalayer that can handle all CRUD actions and centralize data access.
SQL injection attacks
- http://pentestmonkey.net/blog/mssql-sql-injection-cheat-sheet/
- http://www.codeproject.com/KB/web-security/SqlInjection.aspx#prevent
Data Access Layer DAL
DataReader 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.
Delete DAL http://iam.colum.edu/dd/classsource/ado/directsqlDELETE_Dal.aspx -source
without DAL reminder also show client events created on the fly
http://iam.colum.edu/dd/classsource/ado/ADOTest1ClientEvents.aspx -source
Simple ADO using DAL http://iam.colum.edu/dd/classsource/ado/ADODal.aspx -source
ADO Commands
Command object can
- ExecuteReader
- ExecuteNonQuery
- ExecuteScalar
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
http://iam.colum.edu/datadesign/classsource/ado/AdoCommand.aspx
<sql> SqlCommand dc = new SqlCommand("Select RegionName, RegionID from Region WHERE RegionID = @RegionID");
dc.Parameters.Add("RegionID", 3);
</sql>
http://iam.colum.edu/datadesign/gbrowser.php?file=/classsource/ado/AdoCommand.aspx.cs
Microsoft Enterprise Framework
Microsoft provides an open reference library that includes a very extensible DAL
http://msdn.microsoft.com/en-us/library/ff648951.aspx
Data Access Application Block http://msdn.microsoft.com/en-us/library/ff664408%28v=PandP.50%29.aspx
HomeWork
1.
Create Cheese browse as discussed above using SQLDataSource
2.
Using ADO create a page that INSERTS, UPDATES, DELETES rows in a single table.
3. SQL Quiz next week