A Historical Background

---

 Once upon a time, in the primitive and barbarian days before computers, the amount of information gathered by a group of people could be collected in the wisdom and the stories of its older members. In this world, storytellers, magicians, and grandparents were considered great and honored storehouses for all that was known.

Apparently, and according to vast archeological data, campfires were used by the younger members of the community to access the information stored in the minds of the elders using API's such as

public String TellUsAboutTheTimeWhen(String s);.

And then of course, like a sweeping and rapidly-encompassing viral infection, came agriculture, over-production of foodstuffs, and the origins of modern-day commerce.

Dealing with vast storehouses of wheat, rice, and maize became quite a chore for the monarchs and emperors that developed along with the new economy. There was simply too much data to be managed in the minds of the elders (who by now were feeling the effects of hardware obsolescence as they were being pushed quietly into the background).

And so, in order to store all the new information, humanity invented the technology of writing. And though great scholars like Aristotle warned that the invention of the alphabet would lead to the subtle but total demise of the creativity and sensibility of humanity, data began to be stored in voluminous data repositories, called books.

As we know, eventually books propogated with great speed and soon, whole communities of books migrated to the first real "databases", libraries.

Unlike previous versions of data warehouses (people and books), that might be considered the australopithecines of the database lineage, libraries crossed over into the modern-day species, though they were incredibly primitive of course.

Specifically, libraries introduced "standards" by which data could be stored and retrieved.

After all, without standards for accessing data, libraries would be like my closet, endless and engulfing swarms of chaos. Books, and the data within books, had to be quickly accessible by anyone if they were to be useful.

In fact, the usefulness of a library, or any base of data, is proportional to its data storage and retrieval efficiency. This one corollary would drive the evolution of databases over the next 2000 years to its current state.

Thus, early librarians defined standardized filing and retrieval protocols. Perhaps, if you have ever made it off the web, you will have seen an old library with its cute little indexing system (card catalog) and pointers (Dewey decimal system).

And for the next couple thousand years libraries grew, and grew, and grew along with associated storage/retrieval technologies such as the filing cabinet, colored tabs, and three ring binders.

All this until one day about half a century ago, some really bright folks including Alan Turing, working for the British government were asked to invent an advanced tool for breaking German cryptographic "Enigma" codes.

That day the world changed again. That day the computer was born.

The computer was an intensely revolutionary technology of course, but as with any technology, people took it and applied it to old problems instead of using it to its revolutionary potential.

Almost instantly, the computer was applied to the age-old problem of information storage and retrieval. After all, by World War Two, information was already accumulating at rates beyond the space available in publicly supported libraries. And besides, it seemed somehow cheap and tawdry to store the entire archives of "The Three Stooges" in the Library of Congress. Information was seeping out of every crack and pore of modern day society.

Thus, the first attempts at information storage and retrieval followed traditional lines and metaphors. The first systems were based on discrete files in a virtual library. In this file-oriented system, a bunch of files would be stored on a computer and could be accessed by a computer operator. Files of archived data were called "tables" because they looked like tables used in traditional file keeping. Rows in the table were called "records" and columns were called "fields".

Consider the following example:  

First Name	Last Name	Email Address	Phone
Eric	Tachibana	erict@eff.org	213-456-0987
Selena	Sol	selena@eff.org	987-765-4321
Li Hsien	Lim	hsien@somedomain.com	65-777-9876
Jordan	Ramacciato	nadroj@otherdomain.com	222-3456-123

 

The "flat file" system was a start. However, it was seriously inefficient.

Essentially, in order to find a record, someone would have to read through the entire file and hope it was not the last record. With a hundred thousands records, you can imagine the dilemma.

What was needed, computer scientists thought (using existing metaphors again) was a card catalog, a means to achieve random access processing, that is the ability to efficiently access a single record without searching the entire file to find it.

The result was the indexed file-oriented system in which a single index file stored "key" words and pointers to records that were stored elsewhere. This made retrieval much more efficient. It worked just like a card catalog in a library. To find data, one needed only search for keys rather than reading entire records.

However, even with the benefits of indexing, the file-oriented system still suffered from problems including:

