DD Class4

From IAMMediaWiki Create Change

Jump to: navigation, search


Contents

[edit] Review Project Setup

[edit] Project Setup

Magic Folders

  1. App_Code
  2. App_Data
  3. App_Themes
  4. Bin

Deployment Methods 

Project Location(Local vs FTP)
Deployment(Refresh vs Publish)


[edit] Basic SQL and Validation

[edit] SQL Syntax

Create Table 

 
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

 
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 Syntax

[edit] 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 

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

More Inserts for temp_user

SELECT 

 
      		 /* 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

UPDATE 

 
      		 UPDATE temp_user SET firstName = 'jeffrey' WHERE firstName = 'jeff'

DELETE 

 
      		 DELETE FROM temp_user WHERE firstName = 'jeffrey'

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


[edit] 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

[edit] Data Consumers


1 Connection Object 

 
//Connection string comes from web config
SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString);

web.config connection strings 

 
<configuration>
	
               <connectionStrings>
		
		  <add name="cheeseConnectionString" connectionString="Data Source=iamdb;Initial Catalog=cheese;User ID=student;Password=Student" providerName="System.Data.SqlClient"/>
	       </connectionStrings>

2 Command Object 

string strSQL = "SELECT CheeseName,CheeseDescription FROM cheese";
    
    string strResultsHolder = "";
    
    SqlCommand objCommand = new SqlCommand(strSQL, objConnection);

3 Reader and Reading 

 
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;
}
 

[edit] 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

[edit] 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

[edit] 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 

 
    //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

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

[edit] 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 "http://imamp.colum.edu/mediawiki/index.php/DD_Class4"
Personal tools