EziData Solutions

Web, NET and SQL Server

Saving a Web User Control as a reusable DLL

If you have been folowing the previous posts you would now have successfully created a simple Web User Control and used it within a single page website. In the first post we created the Web User Control named CustomDropDown. In the second and third posts we added Events to the CustomDropDown control. The final step in the process is to make this control easily reusable across a multitude of web sites. To do this, we compile the Web User Control as a dll.

Creating a DLL from a Web User Control

Before we set about saving the CustomDropDown control as a dll, we need to make some small changes. The first is to add a ClassName to the @Control directive in the CustomDropDown.ascx file.

<%@ Control Language="VB" AutoEventWireup="false" ClassName="Acme.CustomDropDown" CodeFile="CustomDropDown.ascx.vb" Inherits="UserControls_CustomDropDown" %>

Secondly, to ensure that the custom Event class CustomDropDownEventArgs is accessable from the DLL, we need to copy the code from the class .vb file into the CustomDropDown.ascx.vb file. Make sure you copy the complete class code - i.e. everything including the Public Class CustomDropDownEventArgs tags. The complete code for the CustomDropDown.ascx.vb file is shown below.

Partial Class UserControls_CustomDropDown
    Inherits System.Web.UI.UserControl

    Public Event ListChanged(ByVal sender As Object, ByVal e As CustomDropDownEventArgs)

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged

        Me.Label1.Text = Me.DropDownList1.SelectedValue

        Dim myEvent As New CustomDropDownEventArgs()
        myEvent.FullName = Me.DropDownList1.SelectedItem.Text
        myEvent.PayRollID = Me.DropDownList1.SelectedItem.Value
        RaiseEvent ListChanged(sender, myEvent)

        RaiseEvent ListChanged(sender, myEvent)
    End Sub
End Class
Public Class CustomDropDownEventArgs
    Private _fullname As String
    Private _payrollID As String

    Public Property FullName() As String
        Get
            Return _fullName
        End Get
        Set(ByVal value As String)
            _fullname = value
        End Set
    End Property
    Public Property PayRollID() As String
        Get
            Return _payrollID
        End Get
        Set(ByVal value As String)
            _payrollID = value
        End Set
    End Property
End Class

We can now build and publish the website using the commands in Visual Studio.

  • Un-tick the Allow this precompiled site to be updatable, this ensures that everything, including the mark-up on the ascx page is compiled.
  • Tick the Use fixed naming and single page assemlies, this will ensure that the ascx file, including the code behind file will be saved in its own DLL and not compiled with any other elements.

Adding the DLL to a new Website

Once you have published the control, you can now add it to a new or existing website. To utilise the control, simply use the Add Reference command and browse to the location where you published the website in the step above. The DLL's will be saved under the \bin folder.

To include the control on a page in your new website you need to add two lines of code to the source. The first line registers the control and is similar (but not identical) to the @Register directive added automatically by Visual Studio when you drag and drop the Web User Control onto a page.

<%@ Register TagPrefix="myControl1" Namespace="Acme" Assembly="App_Web_customdropdown.ascx.6bb32623" %>

Two things to note about the above tag, firstly make sure the Namespace element matches the namespace you added to the Classname element in the previous steps and you only need to inlcude the root Namespace component of the Classname. The second it that the Assemby name must match the DLL name that you added a reference to, but does not include the file extension '.DLL'.

Once you have set up a reference and registered it on the page, you can add the tag whereever you need the control to appear in your mark-up. While you are there, add a label control to the page so that you can test the Event handling

<myControl1:CustomDropDown ID="CustomDropDown1" runat="server" /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> 

 Adding Custom Event Handling

The final step is to add the custom event handling we outline in Adding Custom Events to Web User Controls As we named the control the same as the Web User Control in the previous post, we can simply cut and past the CustomDropDown1_ListChanged code from the default.aspx page into our new page.

Private Sub CustomDropDown1_ListChanged(ByVal sender As Object, ByVal e As CustomDropDownEventArgs) Handles CustomDropDown1.ListChanged
        Me.Label1.Text = e.FullName
End Sub

Posted: Oct 08 2009, 22:25 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: VB.NET

Adding Custom Events to Web User Controls

