VB.NET: Upgrading from MS-Access to SQL-Server (Part 2/3)

 

Introduction

---

In part 1 of this series covered preparing to transition from working with MS-Access data operations to Microsoft SQL-Server. In this part of the series reading data using appropriate data providers will be used, OleDb for MS-Access, SqlClient for SQL-Server.

Data provider comparing read operations

---

For a basic reading data from either MS-Access or SQL-Server differs only by the data provider. Most of functions used in MS-Access read operations will work with SQL-Server while many times SQL-Server will have better methods for example, in MS-Access to get dates between two dates.

In MS-Access OrderDate >= StartDate And <= EndDate while with SQL-Server OrderDate between StartDate and EndDate.

To run this example first run the database script included with the source code.

In the following two code examples both read the exact same table structure, one in MS-Access, one in SQL-Server.

MS-Access example

Imports System.Data.OleDb
 
Namespace Classes
    Public Class  DataOperations
 
        Private ConnectionString As String  =
                    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NorthWind.accdb"
 
        Public LastException As Exception
 
        Public Function  LoadCustomerRecordsUsingDataTable() As DataTable
 
            Dim selectStatement =
                    "SELECT Cust.CustomerIdentifier, CT.ContactTypeIdentifier, Cust.CompanyName, " &
                    "Cust.ContactName, CT.ContactTitle, Cust.Address AS Street, Cust.City, " &
                    "Cust.PostalCode, Cust.Country, Cust.Phone, Cust.ModifiedDate " &
                    "FROM Customers AS Cust INNER JOIN ContactType AS CT ON " &
                    "Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier;"
 
            Dim customerDataTable = New DataTable
 
            Using cn As  New OleDbConnection With {.ConnectionString = ConnectionString}
                Using cmd As  New OleDbCommand With {.Connection = cn}
                    Try
                        cmd.CommandText = selectStatement
 
                        cn.Open()
 
                        customerDataTable.Load(cmd.ExecuteReader())
 
                        customerDataTable.Columns("CustomerIdentifier").ColumnMapping = MappingType.Hidden
                        customerDataTable.Columns("ContactTypeIdentifier").ColumnMapping = MappingType.Hidden
                        customerDataTable.Columns("ModifiedDate").ColumnMapping = MappingType.Hidden
 
                    Catch ex As Exception
                        LastException = ex
                    End Try
                End Using
            End Using
 
            Return customerDataTable
 
        End Function
 
    End Class
End Namespace

SQL-Server example

Imports System.Data.SqlClient
 
Namespace Classes
    Public Class  DataOperations
        Private ConnectionString As String  =
                    "Data Source=KARENS-PC;" &
                    "Initial Catalog=NorthWindAzure;Integrated Security=True"
 
        Public LastException As Exception
        Public Function  LoadCustomerRecordsUsingDataTable() As DataTable
 
            Dim selectStatement =
                    "SELECT Cust.CustomerIdentifier, CT.ContactTypeIdentifier, Cust.CompanyName, " &
                    "Cust.ContactName, CT.ContactTitle, Cust.Address AS Street, Cust.City, " &
                    "Cust.PostalCode, Cust.Country, Cust.Phone, Cust.ModifiedDate " &
                    "FROM Customers AS Cust INNER JOIN ContactType AS CT ON " &
                    "Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier;"
 
            Dim customerDataTable = New DataTable
 
            Using cn As  New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As  New SqlCommand With {.Connection = cn}
                    Try
                        cmd.CommandText = selectStatement
 
                        cn.Open()
 
                        customerDataTable.Load(cmd.ExecuteReader())
 
                        customerDataTable.Columns("CustomerIdentifier").ColumnMapping = MappingType.Hidden
                        customerDataTable.Columns("ContactTypeIdentifier").ColumnMapping = MappingType.Hidden
                        customerDataTable.Columns("ModifiedDate").ColumnMapping = MappingType.Hidden
 
                    Catch ex As Exception
                        LastException = ex
                    End Try
                End Using
            End Using
 
            Return customerDataTable
 
        End Function
 
    End Class
End Namespace

 
Both of the examples shown have been coded in a individual class which is a best practice rather than coding these methods in a form. Keep code to interact with data in a form means the code is shut off from the rest of the application and when needed in another form must be copied, possibly altered to suit the needs of that form. This in turn usually leads to additional maintenance and bugs in the application. 

The following code calls both methods in the same form and displays both the MS-Access and SQL-Server data in separate DataGridView controls.

