0

Looking for the best approach to accomplish the following please:

I am building an application that runs on two databases, a common 'library' database for common products, and a 'project' database. Comparably in MS Access, you have an database template file that includes the library tables, and for each new project you simply copy and rename the database .mdb file and start to build your project.

I want something similar for my application, where on startup, the user selects an existing project or a new project and is able to either open an existing database, or create a new database. I have accomplished this with SQLite as these are just files that can be copied and renamed, but I'm not sure how to go about this with SQL Server.

Reason for separate library database is to be able to update the common library without having to update each project. Eventually I'd like to have this as a cloud/web application.

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pdeault
  • 21
  • 1
  • 5
  • Does this answer your question? [Creating a database programmatically in SQL Server](https://stackoverflow.com/questions/9015142/creating-a-database-programmatically-in-sql-server) – NineBerry Nov 14 '21 at 19:10
  • Thanks, I'll check it out. Sorry, my previous reply to this was for another comment that seems to have been deleted. – pdeault Nov 14 '21 at 19:11
  • Another user asked: "Is it a hard requirement for each project to have its own database?" My response was: "No, not necessarily. I'm kind of a newbie with SQL/SQLServer and might be coming from a certain/limited perspective. My thinking is that some projects would have different defaults, standards and requirements based on the region, type of project, etc., and it would be easier to manage separate projects this way should the need arise to go manually do some SQL work on the project/database." – pdeault Nov 14 '21 at 19:14
  • @NineBerry - Thanks. That's a start. What about all the tables, views, SP's? What's the best way to accomplish creating a full database template? executing a script that has a placeholder name, using find/replace in the script? – pdeault Nov 14 '21 at 19:25
  • Check the options of the create database statement. You can create a new database based on an existing file or from a database backup – NineBerry Nov 14 '21 at 19:28
  • Look up code first apporach using entity framework – A Houghton Nov 14 '21 at 19:37
  • 4
    *Reason for separate library database is to be able to update the common library without having to update each project* -- sounds like a nightmare – Caius Jard Nov 14 '21 at 19:47
  • @CaiusJard - It would primarily be to allow adding new or expand on existing equipment/components/categories, updated pricing for estimations/proposals that would be accessible to all the existing and new projects instead of needing to update each individual project. – pdeault Nov 15 '21 at 00:13
  • Perhaps you should take the same approach that SQL Server itself uses. Create your own "model" database and copy (restore, attach) it as a starting point when adding a new "project". – SMor Nov 16 '21 at 14:49

1 Answers1

0

Try this

 var server = new Server();
 var db = new Database(server, "db");
 db.Create();
Arun_Raja1
  • 207
  • 1
  • 1