Databases


What is the difference between ADO and ADO.NET ?

ADO.NET is a powerful successor to ADO which provides you with the same disconnected philosophy as the Web.
ADO.NET only supports forward-only, read-only resultsets and dsiconnected resultsest.
The key difference between ADO and ADO.NET is that ADO.NET can have a disconnected architecture.
Data is retrieved from the database and cached on your local machine. You only connect to the database when you need to change or acquire new data.
ADO.Net uses XML for representing data carries forward the .NET philosophy and ensures that data can be communicated with a wide variety of data sources, objects and applications.


Connected vs Disconnected data

You should use a Connected Data Access when:

  • Transactions are required

  • No user interaction with the data is necessary

  • processes large amounts of data

When you maintain a direct connection to a data source then you should use the classes SqlConnection, SqlCommand and SqlReader


You should use a Disconnected Data Access when:

  • User interaction is required

  • If memory data is required

  • Distributed applications

When you maintain a disconnected connection you should use a DataSet


Typed vs Untyped Datasets

A typed dataset is binded with the database table(s) at design time.
You have direct access to all the schema information and can use/reference the actual table and column names.
The schema file is added as an (.xsd) file


An untyped dataset is binded at runtime. You are not aware of the schema information and have to access the tables and columns using index numbers.



Data Providers

ADO.NET uses the concept of a data provider to provide access to different types of databases.
The .NET framework includes the following two data providers
Different data providers use different namespaces. The two common namespaces are:
System.Data.SqlClient - contains classes that give optimised access to Microsoft SQL Server 7.0 and later
System.Data.OleDB - contains classes that give access to Microsoft SQL Server 6.5 and earlier. Also provides access to Oracle, sybase, Access etc


The DataAdapter is the bridge between the Connection and the DataSet.


 


ADO.NET Namespaces

There are six important namespaces
System::Data - contains all the objects that are generic to ADO.NET and that do not belong to a specific data provider.
(DataSet, DataTable, DataRow, DataColumn, DataRelation)


System::Data::Common - contains the DataAdapter object which both OleDbDataAdapter and SqlDataAdapter inherit from.
System::Xml -


Important Namespaces
System.Data.Common - used for permissions, table column mappings
System.Data.SqlTypes - define native types in SQL Server.


Storing connection strings in the web.config


Server Explorer

This is a graphical tool that lets you establish connections to local, client server or Internet based data sources. Using server explorer you can view the structure of the database tables and learn more about the attributes of the tables, fields and records in a database.


You can also log on to network servers and explore the databases and system services that they offer.
You can drag database components, or nodes from server explorer and drop them onto theVisual Studio.NET designers, such as Windows Forms Designer.


Before you can manipulate the information in a database, you need to establish a connection to it.


The DataAdapter objects Fill method opens up the connection, runs the query through a DataReader, constructs the DataTable objects and closes the connection
The Visual Studio IDE lets you drag and drop a number of Wizards to construct much of the code.


To register a database (View > Server Explorer) right click Data Connections, then Add Connection
Drag the datanase node onto the Windows Form.
This creates an OLEDBConnection to the source



ADO.NET Objects

CommandSqlCommand OleDbCommandOdbcCommand
ConnectionSqlConnectionOleDbConnectionOdbcConnection
DataAdapterSqlDataAdapterOleDbDataAdapterOdbcDataAdapter
DataReaderSqlDataReaderOleDbDataReaderOdbcDataReader


To easily view the layout of the System.Data namespace (View > Other Windows) use the Object Browser


Binding Source

??


Access - AutoNumber Data Type

Access allows you to use the AutoNumber datatype when you want it to automatically generate a unique number.
If your database table has an AutoNumber as the primary key then you not only should define this column as the primary key in your DataTable but you should also retrieve a generated AutoNumber to populate that column when you update the DataSet.


Defining a Primary Key

Note that the Primary key must be an array of DataColumn objects.
The following code defines the primary key to be a single column called "IDNumber".

Dim aobjDataColumns() As System.Data.DataColumn 
ReDim aobjDataColumns(0)
aobjDataColumns(0) = objDataTable.Columns("IDNumber")
objDataTable.PrimaryKey = aobjDataColumns


Returning a Primary Key

??



Combining Data Tables

It is possible to create a gird that reflects the relationship between two or more tables.
You often have a one-to-many relationship and you often need to display this.
You need to have a single DataSet object.
You need to create two DataAdapter objects
Add the two tables to the DataSet making sure you use the TableMappings to give them distinct names


Dim objDataRelation As System.Data.DataRelation 

Dim objDataColumn1 As System.DataColumn
Dim objDataColumn2 As System.DataColumn

'Each datacolumn must be assigned to a particular column in the tables
objDataColumn1 = objDataSet.Tables("TableName1").Columns("ColumnName")
objDataColumn2 = objDataSet.Tables("TableName2").Columns("ColumnName")

'you can know create the relationship passing in a suitable name
objDataRelation = New System.Data.DataRelation("RelationshipName", objDataColumn1, objDataColumn2)

'you can now add that relation to the DataSet
objDataSet.Relations.Add(objDataRelation)