Public Class  MainForm
    Private Sub  MainForm_Shown(sender As Object, e As  EventArgs) Handles  Me.Shown
        Dim accessOperations As New  DataOperationsAccess
 
        Dim customersFromAccessDataTable As DataTable =
                accessOperations.LoadCustomerRecordsUsingDataTable()
 
        If accessOperations.LastException Is Nothing  Then
            ' If no run time exceptions populate DataGridView with the DataTable
            customersAccessDataGridView.DataSource = customersFromAccessDataTable
 
            ' Expand all columns so all data is visible
            customersAccessDataGridView.ExpandColumns
 
            ' Split column headers where a field name is PostalCode make it Postal Code.
            customersAccessDataGridView.Columns.
                Cast(Of DataGridViewColumn).
                ToList().
                ForEach(Sub(col)
                            col.HeaderText = col.HeaderText.SplitCamelCase()
                        End Sub)
        Else
            MessageBox.Show(accessOperations.LastException.Message)
        End If
 
        Dim sqlServerOperations As New  DataOperationsSqlServer
        Dim customersFromSqlServerDataTable As DataTable =
                sqlServerOperations.LoadCustomerRecordsUsingDataTable()
 
        If sqlServerOperations.LastException Is Nothing  Then
 
            customersSqlServerDataGridView.DataSource = customersFromSqlServerDataTable
 
            customersSqlServerDataGridView.ExpandColumns
 
            customersSqlServerDataGridView.Columns.
                Cast(Of DataGridViewColumn).
                ToList().
                ForEach(Sub(col)
                            col.HeaderText = col.HeaderText.SplitCamelCase()
                        End Sub)
 
 
        Else
            MessageBox.Show(accessOperations.LastException.Message)
        End If
 
    End Sub
 
    Private Sub  closeApplicationButton_Click(sender As Object, e As  EventArgs) Handles  closeApplicationButton.Click
        Close()
    End Sub
End Class

Creating MS-Access queries 

---

When working with MS-Access creating queries can be done within MS-Access by selecting the Create tab

When working with one table select "Query Design" which generally will be more flexible than the "Query Wizard", The same goes for multiple table which will be discussed below.

After selecting "Query Design" a window appears prompting for table(s) to use.

Once the table has been selected it appears on the query canvas. Note the * at the top of the field list, rarely is this an option as only the fields which are needed should be selected.  In this case several fields are selected by single clicking on each required field. To add a where condition single click in the criteria text box for the column to create a condition, in this case the Country field.

Next click the down arrow on the view button and select SQL view. This reveals the SQL statement.

Copy the SELECT statement into a function as per below which is called by passing in a country name. In this case the user would pick the country name from a distinct list from the customer table since there is no reference table. The proper method would be to remove the country field, replace it with a key from a country table as done in most of the code samples for this article so a JOIN is used to show a country name as needed.

In the code the query has been altered for a) formatting b) replaced the literal text for the WHERE with a parameter. Using this technique of building a query, any query inside of MS-Access proves the query functions rather than writing the query in code and when it fails a developer may spend a good deal of time attempting to resolve the issue. A next step would be to write unit test for each query.

