Today, you will learn about SQL Variables and how to use them from Visual Basic.
Databases can be friendly creatures if you know how to use them correctly; they can become also very unfriendly, especially if you have no idea on how to use them properly. A database's sole function is to store information, hence the name: data base. Knowing how to properly store inside them takes practice, and lots of planning. People who fail to this get eaten up by the database monster.
A database consist of the following objects (I will not go into great detail on all of them, because this is only an introduction):
- Stored Procedures
Tables are the main building blocks of any database. This is where all the information will be stored. A table consists of rows (records) and columns (fields). A row, or record, contains all the information pertaining to one topic. A column, or field, is once piece of information. Here is a small example of records and fields:
Figure 1: Records and Fields
In Figure 1, you can see that there are seven records, and three fields. We will create this database a bit later.
Although you may have many columns in each table and many tables at your disposal in a database, it doesn't mean that you have to make use of all of them. With databases, less is more. This means that the least amount of fields and the least amount of tables used in your database, will amount to the best performance. Let me give you another example:
Figure 2: Database Tables used productively
This is from one of my programs I had to implement at my work. Its whole purpose is to track student progress according to which course the student is doing. Each course has different subjects. I show this here to explain to you that a simple program can make use of multiple inter-connected tables. An inexperienced programmer would add all the fields into one table, which is possible, but is also wrong. This system has been running successfully for twelve years already. There is more than enough space to store the student's personal information, as well as more than enough space for custom courses and custom subjects.
All these tables are connected by a term called relationships. If you look closely at the tables inside Figure 2, you will see that inside each table there is a reference to another table's field. This reference forms a relationship between tables. A relationship becomes possible when there is a Primary key inside one table referencing a Foreign key in another table. This is not really the main topic of today's article, so let me not get ahead of myself (as always) again. More information on Database tables can be found here.
Now that we know where and how to store data, we need to know how to access that data. This is done through a database query. You usually have to write a query to extract the needed information from the appropriate tables. I will delve deeper into this in my next article (Extracting Data). More information on database queries can be found here.
A View is a dynamic result set. This means that we can create a view based on a query and it will not affect any data that is stored. More information on Views can be found here.
A Stored Procedure is a sequence of SQL code that can perform tedious tasks quickly. By making use of Stored Procedures, you will save a lot of time and won't need to run each query (that can reside in a Stored Procedure) manually. More information on Stored Procedures can be found here.
There are many functions inside the SQL Language. These functions can help with string manipulation, date manipulation, as well as computing Averages, just to name a few. A complete list of SQL Functions can be found here.
SQL Variables serve the same purpose as Visual Basic variables: to store data, or a pointer to data. An example of an SQL Variable is as follows:
DECLARE @Date Date SET @Date = (SELECT CAST(GetDate() AS Date)) SELECT @Date AS [Date] FROM Table WHERE Condition
In the preceding example, I created a variable by using the Declare statement. I then used the Set statement to store data into the variable. Lastly, I used an ordinary Select statement to make use of the variable.
In the above example, you will also notice that I stored the result of a Select statement into the variable, you could obviously hardcode a piece of information as well, but I thought it appropriate to show a more complicated example.
Open SQL Server and create two tables that look as follows:
Figure 3: Table Design
Create the query in SQL Server as follows:
DECLARE @StudentNumber bigint SET @StudentNumber = (SELECT StudentNumber FROM StudentDetails WHERE Name = 'Hannes') DECLARE @Date Date SET @Date = (SELECT CAST(GetDate() AS Date)) SELECT CourseCode, CourseName, @Date AS [Date] FROM CourseDetails WHERE StudentID = @StudentNumber
In the previous query, I created two variables. The first variable hosts the StudentNumber value from the StudentDetails table and the other variable stores the current date that I got by using the GetDate() SQL function.
The Select query makes use of both variables and displays the data accordingly.
Create a new Visual Basic Windows Forms project and add a DataGridView to the form plus one button. Add the following code:
Imports System.Data.SqlClient Public Class Form1 ' Shared variables Dim con As SqlConnection = _ New SqlConnection("Data Source=.\HTG;AttachDbFilename= _ |DataDirectory|\Students.mdf;Integrated Security=True; _ Connect Timeout=30;User Instance=True") Dim cmd As SqlCommand Dim myDA As SqlDataAdapter Dim myDataSet As DataSet Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ShowData() End Sub 'Binding database table to DataGridView Public Sub ShowData() cmd = New SqlCommand("DECLARE @StudentNumber bigint _ SET @StudentNumber = (SELECT StudentNumber _ FROM StudentDetails WHERE Name = 'Hannes') _ DECLARE @Date Date SET @Date = _ (SELECT CAST(GetDate() AS Date)) _ SELECT CourseCode, CourseName, @Date AS [Date] _ FROM CourseDetails _ WHERE StudentID = @StudentNumber", con) If con.State = ConnectionState.Closed Then con.Open() myDA = New SqlDataAdapter(cmd) myDataSet = New DataSet() myDA.Fill(myDataSet, "MyTable") DataGridView1.DataSource = _ myDataSet.Tables("MyTable").DefaultView End Sub End Class
Obviously, you must change the connection string to your database and your server. In the preceding code segments, I made use the SQLCommand object to execute the previous query. The resulting dataset gets displayed inside the DataGridView.
As you can see, SQL Variables aren't difficult to use from Visual Basic. Until we meet again.