import users to ASP.NET membership from a file

Question

I have a site using ASP.NET membership. I also have an excel file with about 60 user records. How can I import the records into the membership database, without having to type all 60 into the ASP.NET Web Site Administration Tool.

Thank you

Answer 1

Choice 1

If you're lucky, Excel has a way to export the rows directly to your database (this depends on which Database you're using)

Choice 2

Write a program that iterates over each row in the excel table and inserts it manually into your database (again, process depends on which one)

Answer 2

Here's three options for you

1. There may be an import data option in SQL Server Management Studio, you can use this (I just checked in SQL Server Express and it wasn't there but I'm certain it exists in the full version of Management Studio - can't check at the moment).

2. Use SSIS if you have it

3. You can do it old school and use Excel to build up a SQL string with references to the cells. Do this for the first row and then copy down. Copy all the statements into a query window and execute!

Answer 3

foreach (record in recordset) {
    if (!(Roles.RoleExists(record["Role"])) {
        Roles.CreateRole(record["Role"]);
    }
    
    if (Membership.GetUser(record["Username"]) == null) {
        System.Web.Security.MembershipCreateStatus status;
        Membership.CreateUser(record["Username"], record["Password"], record["Email"], record["RSecurityQuestion"], record["SecurityAnswer"], true, status);
        if (status == System.Web.Security.MembershipCreateStatus.Success) {
            Roles.AddUserToRole(record["Username"], record["Role"]);
        }
    }
}

I can't recall the code to create a recordset from an Excel file off the top of my head.

EDIT: Here is a good post on how to read/write excel files with ADO.NET