asp.net2.0 database Getting started with SQL Server

Because Access does not really serve high-performance applications, a site that wants to have multiple simultaneous users must deploy a data source that is more appropriate than access. This article describes how to get data from Microsoft SQL Server (an enterprise-class RDMS).

The SQL Server full edition includes three sections. The first is the engine, which is used to actually organize the data and read and write to the command response. The second is a developer's tool package for manipulating the database, such as Query Analyzer and Data Transformation Services. The last is a tool for managing data, including backup programs and replication modes.

While the full version has immeasurable benefits for large businesses, many developers do not need a complete set of tools. Fortunately, Microsoft offers a free version of the SQL Server engine, called SQL Server Express (SSE). Although it has a command-line tool (Osql.exe) that uses T-SQL commands to import patterns and data, it does not contain rich graphical tools that are available in the full version of SQL Server. However, you can easily use the SSE development database using Visual Studio or Visual Web developer. SSE has a limitation of handling only local connections (it is not possible to run SSE on a different machine than the Web server). SSE is a great choice for people with certain hobbies and for students ' web sites. You may use MSDE based on SQL Server 2000, and SSE is based on the Yukon version of SQL Server.

Unless otherwise specifically stated, all of the techniques described here can be applied to three forms of SQL Server (full edition products, SSE, and MSDE), so the common term SQL Server contains these three forms.

Because SSE is just an engine and does not have a built-in development tool (up to 2004), you must use other tools to create, modify, or populate the database. The main tool we will use for this is Visual Studio or Visual Web Developer, which provides database Explorer as part of its interface. Note that the database exporer can change the data and schema in the local database, but it can only change the data (not the structure) in the remote database. In Visual Studio, Database Explorer is called Server Explorer because it includes additional functionality to handle non-database servers. We will also begin by simply using the Osql.exe command-line tool at the beginning of this book to import the initial database that will be used throughout this book. The third way to modify the database structure is to open access and then link an external table in the SSE database.

Using SQL Server requires familiarity with some vocabulary. SQL Server is installed on a machine called server, and can be referenced by its machine name. If SQL Server is on the same machine as the requesting software, the machine can be referenced by (local). The engine can be installed on a single machine multiple times, and each installation is called an instance (instance). SSE installs an instance named (local) \SQLExpress. Within an instance, you can create a database. The database has a table with fields (field) and records (record). A database also has a set of tables, fields, and constraints for use by data consumers, called views. Stored procedures (stored procedures,sproc) can perform tasks on data. An instance of SQL Server automatically installs an account with user name SA, which has permission to perform all actions on all objects when using SQL Server authentication for the user. When using Windows Authentication (also known as hybrid authentication), users who log into Windows also log in to SSE.

Preparation before using SQL Server database

Taking the time to study the database will reduce the error when you design pages that use data. Before you write a page that uses SQL Server, check to see if you have the following types of information available:

· Server, instance, and database name--confirm the exact spelling of the server name, instance, and database name. If you have only one instance of a full version of SQL Server on the server, you do not need to use the instance name. However, even if there is only one instance of SSE on the server, it must be explicitly referenced with myserver\sqlexpress. Make it clear whether you want to test the actual data or the deployment backup of the test database.

· Security information-you need to know your user ID and password to authenticate access to the development database. Similarly, please check whether SSE is using Windows Authentication or SQL authentication (the installation in Chapter 1th has stated that SSE should use Windows authentication).

· Database schema--Understanding the schema of the database. Gets the exact spelling of the table and field names, automatically generated or locked fields, dependencies, and constraints. Carefully note the underscores and spaces in the object name. Find out from the database administrator whether you are using a table directly or using a view or stored procedure (SPROC). Using the latter will probably require parameters for special data types. The SQL syntax used to check these metadata is listed later in this chapter.

· Test the SQL statement (optional)-You may suspect the syntax or logic of the SQL statement. You will find that the effective way to test statements is to use the development tools of Query Analyzer such as SQL, rather than testing them for the first time they appear on the ASPX page.