SQL stands for Structured Query Language. It is a programming language designed to allow the viewing and manipulation of data within a database (Wikipedia – SQL, 2006). There several proprietary versions of the Structured Query Language, primarily because each major database system vendor has created their own version. For instance, Microsoft has created Transact-SQL or T-SQL which is used to interact with SQL Server databases (Wikipedia – Transact-SQL, 2006) and Oracle has created SQL*Plus which is used to interact with Oracle databases, but all versions have certain similarities and share syntax to some extent (Basic Introduction to SQL*Plus, 2006). Understanding the fundamentals of SQL is typically all that is needed in order to understand and use SQL statements.
A SQL statement is a string of text, which is interpreted to build a command that is run against a database or database system. There are many SQL commands available, which allow you to perform a variety of actions. For instance, using various SQL statements, you can view, edit, and delete data within tables as well create and delete entire databases. The four most common SQL commands used to query databases are SELECT, INSERT, UPDATE, and DELETE. Other commands allow you to perform complex statements, such as the JOIN command, which allows running queries against multiple tables simultaneously. A typical SELECT statement specifies a column or columns and a table or tables using a JOIN statement. You can also use keywords such as WHERE and LIKE to specify parameters, which limit the data that is returned.
The following SQL statement would return all rows of data from a table called tblUsers where the fldFirstName is equal to “John”.
SELECT * FROM tblUsers WHERE fldFirstName = ‘John’
If I were interviewing an ASP.NET programmer as a candidate for an ASP.NET development position, I would most likely expect them to have a working knowledge of SQL and at least one major database system such as SQL Server or Oracle. ASP.NET is used primarily to create dynamic web applications, many of which interact with backend database systems. It is important that the developer of an ASP.NET web application be accustomed to working with databases. This will most definitely require knowledge of SQL (W3Schools – Web Building, 2006).The main difference between a web designer and a web developer is that a web designer focuses primarily on the interface design of a website; therefore, they would not necessarily need experience programming in ASP.NET or SQL. However, a web developer is typically responsible for creating the code, which makes a website functional and dynamic. By virtue of the term “ASP.NET programmer”, it is assumed that the duties, which would fall under such a title, would include web development as well as web design. I would expect any seasoned web developer to have experience writing applications, which interact with databases using SQL statements.
Resources:
Wikipedia – SQL. (2006). Retrieved on July 19, 2006 from http://en.wikipedia.org/wiki/SQL
Wikipedia – Transact-SQL. (2006). Retrieved on July 19, 2006 from http://en.wikipedia.org/wiki/Transact-SQL
Basic Introduction to SQL*Plus. (2006). Retrieved on July 19, 2006 from http://www-it.desy.de/systems/services/databases/oracle/sqlplus/sqlplus.html.en
W3Schools – Web Building. (2006). Retrieved on July 19, 2006 from http://www.w3schools.com/site/site_intro.asp
ADO.NET is a newer version of ADO or ActiveX Data Objects. Microsoft created the ADO object model as a way to connect to databases from within programs (Wikipedia – ActiveX Data Objects, 2006). Although ADO.NET is based on ADO, there are some significant differences. Most of the changes made to ADO.NET are improvements such as better XML integration, disconnected data access, and .NET Framework integration (MSDN – ADO.NET for the ADO Programmer, 2006). ADO.NET is used to connect to a variety of different data sources such as MySQL and MS SQL Server. The data can be viewed and manipulated in different ways. For instance, it is possible to view, update, and delete data using different queries (MSDN – Overview of ADO.NET, 2006). The advantages of using ADO.NET are its improvements over ADO as mentioned above, as well as performance and its ease of implementation (ADO.NET Pros and Cons, 2006).
ADO.NET consists of two main components: the Data provider and the DataSet. The Data provider objects are classes that are used to connect to and communicate with a database. Five common objects are used as part of the Data provider. The Connection object is used to connect to a data source. The Command object is used to run a command against a data source, such as a query. The Parameter object is used to send a parameter with a command, such as a stored procedure. The DataAdapter object is used to send data back and forth between the DataSet and the data source. The DataReader object is used to process a large amount of data but only one record can be read at a time. The DataSet is the other main component of ADO.NET. DataSet objects contain classes, which represent an actual database and the data within it. A DataSet object can contain other objects such as a DataTable, a DataRelation and a Constraint. The DataTable object can contain objects such as a DataColumn and a DataRow. All of these objects are used to represent the data and structure in a database (Wikipedia – ADO.NET, 2006).
Knowing how to take advantage of the features of ADO.NET is essential when developing business applications. Many business applications contain or communicate with a backend database. Some standalone applications might include with it a small Access or dBase database. For instance, a bug tracking program or a low-end customer relations management application would need to store information in a database. That information may need to be viewed and/or updated. The application would be designed to limit the control that users have over the backend database. If necessary, a custom application could be developed using .NET to connect to the backend database, which could offer more or less functionality based on the needs of the users. ADO.NET features could be used in the custom .NET application to provide access to the data within the database. Some larger organizations might have huge database systems that contain much more information than a smaller Access database would be able to support. These organizations require higher end database systems, such as Oracle or MS SQL Server. ADO.NET could be used within a client/server .NET application to connect to these higher end systems as well (W3Schools, 2006).
Resources:
Wikipedia – ActiveX Data Objects. (2006). Retrieved June 3, 2006 from http://en.wikipedia.org/wiki/ActiveX_Data_Objects
MSDN – ADO.NET for the ADO Programmer. (2006). Retrieved June 3, 2006 from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/adonetprogmsdn.asp
MSDN – Overview of ADO.NET. (2006). Retrieved June 3, 2006 from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconOverviewOfADONET.asp
ADO.NET Pros and Cons. (2006). Retrieved June 4, 2006 from http://www.vbip.com/books/1861005563/chapter_5563_06.asp
Wikipedia – ADO.NET. (2006). Retrieved June 4, 2006 from
http://en.wikipedia.org/wiki/ADO.NET
W3Schools – ASP.NET – Database Connection. (2006). Retrieved June 4, 2006 from http://www.w3schools.com/aspnet/aspnet_dbconnection.asp
Microsoft SQL Server is a high performance, scalable and secure database management system available for Microsoft Windows systems. The most recent release of MS SQL Server is available in four different versions. The Express Edition, which is essentially a newer release of the Microsoft SQL Server Database Engine (MSDE), is free to use and redistribute as an extremely lightweight version of Microsoft SQL Server. The Express Edition is easy to install and offers enhanced security. As far as scalability and performance is concerned, the Express Edition only supports one CPU and one gigabyte of RAM. In addition, databases are limited to four gigabytes in size (Microsoft – SQL Server Express Edition Features, 2006).
If high performance and scalability are desired, it may be best to go with the Workgroup Edition, the Standard Edition, or the Enterprise Edition, depending on actual requirements. Each of these versions has no limit with regard to database size. The Workgroup Edition supports up to two CPUs. The Standard Edition supports up to four CPUs. The Enterprise Edition has no limit to the number of CPUs that it supports. The Workgroup Edition supports up to three gigabytes of RAM. Both the Standard and Enterprise Editions support as much RAM as the operating system can handle. The Enterprise Edition also supports features such as partitioning and indexed views, which are not available in other versions. All versions, including the Express Edition offer the same security options (Microsoft – SQL Server 2005 Features Comparison, 2006).
The pricing for MS SQL Server is broken down by license type and version. Each version is priced differently according to overall features and there are two types of licensing available for each version. The two types of licensing are Processor Licensing and Client Access Licensing. Processor licensing is broken down by how many processors the server will contain. Client access licensing is broken down by the number of clients who will access the server. The only exception is with the Express Server version, which is free. Client Access Licensing can be further broken down by either users or devices. Either way, the pricing is the same (Microsoft – SQL Server: How to Buy, 2006).
Here is a sample breakdown of the prices for different versions:
|
Version |
Processor Licensing (Retail) |
Client Access Licensing (Retail) |
|
Workgroup |
$3,899 |
$739 with 5 Workgroup CALs |
|
Standard |
$5,999 |
$1,849 with 5 CALs |
|
Enterprise |
$24,999 |
$13,969 with 25 CALs |
Resources:
Microsoft – SQL Server Express Edition Features. (2006). Retrieved May 14, 2006 from http://www.microsoft.com/sql/editions/express/features.mspx
Microsoft – SQL Server 2005 Features Comparison. (2006). Retrieved May 14, 2006 from http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Microsoft – SQL Server: How to Buy. (2006). Retrieved May 14, 2006 from http://www.microsoft.com/sql/howtobuy/default.mspx
