Create your own custom InputBox

The InputBox method is handy for asking the user to enter a piece of information and was heavily used back in the VB.Old days. So much so, that I see a number of posts on forums that still use this to get data from the user even when using VB.NET. However, one draw back of the InputBox method is that you cannot determine what button was pressed like you can with a MessageBox. So if you want to determine if the user pressed the Cancel button for example, then the only option is to test if a result was returned (i.e. an empty string or not). But this is not foolproof as the user could have pressed the Ok button without entering any data.

So, if you need an InputBox and you also need to know whether the Ok or Cancel button was pressed, then you will need to roll out your own Input Box.

The following snippet demonstrates creating your own custom InputBox replacement by creating a simple form that associates a dialogResult value to the Ok and Cancel buttons allowing you to check what was pressed.

To start, create a new form in your project called InputBoxForm and add the following controls so that it looks similar to the screen shot below:

CustomInputBox

Control TypeProperties
LabelName: promptLabel
Text: ""
AutoSize: False
TextBoxName: inputTextBox
ButtonName: okButton
DialogResult: Ok
Text: Ok
ButtonName: cancelButton
DialogResult: Cancel
Text: Cancel

Now add the following code to the InputBoxForm:

Note the three properties at the top, this allows us to set the title of the form and the prompt that will be shown in the label that we added. The final property can be read from the calling routine to determine what was entered by the user.

Finally, to use:

Note how the ShowDialog method is used to show the input box. This allows us to test what button was pressed via the DialogResult property. This will be Cancel if the Cancel button was clicked or if the form was closed via the Close box. Otherwise it will be Ok and we can grab the value that the user entered.

Add VBA to Excel file at runtime

This snippet demonstrates how you can add VBA code to an Excel file at run time.

Prerequisites

In this snippet I am using Microsoft Excel 2013 with the Microsoft Excel 15.0 object library.

Before you can use this code, you will have to change a setting within Microsoft Excel to authorise the use of the VBA Project Object Model from external programs. This is a security feature to protect end users from malicious programs and is disabled by default. If you do not enable this feature then the code below will result in an exception stating “Programmatic access to Visual Basic Project is not trusted” as soon as you attempt to reference the VBA environment.

Enabling trust to the VBA Project Object Model

  1. Open Excel and access the Options section.
  2. Open Excel and access the Options section.
  3. Click the Trust Center Settings button
  4. Select the Macro Settings section
  5. Tick the box labelled “Trust access to the VBA project object model”

The code

You will need the following Imports statements:

 

 

Display Excel data in a DataGridView

In this post, I will show you how to use ADO.NET to load data from an Excel file into a DataGridView.

The principle is pretty straightforward, the first thing we will do is ask the user to select an Excel file. Once selected, we will connect to the file using an OleDbConnection object and once connected we will then use the GetSchema method of the OleDbConnection object to load all Worksheets (tables in ADO.NET speak) into a combo box. When the user selects a worksheet name from the combo box we will then select all data from that sheet and display it in a DataGridView by using standard SQL syntax.

So, create a new Windows Form project with any name you like and add the following controls to your form so that it resembles the screen shot below:

Control TypeProperties
LabelName: excelFileLabel
Text: Excel File
Text BoxName: excelFileTextBox
Anchor: Top, Left, Right
ButtonName: openFileButton
Text: ...
Anchor: Top, Right
LabelName: worksheetsLabel
Text: Worksheets
Combo BoxName: worksheetsComboBox
Anchor: Top, Left, Right
Data Grid ViewName: Name: excelDataGridView
Anchor: Top, Left, Right, Bottom
ButtonName: closeButton
Text: Close
Anchor: Bottom, Right

excelDataGridView

Now add the following code to your form:

You can also download a version I made earlier.

Read and Write Images to Access Ole Object Field

This short snippet demonstrates how to write an image (jpg file) to an Ole Object field in Microsoft Access and then how to retrieve that image and display it in a picture box.

