Exposing SQL 2008 Database with C # Application
I have an internal enterprise application that I have developed for my company built on .Net 3.5 / SQL 2008.
I have two types of databases. The main system database that contains all of our global data like usernames and customer names, etc. And project databases which contain factual data related to our clients' project.
When the system creates a new project for a client, it needs to provide a new SQL database using a special schema providing tables, views, sps, etc. The project database name corresponds to the project ID of the project stored in the system database. Thus, the new project will create a new project database named: Project_XXX, where XXX is the project ID.
My question is, what is the best way to create a custom database programmatically? Right now, the only way I can do this is with a class that reads the SQL script from the file system and parses to replace the project id for the database name. It's easy, but it seems pretty inelegant.
Any approaches some veterans out there prefer?
source to share
Generally, if there is only one (or a few) databases and you have direct control over them (typical corporate environment), I would recommend not automatically updating the databases as it is more hassle than worth it. Just pass the script to those doing the install.
For more mainstream releases in the past, I've used a script as you suggest alongside the SQL Server SMO library (Server. CurrentContext.ExecuteNonQuery ()). I don't find it inelegant, it just works.
In the first release, we will include a complete database build script and then add an update script for each sub-section released. Therefore, if someone installs v1.2 on top of v1.1, we will only run the v1.2 script. However, if they did a fresh install, we will run v1.0, v1.1 and v1.2.
source to share
I would think the best way is to have a script to do this on the source server; it is, after all, the source code for a large portion of your system infrastructure, and will be useful for proper code maintenance, testing, etc. just like anyone else. Then just install from there.
If you have any doubts about this architecture, I can assume that your uncertainty about the individual databases for each project will increase. I call this Big Denorf; I've seen this several times in multiple contexts and I haven't seen it work out well yet. I even put it forward for consideration as an Antipattern.
source to share
Since you are using .NET 3.5, you might need to use LINQ. LINQ has the ability to dynamically create a database ( howto link ) using a mapping file or a strongly typed DBML file that you created. I believe it can create matching tables in multiple places. This probably works fine for simple databases.
I can't figure out how this works if you have functions or stored procedures and / or indexes for non-primary keys. The mapping file can track them, but I've never used one, so I'm skeptical. Probably @doofledorfer's idea of using SQL scripts to recreate the database structure in a new database is the way to go. I usually keep these scripts in my source code.
If you haven't already, you can take a look at the update management tools. Presumably you will need to dump any db changes in existing projects as your code / db changes. We've had good results with Red Gate SQL tools .
source to share