• Data Redundancy - the same data might be stored in different places
• Poor Data Control - redundant data might be slightly different such as in the case when Ms. Jones changes her name to Mrs. Johnson and the change is only reflected in some of the files containing her data
• Inability to Easily Manipulate Data - it was a tedious and error prone activity to modify files by hand
• Cryptic Work Flows - accessing the data could take excessive programming effort and was too difficult for real-users (as opposed to programmers).

Consider how troublesome the following data file would be to maintain.

Name	Address	Course	Grade
Mr. Eric Tachibana	123 Kensigton	Chemistry 102	C+
Mr. Eric Tachibana	123 Kensigton	Chinese 3	A
Mr. Eric Tachibana	122 Kensigton	Data Structures	B
Mr. Eric Tachibana	123 Kensigton	English 101	A
Ms. Tonya Lippert	88 West 1st St.	Psychology 101	A
Mrs. Tonya Ducovney	100 Capitol Ln.	Psychology 102	A
Ms. Tonya Lippert	88 West 1st St.	Human Cultures	A
Ms. Tonya Lippert	88 West 1st St.	European Governments	A

What was needed was a truly unique way to deal with the age-old problem, a way that reflected the medium of the computer rather than the tools and metaphors it was replacing.

Enter the database.

---

 

Simply put, a database is a computerized record keeping system. More completely, it is a system involving data, the hardware that physically stores that data, the software that utilizes the hardware's file system in order to 1) store the data and 2) provide a standardized method for retrieving or changing the data, and finally, the users who turn the data into information.

Databases, another creature of the 60s, were created to solve the problems with file-oriented systems in that they were compact, fast, easy to use, current, accurate, allowed the easy sharing of data between multiple users, and were secure.

A database might be as complex and demanding as an account tracking system used by a bank to manage the constantly changing accounts of thousands of bank customers, or it could be as simple as a collection of electronic business cards on your laptop.

The important thing is that a database allows you to store data and get it or modify it when you need to easily and efficiently regardless of the amount of data being manipulated. What the data is and how demanding you will be when retrieving and modifying that data is simply a matter of scale.

Traditionally, databases ran on large, powerful mainframes for business applications. You will probably have heard of such packages as Oracle 8 or Sybase SQL Server for example.

However with the advent of small, powerful personal computers, databases have become more readily usable by the average computer user. Microsoft's Access is a popular PC-based engine.

More importantly for our focus, databases have quickly become integral to the design, development, and services offered by web sites.

Consider s site like Amazon.com that must be able to allow users to quickly jump through a vast virtual warehouse of books and compact disks.

How could Amazon.com create web pages for every single item in their inventory and how could they keep all those pages up to date. Well the answer is that their web pages are created on-the-fly by a program that "queries" a database of inventory items and produces an HTML page based on the results of that query.

Database Models

Through history several database models have been created and used. Among those are: Hierarchical, Network, and Relational. Studying the history of datbases is a worth while endeavor but is not in the context of this class. Instead, we will concentrate our focus on relational databases. They are by far the most popular being used today.

Relational Databases

---

In the 80's the "Relational Database Model" became the rage. The Relational Model developed out of the work done by Dr. E. F. Codd at IBM in the late 1960s who was looking for ways to solve the problems with the existing models.

Because he was a mathematician, he naturally built the model on mathematical concepts which he expounded in the famous work called "A Relational Model of Data for Large Shared Databanks".

At the core of the relational model is the concept of a table (also called a relation) in which all data is stored. Each table is made up of records (horizontal rows also known as tuples) and fields (vertical columns also known as attributes).

It is important to note that how or where the tables of data are stored makes no difference. Each table can be identified by a unique name and that name can be used by the database to find the table behind the scenes. As a user, all you need to know is the table name in order to use it. You do not need to worry about the complexities of how the data is stored on the hard drive.

This is quite a bit different from the hierarchical and network models in which the user had to have an understanding of how the data was structured within the database in order to retrieve, insert, update, or delete records from the database.

So how do you find data in a relational database if there is no map (like a hierarchy defined by pointers) to follow?

Well, in the relational model, operations that manipulate data do so on the basis of the data values themselves. Thus, if you wish to retrieve a row from a table for example, you do so by comparing the value stored within a particular column for that row to some search criteria.