First, create an Access database (I was using Office 2013 but earlier versions should work) and a table with one field called pic. Set the field type of the pic field to Ole Object.

Writing the image to the database

The following code write an image to the pic field.

Once you have executed this statement you can check your database table and you should see that the field contains the text “Long Binary Data”, this is our image.

Reading an image from the Ole Object field

This next piece of code will read Long Binary Data value from the Ole Object field and convert it to a byte array. Once converted, the byte array will then be converted to an Image which we can then use to assign to a picture box or use in any other way we like. For this example, add a PictureBox control to your form and then use the following code to read the image:

 

Download SQL Server Express 2014 the easy way

Today I decided to install SQL Server 2014 Express Edition and figured how hard can that be? Well, Microsoft have certainly made it more difficult that’s for sure.

To start, I did a standard Google search for “SQL Server Express with Management Studio” as previous installations did not always come with Management Studio, and was pleasantly surprised to find that there are a number of options. You can download just a database engine, or one with tools (including the Management Studio) among others.

The instructions seemed straightforward, click the big green button labelled Get Started and fill in a form stating what version you wanted and what language. Sweet. Then you end up with a page with some resources and a big Download Now button. Couldn’t be simpler. WRONG!

The big Download Now button takes you back to the same page where you started so you end up in a perpetual loop. And Microsoft have a cheek to then send you an email thanking you for downloading with a link back to the same page.

So a little bit more googling and I come across a post from Scott Hanselman with direct links to the different downloads that you would need. For me this was SQL Server Express with Tools. This is how simple it should be.

Here’s the post: Downloading SQL Server Express

Get database schema using GetSchema

This tutorial demonstrates the use of the GetSchema method to find out the structure of a database. This includes tables within the database, columns and indexes of a table and views.

The GetSchema method was introduced as of .NET Framework 2.0, and is a method available as part of the System.Data.Common.DBConnection class which is inherited by the OleDbConnection and the SqlConnection classes meaning that it is very easy to obtain schema information for both SQL Server databases and OleDb databases such as Access and even Excel.

In this tutorial I will be focusing on OleDb and Access databases but I encourage you to play around with the code and see how easy it is to change to work with an SQL Server. Also, I am only focusing on a small amount of data that is available so again, please explore the schema information that is returned at various points in the code to see the wealth of information that can be obtained.

Creating the project

To start, create a new C# Windows Forms Application and when the project has loaded rename Form1 to mainForm.cs.

To create the UI, add the following controls to the form and set their properties according to the list below. There is a screen shot of the UI to assist in laying out the controls:

  • Label. Name: databasePathLabel, Text: Database
  • TextBox. Name: databasePathTextBox, Anchor: Top, Left, Right
  • Button. Name: openDatabaseButton, Anchor: Top, Right, Text: “…”
  • Label. Name: schemaLabel, Text: Schema
  • TreeView. Name: schemaTreeView, Anchor: Top, Bottom, Left, Right
  • Label. Name: viewDefinitionLabel, Text: View Definition, Anchor: Bottom, Left
  • TextBox. Name: viewDefinitionTextBox, Anchor: Bottom, Left, Right, Multiline: True, ScrollBars: Both
  • Button. Name: retrieveSchemaButton, Anchor: Bottom, Left, Text: Retrieve Schema, Enabled: False
  • Button. Name: exitButton, Anchor, Bottom, Right, Text: Exit

UI Layout

Classes

The following classes are used to store schema details. These are very basic as to capture all information returned would detract from the focus of this tutorial. However, you are encouraged to expand on these to suit your requirements.

Add the following classes to your mainForm code file, outside of the “public partial class mainForm : Form” but within the namespace.

Note that the Table class has properties for both the Column and Index classes so that we essentially have a one to many relationship, one table > many columns and many indexes.

We need two form level variables to reference both the Table and the View classes so add the following two declarations to your code file below “public partial class mainForm : Form”

 Basic UI code

Now add the following code to your form for the basic functions such as opening a dialogue to select a database, exiting the application etc.

 GetSchema code

