Connecting to a SQL database from ASP .NET

Muhammad Faizan khan (MFKJ)

advertisment Get 40 Ahadith Collection on different topics  Advertisement This tutorial (divided in two parts) will show you how to connect from an ASP .NET web application to a SQL database using SQL authentication. To accomplish this you need to follow a series of steps, first to change some of the settings of SQL Server 2000 and create a database and a user and then to actually connect to the database using ASP .NET with Visual Studio .NET. I suppose you're going to use the MSDE 2000 (Microsoft SQL Server 2000 Desktop Engine) - the free version of the database. First set up the database using the tutorial named 'Setting up MS SQL Server 2000'.

SQL Membership Provider and aspnet_regsql.exe Utility

(How to add User Management Tables in Our Database or in SQL server)

The aspnet_regsql.exe runs the ASP.NET SQL Server Setup Wizard which allows you to create or configure a SQL Server database to store information for membership, profiles, role management, personalization, and SQL Web event provider. Once created, you may use forms authentication and the login controls to access these tables.

In the Microsoft.NET/Framework/... directory as shown below, find the aspnet_regsql.exe.

Execute the aspnet_regsql.exe file to begin the ASP.NET SQL Server Setup Wizard to create the SQL Server database or add tables to an existing database:

Select to Configure SQL Server for application services:

Select a Server name for Windows authentication and then select an existing database name or use the default. Choosing the default will name the database "aspnetdb."

If you want to use an existing database, simply select the database:

The wizard will now summarize, run and create the database.

After completion, you may use the Database Explorer to view your tables:

When done creating the database, you would setup your web config file with the connection string to the aspnetdb.mdf. You would also make sure your authentication mode is set to forms: authentication mode="Forms" (see below).

Once you've completed the connection string, you will want to setup a user in your tables. To do this, you may use the ASP.NET Configuration under the Website menu item.

You may use the Security tab to add and edit users.

Select the Create User link to create a user. Select the "Administrators" checkbox if you wish this to be an administrative account. Once your Admin account is created, others may use the CreateUserWizard in your application in order to create accounts.

To add Log in and User Creation to your website, use the Login Controls.

Using the Login and CreateUserWizard controls will automatically create the code and forms as shown below:

Log In User Name: Password: Remember me next time.

Sign Up for Your New Account User Name: Password: Confirm Password: E-mail: Security Question: Security Answer: