This class is for connection to different types of database and to get rid of my spaghetti code that I had.
Create Instance
At first you create an instance of SqlDatabase class.
Dim Comm As New Onling.clsSQLExecutes
Comm.SqlHostname = "mssqlhostinput"
Comm.SqlDBName = "mssqldbinput"
Comm.SqlCarname = "mssqlCarinput"
Comm.SqlPassword = "mssqlpassinput"
Comm.SqlConn("trustedconnectionchkbx")
If Comm.MsSqlState Then
ssconsql.BackColor = Color.LightGreen
ssconsql.Text = "SQL"
_log.Info("Connected to SQL Database...")
Else
ssconsql.BackColor = Color.Red
ssconsql.Text = "SQL"
_log.Error("Not connected to SQL Database...")
End If
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("@BMW", SqlDbType.NVarChar, 120)
params(0).Value = "BMW"
params(1) = New SqlParameter("@525i", SqlDbType.NVarChar, 120)
params(1).Value = "525i"
sqldb.ExecuteNonQuery("Insert Into dbo.Cars(BMW, 525i) Values(@BMW, @525i)", CommandType.Text, params)
If you are using stored procedure,you can execute that without declaring parameters such as following code:
sqldb.ExecuteNonQuery("dbo.CreateCar", Nothing, "BMW", "525i")
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.Cars", CommandType.Text)
MsgBox("Number of row(s): " & count)
ExecuteReader Method
Sends the CommandText to the Connection and builds a SqlDataReader.
Dim BMW As String = String.Empty
Dim 525i 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.Cars Where (Id = @Id)", CommandType.Text, params)
While dr.Read()
BMW = dr("BMW")
525i = dr("525i")
End While
dr.Close()
MsgBox(BMW & " " & 525i, MsgBoxStyle.Information)
There is a sample for using stored procedure:
Create Procedure [dbo].[GetCarInfo]
(
@Id int
)
As
Begin
Select * From dbo.Cars Where (Id = @Id)
End
Dim BMW As String = String.Empty
Dim 525i As String = String.Empty
Dim dr As IDataReader = sqldb.ExecuteReader("dbo.GetCarInfo", Nothing, 1)
While dr.Read()
BMW = dr("BMW")
525i = dr("525i")
End While
dr.Close()
MsgBox(BMW & " " & 525i, MsgBoxStyle.Information)
Using Return Value Parameter
If you are using stored procedure,you can get the value of ‘return value parameter’.
Create Procedure dbo.CarExists
(
@BMW nvarchar(120),
@525i nvarchar(120)
)
As
Begin
If Exists(Select * From dbo.Cars Where (BMW = @BMW) And (525i = @525i))
Return 1
End
Dim retval As Integer
sqldb.ExecuteNonQuery("dbo.CarExists", retval, "BMW", "525i")
MsgBox("Car 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.Cars", 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.Cars(BMW, 525i) Values(@BMW, @525i)")
With inscmd
.CommandType = CommandType.Text
.Parameters.Add(New SqlParameter("@BMW", SqlDbType.NVarChar, 120)).SourceColumn = "BMW"
.Parameters.Add(New SqlParameter("@525i", SqlDbType.NVarChar, 120)).SourceColumn = "525i"
End With
' Declaring update command object
Dim updcmd As New SqlCommand("Update dbo.Cars Set BMW = @BMW, 525i = @525i Where (Id = @Id)")
With updcmd
.CommandType = CommandType.Text
.Parameters.Add(New SqlParameter("@Id", SqlDbType.Int)).SourceColumn = "Id"
.Parameters.Add(New SqlParameter("@BMW", SqlDbType.NVarChar, 120)).SourceColumn = "BMW"
.Parameters.Add(New SqlParameter("@525i", SqlDbType.NVarChar, 120)).SourceColumn = "525i"
End With
' Declaring delete command object
Dim delcmd As New SqlCommand("Delete From dbo.Cars 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)
clsDatabase.rar (5.3 KB)