Before getting into the code below, ensure you have the following two using statements:

In order to get schema information from the database we have to have an open connection to a database. The following code is the click event of the retrieveSchemaButton:

This simply connects to the database chosen and then calls two sub routines (GetTables and GetViews). These two sub routines which are shown below will populate our classes before then calling a final routine to update our TreeView with the output.

The GetTables routine below calls the GetSchema method of the OleDbConnection to request table information. When calling GetSchema, there are three overloads, the default returns high level schema information. The second returns schema information related to a specific type such as Tables or Views and the third which we are using in this instance provides a set of restriction values. In the GetTables routine below you will note for the first argument we are asking for Tables and in the second argument we provide a string array containing the restrictions which in this instance is set to TABLE. (More on restrictions later in further reading). If we were to omit the restrictions then we would have all tables returned regardless of their type. For example, we would retrieve information on Tables, Linked Tables, System Tables etc.

Note also that this code makes additional calls to GetColumns and GetIndexes for each table that is returned. Within those routines you will see a different set of restrictions that are passed, in this case the name of the table for which we want schema information.

The next piece of code is responsible for returning Views (queries) from the database:

In this routine we don’t supply any restrictions to the GetSchema call and simply ask it to return all Views. You will also note that we obtain the VIEW_DEFINITION, this is essentially the SQL statement for the view.

Displaying the output

The final piece of code is responsible for updating the TreeView to show all tables with their columns and indexes and all views.

Note that when we are adding the View information to the TreeView we also add the view object to the Tag property of the node. This is then used in the AfterSelect event of the TreeView to obtain the View Definition in order to display it in the Text Box below.

Conclusion

As you can see, it is relatively straightforward to retrieve information about an Access database. As mentioned previously, I would recommend that you set breakpoints within the code for each of the [inline]GetSchema[/inline] calls and use the inspector to view the contents of the DataTable returned to see what type of information can be retrieved.

Further Reading

The following MSDN pages should be consulted for additional information:

Treeview – checking and unchecking nodes

This code sample demonstrates how to check and uncheck nodes within a Treeview control regardless of what level you are at within the tree. For example, if you check a parent node then all child nodes of that parent will be checked. Likewise, if you have a child node that is not checked and you check it, it’s parent (and it’s parent) will be checked.

The following code is fired via the AfterCheck event of the Treeview control and I have provided both VB and C# samples. The only piece to point out is the use of the RemoveHandler and AddHandler code within the AfterCheck event. This is done to avoid a StackOverflowException being thrown.

VB.NET Sample

C# Sample

 

Using recursion to find a string in all text files

Recently I had a need to work through a large number of text files looking for various bits of information. I decided to write a small program that would at least narrow down the number of files that I had to search through by telling me which files within a directory (and sub directories) contained the string I was looking for.

You can download this solution to use it yourself. Below is just a couple of pointers as to how this works.

The following piece of code performs the search for files within a given directory (and sub directories if the user has checked a box indicating that they want to include sub directories):

Firstly, in order to search for files within a directory, we use the System.IO.Directory.GetFiles method which allows you to specify both the path that you want to search and optionally a file pattern, in this case Text files (*.txt). The syntax is very straightforward and a string array is returned containing the file names matching the files found within the path. Secondly, I have a check to see if the user has selected to include sub directories. If they have, then we make use of the System.IO.Directory.GetDirectories method which returns a string array containing all directories within the given path. Using each path returned I then call the same routine again in order to continue searching within that directory for files and further sub directories. This is known as recursion.

The code above calls out to a method called occurrencesOfSearchTerm which first opens the text file and then reads the entire contents before finally using a very basic regular expression to count the number of occurrences of the search term found within the file:

That’s it. Please download the solution to see the full set of code (not much more than what is posted here) and to test it out for yourself.

ADO.NET For Beginners Tutorial

Introduction

