Change desktop application to client server based

Question

We have developed a VB.NET application with SQL Server as the back end.

Currently, the .mdf file is deployed along with the other files required.

Installation of the application is done and the database file (.mdf) is being placed and used from the local system. The application runs well in the local system.

Now the user needs the application to run in a multi-user system (server and client). As we know, to run the application in client system, the .mdf should be attached to the SQL Server. Attaching the .mdf to the server should be done programmatically and SQL Server Management Studio should not be used.

How can this be done?

OS: Windows 7
Front End: VB.NET
Back End: SQL Server Express Instance

do u have access to executing a stored procedure.?. I dnt know .net . so just asking — Jithin Shaji, Nov 05 '14 at 07:15

score 1 · Accepted Answer · answered Nov 05 '14 at 08:15

1

Connect to the database using known approaches. like this

execute CREATE DATABASE command with appropriate parameters. reference

your final code should be something like this in C#

var conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ....
conn.Open();
var command = SqlCommand("CREATE DATABASE DBName ON ( FILENAME = '" + @"C:\DBName.mdf" + "' ), ( FILENAME = '" + @"C:\DBName_log.ldf" + "' ) FOR ATTACH", conn);
com.ExecuteScalar();

answered Nov 05 '14 at 08:15

Hamid Pourjam

20,441
9
58
74

it looks a bit helpful. But what will be the connection string? And how do I check if the database is already existing? – Pooh Nov 05 '14 at 09:15
LOL. I used "Data Source=.\SQLExpress; INITIAL CATALOG=master; uid=; pwd=;" as connection string. I get error "Login failed for user". – Pooh Nov 05 '14 at 09:22
Unable to open the physical file "D:\VS\CKEY BILL\CKEY BILL\CKEY BILL\bin\Debug\Data\STORAGE.MDF". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)" – Pooh Nov 05 '14 at 10:47
Query Used : query.CommandText = "CREATE DATABASE BILLSTORAGE ON(FILENAME='" & My.Application.Info.DirectoryPath & "\Data\STORAGE.MDF'),(FILENAME='" & My.Application.Info.DirectoryPath & "\Data\STORAGE_Log.LDF') FOR ATTACH" – Pooh Nov 05 '14 at 10:48
you should ask this in new question – Hamid Pourjam Nov 05 '14 at 10:50

Jithin Shaji · Answer 2 · 2014-11-05T08:23:39.990

Don't think this as a direct answer to your question, but this information may help you.

See the below case. I need to attach a DB AdventureWorks2012 to server and the mdf file is saved at location D:\BI Developments\AdventureWorks2012_Data.mdf

I can attain this with the below TSQL.

sp_attach_single_file_db 
@dbname='AdventureWorks2012',
@physname='D:\BI Developments\AdventureWorks2012_Data.mdf'

So, if you are able to can an SP with a similar script from your application, you may be able to add the mdf file.

Change desktop application to client server based

2 Answers2