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

Cheese Editor

GridView/DetailsView

http://iam.colum.edu/dd/classsource/ado/GridViewDetailsView.aspx

Editing Multiple tables requires more work

http://brookfield.rice.iit.edu/jmeyers/ITM564/classsource/binding/CheeseDataBindingFull.aspx

Nesting Contols

http://brookfield.rice.iit.edu/jmeyers/ITM564/classsource/binding/CheeseDataBindingFull1.aspx

LINQ

c# 3.0 got some new features and key words

var

is a implicitly typed local variable. it is strong typed you don't need to declare the type when you declare the variable.

Anonymous Types'

allows creation of structural types without declaring an name first http://msdn.microsoft.com/en-us/library/bb397696.aspx

Lambda Expressions =>

similar to anonymous methods. don't need to be declared first and don't need to specify return type http://msdn.microsoft.com/en-us/library/bb397687.aspx

new keywords http://msdn.microsoft.com/en-us/library/bb310804.aspx

http://iam.colum.edu/dd/classsource/LINQBlog/LINQTest.aspx

http://msdn.microsoft.com/en-us/library/bb397933.aspx

http://iam.colum.edu/DD/classsource/Data/LINQ/LINQBinding.aspx

http://iam.colum.edu/DD/classsource/Data/LINQ/CheeseLINQ2.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