Modify ADO connection string...

In a simple drag and drop application, I have:

connectionString="Data Source=MIKE-VAIO;Initial Catalog=WSCGSoftware;Integrated Security=True"

connecting to a database (WSCGSoftware) in SQL Server 2012 Enterprise. I used  "Add New Data Source" wizard to do this.

Question: How can I modify this connection string to the same database at "C:\TestFolder\WSCGSoftware.mdf" on the same computer?

This application will be tested in another computer later with "C:\TestFolder\WSCGSoftware.mdf" on it, but there will be no SQL Server installed on it.

If this is not possible, what if the second computer had SLQExpress installed on it.

Idealy, path "C:\TestFolder\" should be the root directory of the project itself. Some comment on it will be appreciated.

Thank you.
 
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Test_A.My.MySettings.WSCGSoftwareConnectionString"
            connectionString="Data Source=MIKE-VAIO;Initial Catalog=WSCGSoftware;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" />
    </startup>
</configuration>

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Since the connection string is to connect to the SQL Server, not the file, I don't see how you could possibly do that.  You SQL statements and driver are not going to work directly on the file.  They need the SQL Server to operate properly.
0
TheAvengerCommented:
SQL Server will know where the database is because it is attached to it. The database is unique, so once you give the name, you identify the exact database. Note that you can move the database files to different place (even have multiple files at different disks) but it is still one database with one name.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
DaveBaldwin,

re:> to connect to the SQL Server, not the file,

The attached image shows Option 1 (connecting to SQL Server) and Option 2 (connecting to database file). Having said this, I can see how it still all the drivers etc. that comes with SQL Server will be used with option 2.

So, then as stated in the original question: "...what if the second computer had SLQExpress installed on it..." then how would you modify the connection string in order to be working with "C:\TestFolder\WSCGSoftware.mdf" where WSCGSoftware may or may not present in the SQL installed.

 TheAvenger,

re:> SQL Server will know where the database is because it is attached to it.

There might be could of instances of SQL Server with the database with the same name but with different data + plus one in C:\TestFolder again with different data. In response to this part please consider Option 1 and Option 2 on the attached image.


Also see:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28227481.html

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\Mike\Documents\__smc\_CS 32 ADO\WSCGSoftwareDatabase\WSCGSoftware.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

This type of connection is what I am expecting from the experts.

Thank you,

Mike
ChangeDataSource.png
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Dave BaldwinFixer of ProblemsCommented:
You're not going to get that and you didn't read your image text.  It says...

Use this selection is to Attach to a local Microsoft SQL Server instance.

Several connection string on this page http://www.connectionstrings.com/sql-server/ show how to connect a 'file' to the local SQL Server.  There isn't any case that uses a file without an SQL Server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Sorry, this is valid one:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="TestB.My.MySettings.WSCGSoftwareConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WSCGSoftware.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" />
    </startup>
</configuration>

Open in new window


I have generated this on a yet third computer with SQl Express on it and it works.

So, basically, I want have some conversation about it I can get more education about it.

The problem is this:
I have SQL Server Enterprise. I develop this simple application easily. Butthe other computer (the third computer has SQL Express).

1. I do develop with database in SQL Server 2012.
2. Give to someone else who has only SQL Express.
3. In creating the data connection we have agreed to use Option 2 (in the above image). This way, the database travels with the project and it doesn't depend on the database in their SQL Server.

So, I want to develop in SQL Server 2012. When done, modify the connection string and give it to the other person to look at the application with my data included with my app.

Alternatively, you can supply answer to the link given before to my other question this way, I don't have to look for work around like this.

Thanks,

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Dave,

re:> There isn't any case that uses a file without an SQL Server.

I never said there is. I assumed there maybe hence the question because I was not sure about it and then stated if this is not possible then...

Bottom line is:
Option1 :
   <connectionStrings>
        <add name="Test_A.My.MySettings.WSCGSoftwareConnectionString"
            connectionString="Data Source=MIKE-VAIO;Initial Catalog=WSCGSoftware;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

To, Option 2:

    <connectionStrings>
        <add name="TestB.My.MySettings.WSCGSoftwareConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WSCGSoftware.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

Open in new window

0
Dave BaldwinFixer of ProblemsCommented:
You said "but there will be no SQL Server installed on it."
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Sorry Dave,

I am not blessed with English as my first language, I probably have not described properly.

When I wrote: "If this is not possible, what if the second computer had SLQExpress installed on it." I meant to say if lack SQL Server installation was not an option, lets proceed with assumption that SQL Express is present.

Update: Looking at the connection string in my last comment, I think I probably have answered my question partially, if you agree. I can change the connection string but as to manually copying and pasting the database from SQL Server .../DATA/ folder to the project root folder I am not sure but I can test it and post it here.
0
Dave BaldwinFixer of ProblemsCommented:
To be clear, you can not actually use Only the file but you can ask SQL Server or SQLExpress to attach that file and use it.  As for the actual file location, you just have to get the details right.  'mdf' files do not have to be in the standard 'data' directory, I have several that are not that work fine.  I'm not sure but I think you need to 'un-attach' the database to properly copy it.  Maybe you will want to keep a 'reference' copy that is not currently attached so you could copy it and attach it on the target system.
0
TheAvengerCommented:
@Dave: The right term is "Detach". There is a command when you right click on the database. Also you can see which files are in use by the database by right clicking and going to the different options that have the name "file" in them.

The database files can be in any folder that is accessible for the SQL Server user. This means the user has to have rights to read and write to the files.

If you want to make a copy of the database or move it to another server, the options are to detach it (a bit bad because it might mess up some security roles when attaching again) or the better option stop the SQL Server service, copy the files, start the SQL Server service. Works 100%
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you very much for the info. You are right on every point made. After comparing the content of project folder, I see that it is not easily possible to manually copy and paste the files. But at least I know what wouldn't work.

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
TheAvenger,

Thanks for the good comments.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.