Using SQL Queries in ADO.NET

I usually see code samples using SELECT SQL queries but I don't see many articles using other SQL queries. In this article, I will show you how to execute SQL queries from your VB.NET applications.

Before we go to SQL queries, we need to know how to make a connection to a database. I will use Sql data provider to connect to the SQL Server. The Sql data provider classes are defined in the System.Data.SqlCliet namespace and some general database related classes are defined in the System.Data namespace.

Hence I import these two namespace in my application before writing any thing else.

Imports System.Data
Imports System.Data.SqlClient

Now let's create a connection object. The following code snippet creates a SqlConnection by using userid as sa and password as pass. After that code opens and closes the connection:

Dim conn As New SqlConnection("Data Source=computer_name;" + "Initial Catalog=database_name;" + "User ID=sa;" + "Password=pass;")
conn.Open()
conn.Close()

CREATE

I want to show you that it is fairly easy to use SQL queries in your application. In this article, I will explain the CREATE, INSERT, and SELECT queries.

Now if you wonder why do I start with the CREATE SQL query then the simple answer is I want to show you how to create a database and database table before you can select any data. Because to use the SELECT SQL query, you must have a database, database table, and data in the table.

The following source code shows you how to use the CREATE SQL query.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace SimpleSql
Class CreateTable
'Entry point which delegates to C-style main Private Function
Public Overloads Shared Sub Main()
Main(System.Environment.GetCommandLineArgs())
End Sub
Overloads Shared<STAThread()>_
Sub Main(ByVal args() As String)
' Make a SqlConnection and the Connection string as an argument
Dim conn As New SqlConnection("Data Source=computer_name;" + "Initial Catalog=database_name;" + "User ID=sa;" + "Password=pass;")
' Next thing we will do is make a sqlcommand so
'* we can exucute our query'
Dim cmd As New SqlCommand
' We could give this as argument but that won't make our code real clear
cmd.CommandTimeout = 60 '
cmd.Connection = conn ' Sets wich connection you want to use
cmd.CommandType = CommandType.Text ' Sets the command type to use
cmd.CommandText = "CREATE TABLE simplesql " + "(" + "simple_id int," + "simple_text text" + ")"
' Why this approach you could write it in one line right???
'* Yes you can you coudl make it like this
'* "CREATE TABLE simplesql ( simple_id int, simple_text text )";
'* most people would do it the simple way inclusive me :)
'* but I want to make it real clear how to do it right '
' Next We will open the connection and make the query
Try
conn.Open() ' Open the connection if it fails
' it will let you know with an exception
If conn.State = ConnectionState.Open Then
' Execute the query for this nothing is returned
cmd.ExecuteScalar()
Console.WriteLine("Table is Created")
End If
Catch exp As Exception
Console.Write(exp.Message)
Finally
conn.Close() ' close the connection
End Try
Console.WriteLine("Press any key to quite")
Console.Read()
End Sub 'Main
End Class 'CreateTable
End Namespace 'SimpleSql

As you can see from the code, I have commented the code you can understand easily what's going on there.

The last thing we have to do is make a Primary key this is needed so that our simple_id can't have the same value twice "CREATE UNIQUE INDEX PrimaryID ON simplesql (simple_id)". Use this instead of the create table query.

NOTE:I expected you to have a database available if you have not use this query instead of ours

"CREATE DATABASE csharpcorner". Use this instead of the create table query. You also need to change the connection string delete this part from it "Initial Catalog=database_name;"+

INSERT

The INSERT SQL query is second most used queries. I think you have an idea what it is for. Yes, it's for inserting data into a database table. You will see how the INSERT query works in a momeny. The INSERT query works almost the same as the create statement but to make it more interesting I will make something for you so you get an idea of how you could do it for example a guestbook with asp.net.

and here is the code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace SimpleSql1
Class Insert
'Entry point which delegates to C-style main Private Function
Public Overloads Shared Sub Main()
Main(System.Environment.GetCommandLineArgs())
End Sub
Overloads Shared<STAThread()>
Sub Main(args() As String)
Dim conn As New SqlConnection("Data Source=Computername;" + "Initial Catalog=simplesql;" + "User ID=sa;" + "Password=pass;")
' TODO: change comp name
Dim strText As String = Console.ReadLine()
Try
Dim cmd As New SqlCommand
cmd.CommandTimeout = 60
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT Count(*) FROM simplesql"
' Couldn't do it without sorry
conn.Open()
If conn.State = ConnectionState.Open Then
Dim objCount As Object = cmd.ExecuteScalar()
Dim iCount As Integer = CInt(objCount)
Console.WriteLine("Count was succesfull")
cmd.CommandText = "INSERT INTO simplesql (simple_id, simple_text) VALUES (" + iCount + ",'" + strText + "')"
cmd.ExecuteScalar()
Console.WriteLine("Succesfully inserted the string")
End If
Catch exp As Exception
Console.Write(exp.Message)
Finally
conn.Close()
End Try
Console.WriteLine(ControlChars.Lf + ControlChars.Lf + " Press any key to quite")Console.Read()
End Sub 'Main
End Class 'Insert
End Namespace 'SimpleSql1