''' <summary>
''' Return a DataTable of customers by country
''' </summary>
''' <param name="pCountry">Valid Country name</param>
''' <returns>Customers from pCounty</returns>
Public Function  SimpleReadExample(pCountry As String) As  DataTable
    Dim selectStatement =
            <SQL>
            SELECT 
                Customers.CustomerIdentifier, 
                Customers.CompanyName, 
            Customers.ContactName, 
            Customers.Country
            FROM Customers
            WHERE (((Customers.Country)=@Country));
            </SQL>.Value
 
    Dim customerDataTable = New DataTable
    Using cn As  New OleDbConnection With {.ConnectionString = ConnectionString}
        Using cmd As  New OleDbCommand With {.Connection = cn}
            cmd.CommandText = selectStatement
            cmd.Parameters.AddWithValue("@Country", pCountry)
            cn.Open()
 
            customerDataTable.Load(cmd.ExecuteReader())
 
        End Using
    End Using
 
    Return customerDataTable
 
End Function

Working with multiple tables, select "Database tools"  followed by clicking "relationships"

• Right click on the canvas.
• Select "Show Tables"
• Add desired tables.
• To create a relationship between two tables
  • Select the primary table primary key e.g. Customers.CountryIdentifier
  • While holding the mouse down drag it to a child table primary key e.g. Countries.CountryIdentifier and release.
  • A dialog appears, select the join type.
• Repeat until all relationships have been completed.
• Press CTRL+S.

Example for three relationships

Once the above is done using "Query design" when tables defined in the relationships above are used the proper joins will be done for you. Note there are various types of joins so if queries don't present data properly try a different join along with ensuring data is entered properly and or if one side of a join is allowed to be null field values.

Creating SQL-Server queries

---

To properly create queries in SQL-Server they can be created by adding a text file to a project, change the the file extension to .sql and Visual Studio will provide what is needed to write raw queries.  

A toolbar will have

• Connect to a database button
• Run button
• Parse the current query button
• Estimate execution time button
• New query button
• Results to a grid button for select statements
• SQLCMD mode button
• Include execution plan button.

Using the above method is great for those well versed with writing SQL.

Another option in Visual Studio is to select "Server Explorer" window, select a table in a database and write queries.

The best option for writing queries is by downloading SSMS (SQL-Server Management Studio).  All of the basics are explained in the following SQL Docs page.

Connect to and query a SQL Server instance by using SQL Server Management Studio (SSMS) 

Although the above document covers the basics, here are a few things that can help create queries.

Create table relationships (same as done with MS-Access above)

• Connection to a database (explained in the link above).
• Open the database using Object Explorer.
• Double click on "Database Diagrams" a prompt appears to ask permissions to create a new diagram.
• Right click in Object Explorer Database Diagrams, select "New database Diagram"
• A canvas appears with a dialog to select tables (see figure 1), double click on each table to create a relationship followed by clicking the close button.
• Select the master table primary key, while holding the mouse down draw to the key in the child table.
• A dialog appears, for this example click okay and end up with figure 2.
• While the canvas is selected press CTRL+S and close the canvas.

Next, right click (in this case) on the Customers table in the Object Explorer and select "Select top 100 rows". The data is displayed e.g.

SELECT [CustomerIdentifier]
      ,[CompanyName]
      ,[ContactName]
      ,[ContactId]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[CountryIdentifier]
      ,[Phone]
      ,[Fax]
      ,[ContactTypeIdentifier]
      ,[ModifiedDate]
  FROM [NorthWindAzureForInserts].[dbo].[Customers]

Next find the window in SSMS at the top which is a ComboBox, if it shows "master" drop down the ComboBox and select the correct database for the query. In older versions of SSMS master will be selected while in newer versions the current database for the query will be selected. This is important for the next step, if not done the next step is not possible.

Select the entire query followed by right clicking on the canvas and selecting "Design query editor", a window appears as per figure 3 which provides options to alter the query.

Right click on an empty area of the canvas and select "Add table" which displays a window of available tables for this database. If the current database was not set as above in the toolbar Combobox tables from master would be shown. For this example the country table is selected.

After (in this case) selecting Countries table, single click the Customer table titlebar, select properties, in the Alias property type in Cust, click the close button.  Now repeat for the Countries table, this time for the alias type in C. The results are shown in figure 4, note the INNER JOIN was generated because of the database diagram create above.

Pressing the OK button produces the following (has been formatted to fit here). There is one issue, the Address column is a reserved word. 

SELECT Cust.CustomerIdentifier ,
       Cust.CompanyName ,
       Cust.ContactName ,
       Cust.ContactId ,
       Cust.Address ,
       Cust.City ,
       Cust.Region ,
       Cust.PostalCode ,
       Cust.CountryIdentifier ,
       Cust.Phone ,
       Cust.Fax ,
       Cust.ContactTypeIdentifier ,
       Cust.ModifiedDate ,
       C.Name
FROM   Customers AS Cust
       INNER JOIN Countries  AS  C ON  Cust.CountryIdentifier = C.CountryIdentifier;

Place brackets around Address and give it an alias

SELECT Cust.CustomerIdentifier ,
       Cust.CompanyName ,
       Cust.ContactName ,
       Cust.ContactId ,
       Cust.[Address] AS  Street ,
       Cust.City ,
       Cust.Region ,
       Cust.PostalCode ,
       Cust.CountryIdentifier ,
       Cust.Phone ,
       Cust.Fax ,
       Cust.ContactTypeIdentifier ,
       Cust.ModifiedDate ,
       C.Name
FROM   Customers AS Cust
       INNER JOIN Countries  AS  C ON  Cust.CountryIdentifier = C.CountryIdentifier;

Now the above will return all rows, typically this is not desired, instead return only data that is needed. Suppose only a specific country is needed. Open the designer again, select the CountryIdentifier column, in the Filter enter @CountryIdentifier, close the window.

Results

SELECT Cust.CustomerIdentifier ,
       Cust.CompanyName ,
       Cust.ContactName ,
       Cust.ContactId ,
       Cust.[Address] AS  Street ,
       Cust.City ,
       Cust.Region ,
       Cust.PostalCode ,
       Cust.CountryIdentifier ,
       Cust.Phone ,
       Cust.Fax ,
       Cust.ContactTypeIdentifier ,
       Cust.ModifiedDate ,
       C.Name
FROM   Customers AS Cust
       INNER JOIN Countries  AS  C ON  Cust.CountryIdentifier = C.CountryIdentifier
WHERE  ( Cust.CountryIdentifier = @CountryIdentifier );

Directly above the query add

DECLARE @CountryIdentifier INT = 4;

Run the query. Now select all contents of the current query, select the "design in query editor",  An error window appears, press OK. Now select everything except the DECLARE and now selecting "design in query editor" will work.

Once the query fits the needs it was created for copy the query to code for a select operation. For the Command object add a single parameter @CountryIdentifier with a value of in this case 4.

As with MS-Access writing queries outside of code to perfection means if a query fails in code more likely the issue is not the query but information passed in e.g. a country key that does not exists.

Figure 1

figure 2

Figure 3

Figure 4

Backup and restore database

---

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

When coding a solution which manipulates data the best way to backup and restore is using SSMS as described in the following Microsoft documentation.

On the following page is a walk through for backing up a database using code. In short to apply the code in your application create a class with a method to perform the backup using a SqlClient connection and command object wrapped in a try/catch statement.

Caveats
When backing up a database in code in a Visual Studio solution there are several considerations.

• Devise a recovery plan to restore the database.
• If the database is a LocalDb, in an application folder which is preferred rather than storing the database in the default location work out a naming convention e.g. use date and time in the file name of the two back-up files (database and transaction log) in the event the back-up is being performed when the computer happens to crash there is still a viable backup unless that was the first back-up every done,

Backups can also be performed using SMO (SQL Management Objects) for a server based database, not for localDb or SQL-Express editions of SQL-Server. Essentially using SMO methods for back-up and restores are similar to their counterparts T-SQL. See the following Microsoft documentation for learning SMO back-ups.

Caveats

• SMO libraries are specific to a version of SQL-Server e.g. SMO libraries for 2017 will not work for 2012 SQL-Server.
• SMO libraries are located under the following folder, replace 130 with your folder for SQL-Server.

 

Convert Microsoft Access SQL to SQL Server (T-SQL)

---

When moving from MS-Access to SQL-Server queries may fail as they use methods that simply don't map over to SQL-Server. The following are several important items that should be reviewed in MS-Access SQL statements. There are others, these would be the top items to understand.

The following page provides T-SQL functions to work with with a link at the bottom of the page to download information presented. At the top of each page there is a "Applies to", check this against the version of SQL-Server used in your solutions before attempting to use them, for instance STRING_AGG was introduced for SQL-Server 2017 which means it's not available for versions prior to 2017.

String Literals
Access: SELECT "This is a string"
T-SQL: SELECT 'This is a string'

String Concatenation Operator
Access: Value1 & Value2  (both will be implicitly converted to strings if they are not already)
T-SQL: Value1 + Value2 ( note that explicit conversion to a "string" datatypes is necessary in T-SQL)

Converting Data types
Access: CINT(value),  CDBL(value), CDEC(value),  CSTR(value), CDATE(value), CBOOL(value)
T-SQL: CONVERT(DATATYPE, value) – or – CAST(value AS datatype)

Converting NULL values
Access: NZ(Value, ValueToReturnIfNull)
T-SQL: COALESCE(Value, ValueToReturnIfNull) – or –  ISNULL(Value, ValueToReturnIfNull)

Checking for NULLs
Access: WHERE Value IS NULL   – or –  WHERE ISNULL(Value)   (note the difference from T-SQL's ISNULL)
T-SQL: WHERE Value IS NULL
        
String Segments
Access: MID(StringVal, StartPos, [length] )   (length is optional)
T-SQL: SUBSTRING(StringVal, StartPos, length )    (length is required!)

Finding a String within a String
Access: SELECT INSTR(start, StringToSearch, StringToFind) 
T-SQL: SELECT CHARINDEX(start, StringToSearch, StringToFind)

Formatting Dates, Booleans, Numerics as Strings
Access: SELECT Format(Value, FormatSpecification)  (note: this always returns a string value)
T-SQL Do not do this in T-SQL; format data at your front-end application or report

Parameterized queries

---

When working with parameters for queries e.g. SELECT/WHERE, UPDATE, DELETE with MS-Access parameters are ordinal based meaning they are added to a command object in the same order as in the SQL statement and since this is the case generally speaking developers will use a question mark to name a parameter as the name does not matter since parameters are ordinal in nature.

Example of a MS-Access SELECT with two parameters.

SELECT
    C.CustomerIdentifier, 
    C.CompanyName, 
    C.ContactName, 
    C.ContactTypeIdentifier, 
    CT.ContactTitle, 
    C.Country
FROM Customers AS C 
INNER JOIN ContactType  AS  CT ON  C.ContactTypeIdentifier = CT.ContactTypeIdentifier
WHERE C.ContactTypeIdentifier = ? AND C.Country = ?;

The same query for SQL-Server

SELECT C.CustomerIdentifier ,
       C.CompanyName ,
       C.ContactName ,
       C.ContactTypeIdentifier ,
       CT.ContactTitle ,
       C.Country
FROM   Customers AS C
       INNER JOIN ContactType  AS  CT ON  C.ContactTypeIdentifier = CT.ContactTypeIdentifier
WHERE  ( C.ContactTypeIdentifier = @ContactTypeIdentifier )
       AND ( C.Country = @Country );

If parameters are created out of order for the MS-Access query unexpected results will follow while with SQL-Server parameters can be in a different order than placed in the SQL SELECT.

To better keep track of parameters in MS-Access use named parameters and keep them in the same order as placed in the SELECT statement. When there are a good deal of parameters it will make debugging a query that fails to work as expected by being able to match up placeholders in the SELECT, UPDATE, DELETE or INSERT. Also this will become second nature when moving from MS-Access to SQL-Server.

Updated MS-Access SELECT statement.

SELECT
    C.CustomerIdentifier, 
    C.CompanyName, 
    C.ContactName, 
    C.ContactTypeIdentifier, 
    CT.ContactTitle, 
    C.Country
FROM Customers AS C 
INNER JOIN ContactType  AS  CT ON  C.ContactTypeIdentifier = CT.ContactTypeIdentifier
WHERE C.ContactTypeIdentifier = @ContactTypeIdentifier  AND C.Country = @Country;

To try out the above, use the following project, make sure to run NorthWinSript.sql first to create the database.

SQL-Server Stored Procedures

---

Stored procedures groups one or more Transact-SQL statements into a logical unit and is stored as an object in the Database Server. When a stored procedure is called at the first time, SQL Server creates an execution plan and stores it in the plan cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan so that the stored procedure can execute very fast with reliable performance.

They can

• Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
• Contain programming statements that perform operations in the database, including calling other procedures.
• Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include

• They allow modular programming.
• They allow faster execution.
• They can reduce network traffic.
• They can be used as a security mechanism.

There are three camps, those who use stored procedures exclusively, those who use both stored procedures and  mixture of both and those who refuse or don't take the time to understand stored procedures.

Stored procedures can perform simple to very complex task, for instance, exporting data to Excel, Access, JSON, CSV and XML, see the following, SQL-Server export to Excel, Access and XML article.

Practical example, there is a need to read chunks of data from a table for a client side code the SQL is directly in code as per below.

Public Function  GetDataTableListLocal() As List(Of DataTable)
    Dim dataTables = New List(Of DataTable)()
 
    Dim totalRecords = 0
    Dim tableIndex = 1
 
    Using cn = New  SqlConnection With  {.ConnectionString = ConnectionString}
        Using cmd = New  SqlCommand With  {.Connection = cn}
            '
            ' Used to chunk data in quarters of total records
            '
            Dim selectStatement =
                    "SELECT Cust.CustomerIdentifier,Cust.CompanyName,Cust.ContactName,C.[Name] AS Country " &
                    "FROM dbo.Customers AS Cust " &
                    "INNER JOIN dbo.Countries AS C ON Cust.CountryIdentifier = C.CountryIdentifier " &
                    "ORDER BY Cust.CustomerIdentifier  " &
                    "OFFSET @Offset ROWS FETCH NEXT 25 ROWS ONLY;"
 
            Dim countStatement = "SELECT COUNT(Cust.CustomerIdentifier) FROM dbo.Customers AS Cust"
 
            cmd.CommandText = countStatement
 
            cn.Open()
 
            '
            ' Get total records in table
            '
            totalRecords = Convert.ToInt32(cmd.ExecuteScalar())
 
            cmd.CommandText = selectStatement
            cmd.Parameters.Add("@OffSet", SqlDbType.Int)
 
            For rowIndex = 0 To totalRecords - 1
 
                If rowIndex Mod 25 <> 0 Then
                    Continue For
                End If
 
                cmd.Parameters("@OffSet").Value = rowIndex
 
                Dim dt = New DataTable() With {.TableName = $"Table_{tableIndex}"}
                dt.Load(cmd.ExecuteReader())
 
                dataTables.Add(dt)
 
                tableIndex += 1
 
            Next rowIndex
        End Using
    End Using
 
    Return dataTables
 
End Function

To use this as a stored procedure we wrap SQL above into a stored procedure in SSMS or in Visual Studio in a text file with a .sql extension

DECLARE @OffSet INT= 4;
 
CREATE PROCEDURE  dbo.FetchCustomers(@Offset INT)
AS
BEGIN
SELECT Cust.CustomerIdentifier,
       Cust.CompanyName,
       Cust.ContactName,
       C.Name AS  Country
FROM dbo.Customers AS Cust
     INNER JOIN dbo.Countries AS  C ON  Cust.CountryIdentifier = C.CountryIdentifier
ORDER BY  Cust.CustomerIdentifier
OFFSET @Offset ROWS  FETCH NEXT  25 ROWS  ONLY
END

There is very little code change. Note the CommandText changes to the stored procedure name and the CommandType changes to Stored Procedure.

Public Function  GetDataTableListStoredProcedure() As List(Of DataTable)
    Dim dataTables = New List(Of DataTable)()
 
    Dim totalRecords = 0
    Dim tableIndex = 1
 
    Using cn = New  SqlConnection With  {.ConnectionString = ConnectionString}
        Using cmd = New  SqlCommand With  {.Connection = cn}
 
 
            '
            ' Used to chunk data in quarters of total records
            '
            Dim selectStatement = "FetchCustomers"
 
            Dim countStatement = "SELECT COUNT(Cust.CustomerIdentifier) FROM dbo.Customers AS Cust"
 
            cmd.CommandText = countStatement
 
            cn.Open()
 
            '
            ' Get total records in table
            '
            totalRecords = Convert.ToInt32(cmd.ExecuteScalar())
 
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = selectStatement
            cmd.Parameters.Add("@OffSet", SqlDbType.Int)
 
            For rowIndex = 0 To totalRecords - 1
 
                If rowIndex Mod 25 <> 0 Then
                    Continue For
                End If
 
                cmd.Parameters("@OffSet").Value = rowIndex
 
                Dim dt = New DataTable() With {.TableName = $"Table_{tableIndex}"}
                dt.Load(cmd.ExecuteReader())
 
                dataTables.Add(dt)
 
                tableIndex += 1
 
            Next rowIndex
        End Using
    End Using
 
    Return dataTables
 
End Function

Are stored procedures right for every developer, absolutely not yet moving from MS-Access to SQL-Server it's wise to at least explore possibilities in projects.

How to

• Create
• Modify
• Delete
• Parameters
• Return data

Returning multiple results sets

---

In many well designed data centric solutions there are reference tables such as category, product, color, size etc. This reference table relate back to another table by primary key and displayed in controls such as ComboBox or ListBox. This is done using SqlDataReader.NextResult method.

In the example below two list are returned from a class method using ValueTuple (which is installed via NuGet package manager for a Visual Studio solution) for a return type of List(Of Category) and List(Of Employee).

In this case the returning data for both category and employee have commonality, something to display and a key which is a foreign key to a parent table. Since this is the case one class will work.

Public Class  ReferenceItem
    Public Property  Id() As  Integer
    Public Property  Name() As  String
 
    Public Overrides  Function ToString() As String
        Return Name
    End Function
End Class

In the following method the key point is the Do While reader.Read() which is on the first iteration for the categories table. then after the loop completed reader.NextResult is fired, we are in a outer loop Do While reader.HasRows so the inner Do While executes for employee table, after this loop reader.NextResult is called again and then checked with Do While reader.Read, this terminates as there are only two SELECT statements not three.

Public Function  RetrieveMultipleResults() As _
    (employeelist As  List(Of ReferenceItem),
        categoryList As  List(Of ReferenceItem))
 
    Dim employeeList As New  List(Of ReferenceItem)
    Dim categoryList As New  List(Of ReferenceItem)
 
    Dim isCategory = True
 
    Using cn = New  SqlConnection With  {.ConnectionString = ConnectionString}
        Using cmd = New  SqlCommand With  {.Connection = cn}
 
            cmd.CommandText =
                "SELECT CategoryID, CategoryName FROM dbo.Categories;" &
                "SELECT EmployeeID, FirstName + ' ' + LastName As FullName FROM dbo.Employees"
 
            cn.Open()
            Dim reader = cmd.ExecuteReader()
 
            Do While  reader.HasRows
 
                Do While  reader.Read()
 
                    If isCategory Then
                        categoryList.Add(New ReferenceItem() With
                            {.Id = reader.GetInt32(0), .Name = reader.GetString(1)})
                    Else
                        employeeList.Add(New ReferenceItem() With
                            {.Id = reader.GetInt32(0), .Name = reader.GetString(1)})
                    End If
 
                Loop
 
                reader.NextResult()
                isCategory = False
 
            Loop
 
        End Using
    End Using
 
    Return (employeeList, categoryList)
 
End Function

Here is the calling code, create an instance of the class for the method above, call the method then populate two ComboBox controls.

Public Class  Form1
    Private Sub  Button1_Click(sender As Object, e As  EventArgs) Handles  Button1.Click
        Dim ops As New  DataOperations
        Dim dualResult = ops.RetrieveMultipleResults()
 
        CategoryComboBox.DataSource = dualResult.categoryList
        EmployeeComboBox.DataSource = dualResult.employeelist
 
    End Sub
End Class

Entity Framework read operation

---

Although working with data using conventional methods, DataSet/DataTable is a solid way to work with data when moving to SQL-Server consider Entity Framework. There are two paths for working with existing databases. The first EF Designer from database which uses a wizard to create classes which will represent your database or Code first from database which uses a wizard to create classes which represent your database.

The differences between the two, with EF Designer all classes are generated with little capability to modify standard functionality, classes are displayed on a canvas which may appeal to some yet when additional control and functionality is needed this path falls short. Also, with large models (collection of tables) the designer file may become unstable while with Code first this does not happen. Another advantage of EF Designer first is if the database changes simple select the designer, right click on the canvas and select update model from the database.

With Code first from database the wizard creates the same classes as with the EF Designer without the canvas and provides the developer with full control of the model. The downside there is no update from database like with EF Designer as Code first from database is meant to be a starter for getting started. There is a work around, follow the steps using the wizard a second time, give the model name the same name as the original model e.g. NorthWind and append temp e.g. NorthWindTemp and when it's time to select changes or additions run through the wizard. Once the classes are generated copy needs parts to the original model then delete from app.config the connection string for the temp model.

Special note: Before running the wizard a second time on Code first from database make sure to backup the originally generated classes if there have been customization done as running the wizard a second or more times will overwrite custom changes. For example, a custom data annotation has been added to a property, when updating one of these classes the attribute will be gone. 

For an introduction to how to work with the EF Designer and Code first see the following. For a complete overview of Entity Framework see the official home page for Entity Framework.

Reading the same database as in the data provider example

---

When reading Customer data into a DataGridView using the data provider SQL was written to join several tables together e.g.

SELECT Cust.CustomerIdentifier ,
        Cust.CompanyName ,
        Cust.ContactId ,
        Contacts.FirstName ,
        Contacts.LastName ,
        Cust.ContactTypeIdentifier ,
        CT.ContactTitle ,
        Cust.[Address] AS  Street ,
        Cust.City ,
        Cust.PostalCode ,
        Cust.CountryIdentifier ,
        Countries.Name AS  CountryName ,
        Cust.ModifiedDate
FROM   dbo.Customers AS Cust
        INNER JOIN dbo.ContactType AS  CT ON  Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
        INNER JOIN dbo.Contacts  ON  Cust.ContactId = Contacts.ContactId
        INNER JOIN dbo.Countries ON  Cust.CountryIdentifier = Countries.CountryIdentifier
ORDER BY  Cust.CountryIdentifier

This query results were read into a DataTable thus detached from the database. Detached meaning the database connection has been closed and there are no links back to the database without creating a new connection to update, delete or add records. Entity Framework works the same way, perform a request e.g. read, update, add. Both by means of how a developer decides to perform these operations keep a connection open which many novice developers do but there is no reason for this and doing so requires a watchful eye on the connection, is it open, broken or closed? then to handle when the connection is not open. 

In a future part of this series examples will be shown how updates and additions to tables is easier to handle with Entity Framework in comparison to working with a data provider which is not as smart as Entity Framework.

After generating classes using Code first (see instructions here) to read in only the fields needed create a class, this class represents the same as for the data provider method.

Namespace Classes
    Public Class  CustomerEntity
        Public Property  CustomerIdentifier As Integer
        Public Property  Company As  String
        Public Property  ContactIdentifier As  Integer?
        Public Property  FirstName As  String
        Public Property  LastName As  String
        Public Property  ContactTypeIdentifier As Integer
        Public Property  ContactTitle As  String
        Public Property  Street As  String
        Public Property  City As  String
        Public Property  PostalCode As  String
        Public Property  CountryIdentifier As  Integer?
        Public Property  CountyName As  String
        Public Overrides  Function ToString() As String
            Return $"{CustomerIdentifier} -- {Company}"
        End Function
    End Class
End Namespace

Reading data, create a class, in this case DataOperations. In the data provider example a SQL SELECT with several JOIN was required while with Entity Framework the LINQ statement has the retrieval in the C# code which mirror SQL SELECT/JOIN. The method ReadCustomers returns a list of CustomerEntity as per above.

Imports EntityFrameworkCodeFirstWithExistingDatabase
 
Namespace Classes
    ''' <summary>
    ''' Read data using Entity Framework which does the same as the SQL SELECT
    ''' in the project BasicUpdatingDataProvider with less code overall and
    ''' more functionality.
    ''' </summary>
    Public Class  DataOperations
        Public Function  ReadCustomers() As  List(Of CustomerEntity)
 
            Dim customerData As List(Of CustomerEntity)
 
            Using context As  New NorthWindEntity
 
                customerData =
                    (From customer In  context.Customers
                     Join contactType In  context.ContactTypes On customer.ContactTypeIdentifier _
                         Equals contactType.ContactTypeIdentifier
                     Join contact In  context.Contacts On  customer.ContactId Equals contact.ContactId
                     Select New  CustomerEntity With
                         {
                         .CustomerIdentifier = customer.CustomerIdentifier,
                         .Company = customer.CompanyName,
                         .ContactIdentifier = customer.ContactId,
                         .FirstName = contact.FirstName,
                         .LastName = contact.LastName,
                         .ContactTypeIdentifier = contactType.ContactTypeIdentifier,
                         .ContactTitle = contactType.ContactTitle,
                         .Street = customer.Address,
                         .City = customer.City,
                         .PostalCode = customer.PostalCode,
                         .CountryIdentifier = customer.CountryIdentifier,
                         .CountyName = customer.Country.Name
                         }).ToList()
 
            End Using
 
            Return customerData
 
        End Function
    End Class