The purpose of this article is to introduce those new to database programming the basics of using ADO.NET (the database API provided by the .NET Framework). When I first started database programming, there were many books on the subject but they all focused on using the tools and wizards available within the IDE. What they left out was how to write the code yourself. There is nothing inherently wrong with using tools (and in many cases you should do) but it really is worth understanding how to do this by hand coding your database access and manipulation code so that you can then understand what the tools are doing.

This article will focus on several areas of ADO.NET in order to build a fully working database application. The application in question is a simple address book. We will look at how we connect to a database, perform CRUD (Create, Read, Update and Delete) operations on the database and finally how we can query the database for data.

Requirements

For this tutorial, I am using Microsoft Access 2013 and Visual Studio Professional 2013 with C#. However, all of the code in this article should be compatible with .NET 2.0 (Visual Studio 2005) and upwards. Also, if you have an older version of Microsoft Access (or newer – depending on when you are reading this) then it should simply be a case of providing the appropriate connection string to work with your version. See the Further reading section at the end of this article.

If you are developing with Visual Basic.NET then you can download a VB version of the solution which you can use while following this tutorial.

Disclaimer

Before we begin, I want to point out that this tutorial is focused purely on demonstrating how to perform various actions using ADO.NET. The code in this tutorial is certainly not production quality. For example, I am using straight forward SQL with no validation or use of parameters to avoid SQL Injection Attacks. Likewise, I do not provide any validation code to ensure values are present before submitting them to the database so exceptions can occur. I will however point out further resources that you can refer to after you have been through the tutorial.

Creating the database

The first activity is to create the Microsoft Access database that will be used for storing the address book information. In order to keep this tutorial focused, the address book database is kept very simple with one table called Contacts.

If you don’t want to create the database yourself then you can download one I made earlier.

Open Microsoft Access and create a new database called AddressBook.accdb. You can go ahead and delete the default table. Now, create a new table with the following fields and save it. Call the new table Contacts:

ContactsTableDefinition

 

As mentioned earlier, this database is very simple and will not win any awards for good database design but is ideal for this tutorial.

At this point it would be useful to populate the Contacts table with a few entries so that we have something to display when we start creating the application.

Creating the application

Now we will create a Windows Forms application in Visual Studio that will be used to display and manage data in the address book database.

You can download the solution but I recommend following the tutorial and creating the solution yourself.

Open Visual Studio and create a new Windows Forms application. Feel free to call it whatever you like. Once the project has been created you will have the default Form1 form open. Rename this form to mainForm. If you are asked if you would like to update all references within the project, say yes. This form will display all contacts within the address book and provide buttons for managing the data (add new, update and delete).

Add the following controls to the mainForm:

Main Form Controls

Control typeNameAdditional properties
DataGridViewcontactsDataGridViewAnchor: Top, Left, Right, Bottom
ButtoncloseButtonText: Close
Anchor: Right, Bottom
ButtonaddNewContactButtonText: Add New
Anchor: Left, Bottom
ButtonupdateContactButtonText: Update
Anchor: Left, Bottom
ButtondeleteContactButtonText: Delete
Anchor: Left, Bottom

Your form should look similar to:

Main Form Design

Next, add a new Windows Form to the solution by right clicking on the project within the Solution Explorer and selecting Add > Windows Form. When presented with the Add New Item dialogue enter contactDetailsForm as the name for the new Windows Form.

Add the following controls to the contactDetailsForm:

Contact Details Form Controls

Control typeNameAdditional properties
LabeltitleLabelText: Title
TextBoxtitleTextBoxAnchor: Top, Left, Right
LabelfirstNameLabelText: First name
TextBoxfirstNameTextBoxAnchor: Top, Left, Right
LabellastNameLabelText: Last name
TextBoxlastNameTextBoxAnchor: Top, Left, Right
LabeladdressLabelText: Address
TextBoxaddressTextBoxAnchor: Top, Left, Right
LabeltelephoneLabelText: Telephone number
TextBoxtelephoneTextBoxAnchor: Top, Left, Right
LabelemailLabelText: Email address
TextBoxemailTextBoxAnchor: Top, Left, Right
LabelageLabelText: Age
TextBoxageTextBoxAnchor: Top, Left, Right
LabeldateOfBirthLabelText: Date of birth
TextBoxdateOfBirthTextBoxAnchor: Top, Left, Right
ButtonokButtonText: Ok
Anchor: Bottom, Left, Right
DialogResult: Ok
ButtoncancelButtonText: Cancel
Anchor: Bottom, Left, Right
DialogResult: Cancel