For example, you might say (not getting into syntax yet) "Give me all the rows from the 'STUDENTS' table which have 'Selena' in the 'FIRST_NAME' column."

The database might return a list which looks essentially like this:

Selena	Sol	SID-001	213-456-7890
Selena	Roberts	SID-268	818-934-5069
Selena	Smith	SID-991	310-234-6475

 

You could then use the data from a retrieved row to query another table. For example, you might say "Okay, I want to know what grade 'Selena Sol' received in 'Underwater Basket Weaving 101'. So I will now use the 'Student ID' number from my previous query as the keyword in my next query. I want the row in the 'Underwater Basket Weaving Course' table where student ID equals 'SID-001'.

This data access methodology makes the relational model a lot different from and better than the earlier database models because it is a much simpler model to understand. This is probably the main reason for the popularity of relational database systems today.

Another benefit of the relational system is that it provides extremely useful tools for database administration. Essentially, tables can not only store actual data but they can also be used as the means for generating meta-data (data about the table and field names which form the database structure, access rights to the database, integrity and data validation rules etc).

Thus everything within the relational model can be stored in tables. This means that many relational systems can use operations recursively in order to provide information about the database. In other words, a user can query information concerning table names, access rights, or some data and the results of these queries would then be presented to the user in the form of a table.

This makes database administration as easy as usage!

Client/Server Databases

---

As we said before, most databases that you will come across these days will be relational databases. However, there are many types of relational databases and not all of them will be useful for web applications.

In particular, it will be the client/server databases rather than the stand-alone packages that you will use for the web.

A client/server database works like this: A database server is left running 24 hours a day, and 7 days a week. Thus, the server can handle database requests at any hour. Database requests come in from "clients" who access the database through its command line interface or by connecting to a database socket. Requests are handled as they come in and multiple requests can be handled at one time.

For web applications which must be available for world wide time zone usage, it is essential to build upon a client/server database which can run all the time.

For the most part, these are the only types of databases that Internet Service Providers will even provide. However if you are serving web pages yourself, you should consider many of the excellent freeware, shareware or commercial products around. I myself like postgres for UNIX since I prefer a UNIX-based web server. However, there are plenty of good applications for PC and Mac as well.

 

SQL Server 

---

It is difficult in a classroom environment to give everyone database access. This would mean having an instance of a database open so that everyone could connect. Of course this would also leave it open for hackers. In a not too far past we would use an Access database and place a copy of it in each persons folder so that each individual student could have access to their own database. Today we can use SQL Server Express which is nice becuase you can switch your code to a sql database simply by changing the connection string in web.config.

ADO.NET

---

ASP.NET uses a technology called ADO.NET to connect, read, and write to databases. ADO.NET is a set of computer software components that programmers can use to access data and data services. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems, though it can also access data in non-relational sources. ADO.NET is sometimes considered an evolution of ActiveX Data Objects (ADO) technology, but was changed so extensively that it can be considered an entirely new product.

Before delving into using ADO.NET we should first understand how to create a SQL Express database using Visual Studio 2010.

Creating A SQL Server Express Database

---

 To create a SQL Express database using Visual studio you right click on the App_Data folder and select "Add" then "New Item". Select SQL Server Database and give it a name. I will call this one testData.mdf:

Adding A Table

---

You use the server / database explorer to add tables to the database. To use the tool go to the view menu and select "Server Explorer" . The server explorer will show up as tab on top of the docked Toolbox window:

In this example the database is TestData.mdf. To add a table you want to expand the database to view all of the subfolders:

Right click on the "Tables" folder and select "Add New Table". This will open up the table designer:

 If you are experienced with the SQL server management studio then this designer will look very familiar to you.

In this example I am going to switch the guest book from XML to the database. So, I want to add fields for First Name, Last Name, Email, Phone Number.  I will fill the column names out appropriately:

Notice that I have used nvarchar(50) for all of my data types. This is basically a string of characters that has a length of 50. SQL Server has many data types to hold almost any type of data you can think of:

SQL Data Types

---

 

Exact numerics

