ASP. NET2.0 database entry SQL Server

Because Access doesn't really serve high-performance applications, a site that wants to have multiple simultaneous users must deploy a better data source than Access. This article describes how to get data from Microsoft SQL Server(an enterprise RDMS).

The SQL Server full version includes three parts. The first is the engine, which actually organizes the data and reads and writes to the command response. The second is a developer's tool package for manipulating databases, such as Query Analyzer and Data Transformation Services. The last is a tool for managing data, including backup procedures and replication patterns.

While full versions are invaluable for large enterprises, many developers don't need the full 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 that USES the T-SQL command to import patterns and data (osql.exe), it does not yet include the rich graphical tools available in the SQL Server full release. However, it is easy to use Visual Studio or Visual Web Developer to develop databases using SSE. SSE has a limitation that only local connections are processed (it is not possible to run SSE on a different machine than the Web server). SSE is a great choice for people with some hobbies and for students on the Web site. MSDE based on SQL Server 2000 May be used, while SSE is based on Yukon version of SQL Server.

Unless otherwise specified, all of the techniques described here can be applied to all three forms of SQL Server (full product, SSE, and MSDE), so the general term SQL Server includes all three forms.

Since SSE is just an engine and has no built-in development tools (as of 2004), other tools must be used 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 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 referred to as Server Explorer because it includes some additional functionality for handling non-database servers. We will also simply use 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 some familiarity with vocabulary. SQL Server is installed on a machine called a server (server) and can be referenced by its machine name. If SQL Server is on the same machine as the requester, the machine can be referenced with (local). The engine can be installed multiple times on a single machine, and each installation is called an instance (instance). SSE installs an instance named (local)\SQLExpress. Within an instance, you can create a database (database). The database has some tables (table) with fields (field) and records (record). The database also has a set of tables, fields, and constraints for data consumers, called views (view). Stored procedures (stored procedures, SPROC) can perform tasks on data. An SQL Server instance automatically installs an account with the username sa, which has permission to perform all actions on all objects when the user is authenticated with SQL Server. When using Windows authentication (also known as hybrid authentication), users logged into Windows also logged into SSE.

Preparation of before using SQL Server database

Spending time in the database will reduce errors when you design pages that use the data. Before writing a page that USES SQL Server, check that you have the following types of information:

· server, instance, and database names -- verify the exact spelling of the server name, instance, and database names. If there is only one instance of SQL Server in full on the server, the instance name is not required. But even if there is only one instance of SSE on the server, it must be explicitly referenced with MyServer\SQLExpress. Specify whether you are going to test the actual data or the deployment backup of the database.

· security information -- you need to know your user ID and password to authenticate access to the development database. Likewise, check whether SSE is certified by Windows or SQL (the installation in chapter 1 states that SSE should be certified by Windows).

· database schema -- understand database schema. Gets the exact spelling of the table and field names, automatically generated or locked fields, dependencies, and constraints. Pay careful attention to the underscores and Spaces in the object name. Look up from the database administrator whether to use tables directly or views or stored procedures (SPROC). Using the latter may require parameters of special data types. The SQL syntax for examining this metadata is listed later in this chapter.

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

connection string

The main syntax difference between the AccessDataSource and SqlDataSource controls is the way the database is used. For MDB, we simply provide the file name and path name, while SqlDataSource USES the connection string name that contains the server name, database name, and login information. The syntax for concatenation strings is different from the syntax we are familiar with in Visual Basic or C #, and is the reason many students make mistakes. A typical connection string is

ConnectionString="Server=MyServer; Database=MyDatabase;User ID=MyID; Password=MyPass"

The format of the multi-line connection string improves readability, as shown below:

ConnectionString="

Server=MyServer;

Database=MyDatabase;

User ID = MyID;

Password=MyPass"

First, let's talk about syntax. The entire string in the source code is in double quotes. When you specify a connection string in the VWD properties window, you do not need quotes; VWD will be added automatically. In between the quotes are paired messages in formats such as Criteria=value. A semicolon separates the information. Note that values do not need to be quoted. Also, note that even if the sentence sentence contains Spaces (for example, User ID), they do not require quotation marks or square brackets. This syntax is not difficult to understand, but because it differs from the VB, C#, and SQL languages, errors are common. Now let's take a moment to decompose this string.

There are two values in this connection string: the database identifier (server, instance, and database) and the security value (user ID, password, and installation Settings). The database identifier starts with the server value, which is the network name of the machine running SQL Server. In XP the machine name can be obtained by Start-> My Computer; Right-click and select Properties-> Computer Name tab- > Full Computer Name. In Windows 2000, you can right-click -> on My Computer on your desktop Properties- > Network Indentification). If you know that the database server USES the same machine as the Web server running ASP.NET, you can also specify the server name as (local) to indicate that the server is the local machine running the ASP.NET page. You can add instances with Instance=MyInstance. More generally, add an instance to the server name using MyServer \ My-Instance or (local)\MyInstance. You might also see periods (periods) used to represent local machines, for example.\SQLExpress.

By default, SSE installs its own instance. A separate reference (local) will cause a failure. You must use (local)\SQLExpress to reference SSE.

Security Settings are discussed later in this chapter. Now let's look at Windows Authentication, using the property IntegratedTrusted_connection=true instead of the user ID and password properties. SQL authentication requires two values: user=MyUserName; password=MyPassword, where MyUserName and MyPassword will be replaced by your certificate. This article USES Windows authentication.

If you are familiar with earlier versions of ASP, you may be wondering about the provider. The default provider for SqlDataSource is.NET Framework Data Provider for SQL Server. So there is no need to specify a provider in this chapter. The next chapter discusses specifying non-default providers for other databases.