1

I am writing an application which can connect to any SQL Server Database, thus can be considered as stand-alone. In other words, the first thing the application does is request a connection string. This application will be performing queries and other tasks on the database, thus SQL statements are required.

Hence, since I am not going to specifically attribute the application with a pre-defined database, how am I to execute SQL statements from my C# application? I would imagine that stored procedures are out of the question since the application can connect to any database.

I can easily just hard-code the SQL statements in the application itself, but I was wondering what the best practice approach could be in a case like this. Is it ok to embed SQL statements in the application, thus essentially having the business logic and data layer in the same application? I have no logical or technical problem with this, merely just wondering if it is a valid approach, or whether it would be frowned upon by seasoned developers?

I have heard of Entity Framework, however to be perfectly honest I am not sure what it does or if it would be valid. Up till now, all my data-driven applications have utilised stored procedures and this is the first time that I am writing an application which can be used with a database which is specified at run-time.

Thanks!

Dot NET
  • 4,891
  • 13
  • 55
  • 98
  • Will the database have a defined structure, or is it *literally* any SQL Server database*? – podiluska Sep 08 '12 at 10:10
  • Literally any SQL Server Database. All that is required is that it has tables and fields. That's all. The application is a query builder – Dot NET Sep 08 '12 at 10:12
  • Any meaningful query is database specific. For example, to limit the number of returned rows, one database uses `limit`, others use `top`, and yet others use `rownum`. Your query builder will be useless without database specific support. – Andomar Sep 08 '12 at 10:27
  • @Andomar They do say and tag "SQL Server" - i hope we can assume that – podiluska Sep 08 '12 at 10:31
  • As I said, literally any "SQL Server" database. The question is tagged under that, and I mentioned it in the question and my above comment too. – Dot NET Sep 08 '12 at 10:31

3 Answers3

0

Yes embedding the SQL queries into the application is acceptable. Instead of hard coding them in code you can use resource files. The connection strings provider name should enable you to then select the correct SQL dialect for the db you are connecting to.

See Event stores SQL persistence factory ResolveDialect method which uses a similar approach. It uses it too determine the syntax required to create and call stored procedures, while your application will (I assume) need to call SQL which returns database metadata/structure. While the SQL they will be calling is different this pattern should be a nice fit for your application, allowing you to support many different databases.

Depending on what you are attempting to achieve you may also need to create an internal model which represents the tables, columns, etc metadata in the database.

[edit]

Sorry just seen the comments on being SQL server only. The SQL dialect could therefore be used more by the client to determine the structure of the SQL query (e.g. For code complete ).

Alistair
  • 1,064
  • 8
  • 17
0

Since you are building a query builder (no pun intended), you need to query the database catalog, which is the same for all databases under the same kind of DBMS. That will allow you to get the list of tables and other objects in any given database, so you can present them to the user.

Whether you'll use the pure ADO.NET or an ORM is an orthogonal question.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • So would you consider it acceptable to just include SQL statements for retrieving data from this database catalog, within my application itself? i.e. literally just representing the SQL statements with strings? (naturally taking SQL-injection prevention techniques) – Dot NET Sep 08 '12 at 10:51
  • @DotNET If you are using ADO.NET, what other choice do you have? You could push the SQL statements into stored procedures but as you already noted that's not viable in this case. If you are using an ORM, than _it_ will take care of constructing the SQL for you. – Branko Dimitrijevic Sep 08 '12 at 11:00
  • Thanks for the reply :) I have unfortunately been brainwashed to think that embedding sql in an application is something very bad. That's why I'm hesitant about this approach. – Dot NET Sep 08 '12 at 11:07
-2

If you are developing in .Net, you should consider using Microsoft enterprise library

It provides comprehensive details on building scalable applications.

Edit:

You have to consider dealing with different drivers also to deal with different database engines. It may not be possible to run the same query on all the databases as they generally follow different flavors of sql. e.g. mssql uses TSql where as Oracle uses PL-Sql.

Another solution is to follow Ansi sql standards.

Murtuza Kabul
  • 6,438
  • 6
  • 27
  • 34
  • By "all databases" i meant all SQL Server databases, so i wont have to worry with other database engines. – Dot NET Sep 08 '12 at 10:23