End Namespace

In the form to display data in a DataGridView 

Dim ops As New  DataOperations
DataGridView1.DataSource = ops.ReadCustomers()

Once data is displayed the data appears no different then with the data provider read operation. There is one issue, a DataGridView does not know how to sort and filter. To provide sorting and filtering an additional component is needed found here with full source and installed via the solution NuGet package manager.

To implement the special component BindListView, create a private variable in the form with the DataGridView.

Private customerView As BindingListView(Of CustomerEntity)

Then in form Shown (better than form load as form load may swallow errors).

Dim ops As New  DataOperations
customerView = New  BindingListView(Of CustomerEntity)(ops.ReadCustomers())
 
DataGridView1.DataSource = customerView

Working with TableAdapter, DataAdapter

---

TableAdapter components are the oldest of methods to work with data which work with both MS-Access and SQL-Server which start off by using a wizard to create classes which represent tables and relationships in a database. For those who are fluent with TableAdapters the best path to take when moving to SQL-Server is Entity Framework 6 where one option is to permit a wizard to create classes similar to TableAdapter methods while Entity Framework is a good deal more flexible than TableAdapters rather than using SqlClient which requires more knowledge to put together parts to interact with databases. DataAdapter components work well where data is presented, updated, deleted and add to tables using a DataGridView or aftermarket grid. Both TableAdapter and DataAdapter are easy to begin working with data when the data is well formed with primary keys and a good relational model while without proper primary keys and relational model working with databases can be frustrating at best.

