1

I am developing asp.net mvc2 application and I use asp.net membership provider which uses ASPNETDB.mdf database. I have also my own database and now I wonder how to upload these 2 databases to server? Should I upload them as .mdf file or should I use SQL server? I prefer using SQL server and if someone knows the shortest way to convert and upload these 2 databases it would help me a lot.

Thanks in advance,
Ilija

Improve this question

3 Answers 3

Reset to default
6

Funny I just finished doing the same thing. The basic steps are as follows:

  1. From Visual Studio, load your .mdf and choose "publish to provider" to make a .sql file.
  2. Open SQL Management Studio, open a connection to your database and load the sql file. Add a "use yourdbname;" on top to have it output the tables to your database, then run it.
  3. Now you should have the full table structure. What's left is to modify web.config to read the new tables:

First the membership provider:

<membership> <providers> <clear/> <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a " connectionStringName="ConnectionStringLoginInfo" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/" /> </providers> </membership>

Now the role provider:

<roleManager enabled="true"> <providers> <clear/> <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a " connectionStringName="ConnectionStringLoginInfo" applicationName="/" /> </providers> </roleManager>

And lastly the WebPart provider, if you use it:

<webParts> <personalization defaultProvider="SqlDatabaseProviderDRDBLoginInfo"> <providers> <clear/> <add connectionStringName="ConnectionStringLoginInfo" type="System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider" name="SqlDatabaseProviderDRDBLoginInfo"/> </providers> </personalization> </webParts>

In this example I called the connection string ConnectionStringLoginInfo, but whatever you name it, make sure you set it in the connection strings part. Not gonna paste that too :)

This all took me way more than I care to say, but when I saw my app working flawlessly with the App_Data folder deleted, that was quite the moment!

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Another option is to override the LocalSqlServer connection string in your web.config to point to the database on the host. This way you don't have to change each provider to use a different Connection String.
I think I messed that up somehow, this way is the only way I managed to get it to work, and I'll be damned if I ever touch it again :)
thank you Blindy, in my country we would say that your answer appeared as "axe in the honey" :) @Waleed Al-Balooshi, can you give me some more details on how to try this override? Thx!
@ile I posted an answer explaining how to override the LocalSqlServer connection string.
1

Your simplest option for a hosted solution (i.e. your hosting plan is not a Virtual Private Server) is to generate SQL scripts of your database, exporting these to *.sql files and then run them in your hosted SQL connection.

I would normally connect to my web host's SQL instance using SQL Server Management Studio and either open or paste in the scripts generated by my local copy.

Depending on whether or not your web host provides the service, you might also be able to use the "Publish to provider..." option in Visual Studio.

Improve this answer

Comments

1

As a supplement to Blindy's answer I wanted to mention that another way to configure the providers is to change the connection string settings of the default ConnectionString used by most of the providers, which is LocalSqlServer. To do this you just override that particular ConnectionString in your web.config like so:

<connectionStrings> <clear /> <add name="LocalSqlServer" connectionString="change this to be the details of your host database" providerName="System.Data.SqlClient" /> </connectionStrings>

Also, if you don't want to clear the entire connectionStrings section you can just remove the particular connection string like this:

<connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="change this to be the details of your host database" providerName="System.Data.SqlClient" /> </connectionStrings>

This works, because all providers that default to using Sql Server for their Data Store - such as the membership provider - use the "LocalSqlServer" connection string by default. Thus, if you override it, you don't have to change each provider to point to a different Connection String.

Also, for security reasons, you might want to look into encrypting the connectionString section of your web.config file. The following two articles provide more info.

Encrypting and Decrypting Configuration Sections

How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA

Improve this answer

6 Comments

So you are saying that aspnetdb should already exist on the commercial server? If so, how am I suppose to add user to this db?
@ile As Blindy mentioned you will need to upload the contents of your aspnetdb database to your host database. Once uploaded you will need to make sure that your code now points to the correct database and this is where overriding the LocalSqlServer connection string comes in. This is the connection string used by the providers to connect to the aspnetdb.mdf file. So, once you upload the db content to your host, one option is to just change this particular connection string to point to the host database. Otherwise; you can follow step 3 from Blindy's post.
Now I am confused a little bit... I don't see point of 3rd step from Blindy's post but I suppose I will find out tomorrow when I try to set up database. Thank you for your post!!
@ile The point of no 3 from Blindy's post is that the membership provider that you use by default connects to the aspnetdb.mdf file, which is pointed to by the LocalSqlServer connection string. When you move your data to the host database, you need to make sure that the membership provider knows where it is. There are two ways to do this. 1) Change the "LocalSqlServer" connection string like I showed you. 2) Change the membership provider settings in web.config to use a DIFFERENT connectionString than LocalSqlServer that points to your host database like Blindy shows in his step 3.
@ile exactly. IF you want to use a different name, then you need to follow the instructions posted by Blindy in his step 3.. Otherwise; all you will need to do is override LocalSqlServer like I showed you and not have to do anything with the membership provider configuration.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.