Thursday, January 20, 2011

[ASP.Net] - Use SQlDataSource on ASP.net pages


1. Introduction


In this article, we are going to see how we use the SQLDataSource server side control. Also, we will have a look at Grid that displays the table rows. Have a look at the below page:



The combo box is displaying the publisher's name from publishers table of the pubs database. The grid displays the employee names of the selected publishers. We are going to do that without writing any single piece of code. Download the videos which visually describes the steps and you if have enough experience those videos are not required.

2. Pubs and NorthWnd Db [Video: 001_SetupDB.avi]


The downloaded source code has two SQL scripts one for the Pubs database and the other one for NorthWnd Database. Launch your SQL Server 2005 and run these scripts in the query editor window. One you are done, you had both Pubs and NorthWnd database with you. This example uses the Pubs database.

Beginners: Watch the video 001 for the first step.

3. Create a New Website [Video: 002_NewSolution.avi]


Create a new website and name it DataSource. I hope almost everybody knows this step. However, this step also captured as video and you can refer it to know how to create a new Website.

4. Add DataSource control and Configure [Video: 003_AddDataSource.avi]


1) First, we need a way to connect to the pubs database. We can do that by using the SQLDatasource control. From the toolbox expand the data group. You will find SQLDatasource from the expanded node. Drag and drop the control in the Design area of the web form. This is an invisible control. Once placed access the Quick Task menu by clicking the small arrow button at the top and the select the link Configure Data Source. The below Picture is shown for guiding.



2) This will bring chose you data connection. Click the New Connection button as we are going to create a connection to the SQL Server database to connect the Pubs database. In the Add Connection dialog type the server name where the database pubs are running. In my case it is localhost. Then after giving the SQL authentication username and password (sa account) select the pubs database from the database list. Click OK once you selected the Pubs database.

3) Once this done, move to the next step in the wizard by clicking the Next button. Change the connection string to ConnectionStr and click the next button. The check mark allows you to save the connection string to the web configuration file. Click next to proceed.

4) Now you are in Configure Select Statement step. First, select the Publishers table from the Name Combobox. This is the table that will populate the publishers’ name in the combo box of our example. Once the publishers table is selected the list of columns is displayed in the below list box below the combo box. Select pubid and pubname in the columns then click on the finish button.

Now we configured the SQLDataSource to get the required information for the combobox from the Publishers table of the pubs database. All these steps are visually covered in the Step 003 video.

5. Combobox with Publishers [Video: 004_AddCombobox.avi]


Here, we will add a ComboBox to our web form and link that combo box to the previously created Datasource control. Expand the standard toolbox and drag and drop the Dropdown list control to the Web form. Then from the Quick Task select the Auto Post back option. The combo box will inform the grid to show the list of employees those belongs to the selected publishers. So when we make a change in the Combo box selection the changed data is sent to the Server from the client machine and server will take the appropriate action.

Select Chose datasource from the quick task. From the dialog displayed select the datasource created in the previous step and select the fields as shown in the below picture. So, now the combo box displays the publishers name and sends the publisher id to the server.



6. Adding Grid view for Employee display [Video: 005_AddDataGrid.avi, 005a]


1) Our combobox is ready! We need to set up the Grid view control for the Employee table. From the view, menu brings up the server explorer. If the data connection is not available in the server explorer create the one to our Pubs database. Once the connection is created expand the table folder and drag&drop the employee table to the design surface. Using the quick text menu set the auto format style that you like

2) From the Quick task menu, select the "Configure DataSource" menu option.   From the displayed dialog select the connection string that was already configured by us. That means, Select ConnectionStr. Click next, select the employee table and select the columns except job_id and job_lvl.

3) Click the where clause button. The dialog shown below is displayed:



Perform all the six steps shown in the dialog box. Here we have added the where condition to the Employee selection used by the grid view. Here, the where condition is dynamically changed by the combo box item publisher. Look at the SQL Expression: [Pub_id] = @pub_id. The @pub_id is the value passed to the server by the combo as it has the auto box set already. Remember, the display field of the combo box is Publisher name. But, actual value it stored along with that is publisher id. So when we select a different publisher name in the combo box, the publisher id is sent to the server. Here, @pud_id has referred such an id.

4) From the Quick list select Edit Columns options, and from the displayed dialog select the fields from the available fields list and add it to the Selected field list box. The dialog is shown below. Selected fields are the one that displayed in the Grid view.



7) Run the Web page [Video: 006_RunApp.avi]


1) Right-Click the default.aspx (if the name is not changed) and right click on it.
2) Select View in Browser option.
3) From the displayed page, select different publishers.

Note:
1) The application is created on VS 2005 IDE if you have latest version say OK to the conversion dialog.
2) The video is attached to the external source. Click here to download the videos. The download is RAR archive; unzip it to get all the videos. You need WinRAR application to unzip the files. I do have a version downloaded from 4shared.
3) View the videos in Windows media player with the full-screen option and 1024x768 resolution.

Supporting videos for this article: Download

Source Code : Download

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Firend :)