CODEWITHSUNDEEP
c#asp.netsql-serverdatabasedeployment
Dev Dreamer
Dev Dreamer

Reputation: 289

Connection string when database (*.mdf) is copied on the web hosting server

I am continuing my previous question, as the reply lead to further doubts/points/concerns. I need help with the connection string on the web host server.

My connection string in the local computer is:

string connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\data.mdf;Integrated Security=True;User Instance=True";

Now, I simply used the website copy tool with the VS 2010, and the entire website is copied as it is on the web host, with the database being at domainName/httpdocs/Experiment/App_Data/data.mdf

Now, I don't know how the complete connection string on the web hosting server look like. Some of the things which I learned, on the basis of those, I guess it should be:

string connectionString = "Data Source=IP_Address_Of_WebHostingServer;AttachDbFilename=|DataDirectory|\data.mdf;User ID=XXXX;Password=XXXX;User Instance=True";

Is it right (or COMPLETE?)? Also, I have no clue what the user id or password is? So on local computer, with the help of Integrated Security=True the windows authentication was being made. How to do it on the remote web host?

In the webhosting console, I see features such as create database, create database users, I can see the username and password aspects there. If those are required then how do I connect that with the database I just copied? It seems like those are the one's where database is created from scratch, while I already have the *.mdf (database) uploaded/copied.

I am stuck at this stage, and have no clue on how to proceed further. I know its something trivial, but out of scope of my knowledge. Please help me in completing the connection string. How do I make the database (data.mdf) file reachable/accessible?

I came across some articles which told to import the database and such (but where/why?), but I don't get it. When the database is in the App_Data folder, then why/how do I do that?

I am confused, please help.I'll highly appreciate step-by-step approach to fix it.Thanks.

EDIT (solution):

The solution given below is perfect. Apart from that this website/blog is worth checking. -- http://www.asp.net/web-forms/tutorials/deployment/deploying-web-site-projects/asp-net-hosting-options-cs

And if you don't have SQL Management Studio, best way to install is instructed here: -- http://blogs.msdn.com/b/bethmassi/archive/2011/02/18/step-by-step-installing-sql-server-management-studio-2008-express-after-visual-studio-2010.aspx

Upvotes: 1

Views: 4309

Answers (1)

Luke Baughan
Luke Baughan

Reputation: 4686

Step 1 - Create a DB Script from SQL Management Studio

You will need to firstly script off you database schema and data (not as scary as it sounds - follow the steps here http://blog.sqlauthority.com/2011/05/07/sql-server-2008-2008-r2-create-script-to-copy-database-schema-and-all-the-objects-data-schema-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/ ) .

Step 2 - Create your DB at HostGator and Import your DB Script

Create your database at HostGator and import your script file (Follow this guide here http://support.hostgator.com/articles/plesk/plesk-9/how-to-create-or-import-databases-plesk-9 ).

Step 3 - Update your connection string and deploy!

You'll need to update your connection string to be something like this (you will need to add your details).

<add name=”CRMConnectionString” connectionString=”Data Source=Server IP;Initial Catalog=DBName;User ID=UserName;Password=Pwd;” providerName=”System.Data.SqlClient”/>

This connection string was cribbed from this resource here http://asoftwaredeveloper.wordpress.com/2012/01/06/hostgator-web-hosting-and-mssql-db-access/

Then publish your website and upload your files. Its worth noting that you won't need to update your App_Data folder and its contents when you publish because you'll be pointing at the DB on their server not the one in your directory.

Upvotes: 2

Related Questions