Microsoft ASP .NET Professional Projects - Premier Press
.pdf
public String test()
{
StringBuilder SB = new StringBuilder(ls_message); SB.Append(" From test function...");
return SB.ToString();
}
}//class
}//namespace
Except for syntax changes, writing an object in C# is quite similar to writing it in Visual Basic. Note that the constructor in this case is the public method BasicC(). This is a public method with the same name as the class name.
2.Compile the Object
Run the bat file BasicObjC.bat which contains the following DOS commands:
csc /t:library /out:g:\aspnetsamples\bin\BasicObjC.dll BasicObjC.cs
This places the DLL file in the bin folder.
3.Build the Web Form
Finally, build an aspx form to test out the control.
BasicObjC.aspx
<%@ Import Namespace="BasicObjC" %>
<html>
<script language="C#" runat="server">
public void Page_Load(Object sender, EventArgs E)
{
BasicC comp = new BasicC();
comp.message = "Hello World";
String s = comp.test();
display.InnerHtml = s;
}
</script>
<h3>A Simple C# Component</h3>
<h5>Object Output: </h5>
<div id="display" runat="server"/>
</html>
Partitioning Services Between Web Forms and Components
The typical usage of a web form is to render HTML or XML pages to the browser. Since web forms reside on the server, can they be thought of as being business objects? In a component world, the answer would usually be negative. A web form contains both the presentation logic and business logic on the same page. A business object, on the other hand, is free from all presentation elements. It can be thought of as an object that provides services to another form. As a good servant, it is called whenever needed and dismissed when its need is over. This service analogy is also the reason that business objects are referred to as service classes. There are a number of advantages to using business objects. Some of these are summarized in the following:
§Promotes encapsulation: You encapsulate frequently used functionality into an object and expose just its properties and methods to the outside world. For example, most web forms require database access and manipulation
routines. Instead of writing script in each page for database connection routines, we can create a business object that does this work for us. This object will expose properties and methods to the outside world and a user will not need to know its inner workings.
§Easy maintenance: Encapsulated code residing in one business object is far easier to maintain than code that is distributed over numerous Web pages. If our business logic changes, we just need to modify our business object and the consumers of the object need not do anything at all. This is analogous to cascading stylesheets. Suppose you want to change the definition of an H1 tag. You just do it in one place and the change is cascaded to all the pages that use that tag.
§Improves with reuse: One of the OO fundamentals is that codes reuse, refine, and improve the code. As more and more users test the object, the object is debugged thoroughly and increasing reliance can be placed on it. This promotes code reuse because developers can gradually build a tried and tested function library, which can then be shared with other developers.
A Database Class
I will now show you how to build a database class. This class will contain commonly used functionality for working with a database. It will include properties to set a database connection string and pass the object a SQL statement, which will be applied to the database. The SQL statement can return data (as in a SELECT statement) or perform action queries like update, insert, delete, or call a stored procedure, which returns no data. It will have two methods for applying the SQL statement to the database. The first method will return a DataView, which can be used to bind a control. The second method is a generic function to apply "action" SQL statements to the database. I will show you how to build the class, first in Visual Basic.NET, then in C#.
The Database Class in Visual Basic.NET
I start by building the component in Visual Basic.NET. The source code for this example can be found in the ....\SQLClass\ SqlClassvb subfolder on the book's Web site at www.premierpressbooks.com/downloads.asp.
1.The SQL Property: This is the SQL string that is passed by the user to this object. This can be any valid SQL statement. It is defined using the Set and Get methods and has an associated local variable ls_sql.
2.Private ls_sql as string
3.Public Property SQL as string
4.Get
5.Return ls_sql
6.End Get
7.Set
8.ls_sql = value
9.End Set
End Property
10.The ConnStr Property: This is the property that is passed the connection string by the user. It is implemented using the Set and Get methods and a local variable ls_connstr.
11.Private ls_ConnStr as string
12.Public Property ConnStr as string
13.Get
14.Return ls_ConnStr
15.End Get
16.Set
17.ls_ConnStr = value
18. |
End Set |
End Property
19.Function Populate: This function returns a DataView, which can be used to bind a control.
20.Public Function Populate() As DataView
21.Dim dv As DataView
22.Dim i As integer
23.Dim myConnection As OleDbConnection
24.Dim myCommand As OleDbDataAdapter
25.Dim ds As New DataSet
26.myConnection = New OleDbConnection(ConnStr)
27.myCommand = New OleDbDataAdapter(SQL, myConnection)
28.myCommand.Fill(ds, "vTable")
29.Populate = ds.Tables(" vTable").DefaultView
End Function
This function uses the passed connection string to create a new
OleDbConnection. The OleDbDataAdapter is used to populate a DataSet (and table vTable) using the passed SQL statement. The default view of table vTable is returned to the calling object.
30.Function RunSQL: We have met this function in earlier chapters. This is a generic function, which can be used to apply an "action" query to the database. Action queries are queries that do not return data. You can also use this function to run stored procedures that don't return data by calling them with the execute statement. For example, the statement Execute p_masters parameter a, parameter b, etc. will execute the stored procedure p_masters. The procedure p_masters could be a procedure that inserts a row in the Masters table.
31.Function RunSql(vsql as string) as String
32.Dim Message As string
33.try
34.Dim myConnection As OleDbConnection
35.myConnection = New OleDbConnection(ConnStr)
36.Dim mycommand As New OleDbCommand(vsql,myConnection)
37.myconnection.Open()
38.myCommand.ExecuteNonQuery()
39.myconnection.Close()
40.Catch ex As OleDbException
41.Dim errItem As OleDbError
42.Dim errString As String
43.For Each errItem In ex.Errors
44.errString += ex.Message + " "
45.Next
46.Message = "SQL Error.Details follow:<br/><br/>" & errString
47.Catch myException as Exception
48.message = "Exception: " + myException.ToString()
49.End try
50.RunSql = message End Function
51.The Constructor Function: The constructor function gets fired each time the object is initiated. I initialize the two properties to a space in this function.
52.Public Sub New()
53.MyBase.New()
54.ls_sql = ""
55.ls_ConnStr = ""
End Sub
The following is the complete listing of the database class:
SQLClass.vb
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Text
Namespace SQLNameSpace
Public Class SQLClass
Private ls_sql as string
Private ls_ConnStr as string
Public Sub New()
MyBase.New() ls_sql = "" ls_ConnStr = ""
End Sub
Public Property SQL as string
Get
Return ls_sql
End Get
Set
ls_sql = value End Set
End Property
Public Property ConnStr as string
Get
Return ls_ConnStr
End Get
Set
ls_ConnStr = value End Set
End Property
Public Function Populate() As DataView
Dim dv as DataView
Dim i As integer
Dim myConnection As OleDbConnection
Dim myCommand As OleDbDataAdapter
Dim ds As New DataSet
myConnection = New OleDbConnection(ConnStr) myCommand = New OleDbDataAdapter(SQL, myConnection) myCommand.Fill(ds, "vTable")
Populate = ds.Tables("vTable").DefaultView
End Function
Public Function test() as string
Dim SB As StringBuilder
SB = New StringBuilder(ls_sql)
SB = SB.Append("..returned from function test") test = SB.ToString()
End Function
Function RunSql(vsql as string) as String
Dim Message As string
try
Dim myConnection As OleDbConnection
myConnection = New OleDbConnection(ConnStr)
Dim mycommand As New OleDbCommand(vsql,myConnection)
myconnection.Open()
myCommand.ExecuteNonQuery()
myconnection.Close()
Catch ex As OleDbException
Dim errItem As OleDbError
Dim errString As String
For Each errItem In ex.Errors
errString += ex.Message + " "
Next
Message = "SQL Error.Details follow:<br/><br/>" & errString
Catch myException as Exception
message = "Exception: " + myException.ToString()
End try
RunSql = message
End Function
End Class
End Namespace
Compiling the Database Class
I have provided a bat file, which compiles the database class to a DLL. The following is the listing of the file SQLClass.bat:
set outdir=g:\aspnetsamples\bin\SQLClass.dll
set assemblies=System.dll,System.Web.dll,System.Data.dll,System.XML.dll vbc /t:library /out:%outdir% /r:%assemblies% SQLClass.vb
pause
Running this file will compile and place SQLClass.dll in the bin folder.
Testing the Database Class
I have provided a web form, which tests the functionality of the database class. Figure 8.2 shows what it looks like.
Figure 8.2: Testing the database class.
The following is the code listing:
TestVbClass.aspx
<%@ Import Namespace="SQLNameSpace" %> <html>
<script language="VB" runat="server"> Dim Comp As SQLClass
Dim ConnStr As String
Dim SQL As String
Sub Page_Load(Source As Object, E As EventArgs)
Comp = New SQLClass()
Comp.ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial
Catalog=ASPNET;User ID=sa;"
if NOT (isPostBack) rebind
end if End Sub
Sub Show_Click(Sender As Object, E As EventArgs)
Message.Text = "Masters Table Displayed... "
ReBind
End Sub
Sub Insert_click(Sender As Object, E As EventArgs)
sql = "Insert into Masters(code_display,code_category,type)" sql = sql + "Values ('test',701,'E')"
Comp.RunSql(sql) rebind
Message.Text = "Inserted test record... "
End Sub
Sub Delete_click(Sender As Object, E As EventArgs) sql = "delete from masters where code_display = 'test'" Comp.RunSql(sql)
rebind
Message.Text = "Deleted all test records..."
End Sub
Sub Update_Click(Sender As Object, E As EventArgs)
sql = "UPDATE Masters Set Opening = 90 WHE RE code_display = 'test'" Comp.RunSQL(sql)
rebind
Message.Text = "Updated all test records: Set closing balance = 90...! " End Sub
Sub ReBind()
Comp.SQL = "select * from Masters"
DataGrid1.DataSource=Comp.populate()
DataGrid1.DataBind()
End Sub </script>
<body style="background-color='beige'; font-family='verdana'; font -size='10pt'"> <h3>
<font face="Verdana">A DataBase Class in Visual Basic</font> </h3>
<form runat="server">
<asp:button text="Refresh" Onclick="Show_Click" runat="server" />
<asp:button text="Insert" Onclick="Insert_Click" runat="server" /> <asp:button text="Update" Onclick="Update_Click" runat="server" /> <asp:button text="Delete" Onclick="delete_Click" runat="server" /> <asp:label id="Message" runat="server" />
<asp:DataGrid id="DataGrid1" runat="server" /> </form>
</body>
</html>
1.Initiating the Object: The object is initiated in the page_load event of the web form and the connection string passed to it is as follows:
2.Sub Page_Load(Source As Object, E As EventArgs)
3.Comp = New SQLClass()
4.Comp.ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User ID=sa;"
5.if NOT (isPostBack)
6.rebind
7.end if
End Sub
8.The ReBind function passes a SQL query to the object and binds a DataGrid to the DataView returned by the object as follows:
9.Sub ReBind()
10.Comp.SQL = "select * from Masters"
11.DataGrid1.DataSource=Comp.populate()
12.DataGrid1.DataBind()
End Sub
13.The Insert_click event executes an insert statement against the database using the RunSQL function of the object.
14.Sub Insert_click(Sender As Object, E As EventArgs)
15.sql = "Insert into Masters(code_display,code_category,type)"
16.sql = sql + "Values ('test',701,'E')"
17.Comp.RunSql(sql)
18.ReBind
19.Message.Text = "Inserted test record... "
End Sub
20.The Update_Click event executes an update statement against the database using the RunSQL function.
21.Sub Update_Click(Sender As Object, E As EventArgs)
22.sql = "UPDATE Masters Set Opening = 90 WHERE
code_display = 'test'"
23.Comp.RunSQL(sql)
24.ReBind
25. Message.Text = "Updated all test records: Set closing balance = 90...! "
End Sub
26.The Delete_click event executes a delete statement using the RunSQL function.
27.Sub Delete_click(Sender As Object, E As EventArgs)
28.sql = "delete from masters where code_display = 'test'"
29.Comp.RunSql(sql)
30.ReBind
31.Message.Text = "Deleted all test records..."
End Sub
The Database Class in C#
In the following section, I include a complete listing of the database class written in C#. Except for syntax changes, this class is similar to the Visual Basic.NET class discussed in the preceding section, hence I have not gone into a detailed discussion of the code.
The source code for this example can be found in the ....\SqlClass\ SqlClassC subfolder on the book's Web site at www.premierpressbooks.com/downloads.asp.
SQLClassC.cs
namespace SQLNameSpaceC
{
using System; using System.Data;
using System.Data.OleDb; using System.Text;
public class SQLClassC
{
private String ls_connStr; private String ls_sql; public String ConnStr
{
get
{
return ls_connStr;
}