This page describes the code for ADO.Net in Visual Basic.net
'REQUIRED NAMESPACE
Imports System.Data
Imports System.IO
'SELECT APPROPRIATE NAMESPACE
Imports System.Data.SQLClient
Imports System.Data.OleDb
Imports System.Data.OracleClient
Imports System.Data.Odbc
'GET PATH
Dim txtPath as string = 'FULLTEXTPATH'
Data Import
This procedure imports a data source and imports in to a dataset or reads using datareader. The example uses oleDb Data Provider, to use other Data Providers replace oleDb (eg. SQL for SQL Server Data Provider)
Public Sub IMPORT_DATA_SOURCE (txtPath As String)
'OPEN CONNECTION TO TEXT FILE
Dim sConnectionString As String
Dim objConnection As OleDbConnection
Try
If IO.File.Exists(txtPath) Then
sConnectionString = _"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtPath & ";Extended Properties=""Text;HDR=Yes;FMT=Delimited\"""
objConnection = New OleDbConnection(sConnectionString)
objConnection.Open()
'SET COMMAND
Dim sSQL as string
Dim objCommand As oleDBCommand
objCommand = New oleDbCommand()
objCommand.connection = objConnection
sSQL = "SQL …… @placeholder …."
objCommand.CommandText = sSQL
objCommand.CommandType = CommandType.Text
objCommand.Parameters.AddWithValue ("@placeholder", VALUE)
ALSO with SQLClient
objCommand.CommandText = "StoredProcedureName"
objCommand.CommandType = CommandType.StoredProcedure
ALSO with DataReader
Dim myReader as OleDBDataReader
myReader = objCommand.ExecuteReader
While (myReader.Read)
MessageBox.Show(myReader("FIELDNAME")
End while
If Not myReader Is Nothing Then
myReader = Nothing
End If
ALSO with XMLReader (with SQLClient only)
ObjCommand.ExecuteXMLReader
ALSO
ObjCommand.ExecuteNonQuery 'Execute Insert, Update, Delete Statement
ObjCommand.ExecuteScalar 'Returns first field in first row of result eg. aggregate SQL function
'SET DATAADAPTER
Dim objDataAdapter As oleDBDataAdapter
ObjDataAdapter = New oleDbDataAdapter ()
objDataAdapter.SelectCommand = objCommand
'CREATE DATASET
Dim objDataset As Dataset
objDataset = New Dataset ()
objDataAdapter.Fill (objDataset, "TABLENAME")
'CLOSE CONNECTION
If objconnection.State = Data.ConnectionState.Open Then
objconnection.Close()
objConnection = Nothing
Else
MessageBox.Show("File path does not exist", "No file found")
End If
Catch ex As Exception
MessageBox.Show("Connection failed :" & ex.Message, "Connection Error")
Finally
objDataAdapter = Nothing
objDataset.Clear
objDataset = Nothing
End If
End Try
End Sub
Data Manipulation
This script creates a dataset table with one column and one row
CREATE DATASET
Dim objDataset As Dataset
objDataset = New Dataset ("DATASETNAME)
CREATE DATATABLE
Dim objTable as DataTable
objTable = New DataTable ("TABLENAME")
objDataset.Tables.Add(objTable)
CREATE COLUMN
Dim objDataColumn as DataColumn
objDataColumn = New DataColumn ("COLUMNNAME")
objDataColumn.DataType = System.Typ.GetType("System.String")
objDataColumn.DefaultValue = "VALUE"
objDataTable.Columns.Add(objDataColumn)
CREATE ROW
Dim objRow as DataRow
objRow = objDataset.Tables("TABLENAME").NewRow
objDataset.Tables("TABLENAME").Rows.Add(objRow)
objRow("COLUMNNAME") = "VALUE"
objDataset.AcceptChanges ()
Add Constraint to DataTable
Dim objDataTable As DataTable
objDataTable = objDataset.Tables("TABLENAME")
Dim objConstraint as New UniqueConstraint("PrimaryKey, objDataTable.Columns("COLUMNNAME")
objDataTable.Constraints.Add(objConstraint)
Add Data Relationship between DataTables
Dim objParentColumn As DataColumn
Dim objChildColumn As DataColumn
Dim objDataRelation As DataRelation
objParentColumn = objDataset.Tables("TABLENAME").Columns("COLUMNNAME")
objChildColumn = objDataset.Tables("TABLENAME1").Columns("COLUMNNAME")
objDataRelation = New DataRelation ("RELATIONNAME", objParentColumn, objchildColumn)
objDataset.Relations.Add(objDataRelation)
Add Expression to Column
objDataTable.Column("COLUMNNAME").Expression = "COLUMNNAME1 x COLUMNNAME2"
Add Row to DataTable
Dim objRow As DataRow
objRow = objDataset.Tables("TABLENAME").NewRow
objRow("COLUMNNAME") = "VALUE")
objDataset.Tables("TABLENAME").Rows.Add(objRow)
Edit Row
Dim objRow As DataRow
objRow.BeginEdit
objRow("COLUMNNAME") = "VALUE"
objRow.EndEdit
objRow.AcceptChanges
Find Row
Dim objRows() As DataRow
objRows = objDataset.Tables("TABLENAME").Select("COLUMNNAME = 'VALUE'")
Set Value
variable = objRow("COLUMNNAME")
Edit Rows
objRows(0).Item("COLUMNNAME") = "VALUE"
Delete Rows
Dim i As Integer
For i = 0 to objRows.Length - 1
'Mark for deletion
objRows(i).Delete
'OR permanently delete rows
objDataset.Tables("TABLENAME").Rows.Remove(objRows(i))
Next i
Accept changes
If objRow.HasVersion(DataRowVersion.Proposed) Then
If objRow("COLUMNNAME", DataRowVersion.Current) = objRow("COLUMNNAME", DataRowVersion.Proposed) Then
Messagebox.Show (COLUMNNAME & " is unchanged")
objRow.CancelEdit
Else
objRow.AcceptChanges
End if
MessageBox.Show("Row has no proposed data")
End if
Data Export
Export directly
This procedure exports values from a field in a dataset
Public Sub EXPORT_DATASET_VALUES (objDataset as Dataset)
Dim objRow as DataRow
Try
For Each objRow In objDataset.Tables("TABLENAME").Rows
MessageBox.Show (objRow("FIELDNAME"))
Next
Catch ex As Exception
Finally
End Try
End Sub
Export to data adapter
This procedure updates a data adapter
Public Sub EXPORT_DATASET_VALUES (objDataset as Dataset)
Try
objDataAdapter.Update(objDataset)
Catch ex As Exception
Finally
End Try
End Sub
Export to DataView
This procedure exports values to a dataview
Public Sub EXPORT_DATASET_VALUES (objDataset as Dataset)
'CREATE DATAVIEW
Dim objDataView As DataView
Try
ObjDataView = New DataView(objDataset.Tables("TABLENAME")
Catch ex As Exception
Finally
End Try
ALSO sort with DataView
objDataView.Sort = "FIELDNAME", "FIELDNAME2 DESC"
ALSO filter with DataView
objDataView.RowFilter = "FIELDNAME = 'VALUE' And FIELDNAME2 LIKE 'D*'"
ALSO find with DataView
Dim intPosition as integer
objDataView.Sort = "FIELDNAME"
intPosition = objDataView.Find("VALUE")
End sub
