Difference between revisions of "DD Class6"

Latest revision as of 03:23, 9 February 2016

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

The sql language contains commands for everything that the sql server can do.

Create Table

demo in SQLStudio and VS

Create Table Syntax <sql> CREATE TABLE table_name (

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

The 'GO' keyword is special in T-SQL it executes the current batch of statements

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

1. You cannot add a column that is NOT NULL without specifying 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 NOT NULL IDENTITY (1, 1), StateName varchar(50) NOT NULL, StateAbr nchar(3) NOT NULL ) ON [PRIMARY] GO ALTER TABLE states ADD CONSTRAINT PK_states PRIMARY KEY CLUSTERED ( id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

</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 the Abstract Factory the allows the DataProviders to the changed at run time.</p> Abstract Factory: Provide an interface for creating families of related or dependent objects without specifying their concrete classes

http://www.dofactory.com/Patterns/PatternAbstract.aspx

DbProviderFactory

MySQL Connector http://dev.mysql.com/downloads/connector/net/5.0.html

Data Consumers

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

1 Connection Object

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

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

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

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

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

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

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

UPDATE

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

DELETE

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

http://iam.colum.edu/dd/classsource/class4/temp_userDelete.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

1. Create a Cheese Table in you database

Cheese

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

2. Create a states table in you database as shown above. Add all the states to the table sql is above. Now Redo you cheese form assignment but pull the state Names and Abbreviations from you database. The state names should be bound to a DropDown List as the DataTextField and the state abbreviation should be the DataValueField.

3. Create a regions and consistency table in you database. Add the appropriate fields to each table. Then replace the static check-box list of consistencies and radiobuttonlist of regions (you don't need to completely populate the regions table but you should add all the consistencies from the form).

Read Chapter 5 in BAD