In a previous post we investigated how to handle events raised by a Web User Control on the page containing the control. If you need a refresher, check out the post Adding Events to Web User Controls in VB.NET

In our previous posts we have been creating a Web User Control named CustomDropDown that contains a dropdownlist containing employee names and payroll numbers. In the first post we added an event to the CustomDropDown that displayed the PayrollID of the selected employee in a label control within the Web User Control itself. In the next post, we expanded upon this so that when a new employee is selected, a label on the page containing CustomDropDown displays the text 'List Changed'. In this post, we now want to add functionality that will display the details of the employee in a label on the page containing CustomDropDown.

The first step is to create a new Class containing the code that will provide the information we want on the page. Go to the Website menu and select Add New Item, then choose the Class item and enter the Name 'CustomDropDownEventArgs'. If you are prompted to add the code to the App_Code folder, choose Yes.

 

The CustomDropDownEventArgs class will consist of two public properties that will be populated by the controls on the CustomDropDown Web User Control. At this stage, all we need to do is create the properties, as the Event Handler will call this class from the CustomDropDown control.

Public Class CustomDropDownEventArgs
    Private _fullname As String
    Private _payrollID As String

    Public Property FullName() As String
        Get
            Return _fullName
        End Get
        Set(ByVal value As String)
            _fullname = value
        End Set
    End Property
    Public Property PayRollID() As String
        Get
            Return _payrollID
        End Get
        Set(ByVal value As String)
            _payrollID = value
        End Set
    End Property
End Class

We now need to modify the code behide CustomDropDown so that the ListChanged Event we created in the previous post now includes the new CustomDropDownEventArgs class.

Public Event ListChanged(ByVal sender As Object, ByVal e As CustomDropDownEventArgs)

We will also need to change the SelectedIndexChanged event for the DropDownList, so that it will popluate the CustomDropDownEventArgs with its current values.

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged

        Me.Label1.Text = Me.DropDownList1.SelectedValue

        Dim myEvent As New CustomDropDownEventArgs()
        myEvent.FullName = Me.DropDownList1.SelectedItem.Text
        myEvent.PayRollID = Me.DropDownList1.SelectedItem.Value

        RaiseEvent ListChanged(sender, myEvent)
End Sub

That's all we need to do in CustomDropDown.ascx, so we can make a few changed to the page containg the Web User Control, named default.aspx.

First, we modify the CustomDropDown1_ListChanged event to handle the class we created. We can then add code to this event to display the values returned from this class, which have been populated by the Web User Control.

Private Sub CustomDropDown1_ListChanged(ByVal sender As Object, ByVal e As CustomDropDownEventArgs) Handles CustomDropDown1.ListChanged
        Me.Label1.Text = e.FullName
End Sub

Now when we build and run the default.aspx page and select an employee, the SelectedIndexChanged event is raised within the Web User Control and the name of the employee is displayed in a label on the page.

 

Posted: Oct 08 2009, 19:45 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: VB.NET | VB.NET | VB.NET

Adding Events to Web User Controls in VB.NET

In a previous post, Creating Web Custom Controls in VB.NET we discussed how to create a very simple Web User Control, including how to respond to events raised by the controls contained within the Web User Control. In this post, we are going to extend upon this functionality and disuss how to handle events raised within the Web User Control from the page containing the the control. In otherwords, we want to learn how to tell the containing page when an event within the Web User Control has been raised and carry out some processing based on the results.

Firstly, we will need to make some changes to our Web Custom Web Control, code behind file named CustomDropDown.ascx.vb. To start with, we will declare a public Event called ListChanged.

Public Event ListChanged(ByVal sender As Object, ByVal e As EventArgs)

We then modify the DropDownList SelectedIndexChanged event to raise our new public Event.

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
               Handles DropDownList1.SelectedIndexChanged
         Me.Label1.Text = Me.DropDownList1.SelectedValue
         RaiseEvent ListChanged(sender, e)
End Sub

Now we can make use of the ListChanged Event within the containing page, in this case the default.aspx page. When we dragged the CustomDropDown control onto the design surface of the dafault.aspx page Visual Studio added a few lines of code to the source view. By default, it named our control CustomDropDown1 and registered the control within a Page declaration. Start by adding a label to default.aspx in which we can display a message when the event is raised.

