Archive for the 'ASP.NET' Category

GridView To DataSet To Excel

Cross Posted From Here

My weekend was a bit busy as I am working on a small project and one of the requirements that I have been doing is converting GridView data to an Excel document. I checked the resources in the internet and I can’t seem to find anything that fits my need. Things become more difficult as I am working on dynamic GridViews where the number of GridViews that would be displayed on a certain instance depends on the value that will be returned by the query as well as the text in some of the cells that it should contain.

Researching further, I found an article converting DataSets to Microsoft Excel without the use of COM objects. This would be a good headstart to keep things rolling. Though, I found no explicit API that allows me to convert GridView data to DataSets. Moreover, I do customizations on display on my GridView so I prefer to have an exported data that outputs the same content in the Excel worksheet as on my screen.

GridView objects have an event called RowDataBound and this allows the developer to access the value of the fields before it is displayed in a GridView. This is quite helpful in cases wherein if you want to customize display text in a BoundField column from a bit-typed DataField source rather than from a default true/false display to something more descriptive (which in case in an ordering system, display “Delivered/Not Yet Delivered” than a “true/false” value). Having that in mind, I played with the event and created a dynamic DataTable out of the value rendered in the GridView. In my example, I used the Northwind database and queried the Customers table with a SqlDataSource. There are 2 GridViews in my form where the first one consumes the result of the SqlDataSource and the other one that will consume the generated DataSet (I have a decided to append my generated DataTable to a DataSet so that I can consume the code indicated above) from the first GridView.

Continue reading ‘GridView To DataSet To Excel’

ASP.NET Membership Database Migration Helper

This is my project born out of a problem on how to backup the existing data that I have on a website. In one of my projects, I have created a user registration system based on ASP.NET Membership although as I reach the peak of the limits of the database hosting, I need a means to transfer the data from that database to another.

Unfortunately, the original database host of my old project didn’t allow me to connect to the database via SQL Server Management Studio and it would be a pain to manually gather the membership information and maintain its integrity particularly the password if the Membership provider configuration for the password is set to “Hashed”.

I tried searching if there’s an API for programaticaly migrating data but I haven’t found one. Solutions found over the net include backing up the database and restore it in a local store but I find that tedious. I need something that is as much as possible with less clicks. With that, this application is an attempt to solve that problem.

Directions:

1. Start with Start.aspx (Default.aspx will eventually redirect you to that page). Provide the following information:

a) Source Database Connection String
b) Source Database Application Name
c) Target Database Connection String
d) Target Database Application Name

a & c are obvious reasons why you need to provide them. “b” is needed since we need to specify what specific application database are we backing up. Typically, the ASPNETDB.mdf file that is being created whenever we fire up the Website Administration Tool (WSAT) can handle users for use with Membership for different web applications by virtue of applicationId. This can be verified by opening the aspnet_Applications table. On the other hand “c” is needed since we need to check that the database doesn’t contain that application name as it might overwrite any existing data in there (in cases you want to merge Membership databases).

2. Clicking validate will validate the information that you have provided. Until such time all entries appear to be valid, then it would only be at that period you can click on the “Export >>” button that will perform the actual migration. Likewise, once validated, you can also delete or clear the target database of all it’s entries.

WARNING:

Be aware only that this action is irreversible and there’s no way of backing it up unless you have a spare copy of that database somewhere else.

3. You would be informed in Result.aspx if the migration is okay or not. TryExport.aspx is actually the page that I made to test if the export is working or not. Before trying it out, do the following:

  • Modify web.config by editing the connection string list and the provider list. Edit the connection string section first as the providers will depend on the entries of this section. This application is by default populated with two providers namely: AspNetSqlMembershipProvider (source) and ExportedDBMembershipProvider (destination). The connection string attribute of the provider should match that of the connection string name of the source/destination databases. Failure to do so will prohibit you from validating if the export works or not.
  • The applicationName attribute of the destination provider should match the “Target Application Name” specified in Start.aspx. Failure to do so will prohibit you again from validating if the export works or not.
  • You can change the name of the providers and will allow you to validate however, you need to change as well the values set in the code-behind of TryExport.aspx for the provider name.

Additional Notes:

  • I tested the code to work when transporting between two SQL Server 2005 Express database files. However, the application might throw an error when the data is too big to transfer.
  • This application just exports the application data, membership information and roles. I am still making a way to include the Profile data (if there’s any) to the exported database.

The file is in zip format but since wordpress is not accepting ZIP files as attachment, you can download it here and rename it to .zip.

Comments, reactions, suggestions? Post a comment here!

Where I Was Last Weekend

Like what I mentioned in my previous blog entry, I was in Subic last weekend of another ASP.NET talk. The talk went well only that it was after my talk I was reminded that I spoke for an hour and a half. No wonder I got carried away with my talk knowing that the venue is really a good place to talk. Even if my laptop malfunctioned before my talk (function key isn’t working until I brutally force it to work), the audience were delighted with the topic I presented.

All in all, we have 67 persons in attendance and at least 30 of them were students (though I think they comprise majority of the audience).

After spending 2 consecutive weekends out of Manila, I will be having a rest this week but hopefully can get around after. You can read the organization’s write-up here. I just realized, I should learn how to smile again in front of the camera. 😛

Building Some Web TreeViews

