vs2005+sql2005 asp.net2.0 Database connection

asp.net|sql|sql2005| Data | database | database connection

For the novice, the entire database connection is not easy, based on the problems in the connection process summarized below.

1. Open the SQL2005 remote connection function, the opening method is as follows:

The Configuration tool->sql the server perimeter configuration-> service and the perimeter configuration of the connection-> open the Database engine node under the MSSQLSERVER node, select Remote Connection, and then recommend "use tcp/simultaneously" IP and Named pipes ", and then restart the database service after you are OK.

2. The landing settings should read:

SQL Server and Windows authentication modes, as set out below:

SQL Server Management Studio Manager->windows Authentication connection Server-> Object Explorer Select your data server-> Right-click-> Properties-> Security->sql Server and Windows authentication mode is selected.

3. Set a user name and password in SQL Server mode:

The specific settings are as follows:

(1) SQL Server Management Studio Manager->windows Authentication connection Server-> Object Explorer Select your data server-> expand Security on the server-> login name-> Right-click-> on the sa account to select General-> to change the password for the sa login account. This sets a user named SA with the password: sa123456.

(2) "Select Page" SELECT Status-> login modified to enable.

4. Database connection string:

There are many types of database connection strings:

 
  
   
  Data server=.\sqlexpress;initial Catalog=northwind; User Id=sa; Password=sa123456data server= server name \sqlexpress;initial catalog=northwind; User Id=sa; Password=sa123456data server=localhost\sqlexpress;initial Catalog=northwind; User Id=sa; Password=sa123456data Server=.;i Nitial Catalog=northwind; User Id=sa; Password=sa123456data server= server name; Initial catalog=northwind; User Id=sa; password=sa123456 ...
 
  

That's right, this has to do with the database version, and if it's the SQL Server Express version, you have to have "\sqlexpress". And if the string is defined as a variable, VS2005 also adds a red wavy line underneath "\" to prompt You "\s is an unrecognized escape sequence," so if the string is defined as a variable, it should be written as server=.\\sqlexpress.

5. Register SQL Server database:

The ASP.net SQL Server Setup Wizard Wizard appears when you run the aspnet_regsql command under the path "C:\Windows\Microsoft.NET\Framework\v2.0.50727". Press two consecutive next step will appear SQL Server registration interface, fill in the database parameters you want to register. After the registration will be in your database a few more tables (figure):

6. Set the database connection string:

Open iis-> The Default Web site or the virtual directory where the site is located click the Right button to select Properties-> select ASP.net tab-> Edit configuration-> Edit the "localsqlserver" database connection string on the General page:

 
  
   
  Data server=.\sqlexpress;initial Catalog=northwind; User Id=sa; password=sa123456
 
  

7. Set Web.config file:

Add the following program to the Web.config file:

 
  
   
  <connectionStrings>  <add name= "LocalSqlServer" connectionstring= "Data source=.\sqlexpress;initial Catalog=northwind; User Id=sa; password=sa123456 "providername=" System.Data.SqlClient "/> </connectionStrings>
 
  

That's it.

Here's how to test:

1. Loose Connection test:

Loose connection test Use SQLDataSourceVS2005 Server Explorer to select a database click right-> Select Modify Connection-> fill in Server name-> use SQL Server Authentication to populate user name SA and password sa123456-> Select or enter a database name:northwind-> click Test Connection

Loose connection Test Connection success does not mean that the database is connected, at first I was hanging here, the loose connection test test connection success, but always unable to read the contents of the database to the page.

2. The rigorous connection test:

Select the Site menu->asp in VS2005. NET configuration into the ASP.net web Site Administration Tool-> Select provider-> Click AspNetSqlProvider Test, if the test is successful, the database set up completely correct, otherwise have to look at the beginning of the problem.