Difference between revisions of "DD Class6"

esse quam videri
Jump to: navigation, search
(Blanked the page)
Line 1: Line 1:
 +
==Binding to SQL or XML==
  
 +
 +
http://iam.colum.edu/dd/classsource/class3/RadioBindDropDownStates.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/RadioBindDropDownStates.aspx source]
 +
 +
http://iam.colum.edu/dd/classsource/class3/DataBindDropDownStates2.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/DataBindDropDownStates2.aspx source]
 +
 +
Notice the Name and the Value in both of these examples are the same. ListControls support both Name and Value fields and these can have different values. Be when a single dimensional data source is bound to the control the name and value will be the same.
 +
In order to define these parameter we need to have a named field from an object.
 +
btw it just wouldn't be fair if I didn't show you how to do it with objects
 +
 +
http://iam.colum.edu/dd/classsource/class3/DataBindDropDownStatesObjective.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/DataBindDropDownStatesObjective.aspx source]
 +
 +
and finally databinding to sql server (really we are using and object here DataTable)
 +
 +
http://iam.colum.edu/dd/classsource/class3/DataBindDropDownStatesData.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/DataBindDropDownStatesData.aspx source]
 +
 +
 +
Same example with XML
 +
 +
http://iam.colum.edu/dd/classsource/class3/DataBindDropDownStatesCountryData.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/DataBindDropDownStatesCountryData.aspx source]
 +
 +
==SQL server==
 +
 +
Create Database
 +
 +
Create Table
 +
:demo in SQLStudio
 +
 +
Create Table Syntax
 +
<sql>
 +
CREATE TABLE table_name
 +
{
 +
coulumn_name datatype[(length)] [NULL | NOT NULL}
 +
}
 +
#Example
 +
CREATE TABLE temp_user (
 +
userID int PRIMARY KEY IDENTITY NOT NULL ,
 +
firstName varchar (250) NOT NULL ,
 +
lastName varchar (250) NOT NULL
 +
)
 +
#MSSQL Example
 +
