What is the best way to embed SQL in VB.NET.

Question

I am looking for information on the best practices or project layout for software that uses SQL embedded inside VB.NET or C#. The software will connect to a full SQL DB. The software was written in VB6 and ported to VB.NET, we want to update it to use .NET functionality but I am not sure where to start with my research. We are using Visual Studio 2005. All database manipulations are done from VB.

Update: To clarify. We are currently using SqlConnection, SqlDataAdapter, SqlDataReader to connect to the database. What I mean by embed is that the SQL stored procedures are scripted inside our VB code and then run on the db. All of our tables, stored procs, views, etc are all manipulated in the VB code. The layout of our code is quite messy. I am looking for a better architecture or pattern that we can use to organize our code.

Can you recommend any books, webpages, topics that I can google, etc to help me better understand the best way for us to do this.

Answer 1

Would it be possible to convert it to using LINQ? It's safer than converting it to using regular old embedded SQL statements.

Answer 2

It sounds like you're looking for a grand theoretical book that is written for one very specific case, i.e. what is the best practice for managing our current spaghetti SQL code framework, and I don't know that I've ever seen a book or document that describes that.

That being said, we converted several applications from VB6 to C# a few years back and were in the same place. Other than breaking apart certain modules so that their organization made more logical sense overall, our applications are essentially the same as they were, just rewritten in C#. I don't personally like the idea of separating SQL code from the function for which it was written (which is sort of what it sounds like you're trying to do); in general, if we have a function that needs to hit the database, we create a SqlCommand and, optionally, a SqlDataReader, set the CommandType to StoredProcedure and add the parameters, then perform the call and consume the result, doing with it whatever the function was programmed to do.

I don't know that that is the best way to do it, but it works for us.

Answer 3

I am looking for a better architecture or pattern that we can use to organize our code ...

topics that I can google

Take a look at DAL (Data Access Layer) for database transactions Here is a link to a StackOverflow question posted by someone who is facing a similar situation

Creating a Class Library in VB.NET

When you google "How to create DAL", you will see list of how to create a DAL as well as what DAL is. And here is a nice article that shows how DAL fits into an N-Tier achitecture

• What is n-Tier Architecture?
• Webopedia: N-Tier Application Architecture - Shows a nice graphical representation

Answer 4

In addition to what Sung Meister said, you should also consider moving the SQL from the VB.NET code into Stored Procedures, and then call the SPs from the VB.NET code (the Data Access Layer). It provides many advantages.