Pages

Display data from SQL database using GridView

Here i will explain how to display data from SQL database using asp.net GridView control.

Define GridView

You can either define GridView by typing or simply by dragging the same from toolbox in Visual Studio (Referred as VS hereafter) . Go to Toolbox > Data and drag the control on the page (You can also double click on the item)
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

Define and Configure SQL Data Source

You will find SqlDataSource control in the same category as that of GridView. (Toolbox > Data > SqlDataSource). Drag or double click the same.
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server">
</asp:SqlDataSource>

SqlDataSource is the key control here. It fetches data from SQL Server database by using the provided connection information and SQL Query. I assume you are aware of writing basic SQL queries at least SELECT statements! If not, do not panic, continue reading :).

Now its time to actually configure above data source so that we can get the data that is to be displayed by GridView control. At the bottom of the page window (in VS), you will see three tabs namely Design, Split and Source. Click on Design or Split tab. Select the control 'SqlDataSource - SqlDataSource1'. A small arrow button will appear, click it. (Do not think about dummy data which is displayed in GridView control, it will display exactly the same data that we are going to fetch!)

Figure-1

Now click on 'Configure Data Source...' option. This will start data source configuration wizard.

Figure-2

Click 'New Connection...' button and it will open up 'Add Connection' child window. Choose proper server and database name from the respective dropdown lists. You do not need to change other settings. You can 'Test Connection' if you wish before proceeding. Click OK.

Figure-3

Checking/Unchecking this option will not play much role for this sample. But in real applications you should leave this option checked. What it does is, saves the generated connection string value to web.config file with a key shown in Textbox (Is of the form DatabaseNameConnectionString! Or you can give it any name). So that you can use same connection string with multiple data sources in application that use same database. Also storing in web.config makes it easily configurable without any modification to the code. Lets keep that option Checked. Click Next.

Figure-4

Select the table you want to fetch data from. You can select all (*) or specific columns. Click Next. On next screen You can either 'Test Query' or simply hit Finish button.

Set GridView control DataSourceID property to "SqlDataSource1". Now you should see the markup as below:
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>
        
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
     ConnectionString="<%$ ConnectionStrings:AspNetForumsConnectionString %>" 
     SelectCommand="SELECT * FROM [tbl_sample_grid_data]">
</asp:SqlDataSource>

Run the application.


DONE!

14 comments:

  1. i found this code very informative for me in knowing how to configure the sql data source. thank you.

    ReplyDelete
  2. the query is running fine but when i run on browser the table doesnt display. Plz help!

    ReplyDelete
    Replies
    1. The only possible reason I guess, you might have not assigned SQL data source control id to grid view. Please make sure you are doing it exactly the same way its shown above.

      Also make sure that your query result has data and not a blank table, in which case grid view will not be displayed.

      Delete
  3. Thanks a ton bro
    Nothing would have been simpler than this :)

    ReplyDelete
  4. Very nice. I needed to choose the DataSource in the grid.
    Is it possible to autoupdate the grid? Databinding?

    ReplyDelete
    Replies
    1. What do you mean by auto update? Periodic updates!

      Delete
  5. What if i have a button in the same page as an Add button and on clicking the button it redirects me to another web page where the value for the table fields is inserted and that is shown in the database... Can u tel me the code for that?

    ReplyDelete
  6. thanks a lot ......its really working....:)

    ReplyDelete
  7. this is good demo
    its really helping me

    ReplyDelete
  8. This is very simple and helpful. Thank you

    ReplyDelete