Contents |
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.
SQL
Originally developed in the 70's by IBM as a production called SEQUEL or Structured English Query Language</p>
RDBMS
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
Practical PostgreSQL - Understanding SQL
Create a Cheese Table in you database
Cheese
| CheeseID | CheeseName | CheeseDescription |
|---|---|---|
| 1 | SomeCheese | SomeCheese Desc |
| 2 | AnotherCheese | AnotherCheese Desc |
Read Chapter 5 in BAD Read Chapter 7 in Programming Microsoft ASP.NET 2.0
| |||||||||||||||
|
|
||||||||||||||
|
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
SELECT RegionName, RegionID FROM Region ORDER BY RegionName
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 ...
//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
There are essentially two types of controls that support databinding ListControls and DataBoundControls
Presentation Rendering types for data controls
ListControl
Repeat a fixed template
DataBoundControl Iterative Control
http://iam.colum.edu/dd/classsource/ado/SimpleADOAllControls.aspx - source
show examples of DataGrid control and DetailView control
DetialsViewDemo
Make 3 aspx pages
Extra fun stuff