In the code behind page, default.aspx.vb, we can add some code to handle the ListChanged Event.

Private Sub CustomDropDown1_ListChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CustomDropDown1.ListChanged
         Me.Label1.Text = "List Changed"
End Sub

When you build and run the page and select an emplyee, the employees PayrollID is displayed in the label inside the Web User Control as implemented in the previous post, but now we also have a second label that displays the text 'List Changed' after we have selected an employee. This simple example shows how it possible to handle a sinigle event within the Web User Control, in this case the SelectedIndexChanged event, from both inside the control and on the containing page.

 

In the next post we will expand upon this functionality and learn how to create and handle custom events for Web User Controls.

Posted: Oct 08 2009, 19:13 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: VB.NET | VB.NET

Creating Web User Controls in VB.NET

Visual Studio provides a quick and simple way to create controls that can be reused on any page and across multiple applications. These are called Web User Controls and are similar to standard ASP.NET pages in that they can contain other controls, such as TextBox's and ImageButtons. One difference is the the Web User Control cannot be opened directly in a browser and must be contained within another page, as it does not contain HTML page tags of its own.

This simple tutorial will show you how to create a new web user control and add it to a standard ASP.NET page.

Creating the Web User Control

Create a new folder in your project to strore your web user controls, in this example we will create a folder called UserControls

From the Website menu, select Add New Item and choose Web User Control. Name your new control, in this example we are creating a resuable DropDownList so we will name the control CustomDropDown.

 

In the design surface of the of the CustomDropDown.ascx you can drag and drop any of the standard ASP.NET controls. We will drag a Label and a DropDownList. From the DropDownList Tasks popup menu, select Choose Data Source and add the details to retrieve data from your database. In this exa,ple we are retrieving a list of employees from a SQL Server database, so we set the display field to 'FullName' and the value field to 'PayrollID'

 

Adding the Web Custom Control to and Existing Page

That's all we need to do to our web user control for the moment, so its time to add it to our default.aspx page to test it out. Open the default.aspx page in design view and simply drag and drop the CustomDropDown.ascx file into the design surface.

Once you have added the custom control, press F5 to build and run the dafault.aspx page. You should see the label and dropdownlist populated with your data from the database.

Responding to Events inside a Web Custom Control

As mentioned earlier, a Web Custom Control is very similar to a standard ASP.NET Page and can contain any number of other controls. The Web Custom Control can also respond to events raised by the control it contains, just like a standard ASP.NET page.

Going back to our CustomDropDown.ascx control we are going to add an event that will display the PayrollID of the employee selected in the dropdownlist. First, make sure that the dropdownlist is set to automatically postback, so that the SelectIndexChanged event fires when the user selects the name of an employee. Then add this code to you code behind page CustomDropDown.ascx.vb

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
Me.Label1.Text = Me.DropDownList1.SelectedValue
End Sub

Now when we run the default.aspx page, whenever a new employee is selected, their PayrollID is displayed in the label. Yes I know, this is hardly very exciting, but it demonstrates how easy it is to respond to events raised by controls within the Web User Control.

In a future post, we will look at how we can respond to events raised by the Web user Control from the page containing the control, for example if we want to show the name of the employee in a TextBox located on the default.aspx page. We will also look at how to send information to the Web User Control from the containing page, such as connection strings, which is very handy when you want to reuse the control with a different datasource.

Posted: Oct 08 2009, 18:19 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: VB.NET | VB.NET | VB.NET | VB.NET | VB.NET | VB.NET | VB.NET | VB.NET | VB.NET | VB.NET

Retrieving data from a SQL Server Database

As discussed in Retrieving Data from an Access Database, the DataReader object is one of the easiest ways to programmatically read data from a databace. The same is true for returning rows of data from a SQL Server database. A brief overview of what is required to retrieve data from a SQL Server database is as follows:

  1. Create a connection to the database
  2. Create a SQL query, with or without parameters, to access the required data
  3. Assign the SQL Query to a SQLCommand object
  4. Execute the SQLCommand to retrieve the records

Steps 1 to 3 have been covered in the posts detailing how to connect to a SQL Server Database and how to add parameters to a SQL Server query. The code snippet below assumes that you have read and understand those posts, so if you don't it's time to go back and familiarise yourself again.