When its time to move to a web for solutions TableAdapter components should be discarded as these are designed to be form based solutions for working with data. DataAdapter components could be used with web solutions yet are not as lightweight as using classes to represent a data model using Entity Framework or using a data provider e.g. SqlClient.

Learning Transactional SQL (T-SQL)

---

Without exploring what's available in T-SQL developers will resort to building a solution to a problem that may exists in T-SQL.

For example, returning random records from a database were the random records are also controlled by a percent of total rows in a table.  The following SELECT statement handles this requirement. Anytime a data centric solution can be done on the server rather than on the client is best to use the server CPU rather than the client's.

SELECT TOP  (10) PERCENT
    Cust.CustomerIdentifier,
    Cust.CompanyName,
    Cust.ContactName,
    C.[Name] AS  Country
FROM dbo.Customers AS Cust
     INNER JOIN dbo.Countries AS  C
        ON Cust.CountryIdentifier = C.CountryIdentifier
ORDER BY  CHECKSUM(NEWID());

While writing SQL statements in SSMS performing operations such as DELETE FROM SomeTable WHERE some condition and executing the result pane shows rows affected while it might be nice to see what rows actually were removed which can be done with OUTPUT Clause.

We want to remove a country, it does not exists anymore.

DELETE FROM  dbo.Customers
OUTPUT deleted.ID
WHERE (CountryIdentifier = 2)

