ASP.NET Database Driven Web application

This tutorial we will learn how to connect ASP.NET application to an SQL server database and use the Grid data control to retrieve data from a table in the database.

The Database

I created the sample database and its table using SQL Server 2008 and Microsoft SQL Server Management Studio (this is the easiest and most use tool for creating and managing MS SQL Server database.) you can download it for free, but don’t forget you must first install SQL server (if you’ve Visual studio installed, then SQL server may have already come installed with it; but if not, you can download SQL server express edition its free from Microsoft website).

 

Below is the design view of the table I created. (click image for a larger view)

SQL Server 2008 and Microsoft SQL Server Management Studio

Database name is SampleDb, and table name contact_tb

 

The ASP.NET web application

The screenshots, below show how I created an ASP.NET web application (click image for a larger view)

Create ASP.NET web application in VS 2013

The name of the Web project is SampleWebApp

 

Creating the ASP.NET web page (web form)

  • After the SampleWebApp is created, right click on the application "SampleWebApp" from the Solution Explorer panel (at the right hand corner, as shown in the image below)
  • Point to “Add Item” from the menu list, and click “Web Form” from the sub menu.
  •  

    Creating the ASP.NET web form

    (click image for a larger view)

  • This will make a pop-up box to appear, then enter "Default" as the Item name and click OK.
  • Creating the ASP.NET web form

  • The web form is now created and opened as shown below
  • Creating the ASP.NET web form

 

 

Add Data Connection

  • Right-click on Data Connections from the Server Explorer panel, at the top-left

  • Click "Add Connection..." from the menu

  • Add Data Connection

  • The Add Connection pop-up will appear, enter the Server name. I use my windows authentication option for this example (computer name/SQLEXPRESS), this name is the one you use/see in SQL Server Management Studio)

  • This will load the Database names for you to choose your database, for example SampleDb Click OK

  • Add Data Connection


    You can see the database and its table (contact_db) now at the Server Explorer panel under Data Connections

    Add Data Connection

    Now that you have the Data Connection, you can use it to bind a Data source to Grid view control



Bind Data Source to GridView control

  • Expand the Data panel from the ToolBox and drag the GridView control into your web page or web form

  • Bind Data to Grid

  • Then click the GridView Tasks icon (the greater than >) to expand it (if it is not already expanded), and click <New data source> from the Choose Data Source drop down

  • Bind Data to Grid

  • Click Database, from the Data Source Configuration box, you can leave the default name sqlDataSource1 if you want, then click OK

  • Bind Data to Grid

  • Select the Data Connection you want to use, in this example we have just one so we select it and click the Next button

  • Add Data Connection

  • Leave the default checked option, to save the Connection as…. and click the Next button

  • Add Data Connection

  • In the "Specify columns from a table or view" I selected the table name "contact_tb" and check only the columns I want to retrieve, then click Next

  • Add Data Connection

  • I choose to click the Test button to see what the Query result will look like, before clicking the Finish button.

  • Add Data Connection

  • This will now effecting in the GridView, also note that I click the checkboxes to Enable Paging and Enable Sorting for the GridView

  • Add Data Connection

    Here is the preview on web browser.

    Add Data Connection

 

 

Here is the Connection string code, as generated in the Web.config

Web.config

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
    <connectionStrings>
        <add name="SampleDbConnectionString" connectionString="Data Source=HP-PC\SQLEXPRESS;Initial Catalog=SampleDb;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <system.web>
      <compilation debug="true" targetFramework="4.5" />
      <httpRuntime targetFramework="4.5" />
    </system.web>

</configuration>

 

And here is the source of Default.aspx, you can also see the GridView code generated.

Default.aspx
              
<head runat="server">
    <title>Sample Data App</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Sample Data App
        <br />
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="date_submitted" HeaderText="date_submitted" SortExpression="date_submitted" />
                <asp:BoundField DataField="full_name" HeaderText="full_name" SortExpression="full_name" />
                <asp:BoundField DataField="email" HeaderText="email" SortExpression="email" />
                <asp:BoundField DataField="message" HeaderText="message" SortExpression="message" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SampleDbConnectionString %>" SelectCommand="SELECT [date_submitted], [full_name], [email], [message] FROM [contact_tb]"></asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

You could also just write the code in these files or edit the code easily if you want, For example, here is the section of the code in Default.aspx I edited of the HeaderText of the GridView.

<Columns>
	<asp:BoundField DataField="date_submitted" HeaderText="Date Submitted" SortExpression="date_submitted" />
	<asp:BoundField DataField="full_name" HeaderText="Full Name" SortExpression="full_name" />
	<asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" />
	<asp:BoundField DataField="message" HeaderText="Message" SortExpression="message" />
</Columns>

note: I space and capitalize the first characters of the text

GridView

 

Next »