'declare a DataReader

Dim dr As SqlDataReader 'For a SQL Server database

 

'populate the datareader with the results of the command

dr = cmd.ExecuteReader()

 

'create a loop to read values from the datareader

While dr.Read()

'gain access to the fields using something like below

Dim strValue As String = dr(fieldname).ToString()

End While

 

'close the read when done

dr.Close()

 

Posted: Oct 06 2009, 21:50 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: SQL Server

Retrieving Data from an Access Database

There are a number of easy ways to programmatically retrieve data from an Access database in .NET. The DataReader object is one of the easiest, especially when you need to loop through the data being returned to carry out some calculation or function.

The first step is to create and open a connection to the database using the technique shown in a previous post.

'declare a DataReader

Dim dr As OleDbDataReader 'For an Access database

 

'populate the datareader with the results of the command

dr = cmd.ExecuteReader()

 

'create a loop to read values from the datareader

While dr.Read()

'gain access to the fields using something like below

Dim strValue As String = dr(fieldname).ToString()

End While

 

'close the read when done

dr.Close()

 

Posted: Oct 06 2009, 21:35 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: Access

Adding Parameters to SQL Server Queries

Option 1:

Create a new Parameter object for each parameter required in the SQL query or Stored Procedure.

//Create a new command object

SqlCommand cmd = new SqlCommand();

 

//Create a new parameter

SqlParameter param = new SqlParameter("@parameterName", paramValue);

 

//add the parameter to the command

cmd.Parameters.Add(param);

Option 2:

Use the AddWithValue, to create Parameter and assign a value to a Command object.

//Create a new command object

SqlCommand cmd = new SqlCommand();

 

//add the parameter to the command

cmd.Parameters.AddWithValue("@parameterName", paramValue);

Posted: Oct 06 2009, 21:23 by CameronM | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: SQL Server | SQL Server

Connecting to an Access database

Class Declarations

Imports System.Data.OleDb

Function Code

'declare the Sql data controls

Dim conn As New OleDbConnection()

Dim cmd As New OleDbCommand

 

'declare and initialise the connection string

Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|Users.mdb"

 

'assign the string and open the SQL connection control

conn.ConnectionString = strConnection

conn.Open()

 

'set the command type depending on the query being used

'For dynamically created SQL

cmd.CommandType = CommandType.Text

cmd.CommandText = "SELECT * FROM tablename WHERE FilterBy LIKE @ParamName"

 

 

'set the connection to use with the command

cmd.Connection = conn

 

'utilise the command in one of the methods below...

Posted: Oct 06 2009, 20:16 by CameronM | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: Access

Connecting to the SQL Server database

Performing operations on data stored in a database is one of the most common tasks performed by developers. In fact, most modern web and desktop applications rely on data stored in databases at least to some degree. In the .NET world, SQL Server and especially SQL Server Express is almost the defacto standard database management system and even the free Express versions of the popular Visual Studio development tools support SQL Server database straight out of the box.

Of course you can use other database types, such as Access and Oracle, by simply using the libraries that are available for each specific database. Microsoft provide the System.Data.OleDB, which can be used for Access databases and third party libraries, such as the Oracle Data Provider for .NET (ODP.NET) are also available for a number of .NET framework versions.

Class Declarations

using System.Data.SqlClient;

Function Code

//Create a new connection object

SqlConnection conn = new SqlConnection();

conn.ConnectionString = "Data Source=local;Initial Catalog=TestDB;Integrated Security=True";

 

//Create a new command object

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

 

//to define dynamically created SQL

cmd.CommandType = CommandType.Text;

cmd.CommandText = "SELECT * FROM SAMPLE";

 

//to define a Stored Procedure

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "sp_SAMPLE"; //stored procedure name

 

//when using a data reader you must explicitly open and close the connection

conn.Open();

 

SqlDataReader dr = cmd.ExecuteReader();

 

conn.Close();

NOTE: Most experts recommend avoiding dynamically created SQL statements, as these can cause performance issues and represents a potential security threat. Use stored procedures unless there is no other choice (and yes there is always a choice).

Posted: Oct 06 2009, 19:39 by CameronM | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: SQL Server