SQL Data Provider VB.NET Class

Example Usage

SQLDataProvider Class Documentation

This class provides a fast and universal method for accessing SQL Server database.

Create Instance

At first you create an instance of SqlDatabase class.

Dim sqldb As New SqlDatabase("Data Source=(local); Initial Catalog= ; UId = ; Pwd = ;")

For more information about connection strings, visit ConnectionStrings.com.

ExecuteNonQuery Method

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

Dim params(0 To 1) As SqlParameter
params(0) = New SqlParameter("@Firstname", SqlDbType.NVarChar, 120)
params(0).Value = "Stefan"
params(1) = New SqlParameter("@Lastname", SqlDbType.NVarChar, 120)
params(1).Value = "Cameron"
sqldb.ExecuteNonQuery("Insert Into dbo.Users(Firstname, LastName) Values(@FirstName, @LastName)", CommandType.Text, params)

If you are using stored procedure,you can execute that without declaring parameters such as following code:

sqldb.ExecuteNonQuery("dbo.CreateUser", Nothing, "Stefan", "Cameron")

ExecuteScalar Method

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Dim count As Integer = sqldb.ExecuteScalar("Select Count(*) From dbo.Users", CommandType.Text)
MsgBox("Number of row(s): " & count)

ExecuteReader Method

Sends the CommandText to the Connection and builds a SqlDataReader.

Dim FirstName As String = String.Empty
Dim LastName As String = String.Empty

Dim params(0) As SqlParameter
params(0) = New SqlParameter("@Id", SqlDbType.Int)
params(0).Value = 1

Dim dr As IDataReader = sqldb.ExecuteReader("Select * From dbo.Users Where (Id = @Id)", CommandType.Text, params)
While dr.Read()
    FirstName = dr("Firstname")
    LastName = dr("Lastname")
End While
dr.Close()

MsgBox(FirstName & " " & LastName, MsgBoxStyle.Information)

There is a sample for using stored procedure:

Create Procedure [dbo].[GetUserInfo] 
    (
        @Id int
    )
As
Begin
    Select * From dbo.Users Where (Id = @Id)
End

Dim FirstName As String = String.Empty
Dim LastName As String = String.Empty

Dim dr As IDataReader = sqldb.ExecuteReader("dbo.GetUserInfo", Nothing, 1)
While dr.Read()
    FirstName = dr("Firstname")
    LastName = dr("Lastname")
End While
dr.Close()

MsgBox(FirstName & " " & LastName, MsgBoxStyle.Information)

Using Return Value Parameter

If you are using stored procedure,you can get the value of 'return value parameter'.

Create Procedure dbo.UserExists
    (
        @Firstname nvarchar(120),
        @Lastname nvarchar(120)
    )
As
Begin
    If Exists(Select * From dbo.Users Where (Firstname = @Firstname) And (Lastname = @Lastname))
        Return 1
End

Dim retval As Integer
sqldb.ExecuteNonQuery("dbo.UserExists", retval, "Stefan", "Cameron")
MsgBox("User Exists: " & IIf(retval = 1, "Yes", "No"))

FillDataset Method

Adds or refreshes rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table."

Binding a DataGridView with FillDataset method.

DataGridView1.DataSource = sqldb.FillDataset("Select * From dbo.Users", CommandType.Text).Tables(0)

ExecuteDataset Method

Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the System.Data.DataSet with the specified System.Data.DataTable name.

' Getting the System.Data.DataSet.
Dim ds As DataSet = CType(DataGridView1.DataSource, DataTable).DataSet

' Declaring insert command object
Dim inscmd As New SqlCommand("Insert Into dbo.Users(Firstname, Lastname) Values(@Firstname, @Lastname)")
With inscmd
    .CommandType = CommandType.Text
    .Parameters.Add(New SqlParameter("@Firstname", SqlDbType.NVarChar, 120)).SourceColumn = "Firstname"
    .Parameters.Add(New SqlParameter("@Lastname", SqlDbType.NVarChar, 120)).SourceColumn = "Lastname"
End With

' Declaring update command object
Dim updcmd As New SqlCommand("Update dbo.Users Set Firstname = @Firstname, Lastname = @Lastname Where (Id = @Id)")
With updcmd
    .CommandType = CommandType.Text
    .Parameters.Add(New SqlParameter("@Id", SqlDbType.Int)).SourceColumn = "Id"
    .Parameters.Add(New SqlParameter("@Firstname", SqlDbType.NVarChar, 120)).SourceColumn = "Firstname"
    .Parameters.Add(New SqlParameter("@Lastname", SqlDbType.NVarChar, 120)).SourceColumn = "Lastname"
End With

' Declaring delete command object
Dim delcmd As New SqlCommand("Delete From dbo.Users Where (Id = @Id)")
With delcmd
    .CommandType = CommandType.Text
    .Parameters.Add(New SqlParameter("@Id", SqlDbType.Int)).SourceColumn = "Id"
End With

' Updating data source
sqldb.ExecuteDataset(inscmd, updcmd, delcmd, ds, ds.Tables(0).TableName)

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Never trust a programmer in a suit.” - Anonymous