From IAMMediaWiki Create Change

Contents 1 Progamatically add and Bind Data 2 Basic Data and Tables 2.1 Data Types 2.2 Tables 2.2.1 Fields 2.3 What is SQL? 2.4 SQL server 2.5 Homework

Progamatically add and Bind Data

You can dynamically add list items to and control that inherits ListControl ListControl

To add items to a ListControl use the ListControls Items.Add() function

This example assumes the there is a DropDownList called ddlStates

 
<asp:DropDownList id="ddlStates" runat="server" >
</asp:DropDownList>

 
//beware code fragment below
ListItem li = new ListItem("SomeState", "SS");      //create a listitem
ddlStates.Items.Add(li);                            //add the listitem to the dropdown
li = new ListItem("AnotherState", "AS");            //make another listitem
ddlStates.Items.Add(li);                            //add the second listitem

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

Same this but with RadioButtonList

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

Basic Data and Tables

Data Types

Standard SQL types SQL Data Types

With MSSQL you can also define your own data types. We won't be doing this in this class.

Tables

Defines a collection of rows(fields) that have associated columns.

Each column must be of a single type

Fields

Fields or rows hold the actual data that is defined by the data type of the column.

What is SQL?

SQL

Structured Query Language

Originally developed in the 70's by IBM as a production called SEQUEL or Structured English Query Language</p>

RDBMS

Relational Database Management System</p>

A collection of tables that can be linked or related to each other by common keys.</p>

SQL Standards American National Standards Institute (ANSI) tries to keep conformity amount SQL server vendors. The original spec posted in 1986 called SQL86 was updated several times with the most common SQL implementation being SQL99 also approved by the International Standards Organization(ISO). There are various levels of compliance within the SQL99 spec.

Common SQL Servers

• Microsoft SQL Server- T/SQL Transact SQL
• MySQL - least compliant ( InnoDB type finally supports transactions)
• Oracle - PL/SQL (Procedural Language SQL)
• Postgres - most compliant with SQL99

TCP-C Performance Tests

Codd's 12 Rules

Practical PostgreSQL - Understanding SQL

MSSQL Accounts and Demo

SQL server

Create Database

Create Table

demo in SQLStudio

Create Table Syntax

 
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

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

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

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

 
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')
...

Homework

Make a table with all of the columns from your cheese registration form

Convert registration form to use XML and SQL database for the Countries and States.

http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class3/countryfile.xml

Chapter 4 BDD