Your form should look similar to:

Contact Details Form Design

Now that we have everything setup we can start looking at the appropriate code to display and manage data from the address book database.

The code

When using ADO.NET we make use of assemblies provided by the System.Data namespace and in particular the System.Data.OleDb namespace which provides the necessary API’s and objects for working with Microsoft Access.

If you open the Object Browser in Visual Studio (View > Object Browser) you will see an entry called System.Data. If you expand this entry you will see many namespaces such as System.Data.OleDb, System.Data.SqlClient (for working with SQL Server) and System.Data.Odbc (for working with Odbc databases) among others.

The objects that we will be using in this tutorial are:

  • System.Data.DataTable – An in memory table containing data that can be bound to controls or manipulated in code.
  • System.Data.OleDb.OleDbConnection – Provides methods for connecting to a database.
  • System.Data.OleDb.OleDbCommand – Provides methods for executing commands (SQL statements) against the database.
  • System.Data.OleDb.OleDbDataAdapter – Provides methods for populating DataSets and DataTables among other things.
  • System.Data.OleDb.OleDbDataReader – Provides a forward only data retrieval mechanism.

Retrieving all contacts from the Contacts table

When we run the address book application we will want to see all entries in our Contacts table (I hope you have added some records to the Contacts table otherwise the display will be empty). To do this, we will first need to connect to the database. Let’s start by creating a sub routine within our mainForm that will connect to the database and populate a DataTable with all records from the Contacts table. This DataTable will then be used as the data source for the Data Grid View. So open the code window for the mainForm and add the following routine:

Be sure to replace the <Your path> within the connectionString with the fully qualified path to your address book database.

The RetrieveAllContacts() routine will be called from several places hence the reason for adding it to its own sub routine.

If you look at the code you can see that we are creating a number of objects (OleDbConnection, OleDbCommand, OleDbDataAdapter and DataTable). The OleDbConnection is used to connect to the database. The OleDbCommand object will be used to execute the SELECT SQL statement against the database. Notice in the instantiation of the OleDbCommand that we pass both the connection object and the SQL statement that we want to execute. The next thing we do is setup the OleDbDataAdapter which will be used to populate the DataTable with the results of the SELECT statement (i.e. all rows from the Contacts table). With all of these pieces in place we then open the connection to the database and execute the SELECT SQL statement via the OleDbDataAdapter’s Fill() method. Note: Technically, we do not need to explicitly open a connection to the database as the Fill() method of the OleDbDataAdapter will do this for us if the connection is not already open. Finally, with the DataTable populated, we close the connection and bind the DataTable to the contactsDataGridView via its DataSource property.

Now, add a call to the RetrieveAllContacts() sub routine within the load event of the mainForm:

Also add the following code to the Close buttons click event:

If you run the application now you should be presented with the mainForm showing all records from the Contacts table:

Main Form with records displayed

Creating and updating a contact

Now that we can view all existing contacts, we can move onto creating new contacts. As we have the contactDetailsForm that will be used for both creating and updating contact details we need a mechanism for knowing whether we should be performing an update or a create. For this reason, open the code view of the contactDetailsForm and add the following property just above the constructor for the form (i.e. public ContactDetailsForm()):

The above property will be accessed from the mainForm as you will see later. If the value of contactId is zero then we know that we are inserting (creating) a new record, otherwise an existing Id has been passed and therefore we are updating an existing record.

Note: The above property is declared using the syntax supported from VS 2008 upwards.

