Working with Data—Introduction to SQL

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

CSharp-Online.NET:Articles

Database Articles

Working with Data Databases Intro to SQL ADO.NET Classes Roll Your Own Database Define Table Structure Reading Rows Adding Rows Storing as XML Filtering Data Connect to Access Connect to SQL Server Data Binding Using DataGridView

© 2006 Jeffery Suddeth

Contents 1 Introduction to SQL 2 The INSERT Statement 3 The SELECT Statement 4 The UPDATE Statement 5 The DELETE Statement

Introduction to SQL

Most databases use a language called SQL, Structured Query Language, to process their commands. While SQL (commonly pronounced "sequel") is a large topic, the basics are easily grasped by looking at a few statements. The basic operations for any database are to add, retrieve, update, and delete records. The SQL language supports these operations with the following four statements.

The INSERT Statement

The INSERT statement will add new records to a table. To insert a record, you must specify the name of the table and the values for the fields. The following statement inserts a new record into the Customers table of the Northwind Trader database.

INSERT INTO Customers ( CustomerID, CompanyName )
VALUES ('SUD100', 'Jeff Suddeth')

The INSERT statement takes the name of the table that you want to insert a record into. Then, in parentheses, you must specify the list of fields you are setting values to. After the VALUES keyword, there is another set of parentheses that include the list of values to be assigned to the fields. The statement above creates a new Customers record and sets the CustomerID field to "SUD100" and the CompanyName field to "Jeff Suddeth". All other fields will be null unless the field has a default value defined.

The SELECT Statement

Use the SELECT statement to retrieve a set of records based on the criteria you specify. The SELECT statement may return zero or more records depending on the conditions of your selection. The following statement does not specify any selection criteria. It will return the CustomerID and CompanyName fields from every row in the table.

SELECT CustomerID, CompanyName
FROM Customers

To add search criteria to the SELECT statement, you can use the WHERE clause. The WHERE clause takes a comma separated list of conditions. The following select statement will select the record we added above.

SELECT CustomerID, CompanyName
FROM Customers'''
WHERE CustomerID = 'SUD10' 

The UPDATE Statement

Use the UPDATE statement to modify existing records. To use the UPDATE statement, you must specify the table you want to update, the fields that you want to change the values of, and the new values for those fields. The UPDATE statement uses a WHERE clause to determine which records the update will apply to. The next statement updates the record we previously added by setting the Contact Name field.

UPDATE Customers SET ContactName='Jeffery'
WHERE CustomerID='SUD10'

You can update multiple fields in the same statement, separating the assignments by commas.

UPDATE Customers
SET ContactName='Jeffery', ContactTitle='Peon'
WHERE CustomerID='SUD10'

The DELETE Statement

To delete a record you use the DELETE statement. The DELETE statement needs to know the name of the table you want to delete from and the criteria for the delete. The delete statement can remove zero or more records depending on the criteria of the where clause in your statement.

DELETE FROM Customers
WHERE CustomerID = 'SUD10'

There is a lot more that you can do with SQL, such as sorting selected records, grouping, and performing aggregate functions; but, these basic four operations are enough to get us started using a database from C# code. Let us now examine the set of database classes collectively known as ADO.NET, which you will use to access the database.