CREATE TABLE [dbo].[user] (
 +
[userID] [int] IDENTITY (1, 1) NOT NULL ,
 +
[firstName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 +
[lastName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
 +
) ON [PRIMARY]
 +
GO
 +
</sql>
 +
 +
Alter Table
 +
<sql>
 +
ALTER TABLE temp_user
 +
add recCreationDate datetime NULL
 +
#You cannot add a column that is NOT NULL wihout specifing a default value
 +
GO
 +
</sql>
 +
 +
 +
Code to create the States Table from the above Example
 +
 +
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/states/states.sql
 +
 +
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/states/statesSimple.sql
 +
 +
<sql>
 +
CREATE TABLE states(
 +
ID int IDENTITY(1,1) NOT NULL,
 +
StateName varchar(50)  NOT NULL,
 +
StateAbr nchar(3) NOT NULL,
 +
    CONSTRAINT [PK_states] PRIMARY KEY CLUSTERED
 +
    (
 +
    ID ASC
 +
    )
 +
)
 +
</sql>
 +
 +
 +
More SQL Syntax we'll study this in the comming weeks
 +
[[SQL SelectInsertUpdateDeleteSyntax]]
 +
 +
Query Analyzer
 +
:SQL Query Analyzer is an interactive, graphical tool that enables a database
 +
administrator or developer to write queries, execute multiple queries simultaneously,
 +
view results, analyze the query plan, and receive assistance to improve the query performance. -MSSQL help
 +
 +
Sql code to insert all the states in the states table
 +
 +
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/states/InsertStates.sql
 +
 +
<sql>
 +
INSERT into states (StateName, StateAbr) values ('Non US Province' , '')
 +
INSERT into states (StateName, StateAbr) values ('Alabama' , 'AL')
 +
INSERT into states (StateName, StateAbr) values ('Alaska' , 'AK')
 +
INSERT into states (StateName, StateAbr) values ('Arizona' , 'AZ')
 +
INSERT into states (StateName, StateAbr) values ('Arkansas' , 'AR')
 +
INSERT into states (StateName, StateAbr) values ('Armed Forces Americas' , 'AA')
 +
...
 +
</sql>
 +
 +
 +
 +
==SQL Syntax==
 +
 +
Create Table
 +
 +
<sql>
 +
CREATE TABLE table_name
 +
{
 +
coulumn_name datatype[(length)] [NULL | NOT NULL}
 +
}
 +
#Example
 +
CREATE TABLE temp_user (
 +
userID int PRIMARY KEY IDENTITY NOT NULL ,
 +
firstName varchar (250) NOT NULL ,
 +
lastName varchar (250) NOT NULL
 +
)
 +
#MSSQL Example
 +
CREATE TABLE [dbo].[user] (
 +
[userID] [int] IDENTITY (1, 1) NOT NULL ,
 +
[firstName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 +
[lastName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
 +
) ON [PRIMARY]
 +
GO
 +
</sql>
 +
 +
<sql>
 +
Alter Table
 +
 +
ALTER TABLE temp_user
 +
add recCreationDate datetime NULL
 +
#You cannot add a column that is NOT NULL wihout specifing a default value
 +
GO
 +
</sql>
 +
 +
SQL Syntax
 +
 +
===Single Table Operations===
 +
If you are dealing with more than one table then all of your sql statement must be wrapped in a transaction. A transaction it group of statements that  will succeed or fail as a whole. All the statements must succeed if one fails then the entire transaction is rolled back.
 +
 +
SQL statements
 +
 +
*INSERT
 +
*UPDATE
 +
*SELECT
 +
*DELETE
 +
 +
'''INSERT'''
 +
<sql>
 +
      INSERT into temp_user
 +
      ( firstName, lastName, recCreationDate )
 +
      VALUES
 +
      ('jeff', 'meyers', '3/24/03')
 +
     
 +
</sql>
 +
 +
[[More Inserts for temp_user]]
 +
 +
'''SELECT'''
 +
<sql>
 +
      /* Select everyting from the temp_user table */
 +
      SELECT * from temp_user 
 +
     
 +
      /* Select userID,firtName,lastName from the temp_user table */
 +
      SELECT userID,firstName,lastName from temp_user
 +
     
 +
      /* Select userID,firtName,lastName from the temp_user table
 +
      where firstName is jeff*/
 +
      SELECT userID,firtName,lastName from temp_user where firstName = 'jeff'
 +
     
 +
      /* Select userID,firtName,lastName from the temp_user table
 +
      where firstName is jeff then sorts by lastName*/
 +
      SELECT userID,firstName,lastName from temp_user where firstName = 'jeff'
 +
      order by LastName
 +
     
 +
      /* Select userID,firtName,lastName from the temp_user table
 +
      where firstName is jeff then sorts by lastName in reverse order*/
 +
      SELECT userID,firstName,lastName from temp_user where firstName = 'jeff'
 +
      order by LastName DESC
 +
     
 +
      /* Select userID,firtName,lastName from the temp_user table
 +
      where firstName is jeff and last name is meyers then sorts by lastName
 +
      in reverse order*/
 +
      SELECT userID,firstName,lastName from temp_user where firstName = 'jeff'
 +
      and lastName = 'meyers'
 +
      order by LastName DESC
 +
</sql>
 +
 +
'''UPDATE'''
 +
<sql>
 +
      UPDATE temp_user set firstName = 'jeffrey' where firstName = 'jeff'
 +
     
 +
</sql>
 +
 +
'''DELETE'''
 +
<sql>
 +
      DELETE from temp_user where firstName = 'jeffrey'
 +
     
 +
</sql>
 +
Query Analyzer
 +
 +
SQL Query Analyzer is an interactive, graphical tool that enables a database administrator or developer to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance. -MSSQL help
 +
Stored Procedures
 +
 +
A stored procedure is a collection of compiled Transact-SQL statements that can take and return user-supplied parameters. Views
 +
 +
A view is simply a SELECT query saved in the database. Thus, most operations you can perform on queries you can also perform on views. However, there are some operations that apply only to one or the other. -MSSQL Help
 +
 +
 +
==DataProviders==
 +
[http://msdn2.microsoft.com/en-us/library/a6cd7c08(VS.80).aspx .NET Framework Data Providers Data Providers] (language from OLE DB)
 +
 +
# System.Data.SqlClient
 +
# System.Data.OleDb
 +
# System.Data.Odbc
 +
# System.Data.OracleClient
 +
 +
Abstract Factory the allows DataProviders to the changed at run time.
 +
:Abstract Factory: Provide an interface for creating families of related or dependent objects without specifying their concrete classes
 +
 +
==Data Consumers==
 +
 +
*Data Adapter
 +
*DataReader http://iam.colum.edu/dd/classsource/ado/dataReader.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/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("<b>{0}</b>:{1}<br /><br />",
 +
                objDataReader["CheeseName"], objDataReader["CheeseDescription"]);
 +
        }
 +
    }
 +
    catch (Exception e)
 +
    {
 +
        lblErr.Text = "Connection failed to open successfully.<br/>";
 +
        lblErr.Text += e.ToString();
 +
    }
 +
    finally
 +
    {
 +
        objDataReader.Close();      //Close Reader
 +
        objConnection.Close();      //Close Connection
 +
 +
    }
 +
    //Write results
 +
    divListCheese.InnerHtml = strResultsHolder;
 +
}
 +
 +
</csharp>
 +
 +
==ADO.NET Objects==
 +
 +
ADO.NET Objects
 +
[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetarchitecture.asp ADO.NET Architecture]
 +
 +
{{Template:ADO.NET Diagram}}
 +
 +
[[Template:ADO.NET Diagram]]
 +
 +
== Data Reader==
 +
 +
DataReader
 +
:The ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. It buffers the data :rather than trying to return all the data at once whick increases efficiency. [http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp MS DataReader]
 +
 +
http://iam.colum.edu/dd/classsource/class4/datareader.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class4/datareader.aspx source]
 +
 +
==ASPX and execute Non-query==
 +
 +
ExecuteNonQuery
 +
:You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or :creating database objects such as tables), or to change the data in a database without using a DataSet by executing :UPDATE, INSERT, or DELETE statements. Although the ExecuteNonQuery does not return any rows, any output parameters :or return values mapped to parameters are populated with data. For UPDATE, INSERT, and DELETE statements, the :return value is the number of rows affected by the command. For all other types of statements, the return value is :-1. If a rollback occurs, the return value is also -1.
 +
