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)
评论