Type	From	To
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 –1
numeric	-10^38 +1	10^38 –1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

Approximate numerics

Type	From	To
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

datetime and smalldatetime

Type	From	To
datetime (3.33 milliseconds accuracy)	Jan 1, 1753	Dec 31, 9999
smalldatetime (1 minute accuracy)	Jan 1, 1900	Jun 6, 2079

 Character Strings

Type	Description
char	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar	Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max)	Variable-length non-Unicode data with a maximum length of 231 characters (SQL Server 2005 only).
text	Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

 

Unicode Character Strings

Type	Description
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max)	Variable-length Unicode data with a maximum length of 230 characters (SQL Server 2005 only).
ntext	Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

 Binary Strings

Type	Description
binary	Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary	Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max)	Variable-length binary data with a maximum length of 231 bytes (SQL Server 2005 only).
image	Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Primary Key

---

 

When you create a database it is always best to create a primary key field. A primary key is a field that is unique to the record. This is an id that will distinguish that particular record from all others.  I am not a database expert but I like to create a field called ID that is an int type. I also like to have this value auto generated. This means that SQL Server will add the next number to the id field any time a new record is generated.  From past experience I can tell you not having a primary key field can fowl you up when you need to make changes to your table structure or modifying records.

To create an Id field, give the column name Id and seect as the Data Type int, and uncheck the Allows Nulls checkbox:

To create an auto generated field you need to modify the Column properties.  First make sure that your Id field is selected. In the column properties expand "Identity Specification". Change "Is Identity" to Yes. You will notice the first id will be 1 and will increment by 1 each time a record is added. You can modify this if you would like but I never quite saw the point it doing so:

Saving The Table

---

When you select the save button on the tool bar a dialog box will open asking you for the table name. I am simply going to call this table guests:

That is all there is to it. If you need to add more tables you can simply repeat what was just done. We are now ready to move on to reading and writing to the database.

 

Connecting To A Database 

---

In order to read and write to a database you must first create a connection to it. There are a couple of ways to do this. The first ways is to use the server explorer which will create a connection to the database in your App_Data folder automatically. If you want to connect to another server you would use the connection wizard found in the Server Explorer:

This will open the "Choose Data Source" dialog box. Here you select the type of database you want to connect to. In this example I am choosing SQL server:

The "Add Connection" dialog box asks for the server name. In this instance I am choosing the IP address of a server but you can specify server name also if you wish. The dialog also asks you for the authentication type. I am using SQL server authentication but you may choose to use Windows authentication. This all depends how you have things setup. In this example I am also connecting to a database called curriculum:

You should probably at this point test your connection using the "Test Connection" button:

You will note that the connection now shows up in the Server Explorer under Data Connections.

Using A Connection String

---

 You can also connect to a database using a connection string that is stored in the web.config file. This is a popular choice because it provides some flexibility in that if you have to change the database for some reason you simply modify the connection string in the web.config.

If you go back to the server explorer and select the TestData.mdf database and view it's properties you will see a property called "Connection String". This string can be used to create a connection to the database:

Connection strings can be a little baffling and I will try to shed a little light on how to create one:

connectionString="Data Source=71.177.198.91;Initial Catalog=curriculum;User ID=csuser;Password=somePassword"

The first thing is the Data Source this is where you specify the server you wish to connect to. The Initial Catalog is the name of the database you wish to access. User Id is the account id used for login and of course password is the password associated with the account.

Adding to Web.config

---

You add the connection string to the web.config inside the connection strings section which is inside the configuration section:

<configuration>

   <connectionStrings>

      <add name="SiteSqlServer" connectionString="Data Source=71.177.198.91;Initial Catalog=DotNetNuke;User ID=csuser;Password=somePassword" providerName="System.Data.SqlClient" />

   </connectionStrings>

</configuration>

Connection to a Database Programmatically

 

---

 

The first thing that needs to be done to connect programmatically from a database is to read the connection string from the web.config. You access the string using the name that was given. In the above example the name given was SiteSqlServer.  To access the connection string you use the configuration manager which requires you to include the libaray System.Configuration then you simply use the ConnectionStrings property to read the string from web.config:

