Accessing M$ SQL Server with VB.NET

Here is a quick guide to accessing a microsoft sql server with VB.NET

The sqlconnection object is designed for microsoft sql server, all other databases are accessed with the odbcconnection object.

To pull data from your database you need to import the sqlclient namespace and use the following objects:
1. sqlconnection - used to open and close connections
2. sqlcommand - used to query
3. sqldatadapter - used to accept results of query

To begin, include the sqlclient namespace

Code:

---

Imports System.Data.SqlClient

---

Then create a sqlconnection object in the design view by dragging it from the data tab area of the toolbox into your form. You could also declare the object in your code but with the design view it allows you to fill out a few bits of information regarding your connnection and it's setup.

In this example our sqlconnection object will be called: dbconnection

create an array to store your returned data from the database (in this example we are looking up a single integer column from our database)

Code:

---

Dim holddata() as Integer

---

It's important to use exception handling incase an error would occur duing our connection.

Code:

---

Try

---

create a sqlcommand object containing a sql statement and the sqlconnection object (dbconnection)

Code:

---

Dim querystatement As String = "Select column From Table"
Dim dbcommand As SqlCommand(querystatement, dbconnection)

---

create a sqldatareader to accept data

Code:

---

Dim queryresults As SqlDataReader

---

open your connection and query for information

Code:

---

dbconnection.Open()
queryresults = dbcommand.ExecuteReader()

---

check if any rows were returned

Code:

---

if (queryresults.HasRows)

---

fill the holddata array using a while statement

Code:

---

ReDim holddata(0)
Dim counter As Interger = 0
While queryresults.Read()
counter = counter + 1
ReDim Preserve holddata(counter)
holddata(counter - 1) = queryresults.Item("column")
End While

---

if no records were returned then inform user

Code:

---

Else
MessageBox.Show("No Records Found", "Error", MessageBoxButtons.Ok)
End If

---

if something goes wrong display a message informing the user

Code:

---

Catch
MessageBox.Show("Could not connect to server", "Error", MessageBoxButtons.Ok)

---

Makes sure to close the database connection within finally so that the connection is closed regardless of errors occuring.

Code:

---

Finally
dbconnection.Close()
End Try

---

*************************************************
Here is the same code but put together
*************************************************

Code:

---

Imports System.Data.SqlClient

Dim holddata() As Integer

Try
    Dim querystatement As String = "Select column FROM table"
    Dim dbcommand As SqlCommand(querystatement, dbconnection)
    Dim queryresults As SqlDataReader

    dbconnection.Open()
    queryresults = dbcommand.ExecuteReader()

    if (queryresults.HasRows)
          ReDim holddata(0)
          Dim counter As Integer = 0

          While queryresults.Read()
              counter = counter + 1
              ReDim Preserve holddata(counter)
              holddata(counter - 1) = queryresults.Item("column")
          End While

    Else
          MessageBox.Show("No Records Found", "Error", MessageBoxButtons.Ok)
    End If

Catch
    MessageBox.Show("Could not connect to server", "Error", MessageBoxButtons.Ok)

Finally
    dbconnection.Close()
End Try

---

If you run a query with no responses such as Update:

Code:

---

dbcommand.ExecuteNonQuery()

---

If you run a query with a single response such as returning the auto generated PK from an Insert:

Code:

---

dbcomand.ExecuteScalar()

---

To run multiple sql statements be sure to free resources between calls:

Code:

---

queryresults.Close()
dbcommand.Dispose()

---

To load a new sql statement into a sqlcommand object:

Code:

---

dbcommand.CommandText = "Select * From table"

---