SQL Server Building Software

Question

What are the option in terms of applications that help build SQL statements against a SQL Server database?

We have some users that need to build SQL statements, preferably through drag and dropping or linking up tables etc.., against a SQL Server databse who don't have any experience in this area.

Any Ideas?

Answer 1

Visual Studio 2008 / 2010 have this featue and works well. I mean LINQ to SQL or Entity Framework, but in bigger projects I dont prefer Entity Framework.

Answer 2

The Sql Query editor in sql server 2005 and above is very good for that sort of operation. I found that the combination of drag and drop columns with the sql instructions below helps non-sql developers learn a little about sql and tends to wean them off of the editor.

Answer 3

First, I hope they are not doing their queries against a production database!

For regular non developers, try using excel, for these reasons:

1) most generic users might have it on their PC already.

2) drag and link tables. There is a little GUI for simple drag and link tables in there.

3) Support. When they get into trouble, they can e-mail me the file and I can see what they have so far. I'll end up writing views or procedures for them to use.

4) They can use the results data using familiar excel interface and functions, which helps since their SQL ability is so limited.

For developers these GUI tools are extremely limited in what you can do with your queries. I've never found one that can do UNIONs, derived tables, or CTEs. I'm not sure if any can even do OUTER JOINs. For simple tasks, they might be ok, but to switch back and forth all day would be a pain, so I just type them up.

Answer 4

I actually just ran into this problem in my own project.

One option is to purchase a tool that does precisely what you're looking for. If you do something like that, you're more likely to get something that's full-featured, well-supported, and has a lot of nice bells and whistles to make your job easier.

However, as both Svisstack and george9170 pointed out, there are already tools within the Visual Studio development environment. Here is how I'm proceeding:

1. I use Microsoft SQL Server Management Studio's Diagram editor to perform the dragging and dropping of tables. This works fairly well, although it's a bit more cumbersome to work with than some of the tools I investigated. (For the record, the reason I didn't use the tools I investigated was the cost.)
2. Once I develop the tables in the diagram, I tell MSSMS to generate the tables.
3. I then use the Database Publisher (it comes with VS2008 and VS2010) and I connect with the database. Using LINQ to SQL, I let the magic work behind the scenes so that I can utilize the various objects which will interact with the database.

If I'm not explaining this well, check out the two links below. It should make things more clear.

• Using Database Projects for Visual Studio
• NerdDinner - An ASP.NET MVC tutorial, but with good information on LINQ to SQL.

By using LINQ to SQL (as described in the second link), your users should be able to build queries against the database fairly easily. It's not drag and drop, but it's very simple and straightforward.

Answer 5

You can use Access databases as well. They let you form your own queries by dragging and dropping in Access 2010. However, if you are looking for easy to access data without the worries of SQL syntax, you might want to look at using Ruby ActionViews. If you are talking about live editing of the entire database if the database is of any importance (you can easily delete or clear an entire database on accident), I would do it in pure SQL, or better yet, not do this at all. If you are using MySQL, you should look at the possibility of using phpmyadmin for maintaining your database. It is a web based tool that makes database administration easy and can help you form your queries.