DD Class9
Contents
DataReader
Data Reader is a fast forward data reader that can save memory and resources on large datasets
1 Connection Object <csharp> //Connection string comes from web config SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString); </csharp>
web.config connection strings
<xml> <configuration>
<connectionStrings>
<add name="cheeseConnectionString" connectionString="Data Source=iamdb;Initial Catalog=cheese;User ID=student;Password=Student" providerName="System.Data.SqlClient"/> </connectionStrings> </xml>
2 Command Object <csharp>string strSQL = "SELECT CheeseName,CheeseDescription FROM cheese";
string strResultsHolder = ""; SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
</csharp>
3 Reader and Reading <csharp> SqlDataReader objDataReader = null; //Reader to read through the result
try { objConnection.Open(); //Open Connection objDataReader = objCommand.ExecuteReader();
while (objDataReader.Read() == true) { strResultsHolder += String.Format("{0}:{1}
", objDataReader["CheeseName"], objDataReader["CheeseDescription"]); } } catch (Exception e) { lblErr.Text = "Connection failed to open successfully.
"; lblErr.Text += e.ToString(); } finally { objDataReader.Close(); //Close Reader objConnection.Close(); //Close Connection
} //Write results divListCheese.InnerHtml = strResultsHolder;
}
</csharp>
Review First Normal Form
Let build the example for the homework.
Build Interface to GameDB
Games
GameTitle | GameGenre | DeveloperName | Platform(s) | Year | DeveloperWebsite | GameWebsite |
---|---|---|---|---|---|---|
Quake1 | FPS | id | Dos | 1996 | http://www.idsoftware.com/ | http://www.idsoftware.com/games/quake/quake/ |
Diablo | RPG | Blizzard | Windows 95 | 1996 | http://www.blizzard.com/ | http://www.blizzard.com/diablo/ |
SimCity | Sim | Interplay | Dos | 1993 | http://www.interplay.com/ | http://www.maxis.com/ |
Tables Games, Developers, Websites, Platforms?
Build Views
Nesting Data Bound Controls
nesting controls
Datasource Controls can probably handle up to 70% of your data binding needs but they do have drawbacks. Consider the cheese database which uses an SQL view to join Cheese with Region, Consistency, and MilkType. Of course SQL view don't support updating and can lead to some bad data binding. we can fix a few of these problem with stored procedures.
Simple filter Demo in class
GridView Details View Master Child Relationship
http://iam.colum.edu/dd/classsource/data/CheeseDataBindingFull.aspx
source
Stored Procedures
http://iam.colum.edu/dd/classsource/data/sproc/sprocADO.aspx
Cheese browser assignment
Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..
Here's and example of a simple cheese shop browser app.
http://iam.colum.edu/dd/classsource/class8/CheeseShop/CheeseShop1.aspx
Homework
Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..
Examples
http://iam.colum.edu/dd/classsource/Data/GridAndDetails.aspx
http://iam.colum.edu/dd/classsource/Data/GridDataBound.aspx
http://iam.colum.edu/dd/classsource/Data/GridDetails.aspx
http://iam.colum.edu/dd/classsource/Data/GridEdit.aspx
http://iam.colum.edu/dd/classsource/Data/GridSelect.aspx
http://iam.colum.edu/dd/classsource/Data/GridSelectTheme.aspx