DD Class7

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