[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclassexecutenonquerytopic.asp ExecuteNonQuery() topic on msdn]
 +
 +
show examples of the old execute non query
 +
 +
<csharp>
 +
    //Connection string comes from web config
 +
    SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString);
 +
   
 +
    // Create and open the connection object
 +
    SqlConnection objConnection = new SqlConnection(strConnection);
 +
    objConnection.Open();    //open the connection
 +
   
 +
    // set the SQL string
 +
    string strSQL = "INSERT INTO temp_user (FirstName , LastName ) VALUES ( 'Beth' , 'Hart' )";
 +
   
 +
    // Create the Command and set its properties
 +
    SqlCommand objCmd = new SqlCommand(strSQL, objConnection);
 +
   
 +
    // execute the command
 +
    int i = objCmd.ExecuteNonQuery();
 +
    objConnection.Close();    //close the conection
 +
</csharp>
 +
 +
INSERT
 +
 +
http://iam.colum.edu/dd/classsource/class4/directsqlINSERT.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class4/directsqlINSERT.aspx source]
 +
 +
UPDATE
 +
 +
http://iam.colum.edu/dd/classsource/class4/directsqlUPDATE.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class4/directsqlUPDATE.aspx source]
 +
 +
DELETE
 +
 +
http://iam.colum.edu/dd/classsource/class4/directsqlDELETE.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class4/directsqlDELETE.aspx source]
 +
 +
Example with panels and a form
 +
 +
http://iam.colum.edu/dd/classsource/class4/temp_userInsert.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class4/temp_userInsert.aspx source]
 +
 +
 +
http://iam.colum.edu/dd/classsource/class4/temp_userDelete.aspx
 +
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class4/temp_userDelete.aspx source]
 +
 +
 +
==Homework==
 +
 +
 +
Create a Cheese Table in you database
 +
 +
Cheese
 +
{| class="wikitable" cellpadding="5" cellspacing="0"
 +
!CheeseID !!  CheeseName !! CheeseDescription
 +
|-
 +
|1 || SomeCheese ||  SomeCheese Desc
 +
|-
 +
|2 || AnotherCheese || AnotherCheese Desc
 +
|}
 +
 +
#Create a page the reads all the data in you cheese table using a DataReader and displays it in an aspx page
 +
#Create a page that adds cheese to the cheese table using ExecuteNonQuery and an aspx form
 +
 +
 +
 +
Read Chapter 5 in BAD
 +
Read Chapter 7 in Programming Microsoft ASP.NET 2.0

Revision as of 01:37, 19 October 2009

Binding to SQL or XML

http://iam.colum.edu/dd/classsource/class3/RadioBindDropDownStates.aspx source

http://iam.colum.edu/dd/classsource/class3/DataBindDropDownStates2.aspx source

Notice the Name and the Value in both of these examples are the same. ListControls support both Name and Value fields and these can have different values. Be when a single dimensional data source is bound to the control the name and value will be the same. In order to define these parameter we need to have a named field from an object. btw it just wouldn't be fair if I didn't show you how to do it with objects

http://iam.colum.edu/dd/classsource/class3/DataBindDropDownStatesObjective.aspx source

and finally databinding to sql server (really we are using and object here DataTable)