To open the contactDetailsForm we need to add code to both the addNewContactButton and the updateContactButton. So firstly, add the following code to the addNewContactButton:

In the above code we use the ShowDialog() method to open the form which basically means that the mainForm cannot be accessed until the contactDetailsForm is closed. Note the check to see if the DialogResult of the contactDetailsForm is equal to an OK result. This is achieved via the DialogResult property that you set when creating this button and allows us to determine if we should do something (in this case, refresh the list of contacts as the Ok button was pressed and therefore we assume a new contact has been added).

Now add the following code to the updateContactButton:

The only difference with this code is the assignment of the ID to the contactId property of the contactDetailsForm. The remainder of the code is identical to the addNewContactButton.

Open the contactDetailsForm and add the following code to the click event of the okButton:

There are quite a few things to note about this code. First, note the check to see if we have an Id or not to determine which type of SQL Statement to run. When creating a new record we run an INSERT INTO SQL statement and when we are updating an existing record we run an UPDATE SQL statement.

Next, the SQL statements themselves. Note the use of apostrophes around the arguments (VALUES ‘{0}’, ‘{1}’ etc.). When writing string data to a field, that data must be enclosed within apostrophes. However, note that argument {6} does not have any surrounding characters. This is because it is a number value and therefore does not need to be enclosed in any special characters. Finally, note that {7} is enclosed with the hash (#) character. This is because it is a DateTime field. Finally, also note that when providing textual values to the SQL statement I am using the Replace function to take a single apostrophe and replacing them with two apostrophes. This is known as escaping and is done to stop the SQL string from throwing an error if the textual value already contains an apostrophe. What I mean by this is consider the following SQL Statement:

INSERT INTO Table (Field1) VALUES (‘Fred’)

This is perfectly valid and will not throw any SQL errors. However, consider the following:

INSERT INTO Table (Field1) VALUES (‘O’Reilly’)

This now breaks the string as there is an additional apostrophe within the textual value. To ensure that we can handle textual values that contain an apostrophe we can escape it by supplying two apostrophes so that the string becomes:

INSERT INTO Table (Field1) VALUES (‘O”Reilly’)

A better approach to this is to use parameters, but that is out of the scope of this tutorial (see further reading at the bottom of this article).

Now that we have the appropriate SQL statement to be processed we setup similar objects to those that we have already seen but this time we use the ExecuteNonQuery() method of the OleDbCommand object to execute the SQL statement.

Now if you run the application again you should be able to add a new record. Do not try to update a record yet as we haven’t provided the mechanism for displaying the existing details to be updated yet.

Retrieving a record

In order to update an existing record we first need to display the existing details on the contactDetailsForm so that they can be modified by the user. We have everything in place for providing the Id of the record and for performing the update, we just don’t have the display of the record yet.

Add the following routine to the contactDetailsForm:

The above code is similar to the code used to create and update a record. The main difference here is the use of the OleDbDataReader and the ExecuteReader() method of the oleDbCommand object.  When we want to read a single row or move through a resultset in a forward only way then the DataReader is the most performant way to do this. In the code above, we first check that we have some data returned and if we do we then use the Read() method of the DataReader to access the row. We can then access each individual column within the row via its index. The index can be an ordinal number (position within the table) or accessed via the column name. For readability, I suggest you use the name of the column.

Add the following code to the load event of the contactDetailsForm that will retrieve the contact record if a contactId has been provided (i.e. we are updating an existing record):

Ok, now you can run the application again and you should now be able to add and update records.

Deleting a record

The final piece is to create the code to delete a record. This code is very similar to the code used to update or add a new contact, the only difference is the use of the DELETE SQL statement. Add the following code to the deleteContactButton on the mainForm:

That’s it, you now have an application that can retrieve all records or a single record from a table, update and add new records and delete records.

As I mentioned at the beginning of this tutorial, this code is not production code and I would highly recommend that you do some further reading, especially regarding the use of SQL statements and parameterised queries to avoid SQL Injection attacks (especially when developing web applications).

Further reading and resources: