Difference between revisions of "DD Class9"

Revision as of 02:41, 9 November 2009

DataReader

• DataReader http://iam.colum.edu/dd/classsource/ado/dataReader.aspx source

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

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

http://iam.colum.edu/dd/classsource/data/CheeseDataBindingFull.aspx source

Stored Procedures

http://iam.colum.edu/dd/classsource/data/sproc/sprocADO.aspx