The TreeView control in ASP.NET 2.0 is used to display hierarchical data, such as table of contents or file directory in a tree structure. In most applications that I do, I use the TreeView control to show file directory structure. I was able to create a class that populates a TreeView provided with a physical directory structure residing on the same computer as to where the server is running. Rename the file below to .dll and use it as a reference on your web application. Once added to your web application usage would be:


using System;
// include other namespaces as needed
using PhysicalTreeView;

public partial class Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string location;
        pTreeView ptv = new pTreeView(location);
        // the example below is valid without specifying the "isWeb" to false.
        pTreeView ptv = new pTreeView("~/");
        ptv.isWeb = false;
        ptv.PopulateTree(TreeView1);
    }
}

By default, the variable location will treat the path as a virtual path. If you want to specify a specific physical directory, you can specify the 8 character directory format as a location value but you need to specify the property “isWeb” as “false” and as shown above. The “TreeView1” is assumed to be a TreeView control placed onto the ASPX file. For testing purposes, you can put in your code behind the following code without placing a TreeView control in your ASPX file but will output the same.


TreeView TreeView1 = new TreeView();
string location;
pTreeView ptv = new pTreeView(location);
// the example below is valid without specifying the "isWeb" to false.
pTreeView ptv = new pTreeView("~/");
ptv.isWeb = false;
ptv.PopulateTree(TreeView1);

this.Form.Controls.Add(TreeView1);

Physical Drive TreeView DLL

The challenge for me is to bring the same functionality to a SourceSafe database which is despite having a directory like structure isn’t directory based at all when accessed programmatically. Furthermore, the DLL provided with Visual Source Safe 2005 isn’t a native .NET object but a COM interop one.

Luckily, using the same logic behind the previous DLL, I managed to create a DLL to populate a TreeView from a SourceSafe database. The constructor needs only 3 parameters namely the database location, username, and password.

Sample usage would be:


using ProjectTreeView;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        string location, user, password;
        // Populate your own values

        ssTreeView stv = new ssTreeView(location, user, password);
        stv.BuildSourceSafeTree(TreeView1);

    }
}

Same with the code above, the code assumes that there’s a TreeView control instance in the ASPX file that has an ID of “TreeView1”.

Sourcesafe TreeView

For questions, feel free to add some comments. 🙂

Sourcesafe Automation Bug

As part of my continuing project in Sourcesafe Automation, I found a weird behavior of the Visual Studio Sourcesafe Interop DLL.

The following code is in Visual Basic 5 which basically connects to a Sourcesafe database and lists all the projects in the root folder of the database.


Dim vssdb As New VSSDatabase
 
vssdb.Open "<location to sourcesafe srcsafe.ini>", "<username>", "<password>"
 
Dim xitem As VSSItem
For Each xitem In vssdb.VSSItem(0).Items(False)
   Debug.Print (xitem.Name)
Next

Subsequently, the code equivalent in C# (.NET) , is something that I have blogged before. What I find unusual about the Sourcesafe Interop DLL are the following items:

Continue reading ‘Sourcesafe Automation Bug’

Searching Approved Users in Roles in ASP.NET Membership

Still more ASP.NET membership tweaks!

The role manager in ASP.NET Membership provides a mechanism to list all the users in the ASP.NET Membership Database for a specific role.


Roles.GetUsersInRoles("rolename");

This method returns a string array of usernames that’s a member of that role. Unfortunately, this list returns all the users even if they are approved or not, locked out or not. If you are working on an application that requires to filter out users in the role that’s locked out or isn’t approved yet, there’s no other mechanism on the built-in methods. There’s no overload to the method provided out of the box.roles.png

Luckily, as mentioned in the previous blog about ASP.NET membership, the database is filled with various stored procedures that you can use (or even pattern after) for various usage. I tweaked one of the stored procedures and came up with a stored procedure that gets all the approved users in the ASP.NET membership database.


CREATE PROCEDURE dbo.Membership_GetActiveUsersInRoles

    @RoleName nvarchar(256)

AS

    BEGIN

        DECLARE @RoleId uniqueidentifier

        SELECT @RoleId = RoleId
        FROM dbo.aspnet_Roles
        WHERE LOWER(@RoleName) = LoweredRoleName

        SELECT u.UserName, u.UserId
        FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur, dbo.aspnet_Membership m
        WHERE
        u.UserId = m.UserId AND
        u.UserId = ur.UserId AND
        @RoleId = ur.RoleId and
        m.IsApproved = 'true'
         -- You can also specify m.IsLockedOut = 'false' for other purposes

END

Just copy the said code above and create it in your ASP.NET Membership database (either that ASPNETDB.MDF or an ASP.NET membership configured database) and run it.Happy Coding!

PHINUG February Technical Sharing

PHINUG is back for its first event for 2008!

Presenting new features for Visual Studio 2008 and ASP.NET 3.5 are our topics for the February Technical Sharing. Our speaker is Edwin Sarmiento, a senior systems engineer/DBA for Fujitsu Asia Pte Ltd in Singapore and a Microsoft MVP for Windows Small Business Server. He is responsible for maintaining 200+ servers and databases for a global client in 10 countries.

The event is free and the link to the sign-up page is located here.


Blog Stats

  • 3,922 hits
June 2017
S M T W T F S
« Nov    
 123
45678910
11121314151617
18192021222324
252627282930