CODEWITHSUNDEEP
c#sql-serverasp.net-mvcdatabaselinq
Mike H.
Mike H.

Reputation: 1751

Create new database with schema from code

We are building a new cloud based application for our pre-existing clients that will add/replace functionality to our NaCl program they are all using. Each client has their own database (schemas are 100% identical to one another), and we must create a new instance of the database on our cloud provider.

The sync process for the data is already figured out, and I've managed to create the databases with relative ease. The only problem is updating the db schemas to match the "db template" (if you will). I seem to have run into one problem or another depending on my approach:

If I try to send a script (LINQ-to-SQL) to the sql server via ExecuteCommand() I get a slew of errors from declaring scalar variables, to syntax errors, to certain functions needing to be the first statement in a query batch. This script, mind you, is the script generated from SSMS.

The only other approach I've thought about was executing the script stored locally on the machine (which I have not actually tried yet), but I read that you cannot run a local script with a variable database name...which is a necessity.

Any help would be much appreciated!

Upvotes: 0

Views: 1118

Answers (1)

Andomar
Andomar

Reputation: 238096

SSMS generated scripts contain go. In SSMS go ends a batch, but it is not so with ADO.NET's ExecuteCommand.

Split the script on go, and send each part separately using ExecuteCommand().

Running the script locally (or remotely) using SSMS should also work.

I'm not sure what you mean with "variable database name". In ADO.NET you can set the database in the connection string. In SSMS, you can use use <dbname> or the dropdown box in the query toolbar.

Upvotes: 2

Related Questions