Create ASP.net data storage layer (6)

Asp.net| Create | Data creation asp.net test page

Creating a test page is always a good way to access the SQL Server data tier and verify that input and output parameters are handled correctly. In fact, this is the only way to ensure that asp.net pages and components in future production solutions work as expected. This is especially true for authentication trust boundaries and security issues when calling other tiers from a layer in the solution.

Also, do not rigidly adhere to the creation of a production class interface when testing. You only need to test the target method. In fact, deliberately creating "ugly" test pages that you don't want to be a final production solution is a good strategy! In this article, I created some very simple asp.net pages that contain a list of test records and an input form for adding, editing, and deleting Test records.

For example, the following is the WebForm layout used to test topic records. You will notice that it contains the status label for the error message or other message, the record Count label, the data grid that displays the list of records, the input box for the record ID used to enter the retrieval, and the small table that supports adding, editing, and deleting records (see Figure 10).


Figure 10: WebForm layout for testing the subject record

When you create a test page, it's best to keep your code simple and straightforward. I usually add a small piece of code to each button to call the local method to handle the database operation. The following is the code for the Get record (Fetch Records) button on the Topictest.aspx page.

Private Sub Btngettopic_click (ByVal sender as System.object,byval e as System.EventArgs) Handles Btngettopic.click
Try
Dim ID as Integer = Int32.Parse (Txqueryid.text)
GetItem (ID) ' for database calls
Txid.text = Txqueryid.text
Txtitle.text = Mtitle
Txdescription.text = Mdescription
Lbstatus.text = "success!"
Catch ex as Exception
Lbstatus.text = ex. Message
End Try
End Sub
Note that the only action actually performed in this method is handled by the GetItem (ID) method call. It executes the database call and sets the local variable with the value returned. The following is the code for the GetItem method. Note that it uses a large number of SqlParameter objects to handle input and output values.

Private Sub GetItem (ByVal ID as Integer)
Try
PR = New SqlParameter ("Return_value", SqlDbType.Int)
pr. Direction = ParameterDirection.ReturnValue
Dim ptitle as SqlParameter = New SqlParameter
With Ptitle
. Direction = ParameterDirection.Output
. DbType = dbtype.string
. ParameterName = "@Title"
. Size = 30
End With
Dim pdescription as SqlParameter = New SqlParameter
With Pdescription
. Direction = ParameterDirection.Output
. DbType = dbtype.string
. ParameterName = "@Description"
. Size = 500
End With

cd = New SqlCommand

With CD
. CommandText = "Topicsgetitem"
. CommandType = CommandType.StoredProcedure
. Parameters.Add (New SqlParameter ("@AdminCode", "ADM"))
. Parameters.Add (New SqlParameter ("@ID", ID))
. Parameters.Add (Ptitle)
. Parameters.Add (pdescription)
. Parameters.Add (PR)
. Connection = cn
. Connection.Open ()
. ExecuteNonQuery ()
. Connection.close ()
End With

' Check the return code
If not PR. Value is nothing Then
Select Case Int32.Parse (pr. Value)
Case 100:throw New applicationexception ("Access violation")
Case 101:throw New applicationexception ("Invalid ID")
End Select
End If
' Set return value
Mtitle = pTitle.Value.ToString ()
Mdescription = pDescription.Value.ToString ()
Catch ex as Exception
Throw New Exception (ex. Message, ex)
End Try
End Sub
Another important aspect of the getitem approach is the use of return value parameters. It is declared in the first few lines of code and is checked after the stored procedure is executed. Please note that I checked for known error codes 100 and 101. For other errors, we'll explain how to create a mature middle tier later. The problem is that I'm going to take advantage of the return value and throw a custom exception when I need it.

For this solution example, I finally generated six Web forms and tested nearly 30 stored procedures and custom functions. You can find all of these completed forms in the download packages listed at the beginning of this article.

Now that we have defined tables, created stored procedures and functions, and generated asp.net Web forms, you can use Visual Studio. NET 2003 to build a database-tier installation script. The database administrator (sometimes yourself) can apply this script to the production server.