ASP.NET DB Admin Control

• Download source files - 45.9 Kb

Login Page

Admin Menu

Record form

Record list

Creating Web applications to perform database driven Login, Add, Edit and Remove operations on any kind of database.

Introduction

Creating a database control panel or dynamic website control panel in ASP.NET can be done by using the following steps:

1. First you have to create a login page, so that, only the admin user is able to login using the username and password.
2. Add new data: a web form is required to take the user input and when the user presses the Save or Submit button, the data is saved in the database.
3. View data: query database and fetch the required result/recordset, and to show that in some kind of presentable control.
4. Delete data: obtain a list of records and then select the record to be deleted, after that perform the delete operation.
5. Modify data: select the record to be modified, once the values are modified, you have to perform the query to update the database.

How to interact with the database

ASP.NET has many built in Database Management objects under the namespace of DATA, most common among these are:

• OleDbConnection: to perform connectivity functions on different kinds of database.
• OleDbCommand: to perform queries.
• OleDbDataReader: to fetch recordset.
• DataSet: to store data in the memory after selecting from the database, like in a recordset.

Now I'll try to explain this application step by step:

Login

I have used form based authentication system for security and access permissions. I have added a Web form to get the username and password. When the user fills in this form and sends the information for login, the following code is executed:

Dim cn As New OleDb.OleDbConnection
Dim cm As New OleDb.OleDbCommand
Dim dr As OleDb.OleDbDataReader
Dim username As String = txtUsername.Text
Dim password As String = txtPassword.Text
Try
    cn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
    cn.Open()
    cm.Connection = cn
    cm.CommandText = "Select * From admin where username='" _
    & username & "' and password='" & password & "'"
    dr = cm.ExecuteReader()
    'dr.Read()

    If dr.HasRows Then
       System.Web.Security.FormsAuthentication.RedirectFromLoginPage(username, False)
    Else
       lblMsg.Text = "Incorrect username or password!"
    End If
Catch ex As Exception
   lblMsg.Text = ex.Message
End Try

The action performed is explained here:

Fist of all, we have to define the connection and command objects, and two variables are declared to store the username and password from the submitted form. Start a Try block and load the ConnectionString value from the application settings. After this, perform the query to check the username and password, if a record is selected for a given username and password, it means that the login info is correct, else it means that the login is incorrect and a message is shown for incorrect username or password. System.Web.Security.FormsAuthentication.RedirectFromLoginPage(username, False) is used to tell the ASP.NET application that the login information provided by the user is correct, so that user can access secure contents.

Admin menu

Main navigation system for the application to access different sections.

Add New/Edit record

Used a Web form to take the inputs from the user including the content title and content text. Here is the code that is used for saving a new record in the database:

Dim cn As New OleDb.OleDbConnection
Dim cm As New OleDb.OleDbCommand
Dim title As String = txtTitle.Text
Dim content As String = txtContent.Text
Try
    cn.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0; " & _
    "Data Source=C:\Inetpub\wwwroot\ASPNETDBAdminControl\database\data.mdb"
    cn.Open()
    cm.Connection = cn
    If Request.QueryString("id") <> "" Then
        cm.CommandText = "Update Content Set Title='" & _
        title & "', content='" & content & "' " _
        & "Where id=" & Request.QueryString("id")
        lblMsg.Text = "Content Record is updated successfully"
    Else
        cm.CommandText = "Insert Into Content (title,content)" _
        & " Values('" & title & "','" & content & "')"
        lblMsg.Text = "Content Record is inserted successfully"
    End If
    cm.ExecuteNonQuery()
Catch ex As Exception
    lblMsg.Text = ex.Message
End Try

These events perform two functions based on the condition Add New/Edit. If the parameter ID is provided then editing is done, otherwise Add New is performed. I have used the same form to perform both the Add New and Edit operations. In Add New, the information entered by the user is inserted by calling the Insert query in the background. In cases where we have to perform the edit operation, the form load event loads the data of the selected record ID, and then the information is edited when the user presses the Save button. Here, the Update query is called.

List records / and Edit / Delete functions

Used a database control to display the list of records in the database, this control template can be customized to show the required format information. The following code is used to fill the records on form load event, a check is performed for the delete operation, if delid parameter is given then the corresponding record is deleted.

Dim cn As New OleDb.OleDbConnection
Dim cm As New OleDb.OleDbCommand
Dim dr As OleDb.OleDbDataReader
Try
    cn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
    cn.Open()
    cm.Connection = cn
    If Request.QueryString("delid") <> "" Then
        cm.CommandText = "DELETE From Content Where ID=" & _
                         Request.QueryString("delid")
        cm.ExecuteNonQuery()
        lblMsg.Text = "Content Deleted: " & Request.QueryString("delid")
    End If
    cm.CommandText = "Select id as ID,title as" & _ 
                     " Title,left(content,50) as Content From Content"
    dr = cm.ExecuteReader()
    DataList1.DataSource = dr
    DataList1.DataBind()
Catch ex As Exception
    lblMsg.Text = ex.Message
End Try

The following code is called when the user clicks an option on the DataList:

Select Case e.CommandName
    Case "Edit"
        Response.Redirect("frmcontent.aspx?id=" & e.CommandArgument)
    Case "Delete"
        'lblMsg.Text = "Delete: " & e.CommandArgument

         Response.Redirect("listcontents.aspx?delid=" & e.CommandArgument)
End Select

If the user presses Edit, frmcontent is called with the selected record ID, and if Delete is pressed, the delete function is called.

About this application

This is a small Web application that contains basic database functions using ASP.NET, including a database driven user authentication, adding new records, viewing records, editing and deleting records.

Login information

• Username: admin
• Password: admin

Configuration

Change the following ConnectionString value according to your settings:

<appSettings>
<add key="ConnectionString" 
  value="Provider=Microsoft.JET.OLEDB.4.0; 
  Data Source=C:\Inetpub\wwwroot\ASPNETDBAdminControl\database\data.mdb" />
</appSettings>
<authentication mode="Forms"> 
<forms loginUrl="login.aspx" name="DBLogin" />
</authentication>
<!-- AUTHORIZATION 
This section sets the authorization policies 
of the application. You can allow or deny access
to application resources by user or role. 
Wildcards: "*" mean everyone, "?" means anonymous 
(unauthenticated) users.
-->
<authorization>
<deny users="?" /> <!-- Allow all users -->
<!-- <allow users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
<deny users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
-->
</authorization>

What next

Well in my point of view, this is a very compact application providing database driven user authentication, login webform, add new records, view records, edit records and delete record functions. This can be very useful for beginners and advanced users can customize this template for database driven web application development.