Microsoft ASP .NET Professional Projects - Premier Press
.pdfMessage.Text = "Masters Table Displayed... "
ReBind
End Sub
Sub Insert_click(Sender As Object, E As EventArgs)
try
sql = "Insert into Masters(code_display,code_category,type)" sql = sql + "Values ('test',701,'E')"
Dim mycommand As New OleDbCommand(sql,myConnection) myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
catch myException as Exception Response.Write("Exception: " + myException.ToString())
End try 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'"
Dim mycommand As New OleDbCommand(sql,myConnection) myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close() rebind
Message.Text = "Deleted all test records..."
End Sub
Sub Update_Click(Sender As Object, E As EventArgs)
try
Dim mycommand As New OleDbCommand( _
"UPDATE Masters Set Opening = 90 WHERE code_display = 'test'", _ myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
catch myException as Exception Response.Write("Exception: " + myException.ToString())
End try rebind
Message.Text = "Updated all test records: Set closing balance = 90...! " End Sub
Sub ReBind()
SQL = "select * from Masters"
myCommand = New OleDbDataAdapter(SQL, myConnection) 'use Fill method to populate dataset
myCommand.Fill(ds, "Masters") 'Binding a Grid
DataGrid1.DataSource=ds.Tables("Masters").DefaultView
DataGrid1.DataBind()
End Sub </script> <body>
<h3><font face="Verdana">Action Queries</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>
You will note that the process of populating the DataSets and binding the grid is the same as in the MastersGrid.aspx example. However, I have moved the variable declarations outside the Page_Load event to give them global scope over the form. I have also added four buttons for the insert, update, delete, and refresh functionality. The if NOT (isPostBack) .. statement ensures that the grid is only loaded once (on page load). We have four events that fire when the appropriate button is clicked. The command syntax is straightforward. For example row deletion is achieved by the following code:
Sub Delete_click(Sender As Object, E As EventArgs)
sql = "delete from masters where code_display = 'test'"
Dim mycommand As New OleDbCommand(sql,myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
rebind
Message.Text = "Deleted all test records..."
End Sub
You pass the OleDbCommand a SQL string and an active connection, open the command, execute it, and then close it. Please note that it is very important to close the connection or else you may inadvertently exhaust the connection limit while waiting for the page instances to be garbage-collected.
Stored Procedures
In ADO.NET you can call stored procedures using the command object. You tell the command object the name of the stored procedure and then add a parameter for each input parameter required by the stored procedure. I also show you a "short-cut" method of calling stored procedures in MS SQL Server, where you take advantage of using the "Execute" keyword of T-SQL. Using this method, you can get away from the drudgery of populating the parameter collection.
I will use a simple stored procedure called p_authors that accepts a single input parameter called @state (you can find the code for the examples discussed in this section in the ...samples\StoredProcedure subdirectory on the book's Web site at www.premierbooks.com/downloads.asp). This stored procedure needs to be applied to the pubs database.
p_authors
Create Procedure p_authors
@state varchar(10)
as
select * from authors where state = @state
Using the Parameters Collection
The command object exposes a Parameters collection that needs to be populated with each of the parameters expected by the stored procedure. In the example that follows, I show you how to call the stored procedure p_authors with a parameter of "CA". The result returned by the database will include all authors in the state of California. I will then bind this resultset to a DataGrid.
Parameters.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<head>
<H4>Using Parameters</H4>
<script language="VB" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim dr As OleDbDataReader
Dim ConnStr As String
'Connect
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=Pubs; User
ID=sa"
myConnection = New OleDbConnection(ConnStr)
'command
Dim myCommand As New OleDbCommand("p_authors", myconnection)
myCommand.CommandType = CommandType.StoredProcedure
'Parameter object
Dim objParam As OleDbParameter
objParam = myCommand.Parameters.Add("State", OleDbType.VarChar, 10)
objParam.Direction = ParameterDirection.Input
objParam.Value = "CA"
Try
'open the connection and execute the command
myconnection.Open()
'ExecuteReader returns a Reader
dr = myCommand.ExecuteReader()
Catch objError As Exception
'display error details here and stop execution on error
Exit Sub '
End Try
DataGrid1.DataSource=dr
DataGrid1.DataBind()
End Sub
</script>
</head>
<body>
<form runat=server>
<asp:DataGrid id="DataGrid1" runat="server" />
</form>
</body>
</html>
You will note that I have specified the CommandType as StoredProcedure after passing the name of the stored procedure as a parameter to the command object as follows:
Dim myCommand As New OleDbCommand("p_authors", myconnection)
myCommand.CommandType = CommandType.StoredProcedure I then add a parameter to the Parameters collection as follows:
Dim objParam As OleDbParameter
objParam = myCommand.Parameters.Add("State", OleDbType.VarChar, 10)
objParam.Direction = ParameterDirection.Input
objParam.Value = "CA"
The Add method takes three arguments; the name of the parameter, its type, and optionally its size. The ParameterDirection property sets the direction of the parameter. This can be Input, Output, InputOutput, or ReturnValue. Finally the Value property is used to provide a value for the parameter.
The ExecuteReader method of the command object is used to execute the command. This returns a Reader object, which is then used to bind a DataGrid.
Using the Execute Keyword to Call Stored Procedures
The process of populating the Parameters collection of the command object described in the preceding section is quite code intensive. A short-cut way of calling stored procedures in MS SQL Server is available. You can make use of the Execute keyword of T-SQL to call a stored procedure.
Caution Note that this method will only work with MS SQL Server.
In the following example, I call the procedure p_authors using the Execute keyword:
Execute.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<head>
<H4>Using execute Keyword</H4>
<script language="VB" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbDataAdapter
Dim ds As New DataSet
Dim ConnStr As String
Dim SQL As String
'Connect
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=Pubs; User
ID=sa"
myConnection = New OleDbConnection(ConnStr)
'DataSetCommand
SQL = "Execute p_authors 'CA'"
myCommand = New OleDbDataAdapter(SQL, myConnection)
'use Fill method of DataSetCommand to populate dataset
myCommand.Fill(ds, "Authors")
'Binding a Grid
DataGrid1.DataSource=ds.Tables("Authors").DefaultView
DataGrid1.DataBind()
End Sub
</script>
</head>
<body>
<form runat=server>
<asp:DataGrid id="DataGrid1" runat="server" />
</form>
</body>
</html>
Note that I make a call to the stored procedure p_authors and supply the required parameters with a single SQL statement using the Execute keyword as follows:
SQL = "Execute p_authors 'CA'"
The code now is quite compact, as I did not have to write script to populate the Parameters collection of the command object.
DataViews
A DataView in ADO.NET is roughly equivalent to a database view. Different views can be applied to a DataTable existing in the DataSet. For example, one view could show all the rows in the table whereas another could show rows based on a selection criteria. List bound controls like DataGrids and DropDownLists then use these views as their datasource.
The Default View
Each DataTable has a default view assigned. In the MasterGrid.aspx example, the DataGrid is bound to the following default view:
DataGrid1.DataSource=ds.Tables("Masters").DefaultView
This default view is a view containing all the rows and columns of the DataTable.
Applying Filters to Views
In this example, I will populate a DataSet, which contains all the rows in the Groups table. I will then create a DataView which filters the Groups table in the DataSet for records having the criteria code_value = 700. In the process I will introduce the RowFilter and sort property of the DataView. Figure 3.3 shows a form with a button. If you click on this button, the appropriate filter is applied and the code_display and the code_value of all matching items are written out to the screen.
Figure 3.3: Filtering a DataView.
DataView.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="VB" runat="server">
Sub readDs(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"))
'// Sort the view based on the code_display column
dv.Sort = "code_display"
'// Filter the dataview to only show customers with the lastname = Smith
dv.RowFilter = "code_value = '700'"
for i = 0 to dv.Count -1
Response.Write(dv(i)("code_display").ToString + " - " + dv(i)("code_value").ToString())
next
End Sub
</script>
<body>
<h3><font face="Verdana">DataView</font></h3>
<form runat=server>
<asp:button text="Read DataView" Onclick="readDs" runat=server/>
</form>
</body>
</html>
A connection is established to the database, and a DataSet is populated with all rows from the Groups table. A DataView is defined to hold all the rows from the DataSet. The sort command sorts the DataView according to the code_display. The RowFilter method filters the DataView using the criteria "code_value= '700'". The DataView is then iterated and the elements are displayed to the screen.
Reading the Rows and Columns Collection of a DataTable
In ADO a basic requirement was to read the "fields" collection of a recordset and display the field name of a database table alongside the value of the field. I will show you how this can be done in ADO.NET. I will read the Groups table and list out both the field name and the value of a record as shown in Figure 3.4.
Figure 3.4: Reading rows and columns collection of a DataTable.
Collection.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 where code_value = 700 " myCommand = New OleDbDataAdapter(SQL, myConnection) myCommand.Fill(ds, "groups")
dv = new DataView(ds.Tables("groups")) Dim t As DataTable