如何在Visual Studio中创建和开发新的数据库项目？

I want to find a way to develop database projects quickly in Visual Studio. Any ideas?

我想找到一种在Visual Studio中快速开发数据库项目的方法。有任何想法吗?

3 个解决方案

#1

I have a method of creating and updating database projects in Visual Studio 2005 that I thought was common knowledge. After asking a few coworkers if they knew how to update their database projects with this method and receiving no's, I thought I would blog about it and pass along some helpful hints and best practices.

我有一种在Visual Studio 2005中创建和更新数据库项目的方法,我认为这是常识。在询问了几个同事是否知道如何使用这种方法更新他们的数据库项目并且没有接收之后,我想我会在博客上发布并传递一些有用的提示和最佳实践。

I work a lot with databases and especially stored procedures that are built to be used with business logic/data access .NET framework. I enjoy working with databases and always create database projects to live with my .NET projects. I am psychotic about keeping database projects up to date. I have been burned too many time in my younger years where I needed to create a stored procedure that was deleted or was out of sync with the application using the database.

我经常使用数据库,特别是构建用于业务逻辑/数据访问.NET框架的存储过程。我喜欢使用数据库,并始终创建数据库项目以与我的.NET项目一起使用。关于保持数据库项目最新,我精神病。在我年轻的时候,我被烧了太多时间,我需要创建一个已删除或与使用数据库的应用程序不同步的存储过程。

After creating your database project in Visual Studio 2005 as shown:

在Visual Studio 2005中创建数据库项目后如图所示:

alt text http://www.cloudsocket.com/images/image-thumb16.png

Create 3 new directories in the projects: Tables, Stored Procedures and Functions. I usually only stored these for my projects.

在项目中创建3个新目录:表,存储过程和函数。我通常只为我的项目存储这些。

alt text http://www.cloudsocket.com/images/image-thumb17.png

I now open the Server Explorer in Visual Studio and create a new connection to my desired database. I am using Northwind as my example. I am not going to walk through the creation of the connection for this example.

我现在在Visual Studio中打开Server Explorer并创建一个到我所需数据库的新连接。我使用Northwind作为我的例子。我不打算为这个例子创建连接。

alt text http://www.cloudsocket.com/images/image-thumb18.png

I will use a stored procedure as my example on how to update the database project. First I expand the "Stored Procedures" directory in the Server Explorer for the Northwind database. I select a stored procedure.

我将使用存储过程作为我如何更新数据库项目的示例。首先,我在Northwind数据库的服务器资源管理器中展开“存储过程”目录。我选择了一个存储过程。

alt text http://www.cloudsocket.com/images/image-thumb19.png

I drag the stored procedure to the "Stored Procedures" directory in the Solution Explorer and drop it.

我将存储过程拖到解决方案资源管理器中的“存储过程”目录并将其删除。

alt text http://www.cloudsocket.com/images/image-thumb20.png

alt text http://www.cloudsocket.com/images/image-thumb21.png

If you open the file for the dragged stored procedures you will find that the IDE created the script as followed:

如果打开拖动的存储过程的文件,您会发现IDE创建了如下脚本:

/****** Object:  StoredProcedure [dbo].[CustOrdersOrders]    Script Date: 08/25/2007 15:22:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustOrdersOrders]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CustOrdersOrders]
GO
/****** Object:  StoredProcedure [dbo].[CustOrdersOrders]    Script Date: 08/25/2007 15:22:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustOrdersOrders]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
    OrderDate,
    RequiredDate,
    ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
'
END
GO

You can now drag over all the tables, functions and remaining stored procedures from your database. You can also right click on each script in the Solution Explorer and run the scripts on your database project's referenced database.

您现在可以从数据库中拖动所有表,函数和剩余存储过程。您还可以右键单击解决方案资源管理器中的每个脚本,然后在数据库项目引用的数据库上运行脚本。

#2

DataDude? http://msdn.microsoft.com/en-us/vsts2008/db/default.aspx

#3

Hey Chris, I also use the same way for keeping a database project, the only problem, is that you often make changes to stored procedures, and sometimes you forget which ones you changed, so you might drag one and forget the other. Do you know of a way to synchronize the database project with the database, or a way to import latest script for stored procs in your project, after they have been added by dragging the first time.

嘿Chris,我也使用相同的方式来保存数据库项目,唯一的问题是你经常对存储过程进行更改,有时你会忘记你改变了哪些,所以你可能会拖一个而忘记另一个。您是否知道将数据库项目与数据库同步的方法,或者在第一次拖动添加它们之后为项目中的存储过程导入最新脚本的方法。

#1