# Database Class for MySQL, MsSQL, Transoft, ODBC, OLE

**URL:** <https://forums.thehomestack.io/t/database-class-for-mysql-mssql-transoft-odbc-ole/350>\
**Category:** vb.Net\
**Created:** [April 4, 2009, 7:15pm UTC](https://forums.thehomestack.io/t/database-class-for-mysql-mssql-transoft-odbc-ole/350 "2009-04-04T19:15:46Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![system](https://forums.thehomestack.io/uploads/default/original/1X/161426793bc0d8bdf0d1a6692b3e44446861cc72.jpeg) [@system](https://forums.thehomestack.io/u/system)\
**Post date:** [April 4, 2009, 7:15pm UTC](https://forums.thehomestack.io/t/database-class-for-mysql-mssql-transoft-odbc-ole/350/1 "2009-04-04T19:15:46Z")

</div>

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.

```auto

Dim Comm As New Onling.clsSQLExecutes

```

```auto

                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](http://ConnectionStrings.com).

ExecuteNonQuery Method

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

```auto
	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:

```auto
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.

```auto
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.

```auto
	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:

```auto
	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’.

```auto
	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.

```auto

	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.

```auto
' 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](https://forums.thehomestack.io/uploads/short-url/WSqZug7Te0GHbUflvNuQaZyv2U.rar) (5.3 KB)
