Usally the best place to put his code is in the BLL of the app. This allows a
nicer migration when your schema changes. Steve Lasker has some really cool
demos and ideas regarding this, check out his blog.
Also note, if you are using any version of Microsoft SQL Server Compact (not
sure about other products) you can't simply run a script which contains
multiple queries as the engine can only process one query at a time.
I've just copied this code from an app I'm working on a present. It simply
loads a script Tables_Create.sql stored in a resx file, if a database doesn't
not already exist it goes off any creates it from scratch:
//Now for the SQL.
StringBuilder sql = new StringBuilder();
List<string> commands = new List<string>();
System.IO.StringReader sr = new
System.IO.StringReader(Properties.StoredProcs.Tables_Create);
while (true)
{
string s = sr.ReadLine();
if (s == null)
break;
if (s.Length >= 2)
{
if (s.ToUpper().Substring(0, 2) != "GO")
sql.Append(s);
else
{
commands.Add(sql.ToString());
sql = new StringBuilder();
}
}
}
//Execute each statement in turn.
foreach (string s in commands)
{
cmd.CommandText = s;
connection.ExecuteNonQuery(cmd, null);
}
The query might look like:
CREATE TABLE Person (
PersonId int IDENTITY(1,1)
CONSTRAINT PersonId_PK PRIMARY KEY,
AddressId int NULL,
Name nvarchar(50),
ChangeStamp rowversion NOT NULL)
GO
CREATE INDEX Name_IX ON Person(Name)
GO
ALTER TABLE Person
ADD CONSTRAINT Name_FK
FOREIGN KEY(Name)
REFERENCES Address(AddressId)
ON DELETE CASCADE
GO
The code divides the query up, you get the idea. Of course the above is
wrapped in a transaction.