As you can see a little less comments but if you need any help with connection see the first code.

I first let the user enter a string this was just to let you know how it works. For example you could make an application that has a Textbox in it en when clicking a button it will insert the string from the textbox to the table.

Next thing was count to count the numbers of rows in the database. I will explain this at the SELECT section because it uses a select query but you will know how it works in just a matter of minutes.

The select query is used like this and I will take our example for this
"INSERT INTO <table> (simple_text, simple_id) VALUES ('some text', 0)" NOTE that the order is reversed because we created the table like id, text.

This would be the normal query "INSERT INTO <table> VALUES (1, 'some text')"
But I always use the first one this is because it will give you more control of how it is ordered.

SELECT

And at last after the though work with creating and inserting something to our database we will read it from it. I guess this is the most used command in SQL this because the query let you show you what’s in the database. I will make a application that count the rows in the table this is because we need to know how long our for loop will be. This application will be more difficult then you already saw this article this because we also need a SqlDataReader for reading from the database.

I will first show you the code this code will be commented like the first one so read carefully

Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace SimpleSql1
Class [Select]
'Entry point which delegates to C-style main Private Function
Public Overloads Shared Sub Main()
Main(System.Environment.GetCommandLineArgs())
End Sub
<STAThread()> _Overloads Shared Sub Main(ByVal args() As String)
' we will first do the same thing
'* connecting to the database as we did before'
Dim conn As New SqlConnection("Data Source=computer_name;" + "Initial Catalog=simplesql;" + "User ID=sa;" + "Password=pass;")
Dim dr As SqlDataReader
Try
Dim cmd As New SqlCommand
cmd.CommandTimeout = 60
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT Count(*) FROM simplesql"
conn.Open()
If conn.State = ConnectionState.Open Then
Dim objCount As Object = cmd.ExecuteScalar()
Dim iCount As Integer = CInt(objCount)
Console.WriteLine("Count was succesfull " + ControlChars.Lf)
cmd.CommandText = "SELECT simple_id, simple_text FROM simplesql ORDER BY simple_id"
dr = cmd.ExecuteReader(CommandBehavior.SingleResult)
Console.WriteLine("Succesfully Selected " + ControlChars.Lf)
' For loop to read everything from the table
Dim i As Integer
For i = 0 To iCount - 1
dr.Read() ' Read one row from the table
Console.WriteLine("ID: {0} " + ControlChars.Tab + " Text: {1}", dr(0), dr(1))
Next i
End If
Catch exp As Exception
Console.Write(exp.Message)
Finally
conn.Close()
End Try
Console.WriteLine(ControlChars.Lf + ControlChars.Lf + " Press any key to quite")
Console.Read()
End Sub 'Main
End Class '[Select]
End Namespace 'SimpleSql1

Now I will explain a couple of code.

cmd.CommandText = "SELECT Count(*) FROM simplesql", What we do here is count all the rows from simplesql this must be done to know how long our for loop will be.

cmd.CommandText = "SELECT simple_id, simple_text FROM simplesql ORDER BY simple_id", What we do here is select simle_id and simple_text from simplesql and order it by simple_id that means like 1, 2, 3,...

if you use ORDER BY simple_id DESC it will order reversed like ...., 3, 2, 1

Again you could choose to do it like this.

"SELECT * FROM simplesql", This will make the query shorter but the gives you fewer control.So if we only want to select the text we could do this.

"SELECT simple_text FROM simplesql ORDER BY simple_id", Or if we want to select text where id equals 3 we would do this.

"SELECT simple_text FROM simplesql WHERE simple_id=3"

There is one more I want to teach you and that is if you want to select text between some id numbers you will use this.

"SELECT simple_text FROM simplesql BETWEEN simple_id=0 AND simpel_id=5"

This would be usefully if you have a news system and want to show the last
5 news items this is the way

dr.Read()
Console.WriteLine("ID: {0} \t Text: {1}", dr[0], dr[1])

Read one row from the database dr[0] means the first variable you selected so if you had the query like this:

"SELECT simple_text, simple_id ...." simple_text was dr[0].

But now simple_id is.