Microsoft ASP .NET Professional Projects - Premier Press
.pdf
Figure 7.3: The GenEditAdd control in the Add mode.
The control automatically builds a procedure call to the stored procedure p_masters and passes it the appropriate parameters. I will discuss this procedure in Chapter 15, "Chart of Accounts." For now, know that this procedure is responsible for both inserting and updating a masters record. If we need to modify a record, I send it the primary key of the record to be modified as a parameter. The code_value is the primary key of the Masters table. Thus, to update a record with code_value of 3, I call the stored procedure as follows:
Execute p_masters @code_display='Visa Card ', @code_category=604, @type='A', @closing=700, @code_value=3
If we want to insert a new record, I send a null value as the code_value to this procedure as follows:
Execute p_masters @code_display='Test', @code_category=1, @type='', @closing=10, @code_value=NULL
I do not have to manually build this string as the GenEditAdd control reads the user input to extract the input values, as well as the database fields collection to get the column names. It automatically builds this procedure call string, and posts it to the database.
The Config File
The DataGrid in the Masters web form has two hyperlink columns: one for the edit link and the other for the add link. These links navigate to the config_masters.aspx form. The edit link passes it the primary key of the record (code_value in this case) as follows:
<asp:HyperLinkColumn Text="Edit" DataNavigateUrlField="code_value" DataNavigateUrlFormatString="config_masters.aspx?code_value={0}"/> The add link passes it a code_value of zero as follows:
<asp:HyperLinkColumn Text="Add" DataNavigateUrlField="code_value" DataNavigateUrlFormatString="config_masters.aspx?code_value=0"/>
The config_masters.aspx is the form where the GenEditAdd control resides. Each DataGrid that wants the edit and add functionality will define a separate config form. This form contains the GenEditAdd control and a number of property settings for this component. The code_value passed to this form is extracted in the page load event. Various properties are also set here. Take a look at the following list:
The Config_Masters.aspx form
<%@ Register TagPrefix="Hersh" Namespace="Generic_chap7" Assembly="GenEditAdd_Chap7" %>
<html>
<script language="VB" runat="server">
Sub page_load(sender As Object, e As EventArgs) if NOT (isPostBack)
Dim sql As string
Dim ls_CodeValue As string
ls_CodeValue = Request.QueryString("code_value") SQL = "Select * from masters"
Gen.sql = SQL
if cint(ls_codeValue) = 0 then Gen.Where = ""
else
Gen.where= " Where code_value =" + ls_CodeValue end if
Gen.display = "111110"
Gen.KeyField = "code_value"
Gen.KeyValue = ls_codeValue
Gen.procedure = "p_masters"
Gen.ExitPage = "masters.aspx"
end if End Sub
</script>
<body>
<form runat = "server" >
<Hersh:GenEditAdd_Chap7 id = "Gen" runat=server
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User ID=sa;" />
</form>
</body>
</html>
In this form, the GenEditAdd control is first registered with the following page directive:
<%@ Register TagPrefix="Hersh" Namespace="Generic_chap7" Assembly="GenEditAdd_Chap7" %>
The control is then created with the id of Gen.
<Hersh:GenEditAdd_Chap7 id = "Gen" runat=server ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User ID=sa;" />
The GenEditAdd control requires certain properties to be set and these are set in the Page_Load event. The following tables show the properties of the GenEditAdd
component:
1. Property
Meaning
SQL
The SQL String without the Where Clause
Example
2. Property
Meaning
Select * from Masters
Where
If the Where property is provided, GenEditAdd displays the Edit mode. If it is blank, it displays the insert mode. The where clause is built dynamically in the config form based on the passed code_value as follows:
ls_CodeValue = Request.QueryString("code_v alue")
SQL = "Select * from masters" Gen.sql = SQL
If cint(ls_codeValue) = 0 Then Gen.Where = ""
Else
Gen.where= " Where code_value =" + ls_CodeValue
End If
If a code_value of zero is passed in the query string, the Where property is set to blank, thus displaying the control in the insert mode. If the Where property is provided, a where clause is built and the control displays in the Edit mode.
3. Property
Meaning
Display
This is a string of 0s and 1s. Zero means don't show a field and 1 means show it. Say the masters table has four fields. The string 0101 would mean hide the first and third fields and
1. Property
Example
4. Property
Meaning
Example
5. Property
Meaning
Example
6. Property
Meaning
Example
7. Property
Meaning
Example
8. Property
Meaning
Example
SQL
show the second and forth.
0101
KeyField
The primary key field name
code_value
KeyValue
The value of the primary key
2 (this will be passed to the config form from the calling form)
Procedure
The stored procedure to be called for inserting/updating a record
p_masters
ExitPage
This creates a hyperlink on the top of the edit/add form which allows you to navigate back to the calling form.
masters.aspx
ConnStr
The Connection String
"Provider=SQLOLEDB; Data Source=(local); Initial
_ Catalog=ASPNET;User ID=sa;"
As you can see there are eight properties that need to be set for the GenEditAdd component. The code of the control resides in the file GenEditAdd.vb. I will now start building it and discuss the theory of building custom controls as I go along.
Building the Control
The complete source code for this custom control resides in the file GenEditAdd_chap7.vb. There is a fair amount of code in this file and some theory that needs to be explained. I have thus decided to break the topic in a series of steps. I will be building intermediate code files, compiling it, explaining some aspects of the control until I build the final control in the file GenEditAdd_chap7.vb. You will find the code for these steps in the subfolder called "steps" on book's Web site at www.premierpressbooks.com/downloads.asp.
Step 1: The Edit Mode
I have not started building the control in this step. Step1.aspx is a simple aspx form that shows how I build a user input form in the Edit mode of the control. For GenEditAdd to
display in Edit mode, we must pass it a SQL string as well as a "Where" clause. This is hardcoded in this web form, but will be converted to properties in the GenEditAdd control. Figure 7.4 shows the output generated by the form.
Figure 7.4: The GenEditAdd component in the Edit mode.
Step1.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim dv As DataView
Dim i As integer
Dim myConnection As OleDbConnection
Dim myCommand As OleDbDataAdapter
Dim ds As New DataSet
Dim ConnStr As String
Dim SQL As String
Dim Where As String
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User
ID=sa;"
myConnection = New OleDbConnection(ConnStr)
SQL = "select * from groups "
Where = "where code_value = 700"
myCommand = New OleDbDataAdapter(SQL + Where, myConnection)
myCommand.Fill(ds, "groups")
dv = new DataView(ds.Tables("groups"))
Dim t As DataTable
t = dv.Table
Dim r As DataRow
Dim c As DataColumn
Dim cell As TableCell
Dim row As DataRow
For Each r in t.Rows
For Each c in t.Columns
response.write(c.ToString + ": ")
response.write(r(c).ToString + "<br>")
Next c
Next r
End Sub
</script>
<body style="background-color='beige'; font-family='verdana'; font -size='10pt'">
<asp:Table id="Table" Font-Name="Verdana" Font-Size="8pt" CellPadding=5 CellSpacing=0 BorderColor="black"
BorderWidth="1" Gridlines="Both" runat="server"/>
</body>
</html>
Note that I have a SQL statement and a "Where" clause as follows:
SQL = "select * from groups "
Where = "where code_value = 700"
I load a DataSet with the data returned from this query and assign it to a DataView. This DataView is assigned to a DataTable, which allows me to iterate the DataColumn collection (the inner loop) for each DataRow (the outer loop) as follows:
dv = new DataView(ds.Tables("groups"))
Dim t As DataTable
t = dv.Table
Dim r As DataRow
Dim c As DataColumn
Dim cell as TableCell
Dim row as DataRow
For Each r in t.Rows
For Each c in t.Columns
response.write(c.ToString() + ": ")
response.write(r(c).ToString() + "<br>")
Next c
Next r
I get the column name (c.ToString) and will display it as a label in the GenEditAdd control. I obtain the value of the column (by r(c).ToString) and in the GenEditAdd control, which we will build in Step 3, this will be displayed inside a textbox which can be modified by the user.
Step 2: The Add Mode
I have still not started building the control. Step2.aspx is a web form that explains the code in the add mode. In this mode, I do not supply the "Where" clause. The "SQL" clause is still required, as I must read the columns collection to extract the column names and display them as the labels against fields. Here is Step2.aspx.
Step2.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim dv As DataView
Dim i As integer
Dim myConnection As OleDbConnection
Dim myCommand As OleDbDataAdapter
Dim ds As New DataSet
Dim ConnStr As String
Dim SQL As String
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User
ID=sa;"
myConnection = New OleDbConnection(ConnStr)
SQL = "select * from groups "
myCommand = New OleDbDataAdapter(SQL, myConnection) myCommand.Fill(ds, "groups")
dv = new DataView(ds.Tables("groups")) Dim t As DataTable
t = dv.Table
Dim r As DataRow
Dim c As DataColumn
Dim cell as TableCell
Dim row as DataRow
For Each c in t.Columns response.write(c.ToString() + "<br> ")
Next c
End Sub </script>
<body style="background-color='beige'; font-family='verdana'; font -size='10pt'">
<asp:Table id="Table" Font-Name="Verdana" Font-Size="8pt" CellPadding=5 CellSpacing=0 BorderColor="black"
BorderWidth="1" Gridlines="Both" runat="server"/> </body>
</html>
In the insert mode, I need only iterate the DataColumns collection. Figure 7.5 shows the output from this form.
Figure 7.5: The Add Mode.
Step 3: The First Build
We are now ready to start building the control. Before we do that, let's build a web form, which will allow us to test the control as we are building it. The code for this step can be found in the ...GenEditAdd\Steps\step3 subfolder on the book's Web site at www.premierpressbooks.com/downloads.asp.
GenTestStep3.aspx
<%@ Register TagPrefix="Hersh" Namespace="Generic_chap7_step3" Assembly="GenEditAdd_Chap7_step3" %>
<html>
<script language="VB" runat="server">
Sub page_load(sender As Object, e As EventArgs)
if NOT (isPostBack)
Dim vsql As string
Gen.sql = "select * from masters"
Gen.where= " Where code_value = 1"
Gen.display = "111110"
Gen.KeyField = "code_value"
Gen.KeyValue = "1"
Gen.procedure = "p_masters"
Gen.ExitPage = "GenTestStep3.aspx"
end if
End Sub
</script>
<body>
<form runat = "server" >
<Hersh:GenEditAdd_Chap7 id = "Gen" runat=server
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User ID=sa;" />
</form>
</body>
</html>
This web form simply initiates the GenEditAdd control and sets its eight properties. The user control will display in the Edit mode as we are passing it a "where" clause. To display it in the add mode, just pass a blank value for the where clause.
In this step, we make the first build of the control. The code resides in the file Step3.vb and it should be compiled by running Step3.bat. Edit the bat file so that the outdir variable points to your bin folder. I shall list out the code file and then discuss it.
Step3.vb
Option Strict Off
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.OleDb
Namespace Generic_Chap7_step3
Public Class GenEditAdd_Chap7 : Inherits Control : Implements INamingContainer
Private ls_display as string
Private ls_where as string
Private ls_sql as string
Private ls_ConnStr as string
Private ls_keyField as string
Private ls_keyValue as string