In the results window the primary key is shown.

The same can be done for INSERT and UPDATE

INSERT INTO  Customers(CompanyName, ContactName)
OUTPUT inserted.ID
VALUES ('ABC Inc', 'Miss Joan')

Other features such as obtaining column information for a table can be useful.

SELECT  COLUMN_NAME ,
        CHARACTER_MAXIMUM_LENGTH ,
        ORDINAL_POSITION ,
        IS_NULLABLE
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = 'Customers'
        AND DATA_TYPE = 'nvarchar';

Summary

In the part of the series basics of reading MS-Access data compared to the same table in SQL-Server have been presented using data providers, in addition working with Entity Framework to read the same table in SQL-Server that was read in using a data provider. Using these comparisons provides a solid foundation for moving from MS-Access to SQL-Server in tangent with the information provided in part 1 of this series. 

Requirements

• Microsoft SQL-Server  Express edition, version 2012 or higher
• Microsoft SSMS  (SQL-Server Management Studio)
• Microsoft Visual Studio  2017 or higher

See also

• VB.NET: Upgrading from MS-Access to SQL-Server (Part 1/3) 
• Convert VBA Code to Transact-SQL 
• Working with parameterized SQL operations  
• Working with parameterized SQL operations part 2 
• Transact-SQL Portal 
• SMO.SQL-Server VB.NET Part 1 
• SQL-Server database login for Windows Forms (VB.NET) 

Resources

• VB.NET/SQL-Server: Dynamic WHERE conditions 
• Writing SQL Statements Properly for your solution 

Source code

https://github.com/karenpayneoregon/MoveFromAccessToSqlServerWithVisualBasicNet