ASP.NET Represent a SQL database in Web Page

You can quickly represent a SQL database in a web page using SqlDataSource. Using SqlDataSource control you can write a database-driven web page without writing a single line of code.
  • 2268
Download Files:
 
ARTICLE
 

Quickly represent a SQL database in a web page

Posted by Gurjeet Singh  Articles | ASP.NET using VB.NET  June 20, 2011
 
You can quickly represent a SQL database in a web page using SqlDataSource. Using SqlDataSource control you can write a database-driven web page without writing a single line of code.
 
 
Reader Level:
 
Download Files:
 

 

 

You can quickly represent a SQL database in a web page using SqlDataSource and it is used Microsoft SQL server by default. Using SqlDataSource control you can write a database-driven web page without writing a single line of code.SqlDataSource control represents connection to database and SQL commands.

SqlDataSource control enables you to uses ADO.NET objects such as Connection, Command, DataSet, DataReader and DataAdapter.

SqlDataSource control is an invisible control, it does not render any thing but it is used by other control like GridView, DetailView, ListView, FormView and so on to display and do modification in database data.

In this example, I am using GridView to display data. SqlDataSource get data from database and pass to other controls.

Step 1: First add GridView and SqlDataSource control to page.

1.gif

Figure 1

Step 2: 
Click on right top of SqlDataSource control than click on Configure Data Source.
Step 3: Click on New Connection button.

2.gif

Figure 2

Step 4: 
Enter SQL Server name, Select use SQL Server Authentication, Enter user name and password and select your database name from drop down list.

3.gif

Figure 3

Step 5:
 Click on Test Connection to know connection is established properly or not. The following message box is displayed.

4.gif

Figure 4
 

Click on OK button in Test connection.

Step 6: Click on OK button in Add Connection. 

Step 7: Expand connection string, as shown in the following figure.

5.gif

Figure 5

Step 8:
 Click on Next button

Step 9: First check, Yes, save this connection as:

Step 10: Enter the name that you want to save the connection string in web configuration file, as shown the following figure. Click on Next button.

6.gif

Figure 6

Step 11: 
Select Specify a custom SQL statement or stored procedure option and click on Next button, as shown the following figure.

7.gif

Figure 7

Step 12: 
Click on Query Builder button, as shown the following figure.

8.gif

Figure 8

Step 13:
 Select the Employee table form Add Table and click on Add Button, as shown the following figure. After adding the Empoyee table on Query Builder, click on Close button on the Add Table.

9.gif

Figure 9

Step 14:
 Select the columns from employee table and click on Execute Query button to know that query is execute properly than click on OK button, as shown the following figure.

10.gif

Figure 10

Step 15: 
Click the Update option from Define Custom Statements or Stored Procedure, as shown the following figure.

14.gif

Figure 11

Step 16: 
Click the Query Builder button, as shown the following figure.

15.gif

Figure 12

Step 17: 
Select the Employee table from Add Table and click on Add button as shown the following figure.

13.gif

Figure 13

Step 18: 
Select the columns form Employee table in Query Builder, as shown the following figure.

14.gif

Figure 14

Step 19: 
Those column you want to update, enter the value in New Value with sign and id column value set in Filter column as shown the following figure.

15.gif

Figure 15

Step 20: 
Click on Execute Query button to know query work properly or not and you will shown the following figure. Click on OK button.

16.gif

Figure 16

Step 21: 
Click on OK button in Query Builder, as shown the following figure.

17.gif

Figure 17

Step 22: 
Click on INSERT option in Define Custom Statement or Stored Procedure. After that click on Query Builder button as shown the following figure.

18.gif

Figure 18

Step 23: 
Follow step 17 and 18. After completed step 17 and 18 click on OK button in Query Builder, as shown the following figure.

19.gif

Figure 19

Step 24: 
Click on DELETE option. Click on Query Builder, as shown the following figure.

20.gif

Figure 20

Step 25:
 Select id column to verify by WHERE clause, as shown the following figure.

21.gif

Figure 21

Step 26: 
Click the OK button. The Configure Data Source as shown the following figure.

22.gif

Figure 22

Step 27: 
Click the Next button.

Step 28: Click the Test Query. To know query properly generated or not. If it will display data it means query work properly, as shown the following figure.

23.gif

Figure 23

Step 29: 
Click on Finish button.

Step 30: Click on right top corner arrow and select the SqlDataSource. After selecting the SqlDataSource employee table added to GridView.

24.gif

Figure 24

Step 31: 
Set the True value of property AutoGenerateDeleteButton, AutoGenerateEditButton, AutoGenerateSelectButton and set EMP_ID value in DataKeyNames property. DataKeyNames property can associate a value with each row in a GridView. You can assign the name of a single column or list of column names using comma separated, as shown the following figure.

25.gif

Figure 25

Press F5 to execute, see the below output:

26.gif

Figure 26

If you click on Edit button we will see the difference. Edit button change into Update button, as shown the following figure.

27.gif
  

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.