I hope I am in the right place. I am new at MS SQL so please bear with me - I am probably asking dumb questions here.
Situation: I am moving a website from ExpressionEngine to an Asp.Net website. There is nothing really straight forward about this. The main thing I am trying to do before I get to the easy part is to import all of the members (over 1000 users) from the MySql
database to SQLExpress. I have the MS Sql database set up using aspnet_regsql.exe. I can connect to it with no problem from VWD 2008 and can register a new user or log in / out. I am hoping to import all of the users and their datainto the existing database.
The MySql database table for the users is exp_members and the table for the MS Sql database is aspnet_users. They of course have different structures and I am not very familiar with database operations.
Any help or suggestions would be greatly appreciated.
Select the required info (for the example I’ll assume it is ‘UserName, ‘Password’ & ‘Email’) from your exp_members table and save it to a csv file.
Create a ‘staging’ table (with fields: ‘UserName, ‘Password’ & ‘Email’), in your new SQL database, to hold this info until you’ve created the users.
Import the csv file to the table created. Using a SQL query like the on below - which assumes you have column heading and will start the import from row 2.
BULK INSERT userStagingTable
FROM 'C:\ImportFile.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
GO
Create a webpage “CreateUser.aspx” and add a button to it with a click Event “btnCreate_Click”
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class CreateUser : System.Web.UI.Page
{
protected void btnCreate_Click(object sender, EventArgs e)
{
SqlConnection conn = null;
string connString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("SELECT * FROM userStagingTable", conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dt = new DataTable();
dt.Load(dr);
for (int i = 0; i < dt.Rows.Count; i++)
{
string strUserName = dt.Rows[i]["UserName"].ToString();
string strEmail = dt.Rows[i]["Email"].ToString();
string strPassword = dt.Rows[i]["Password"].ToString();
Create_User(strUserName, strEmail, strPassword);
}
conn.Close();
}
protected void Create_User(string strUserName, string strEmail, string strPassword)
{
MembershipCreateStatus status;
MembershipUser user = Membership.CreateUser(strUserName, strPassword, strEmail, null, null, false, out status);
Roles.AddUserToRole(strEmail, "Member");
MembershipUser myObject = Membership.GetUser(strEmail);
string strUserId = myObject.ProviderUserKey.ToString();
}
}
Above will loop through you table of users, creating each one and setting a password and their email address.
I will give this a try and if I have any problems, I will ask here again. This is what I was looking for. I will mark as resolved as soon as I try this.
I have been working with this. So far I have been able to import the data into the staging table.
When I run the code in the page I get the error:
Object reference not set to an instance of an object.
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Line 38:
Line 39: Dim myObject As MembershipUser = Membership.GetUser(strEmail)
Line 40: Dim strUserId As String = myObject.ProviderUserKey.ToString()
Line 41: End Sub
Line 42: End Class
I am not smart enough to know what to look for here but I suspect it is something pretty simple.
Getting closer I think, but still get the same error:
"Object Reference not set to an instance of an object"
That being said, it does appear to get the first user in the table but nothing after that. If I run it twice, I get the message:
"The user 'Joe' is already in the role 'members'" so it is doing something, just does not appear to be looping.
The fields in the "CSVTest" table it is pulling from are:
Username, Screen_Name, password, email
Here is what I have for code:
Line 38:
Line 39: Dim myObject As MembershipUser = Membership.GetUser(strEmail)
ERROR --> Line 40: Dim strUserId As String = myObject.ProviderUserKey.ToString()
Line 41: End Sub
Line 42: End Class
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.Security
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Public Class CreateUser
Inherits System.Web.UI.Page
Protected Sub btnCreate_Click(sender As Object, e As EventArgs)
Dim conn As SqlConnection = Nothing
Dim connString As String = ConfigurationManager.ConnectionStrings("Altered_ConnectionString").ConnectionString
conn = New SqlConnection(connString)
Dim cmd As New SqlCommand("SELECT Username, password, email FROM CSVTest", conn)
conn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim dt As New DataTable()
dt.Load(dr)
For i As Integer = 0 To dt.Rows.Count - 1
Dim strUserName As String = dt.Rows(i)("UserName").ToString()
Dim strEmail As String = dt.Rows(i)("email").ToString()
Dim strPassword As String = dt.Rows(i)("Password").ToString()
Create_User(strUserName, strEmail, strPassword)
On Error Resume Next
Next
conn.Close()
End Sub
Protected Sub Create_User(strUserName As String, strEmail As String, strPassword As String)
Dim status As MembershipCreateStatus
Dim user As MembershipUser = Membership.CreateUser(strUserName, strPassword, strEmail, Nothing, Nothing, False, _
status)
Roles.AddUserToRole(strUserName, "Members")
Dim myObject As MembershipUser = Membership.GetUser(strEmail)
Dim strUserId As String = myObject.ProviderUserKey.ToString()
End Sub
End Class
clausont
Member
30 Points
102 Posts
Import Users Membershipprovider
Nov 25, 2012 03:09 PM|LINK
I hope I am in the right place. I am new at MS SQL so please bear with me - I am probably asking dumb questions here.
Situation: I am moving a website from ExpressionEngine to an Asp.Net website. There is nothing really straight forward about this. The main thing I am trying to do before I get to the easy part is to import all of the members (over 1000 users) from the MySql database to SQLExpress. I have the MS Sql database set up using aspnet_regsql.exe. I can connect to it with no problem from VWD 2008 and can register a new user or log in / out. I am hoping to import all of the users and their datainto the existing database. The MySql database table for the users is exp_members and the table for the MS Sql database is aspnet_users. They of course have different structures and I am not very familiar with database operations.
Any help or suggestions would be greatly appreciated.
Thanks in advance.
Basquiat
Contributor
2379 Points
628 Posts
Re: Import Users Membershipprovider
Nov 27, 2012 06:09 AM|LINK
Hi
Select the required info (for the example I’ll assume it is ‘UserName, ‘Password’ & ‘Email’) from your exp_members table and save it to a csv file.
Create a ‘staging’ table (with fields: ‘UserName, ‘Password’ & ‘Email’), in your new SQL database, to hold this info until you’ve created the users.
Import the csv file to the table created. Using a SQL query like the on below - which assumes you have column heading and will start the import from row 2.
Create a webpage “CreateUser.aspx” and add a button to it with a click Event “btnCreate_Click”
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.Security; using System.Data; using System.Data.SqlClient; using System.Configuration; public partial class CreateUser : System.Web.UI.Page { protected void btnCreate_Click(object sender, EventArgs e) { SqlConnection conn = null; string connString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString; conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand("SELECT * FROM userStagingTable", conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); DataTable dt = new DataTable(); dt.Load(dr); for (int i = 0; i < dt.Rows.Count; i++) { string strUserName = dt.Rows[i]["UserName"].ToString(); string strEmail = dt.Rows[i]["Email"].ToString(); string strPassword = dt.Rows[i]["Password"].ToString(); Create_User(strUserName, strEmail, strPassword); } conn.Close(); } protected void Create_User(string strUserName, string strEmail, string strPassword) { MembershipCreateStatus status; MembershipUser user = Membership.CreateUser(strUserName, strPassword, strEmail, null, null, false, out status); Roles.AddUserToRole(strEmail, "Member"); MembershipUser myObject = Membership.GetUser(strEmail); string strUserId = myObject.ProviderUserKey.ToString(); } }Above will loop through you table of users, creating each one and setting a password and their email address.
clausont
Member
30 Points
102 Posts
Re: Import Users Membershipprovider
Nov 27, 2012 08:09 PM|LINK
Thanks Basquiat,
I will give this a try and if I have any problems, I will ask here again. This is what I was looking for. I will mark as resolved as soon as I try this.
Thanks!
clausont
Member
30 Points
102 Posts
Re: Import Users Membershipprovider
Dec 01, 2012 04:49 PM|LINK
I have been working with this. So far I have been able to import the data into the staging table.
When I run the code in the page I get the error:
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Basquiat
Contributor
2379 Points
628 Posts
Re: Import Users Membershipprovider
Dec 02, 2012 03:16 AM|LINK
clausont
Member
30 Points
102 Posts
Re: Import Users Membershipprovider
Dec 02, 2012 06:15 PM|LINK
Getting closer I think, but still get the same error:
"Object Reference not set to an instance of an object"
That being said, it does appear to get the first user in the table but nothing after that. If I run it twice, I get the message:
"The user 'Joe' is already in the role 'members'" so it is doing something, just does not appear to be looping.
The fields in the "CSVTest" table it is pulling from are:
Username, Screen_Name, password, email
Here is what I have for code:
Line 38: Line 39: Dim myObject As MembershipUser = Membership.GetUser(strEmail) ERROR --> Line 40: Dim strUserId As String = myObject.ProviderUserKey.ToString() Line 41: End Sub Line 42: End Class Imports System.Collections.Generic Imports System.Linq Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Imports System.Web.Security Imports System.Data Imports System.Data.SqlClient Imports System.Configuration Partial Public Class CreateUser Inherits System.Web.UI.Page Protected Sub btnCreate_Click(sender As Object, e As EventArgs) Dim conn As SqlConnection = Nothing Dim connString As String = ConfigurationManager.ConnectionStrings("Altered_ConnectionString").ConnectionString conn = New SqlConnection(connString) Dim cmd As New SqlCommand("SELECT Username, password, email FROM CSVTest", conn) conn.Open() Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Dim dt As New DataTable() dt.Load(dr) For i As Integer = 0 To dt.Rows.Count - 1 Dim strUserName As String = dt.Rows(i)("UserName").ToString() Dim strEmail As String = dt.Rows(i)("email").ToString() Dim strPassword As String = dt.Rows(i)("Password").ToString() Create_User(strUserName, strEmail, strPassword) On Error Resume Next Next conn.Close() End Sub Protected Sub Create_User(strUserName As String, strEmail As String, strPassword As String) Dim status As MembershipCreateStatus Dim user As MembershipUser = Membership.CreateUser(strUserName, strPassword, strEmail, Nothing, Nothing, False, _ status) Roles.AddUserToRole(strUserName, "Members") Dim myObject As MembershipUser = Membership.GetUser(strEmail) Dim strUserId As String = myObject.ProviderUserKey.ToString() End Sub End Class