The Basics of Using SQL in ASP.NET

XXXXXXXX

As you can see from the title, what we’re exploring here are the basics of SQL – Structured Query Language. Naturally, this won’t, then, be an exhaustive tutorial on SQL – that would require a book – and there are many already out there.

With SQL, we can query databases. It doesn’t matter whether the database is SQL Server, MS Access, MySQL, Oracle or any other. It just requires that the database is sql compliant, which, naturally, most commercially available databases these days, are.

The recommended methd for querying databases, in order to stop injection attacks, is parameterized queries….we won’t go into that in this tutorial, since a tutorial on that subject already exists.

The 4 basic types of queries we’re going to explore are:
Select
Insert
Update
Delete
Along with these 4 types, we’ll address a couple of clause types (Where and Order by)

Select Statement
I’d say that arguably, the most common use of SQL on the web is the SELECT statement. With the SELECT statement, we select data from the database and return the results to the user. The two most common forms are what I call the pure Select statement, and the qualified Select Statement. Here’s an example of a Select Statement in it’s purest, or most simple form:

Select FirstName, LastName, Phone from TableName

This query will return all the Firstname, LastName and Phone number for each record in the database, no matter how many records are in the database.

The other Select Statement I listed is the ‘qualified’ Select Statement. An example of this would use a ‘Where’ clause, like this:

Select FirstName, LastName, Phone from TableName’ Where LastName=’Jones’

As you probably expected, this will only return the FirstName, LastName and Phone for those records where the LastName is ‘Jones’.

Where clauses can contain multiple filtering sections also, using either ‘OR’ or ‘AND’. An example would be

Select FirstName, LastName, Phone from TableName’ Where LastName=’Jones’ OR LastName=’Smith’ OR FirstName=’Jim’

Another example would be:

Select FirstName, LastName, Phone from TableName’ Where LastName=’Jones’ AND FirstName=’Jim’

Insert Statement
Here, we would take user input and INSERT it into the table. We’ve all filled out forms with TextBoxes, RadioButtons, Checkboxes, etc. The insert statement takes the data the end-user supplies in the form, and inserts it into the table. For example:

Insert into TableName (FirstName, LastName, Phone) Values (‘Jim’, ‘Jones’, ‘555-1212′)

In a parameterized query, it would look like this:

Insert into TableName (FirstName, LastName, Phone) Values (@FirstName, @LastName, @Phone)

Then, in the parameter section, we would assign the values of the form field contents to the parameters (@FirstName, @LastName, @Phone)

Update Statement
In an Update Statement, we would take existing data and Edit/Update that data in the table. Remember that an INSERT statement is used to Insert NEW data, while the Update statement is used to EDIT data. An example of this would be:

Update TableName Set FirstName=@FirstName, LastName=@LastName, Phone=@Phone where id=@ID

It is VERY important that you notice the WHERE clause in the Update statement. If you don’t include a where clause in the statement, it will update EVERY record with the same information you supply in the statement! Though sometimes, there are times we would possibly like to update every record with the same information (in one or more fields), the most common useage is editing/updating ONE record at a time. If you are not careful, and forget to use a WHERE clause, you could give everyone in the table the same First & Last Name!

Delete Statement
The last statement at which we’re going to look is the Delete statement. Here, like with the Update statement, a WHERE clause is EXREMELY important! But, since we’re deleting records, without a WHERE clause, you could possibly delete EVERY record in the TABLE!!!

Here is an example of a Delete statement:

Delete From TableName where ID=@ID

The only difference is that, if you are using earlier versions of MS Access, you would need to do it like this:

Delete * From TableName where ID=@ID

Where Clauses
There are several types of operators that may be used in WHERE clauses. Up until now, all we’ve looked at is ‘=’. However, in Numeric or Date type fields, you can use the normal programming operators like >, <, <=, >=. Also, a very powerful type of WHERE clause, uses ‘LIKE‘. Here’s an example:

Select * from TableName where LastName Like ‘FA%’

Here, the percent sign (used with SQL Server – the asterisk is used in early versions of MS Access) is used as a wild card. Therefore, this will return all records where the Last Name starts with ‘FA’. If we put the percent sign (or asterisk) at the beginning of the ‘FA’, then, it would return all records where the Last Name ends with ‘FA’. Again, check out the Parameterized Query tutorial on how to use this more extensively.

Order By Clauses
Order By clauses are great for sorting the data returned from your query. Using this clause, you can sort by any field in the table. Here’s an example:

Select * from TableName Order By LastName

This will list all returned records, sorted by Last Name, alphabetically. By not including ‘ASC’ (ascending order) or ‘DESC’ (descending order)after the Order By clause, it uses the default of ‘ASC’ If you want your data returned in reverse order, you would use:

Select * from TableName Order By LastName DESC

This also works with numeric and date field datatypes.

As you can see, we’ve hit the ‘highlights’ of SQL statements in this tutorial. But it can get much more intricate and more complicated. Hopefully, this will give you a basic foundation of understanding and will get you started using SQL to query databases online!

Related Posts:

• No Related Posts