Each data provider in ADO.NET contains a Connection class that inherits from the System.Data.Common.DbConnection class. The DbConnection serves as the…
Before you deal with accessing and modifying databases, you first need to know the basics of SQL (Structured Query Language). This is the language used for querying databases. SQL is simple and easy to understand. SQL is not only used for querying data, it can pretty much do anything from creating, updating, and deleting databases and tables. Since this site is not about SQL, we will only tackle those that will be needed in the upcoming lessons.
You can create a database using the following syntax. Please do note that SQL is case insensitive. So it doesn’t matter if you write the keywords in lowercase.
CREATE DATABASE <Name of Database>;
The <Name of Database> is the name that you want to give to your database. Here’s an example.
CREATE DATABASE MyDatabase;
For us to store a Database, we need to create a table. We use the following syntax.
CREATE TABLE <Name of Table> ( <Column1> <Datatype>, <Column2> <Datatype>, . . . <ColumnN> <Datatype> );
Again <Name of Table> is the name of the table we want to create. Inside the parentheses are a list of columns and their data type. The data types that you will commonly used is listed below.
|int||integer data||-3, 1, 12, 57|
|char(n)||fixed length string where n is the maximum length||‘hello’, ‘goodbye’|
|varchar(n)||variable length string where n is the maximum length||‘hello’, ‘goodbye’|
|datetime||stores date and time||Jan 1, 2010 3:00PM|
|date||stores date only||Jan 1, 2010|
|time||stores time only||3:00PM|
|money||stores monetary data||123.45|
You might notice that char(n) and varchar(n) seems to be the same. char(n) is fixed and once you give size to it, it will take up memory depending on that size. If the size you have given is 100, and the string you store only has 3 characters, then char(n) will still use memory for 100 characters. varchar(n) is different. It only takes up what is needed. Also, note that character strings were enclosed inside single quotes.
Let’s create an example table that can store the FirstName, LastName and Age of employees.
CREATE TABLE Employees ( EmployeeID int primary key, FirstName varchar(100), LastName varchar(100), Age int );
Notice that we added the primary keyword after the data type of EmployeeID. This will signify that EmployeeID will be the primary key of the Employees table. A primary key is a value that identifies a row or record. The primary key must be unique for each of the records. We used EmployeeID as the primary key and not, for example, the FirstName, because multiple employees could have the same name, therefore, that will conflict the rule that primary keys need to unique. The FirstName and LastName fields have a data type of varchar(100), that means the length has a maximum of 100 characters.
You can also specify a primary key field to be an identity field. An identity field is a field that is automatically being assigned a value when adding new records. For example, if we add a new record, it will automatically assigned with value 1, and this will be incremented by 1 as new records are added. The following modifies our previous example to make the EmplyeeID as the identity column.
CREATE TABLE Employees ( EmployeeID int identity primary key, FirstName varchar(100), LastName varchar(100), Age int );
We simply used the identity keyword that will specify a column as the identity column.
You can use the following SQL Command to Delete an existing table.
DROP TABLE Employees;
This will delete the Employees table and all of its records.
We use the following syntax to insert records to a table.
INSERT INTO <Table Name> VALUES (<Value1>, <Value2>, ... , <ValueN>);
The <Table Name> is the name of the table where we want to insert our data into. Inside the parenthesis after VALUES, you list all the values for each of the field of the table. The order of data is important. You must follow the order defined when you created the Table. Let’s add some records to our Employee Table.
INSERT INTO Employees VALUES (1, 'John', 'Smith', 21); INSERT INTO Employees VALUES (2, 'Mark', 'Mayer', 23); INSERT INTO Employees VALUES (3, 'Alvin', 'Minsky', 27);
This will insert the 3 employees to our Employees table. Note the order of the parameters. Our table definition defines that the first parameter is the EmployeeID, the second is FirstName, LastName and then the last is the Age. Again, the EmployeeID must be unique because that is the primary key.
Another form of the INSERT TO statement allows you to explicitly indicate the columns that you want to add with values. The following only adds values to FirstName and LastName fields.
INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Smith');
Note that the number and type of values should match the number and type of fields that have been specified.
To delete a record, we used this syntax.
DELETE FROM <Table Name> WHERE <Condition>;
The condition tells what record you want to delete. Most of the time, you use the primary key field to delete a record. As an example, Let’s delete John from our table.
DELETE FROM Employees WHERE EmployeeID = 1;
The above statement reads as “Delete a record from the Employees table whose EmployeeID is equal to 1. Since John has an EmployeeID of 1, he is deleted from the table.
If you want to change a value of a field or fields of an existing record, you can do that using the following syntax.
UPDATE <Table Name> SET <Column> = <Value> WHERE <Condition>;
For example, if you want to change the FirstName of Mark, use the following code.
UPDATE Employees SET FirstName = 'Marco' WHERE EmployeedID = 2;
We set the FirstName field of the employee with EmployeeID that is equal to 2 to ‘Marco’. If you want to change the values of multiple fields, then you can separate them with commas.
UPDATE Employees SET FirstName = 'Marco', LastName = 'Miller' WHERE EmployeedID = 2;
We can use the SELECT statements to query or get data from our database. The syntax is as follows:
SELECT <Column1>, <Column2>, ... <ColumnN> FROM <TableName>;
For example, if we want to query all the FirstNames of the employee, we can write this code:
SELECT FirstName FROM Employees;
FirstName John Mark Alvin
You can select multiple columns just like this.
SELECT FirstName, LastName FROM Employees;
FirstName LastName John Smith Mark Mayer Alvin Minsky
You can also use the WHERE keyword to only select those that meet the condition.
SELECT FirstName, LastName FROM Employees WHERE FirstName = 'John';
FirstName LastName John Smith
To Select Everything from the table, you can use the * character.
SELECT * FROM Employees
EmployeeID FirstName LastName Age 1 John Smith 21 2 Mark Mayer 23 3 Alvin Minsky 27
You can arrange the result by using the ORDERBY keyword.
SELECT FirstName, LastName FROM Employees ORDERBY FirstName
FirstName LastName Alvin Minsky John Smith Mark Mayer
If you want to arrange the records in descending order, you can use the DESC keyword.
SELECT FirstName, LastName FROM Employees ORDERBY FirstName DESC
FirstName LastName Mark Mayer John Smith Alvin Minsky
SQL alone is a very big language. There is many more not discussed in this lesson. Now that you have learned the basics of SQL, we can now proceed to how we can access databases from our application.