'you then need to create a DataViewManager that provides a view of the DataSet for the DataGrid
Dim objDataSetView As System.Data.DataViewManager
objDataSetView = objDataSet.DefaultViewManager

DataGrid1.DataSource = objDataSetView

'you must then tell the DataGrid which table is the parent table. You can do this using the DataMember property
DataGrid1.DataMember = "TableName1"


Multiple Queries

You could use a SQL Inner Join but a much more efficient solution is to use multiple datatables in the same dataset.
The queries can then be run individually or grouped in a stored procedure or in a batch.
Using split queries results in a much more compact dataset.
Separate queries support cascading changes and batch updates and they don't require synchronisation code to be written to retrieve the children of a parent roe.


However filtering records over a relation is not particularly easy.


Dim ascolumnsarray As String(,) 

create the application DataSet
clsDataSet.mobjDataSet = New System.Data.DataSet()

clsDataSet.mobjDataSet.Tables.Add(New System.Data.DataTable("Worksheets"))

ascolumnsarray = DataGrid_DefineColumns()

Call clsDataTable.DefineColumns(Me.dgrWorksheets, "Worksheets", ascolumnsarray)

Call DataGridWorksheets_TablePopulate()

Call clsDataGrid.ViewCreate(Me.dgrWorksheets, "Worksheets", True)

Call clsDataGrid.TableStyleDefine("Worksheets", _
                                  Me.dgrWorksheets, _
                                  ascolumnsarray, _
                                  New clsDataGrid.DataGridFormatCellEventHandler(AddressOf DataGrid_CellFormatEvent), _
                                  System.Drawing.Color.DarkBlue, _
                                  System.Drawing.Color.White)


Public Function DataGrid_DefineColumns() As String(,) 

   Dim sacolumnsarray(,) As String
   Dim icolumnumber As Integer
   ReDim sacolumnsarray(3, 5)
   icolumnumber = -1

'Type
'Mapping Name
'Header Name
'Width
'Registry Prefix
'Read Only

   icolumnumber = icolumnumber + 1
   sacolumnsarray(icolumnumber, 0) = "TextBox"
   sacolumnsarray(icolumnumber, 1) = "Name"
   sacolumnsarray(icolumnumber, 2) = "Name"
   sacolumnsarray(icolumnumber, 3) = "90"
   sacolumnsarray(icolumnumber, 4) = "Name"
   sacolumnsarray(icolumnumber, 5) = "TRUE"


   clsDataTable.DefineColumns(gfrmDISPLAYCONDITIONS.dgrDataGridStocks, _
                              "Stocks", _
                              sacolumnsarray)

   DataGridStocks_DefineColumns = sacolumnsarray

End Function



Dim sConnectionStr As String 
Dim sSQLQuery As String
Dim objDataAdapter As System.Data.SqlClient.SqlDataAdapter
Dim objDataSet As System.Data.DataSet
Dim objDataTable As System.Data.DataTable
Dim objDataRow As System.Data.DataRow

sConnectionStr = "Provider=Microsoft.JET.OLEDB.4.0;" & _
                 "Data Source=C:\Temp\Name.mdb"

sSQLQuery = "SELECT Column FROM TableName"

'using this method will create a connection and SelectCommand object automatically
objDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sSQLQuery, sConnectionStr)

objDataSet = New System.Data.DataSet

objDataAdapter.Fill(objDataSet, "DataSetName")

objDataTable = onjDataSet.Tables(0)

For Each objDataRow in objDataTable.Rows
   Call Msgbox(objDataRow("Column_Name")
Next



Public Sub SQL_ClientsCompanies_CreateSelect() 

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objdatacommand As New System.Data.OleDb.OleDbCommand()

      clsDatabase.gsSQLQuery = ""
      clsDatabase.gsSQLQuery = clsDatabase.gsSQLQuery & " SELECT "
      clsDatabase.gsSQLQuery = clsDatabase.gsSQLQuery & " Clients.ClientID As ClientsClientID,"
      clsDatabase.gsSQLQuery = clsDatabase.gsSQLQuery & " Clients.CompanyID As ClientsCompanyID,"

      clsDatabase.gsSQLQuery = clsDatabase.gsSQLQuery & " Companies.ReciprocalLink As CompaniesReciprocalLink,"
      clsDatabase.gsSQLQuery = clsDatabase.gsSQLQuery & " Companies.Comments As CompaniesComments"

      clsDatabase.gsSQLQuery = clsDatabase.gsSQLQuery & " FROM Clients,"
      clsDatabase.gsSQLQuery = clsDatabase.gsSQLQuery & " Companies"

      clsDatabase.gsSQLQuery = clsDatabase.gsSQLQuery & " WHERE Companies.CompanyID = Clients.CompanyID"

      objdatacommand.Connection = clsDatabase.gDataConnection
      objdatacommand.CommandText = clsDatabase.gsSQLQuery

      clsDatabase.gDataAdapter.SelectCommand = objdatacommand

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_ERRMSG = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("SQL_Clients_CreateSelect", msCLASSNAME, _
              "", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Next