locked
visual studio database projects

    Question

  • I have a database which contains tables, stored procs, etc. for multiple projects.  how would i set this up in visual studio project wise?  Vs only allows you to import the entire database and the project i want to setup only contains 15 tables and 30 stored procs.

     

    Thursday, August 23, 2007 3:07 PM

Answers

  •  

    We definitely handle cross-database references. There are a couple of ways - the simplest of which is to have projects for each database residing inside the same Visual Studio Solution. For best results, you really want to be running our Service Release 1, which has enhanced support for cross-database referencing.

     

    For the definitive tutorial on this subject, let me point you to an entry on Gert Draper's Blog, here. If this doesn't answer your questions, make sure you let us know. Or hey - even if it does!

    Monday, August 27, 2007 3:49 PM

All replies

  •  

    Here is one way:

     

    1.  Create an Empty Database Project

    2.  Do a Schema Compare:  set the Source to be your database, and the Target to be the empty project

    3.  Scan through the results, and select only the 15 tables and 30 procs to be Created.  Set the rest of the objects to 'Skip'

    4.  Click Write Updates to Target

     

    The project at this point should create those 45 objects.

     

    You can also use the Import Script feature if you already have a SQL script containing the tables and procs.  Just right-click on the project and select Import Script.   

     

    Hope this helps,

     

    -Tom

     

    Thursday, August 23, 2007 5:23 PM
  • oh sorry.  i know how to do it technically.  i guess i was asking if that is the way it should be done.  Here is my problem.  Lets say that i have a stored procedure (abc) that is not in the project but it is in the database that references one of the tables (xyz) that i did bring into the project.  i change a column in table (xyz).  visual studio can not tell me if i have broken anything in stored proc (abc)?  How do i handle these situations?

     

    Thursday, August 23, 2007 5:52 PM
  • Well - you can't get there from here?

     

    No, seriously - I think you need to get the stored procedure into the Project. Once the Project is established, we don't reference the original database any more at all (unless you make it your outgoing target, in which case we recognize it at deployment time). Our model is in the Database Project, and if the stored procedure isn't in the Project, then we know nothing of its existence. And therefore, of course, can't evaluate it for the changes.

     

    I'm curious - is there a compelling reason to keep this procedure out of the project? Obviously you want to update it at the same time you update it underlying table - otherwise, it's, well, broken. So what is the advantage of excluding it from the project?

     

    Remember, if you just want to add it as a single SP, you can always script the procedure out of the database and then use DBPro's Project/ Import Script to add it into the Database Project, even tho' the project already exists...

     

    Let us know if this makes sense to you...

    Thursday, August 23, 2007 6:19 PM
  • Let me first thank you for your input thus far.  it sounds like you have a great deal of knowledge about these thing and it is of huge benefit to my process.

     

    I think i follow you.  Let me paint another picture.  the database has 200 tables and 150 stored procedures.  of that 10 table and 15 stored procs belong to one "business project(A)".  Another business project (B) contains 25 tables and 40 stored procs.  So would i create 2 database projects in visual studio? 

     

    If i then have 1 more "business(C)" project that has a stored procedure in it that references one of the tables in my first "business project(A)".  if a column changes in a table in (A) but I have not created a database project for "business project(A)" i guess i am just putting myself behing the 8 ball huh?

     

    There are cross dependencies between these projects.  How does visual studio handle these?

     

    Friday, August 24, 2007 8:22 PM
  •  

    We definitely handle cross-database references. There are a couple of ways - the simplest of which is to have projects for each database residing inside the same Visual Studio Solution. For best results, you really want to be running our Service Release 1, which has enhanced support for cross-database referencing.

     

    For the definitive tutorial on this subject, let me point you to an entry on Gert Draper's Blog, here. If this doesn't answer your questions, make sure you let us know. Or hey - even if it does!

    Monday, August 27, 2007 3:49 PM
  • Hi Tom,

    I am having near about same problem.

    I have a database and now i am starting new module using same database. would you tell me how i can point to same database and create new objects like table and stored procedure.

    I don't want to import all data in my new databse.

    e.g i have databse employee management and for that i havn't use database project. Now i am going to develop new module say payroll and i want to use database project with employee database.

    Thanks in advance.

    From
    Manish Jain
    Monday, April 06, 2009 7:57 AM