Now the connection string can be used in conjuction with a SqlConnecton object to open the database. To use any sql object you must first include the System.Data.SqlClient libarary:

 

 

SqlDataSource

---

 

As you have seen there are a couple of ways to connect to a database. The easiest way to read, write, and update a database is through the use of the SqlDataSource. As you have you can use the server explorer to setup a connection. The explorer automatically opens a connection to the database. To read and write you want to add a SqlDataSource:

You simply drag it on to the form.  You can change it's name if you wish. I am changing mine sqlGuests:

The next thing to do is to simply configure the data source for the database you want to open. In this example we will use the TestData database that has the Guest table within it. Notice that it uses the connection string that was created by the server explorer:

Next we select the columns. In this case I might want to have access to all fields so I will simply select the * :

The last thing to do is to test the query to make sure that you are connect and getting data from the database. I have added some information to the Guest Table. Here is the output from the Test Query Button:

Click the Finish button and you are ready to start accessing the database.

SQL Commands

 

---

 

This is an ASP.NET class not sql so we will only touch on a couple of basics here. If you really want to know how to query a database you should take a sql class.

INSERT INTO

The INSERT INTO command will allow you to add new records to a database. It takes the following form:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

example:

INSERT INTO Guests(FirstName, LastName, Email, Phone) 
VALUES('Jane', 'Doe', 'jdoe@gmail.com','951-555-0000')";

SELECT

The SELECT Command allows you to retrieve records from a database. It has the following form:

SELECT column_name(s)
FROM table_name

or

SELECT * FROM table_name

You can also add a WHERE clause to filter the data:

SELECT * FROM Guests WHERE  Id = 1

In this case get all the information from the guest who has an id of 1.

UPDATE

The UPDATE command allows you to modify an existing record:

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

UPDATE Guests SET Phone = '951-555-1111' WHERE Id = 1

SqlDataSource InsertCommand

---

The insert command that is part of the SqlData Adapter allows you to insert records into the data base. In this example Jane Doe is added:

 

 

SqlDataSource UpdateCommand

---

The SqlDataSource Update command works the same way. You create the sql statement, set the update command, and then execute the update. The following modifies the phone number of Joe Smith:

SqlDataSource DeleteCommand

---

 

The Delete command allows you to remove records from the database:

 

SqlDataSource SelectCommand

 

---

The select command is slightly different because it returns data back to you. It returns the data in the form of an IEnumerable collection which is list of data rows. In this example we will use a DataView becuase it will contain a table that is really a snap shot of what is being queried.

 

The command also takes an argument of type DataSourceSelectArguments that allows you to request operations on the data that are beyond basic retrieval. Since we typically want to do basic retrieval we will supply an argument type of DataSourceSelectArguments.Empty

The data from the table at the first row and first and second column is then placed inside a Label control:

Command Parameters

---

 

If you haven't noticed by now you should note that when you are adding a string to the database it has to be double qouted:

string command = "UPDATE Guests SET Phone = '951-555-9999'";
command += "WHERE LastName = 'Smith'";

On the surface this doesn't look lilke a big deal but this can complicate things when you want to take data from a text box and put it into the string. This example shows how the above would be rewritten if you needed to take data from text boxes:

string command = "UPDATE Guests SET Phone = '" + txtPhone.Text + "'";
command+= "WHERE LastName = '" + txtLastName.Text + "'";

When you have to add this many quotes it becomes easy to make mistakes. To help alleviate this you can use command parameters. Each of the commands: insert, update, select, delete have command paramaters:

string command = "UPDATE Guests SET Phone = @Phone WHERE LastName = @lName";

Using the @ is like a place holder for the actual value which will get replaced by using the add method that is part of the parameters.  In the example so far the SqlDataSource name is sqlGuests. The following will update the phone number based on the last name:

   string command = "UPDATE Guests SET Phone = @Phone ";
   command += "WHERE LastName = @lName";
   sqlGuests.UpdateParameters.Add("Phone", txtPhone.Text);
   sqlGuests.UpdateParameters.Add("lName", txtLastName.Text);
   sqlGuests.UpdateCommand = command;
   sqlGuests.Update();

Video - Guest Book Revisited

---