Library code snippets
SQL Data Provider VB.NET Class
Page 2 of 3
- The Class
- Example Usage
- SqlDatabaseException
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)
EndDim 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
EndDim 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)
Related articles
Related discussion
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
VB.NET Type 'SqlDatabaseException' not defined
by Mulish Mehdi (1 replies)
-
hey developers out there
by pitsophera (0 replies)
-
An Introduction to VB.NET and Database Programming
by carlosmen (14 replies)
-
How to view MYSQL db data in Microsoft word
by spanish (0 replies)
Related podcasts
-
Episode 10: LINQ
K Scott leads us in a discussion of LINQ, including: What is it How introducing LINQ to .NET changed the framework LINQ Providers LINQ to XML LINQ to SQL - how it's different from EF, tips and tricks, when to use it Links: LINQpad 3rd Party LIN...
Events coming up
-
Nov
18
15 Minutes of Fame
Dresher, United States
This is a yearly tradition. We select 10 of the favorite speakers from monthly meetings, code camps, and hands on labs. Each one does a 15 minute talk on their favorite .NET technology. This is our 10th anniversary so we plan a gala event with special prizes and refreshments.
how can define "SqlDatabaseException"?
This thread is for discussions of SQL Data Provider VB.NET Class.