Introducing ADO.NET and Basic SQL statements

Introducing ADO.NET

ADO.NET is the main data access system that Visual Basic 2008 uses to work with the data stored in the tables of databases. ADO.NET uses a disconnected data architecture, which means that the data you work with is just a copy of the data in the database. ADO.NET follows disconnected data architecture because of number of reasons. In traditional client/server applications, while an application is running, constant connection with the database maintained. However, maintaining these connections constantly consumes a lot of server resources. In case of the Internet, disconnected data architecture is maintained, instead of maintaining direct and continuous connections with the server to reduce the load on servers.

New Features in ADO.NET

• Supports SQL Server 2008 SqlClient: Provides support for new features of SQL Server 2008 Database Engine by introducing .NET Framework Data Provider for SQL Server (System.Data.SqlClient).
• Language-Integrated Query (LINQ): LINQ is an innovative technology and one of the components of .NET Framework 3.5, which adds native data-querying capabilities to .NET language by using syntax similar to SQL. This Implies that LINQ simplifies querying by eliminating the need to use a separate query language. LINQ to ADO.NET is a LINQ technology, which enable querying in ADO.NET by using the LINQ programming model. LINQ to ADO.NET consists of two related technologies. LINQ to DataSet and LINQ to SQL. LINQ to DataSet facilitates faster querying of data on the contents of a DataSet and LINQ to SQL enables you to directly query SQL Server database.
• LINQ to DataSet: LINQ to DataSet provides LINQ capabilities for disconnected data stored in a DataSet. LINQ to DataSet makes it easier and faster to query data cached in a DataSet object. DataSet objects are important as they allow users to use a copy of the data stored in the tables of a database, without actually getting connected to the database.
• LINQ to SQL: LINQ to SQL ia a component of .NET 3.5 Framework that provides a run-time infrastructure to manage relational data as objects. You can use the LINQ to SQL technology to translate a query into SQL supports all key functions that you like to perform while working with SQL .

Business is growing faster day by day and so is the need to store data. Data is a collection of raw information. A database is a collection of tables and each stores large amount of data logically, so that information can be accessed from the database quickly and efficiently whenever required. Some popular relational database are SQL Server, Oracle, and Microsoft Access.

Basic SQL Statements

SQL stands for structure Query Language. SQL is the standard language for accessing and manipulating data stored in databases. A database is a structured collection of records or data so that you can easily access, manage, and update a database. A database contains table for data storage. Tables stored data in columns and rows. Each column of a table represents a different attribute termed as a field. Each row of a table stores entries for all the attribute values for a single record. SQL is used to write query statements related to inserting new records and creating new tables and datasets that match some specific criteria. A dataset is an in-memory representation of data.

The SELECT Statement

The SELECT statement is used to retrieve values for some or all the field of a table. For example, you can write the following SQL statement to retrieve all the records from the Customers table by using the wildcard character*:

SELECT * FROM Customers

The preceding SELECT statement returns all the records from the Customers table. You Can also use the SELECT statement to retrieve values of specific fields of a tables. For example, you can retrieve values from the CustomerID, Address, and City fields of the Customers table by using the following SQL statement.

SELECT CustomerID, Address, and City FROM Customers

The WHERE Clause

In SQL, you can use the WHERE clause to specify a criterion that you want the records to satisfy. For example, to retrieve all the records in the Customers table, Where the City field value is London, you can use the following SQL statement.

SELECT * FROM Customers WHERE City = 'London'

The following operators in the WHERE clause:

• < (less than)
• <= (less than or equal to)
• > (greater than)
• >= (greater than or equal to)
• BETWEEN
• IN
• LIKE

The BETWEEN Clause: To access values that lie in a specified range. For example, if you want to select all the records from the customers table, where the range for the CustomerID field is specified as fields for which the first letter lies in the range R to T, use the following SQL statement:

SELECT * FROM Customers WHERE CustomerID BETWEEN'R*' AND 'U*'

The IN Clause: To match the values for a given field of a table with a set specified values. For example, to select those records for which the city field has a value London, Sao Paulo, or Madrid, use the following SQL statement:

SELECT * FROM Customers WHERE City IN ('London', 'Sao Paulo', 'Madrid')

The LIKE Clause: Used to match the values of a field in a table with some specified patterm. You can use the wildcard character % to specify the pattern. Use the following SQL statement:

SELECT * FROM Customers WHERE City LIKE 'Lo%'

Lo% Matches the values starting with Lo, such as London or Los Angeles

%Lo Matches the value ending with Lo, such as Sao Paulo

%Lo% Matches the values containing Lo anywhere, such as Barelona

The different logical operators that you can use with the WHERE clause are as follows:

• AND: Specifies that both conditions in the WHERE clause must be TRUE
• OR: Specifies that at least one of the conditions in the WHERE clause should be TRUE
• NOT: Specifies that the condition in the WHERE clause should be FALSE.