http://iam.colum.edu/dd/classsource/class3/DataBindDropDownStatesData.aspx source


Same example with XML

http://iam.colum.edu/dd/classsource/class3/DataBindDropDownStatesCountryData.aspx source

SQL server

Create Database

Create Table

demo in SQLStudio

Create Table Syntax <sql> CREATE TABLE table_name { 

coulumn_name datatype[(length)] [NULL | NOT NULL}

}

  1. Example

CREATE TABLE temp_user ( userID int PRIMARY KEY IDENTITY NOT NULL , firstName varchar (250) NOT NULL , lastName varchar (250) NOT NULL )

  1. MSSQL Example

CREATE TABLE [dbo].[user] ( [userID] [int] IDENTITY (1, 1) NOT NULL , [firstName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO </sql>

Alter Table <sql> ALTER TABLE temp_user add recCreationDate datetime NULL

  1. You cannot add a column that is NOT NULL wihout specifing a default value

GO </sql>


Code to create the States Table from the above Example

http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/states/states.sql

http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/states/statesSimple.sql

<sql> CREATE TABLE states( ID int IDENTITY(1,1) NOT NULL, StateName varchar(50) NOT NULL, StateAbr nchar(3) NOT NULL, 

   CONSTRAINT [PK_states] PRIMARY KEY CLUSTERED 
   (

ID ASC 

   )

) </sql>


More SQL Syntax we'll study this in the comming weeks SQL SelectInsertUpdateDeleteSyntax

Query Analyzer

SQL Query Analyzer is an interactive, graphical tool that enables a database

administrator or developer to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance. -MSSQL help

Sql code to insert all the states in the states table

http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/states/InsertStates.sql

<sql> INSERT into states (StateName, StateAbr) values ('Non US Province' , ) INSERT into states (StateName, StateAbr) values ('Alabama' , 'AL') INSERT into states (StateName, StateAbr) values ('Alaska' , 'AK') INSERT into states (StateName, StateAbr) values ('Arizona' , 'AZ') INSERT into states (StateName, StateAbr) values ('Arkansas' , 'AR') INSERT into states (StateName, StateAbr) values ('Armed Forces Americas' , 'AA') ... </sql>


SQL Syntax

Create Table

<sql> CREATE TABLE table_name { 

coulumn_name datatype[(length)] [NULL | NOT NULL}

}

  1. Example

CREATE TABLE temp_user ( userID int PRIMARY KEY IDENTITY NOT NULL , firstName varchar (250) NOT NULL , lastName varchar (250) NOT NULL )

  1. MSSQL Example

CREATE TABLE [dbo].[user] ( [userID] [int] IDENTITY (1, 1) NOT NULL , [firstName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO </sql>

<sql> Alter Table

ALTER TABLE temp_user add recCreationDate datetime NULL

  1. You cannot add a column that is NOT NULL wihout specifing a default value

GO </sql>

SQL Syntax

Single Table Operations

If you are dealing with more than one table then all of your sql statement must be wrapped in a transaction. A transaction it group of statements that will succeed or fail as a whole. All the statements must succeed if one fails then the entire transaction is rolled back.

SQL statements

  • INSERT
  • UPDATE
  • SELECT
  • DELETE

INSERT <sql> 

     		 INSERT into temp_user 
     		 ( firstName, lastName, recCreationDate )
     		 VALUES
     		 ('jeff', 'meyers', '3/24/03')

</sql>

More Inserts for temp_user

SELECT <sql> 

     		 /* Select everyting from the temp_user table */
     		 SELECT * from temp_user  
     		 
     		 /* Select userID,firtName,lastName from the temp_user table */
     		 SELECT userID,firstName,lastName from temp_user
     		 
     		 /* Select userID,firtName,lastName from the temp_user table 
     		 	where firstName is jeff*/
     		 SELECT userID,firtName,lastName from temp_user where firstName = 'jeff'
     		 
     		 /* Select userID,firtName,lastName from the temp_user table 
     		 	where firstName is jeff then sorts by lastName*/
     		 SELECT userID,firstName,lastName from temp_user where firstName = 'jeff' 
     		 order by LastName
     		 
     		 /* Select userID,firtName,lastName from the temp_user table 
     		 	where firstName is jeff then sorts by lastName in reverse order*/
     		 SELECT userID,firstName,lastName from temp_user where firstName = 'jeff' 
     		 order by LastName DESC
     		 
     		 /* Select userID,firtName,lastName from the temp_user table 
     		 	where firstName is jeff and last name is meyers then sorts by lastName 
     			in reverse order*/
     		 SELECT userID,firstName,lastName from temp_user where firstName = 'jeff' 
     		 and lastName = 'meyers'
     		 order by LastName DESC

</sql>

UPDATE <sql> 

     		 UPDATE temp_user set firstName = 'jeffrey' where firstName = 'jeff'

</sql>

DELETE <sql> 

     		 DELETE from temp_user where firstName = 'jeffrey'

</sql> Query Analyzer

SQL Query Analyzer is an interactive, graphical tool that enables a database administrator or developer to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance. -MSSQL help Stored Procedures

A stored procedure is a collection of compiled Transact-SQL statements that can take and return user-supplied parameters. Views

A view is simply a SELECT query saved in the database. Thus, most operations you can perform on queries you can also perform on views. However, there are some operations that apply only to one or the other. -MSSQL Help


DataProviders

.NET Framework Data Providers Data Providers (language from OLE DB)

  1. System.Data.SqlClient
  2. System.Data.OleDb
  3. System.Data.Odbc
  4. System.Data.OracleClient

Abstract Factory the allows DataProviders to the changed at run time.

Abstract Factory: Provide an interface for creating families of related or dependent objects without specifying their concrete classes

Data Consumers


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>

ADO.NET Objects

ADO.NET Objects ADO.NET Architecture

  • Connection - used to connect the data consumer ASP.NEt to a Data Provider
  • Command - used to issue SQL commands to a Data Provider
  • DataReader - A fast forward only DataSet that holds one or more resultsets.Fast and efficient
  • DataSet - can contain multiple tables and relationships between tables
  • DataAdapter - Passes Data from a Connection object to a DataSet and updates that data. ( A bridge used to fill dataSets with DataAdapter.Fill and DataAdapter.Update)
  • DataTable - structure that holds a set of rows and columns
  • DataRelation - holds relations between DataTables in a DataSet
  • DataRow - data within data row
  • DataView - a specific view of the DataTables within a Dataset

Connection
DataAdapter
DataReader
DataSet
DataTable
DataView
DataRow
DataRow
DataRow
DataTable
DataRow
DataRow
DataRow
DataTable
DataRow
DataRow
DataRow

Template:ADO.NET Diagram

Data Reader

DataReader

The ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. It buffers the data :rather than trying to return all the data at once whick increases efficiency. MS DataReader

http://iam.colum.edu/dd/classsource/class4/datareader.aspx source

ASPX and execute Non-query

ExecuteNonQuery

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or :creating database objects such as tables), or to change the data in a database without using a DataSet by executing :UPDATE, INSERT, or DELETE statements. Although the ExecuteNonQuery does not return any rows, any output parameters :or return values mapped to parameters are populated with data. For UPDATE, INSERT, and DELETE statements, the :return value is the number of rows affected by the command. For all other types of statements, the return value is :-1. If a rollback occurs, the return value is also -1.

ExecuteNonQuery() topic on msdn

show examples of the old execute non query

<csharp> 

   //Connection string comes from web config
   SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString);
    
   // Create and open the connection object
   SqlConnection objConnection = new SqlConnection(strConnection);
   objConnection.Open();     //open the connection
   
   // set the SQL string
   string strSQL = "INSERT INTO temp_user (FirstName , LastName ) VALUES ( 'Beth' , 'Hart' )";
   
   // Create the Command and set its properties
   SqlCommand objCmd = new SqlCommand(strSQL, objConnection);
   
   // execute the command
   int i = objCmd.ExecuteNonQuery();
   objConnection.Close();    //close the conection

</csharp>

INSERT

http://iam.colum.edu/dd/classsource/class4/directsqlINSERT.aspx source

UPDATE

http://iam.colum.edu/dd/classsource/class4/directsqlUPDATE.aspx source

DELETE

http://iam.colum.edu/dd/classsource/class4/directsqlDELETE.aspx source

Example with panels and a form

http://iam.colum.edu/dd/classsource/class4/temp_userInsert.aspx source


http://iam.colum.edu/dd/classsource/class4/temp_userDelete.aspx source


Homework

Create a Cheese Table in you database

Cheese

CheeseID CheeseName CheeseDescription
1 SomeCheese SomeCheese Desc
2 AnotherCheese AnotherCheese Desc
  1. Create a page the reads all the data in you cheese table using a DataReader and displays it in an aspx page
  2. Create a page that adds cheese to the cheese table using ExecuteNonQuery and an aspx form


Read Chapter 5 in BAD Read Chapter 7 in Programming Microsoft ASP.NET 2.0

Retrieved from "https://imamp.colum.edu/mediawiki/index.php?title=DD_Class6&oldid=15757"