Basic SQL and Validation
Basic Data and Tables
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.
Defines a collection of rows(fields) that have associated columns.
Each column must be of a single type
Fields or rows hold the actual data that is defined by the data type of the column.
What is SQL?
- Structured Query Language
Originally developed in the 70's by IBM as a production called SEQUEL or Structured English Query Language</p>
- 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
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
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 ...
- setup Connection object
- Connection String
- setup Command object
- need Connection Object and SQL string
- create Adapter
- create DataSet
- User Adapter to fill DataSet using Connection and Command
- get table out of DataSet using a DataTable
- 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" />
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.
show examples of DataGrid control and DetailView control
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.