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


SQL-Server example

 
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.

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.


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.

Creating SQL-Server queries

---

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.

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. 


Place brackets around Address and give it an alias


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

Directly above the query add

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.

The same query for SQL-Server


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.


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.


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


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


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.


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.


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

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.


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.


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.


In the form to display data in a DataGridView 

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.


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

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.


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.


In the results window the primary key is shown.

The same can be done for INSERT and UPDATE



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

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