DD Class5

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

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

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

http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

http://en.wikipedia.org/wiki/Microsoft_SQL_Server

MSSQL Accounts and Demo

Review ADO Objects

• 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

Data Adapter

Allows use of a disconnected data. The data is copied from the sql server and is held by the framework in a dataset.

In order to obtain the data in the dataset a DataAdapter is used. This same adapter is use to modify or update the original data.

In the example below we are ugin the Regions Table in the Cheese Database. The Region table design looks like this.

We will use an SQL Statement like this to Select all the rows in the table <sql> SELECT RegionName, RegionID from Region ORDER BY RegionName </sql>

This will return a DataSet similar to the one below.

RegionName                                         RegionID
-------------------------------------------------- -----------
Argentina                                          13
Belgium                                            12
Cypus                                              14
Danish                                             15
Dutch                                              6
English                                            3
Finland                                            11
...

1. setup Connection object
   1. Connection String
2. setup Command object
   1. need Connection Object and SQL string
3. create Adapter
4. create DataSet
5. User Adapter to fill DataSet using Connection and Command
6. get table out of DataSet using a DataTable
7. Bind the DataTable to a DataControl using DataBind

Simple ADO Binding

//Connection string comes from web config
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString);             
        
        string strSQL = "Select RegionName, RegionID from Region";  
        
        SqlCommand dc = new SqlCommand(strSQL , conn);     //Initalize SqlCommand
        SqlDataAdapter da = new SqlDataAdapter(dc);            //Inialize DataAdapter with DataCommand
        DataSet ds = new DataSet();                     //Initialize empty DataSet
        DataTable dt = new DataTable();
        
        da.Fill(ds, "Region");          //Use the DataAdapter to fill the DataSet with a named Table
        dt = ds.Tables["Region"];       //Retreive the named table from the DataSet
        
        gvTestOrig.DataSource = dt;     //Set the DataTable as the source for the GridView note AutoGenerateColumns = true
        gvTestOrig.DataBind();          //Bind the data form the table to the GridView

The above code binds to a control called gvTestOrig

<asp:GridView ID="gvTestOrig" runat="server" AutoGenerateColumns="true" />

http://iam.colum.edu/dd/classsource/ado/SimpleADO.aspx - source

Same code but I moved some of the code into a function and split the declareation and initialization of some object to that they are scoped to the page not to Page_Load. This allows other functions on the page to use these objects.

http://iam.colum.edu/dd/classsource/ado/ADOTest1.aspx - source

http://iam.colum.edu/dd/classsource/ado/SimpleADOFilter.aspx - source

ASPX 2.0

show examples of DataGrid control and DetailView control

DataSource

• SqlDataSource
• AccessDataSource
• ObjectDataSource
• XmlDataSource

SqlDataSource Demo

GridView

GridView Demo

DetailsView

DetialsViewDemo

Home work

UPDATED Homework

Create you're own table on iamdb in the database we created in class. The table can have whatever columns you like. Setup some columns and use what you think are appropriate. Add some data to your table. You may use Microsoft SQL Management Studio or Visual Studio.

Use the SQLDataSource like we did last week to connect to your new database and display data in a gridview on a webpage.