SQL Server & VB.NET Applications
Article Introduction:
This article is all about the integration between .NET applications (VB.NET in this article) and SQL Server 2008/R2 (No relevance for edition at this time).
Relevant Background:
In order to connect to SQL Server and use your database you must have the right security settings. You need to understand that SQL Server has two types of user entities in its system. The first one is "Logins" and it apears in the 'Security' folder in the server level tree of your SSMS (SQL Server Management Studio). The second one is a "Users" folder which appears within every database in your server. In other words, a login is like your entry card to the lobby of the building. A user on the other hand, is your second entry card to the specific office (database) you are trying to get into. Users may be manual users created by you or they can be imported from active directory. If you would like to know more about users please read this artice: http://msdn.microsoft.com/en-us/library/aa337562.aspx
Let`s jump into the actual work...
Well, now that we have a basic core understanding of users we can just do our work... the VB work. Please remember that this article assumes that you have a fair understanding and experience in VB.NET (VB6 users... sorry!). The first thing needs to be done is importing the package which contains the connection objects (and all others... you`ll see in a bit). This is done by writing the following code:
Import System.Data.SqlClient
This will import the objects we will use in a few seconds. As common logic probably says now: "Gee... we need to connect to SQL Server". You are right ! We must connect to SQL Server first before any action can be done. This is done by the following code (VB.NET Programmers: please remember that the connection object must be declared to be globally available to all forms (WinForms) or to be stored in the Session holder (ASP.NET).
Public con As New SqlConnection(Your Connection String Here)
If you don`t know your connection string, you will probably find it here: http://www.connectionstrings.com/sql-server-2008
After declaring your connection object, you need to... connect ! Now most of the developers I know do it like this (to catch runtime errors):
Try
con.Open()
Catch
' Your error handling goes here...
End Try
If no error is present, you are most likely connected to SQL server and to your default database (Just to make sure, don`t forget to run the T-SQL command "USE YourDatabaseName;", it prevents future errors... trust me !).
So, now that we`ve connected to SQL Server, we need to run some T-SQL code or at least select some data for our application. This is done by two simple steps. The first one is to declare an object called "SqlCommand". The SqlCommand object must have two variables at its construction (New). Declaring the SqlCommand looks like this:
Dim cmd As New SqlCommand(YourTSQLCodeHere,ConnectionObjectName)
This is important: The declaration of the SqlCommand object does not mean that the T-SQL you wrote is actually executed at this point! The T-SQL will be executed using another object called SqlDataReader which handles all of the data extraction. Please remember this: Only one open data reader allowed for each connection. You can NOT use two open data readers at the same time which both of them are related to the same connection object!
So, how do we read data ? Let`s create a SqlDataReader object and relate it to the command first:
Dim rs As SqlDataReader = cmd.ExecuteReader()
This is the actual point that the T-SQL you wrote before is executed. If you have any errors in your T-SQL code... this is the place for the Visual Studio to present the runtime error. You can do a Try with VB.NET and handle the specific error as well but that kinda out of our scope. Assuming you have no runtime errors, you now have an open data reader active per your connection. So, how do we read data ? First of all, we must use the Read method of the SqlDataReader object. WIthout running the Read method, you will get a runtime error. The read method is executed like this:
rs.Read()
(I bet you thought this would be harder... right ?)
Now, let`s take a short break from VB.NET code, and understand another principle about the SqlDataReader object. SqlDataReader reads data according to the table`s column order. If you have a field which is called... let`s say "MyLine", and it is a VARCHAR field (If you don`t know what VARCHAR is... you need to read other articles), and it is the first column of your table, the reading process would look like this:
rs.GetString(0)
The field count starts at 0 and not at 1. If you have 10 fields in your table, the last field would be number 9 according to the SqlDataReader. The reader has many types of fields it can extract (String INT etc.) however it has one feature which makes things easy (or not... depends on your needs). If you will try to read an INT field with the method GetString, it will work ! It will automatically convert the INT value into a string value. Try to have fun with it and create fields and extract them (read them) from VB.NET and see what happens.
Final Note: Always Close And Clear !!
One important aspect of this article is to teach you how to close your reader and clean it from memory. I like to do all of these actions together and it looks like this:
rs.Close()
rs = Nothing
cmd = Nothing
This way, I've closed my SqlDataReader and allowed other objects to be opened, I have removed this reader from the memory (this is more for your applications performance and memory leaks) and I have also removed the SqlCommand object from memory.
Well, I hope that this little article helped some of you doing some VB.NET work and need to connect to SQL Server. I will publish other articles soon. Some of them are for beginners and some are for advanced users. I plan to publish a large article about MySQL migration to SQL Server in the near future.
Be Well !
Doron Yaary.
Senior DBA, Israel.