Vb Sql Tutorial

The VB.NET-MySQL Tutorial - Part 1 Introduction While the articles at vbmysql.com have been well received, there is always something new to cover. Every week I get requests for new sample code and articles explaining some new aspect of using Visual Basic and MySQL. One of the recurring requests has been for a full tutorial covering the creation of an application, from design to deployment, using Visual Basic and MySQL. In response to these requests I have written this article as the first of a series of articles that will provide a full tutorial for the creation of an application using Visual Basic.NET and MySQL. I will cover application design, database design, VB.NET database programming, and deployment of the finished application. This tutorial will be very hands-on, documenting the creation of a real application that will then be available for download in both source and binary form for future reference. Intermediate files will be available after each tutorial section that can be used to familiarize yourself with the project as it progresses. In this first installment of the series I will be covering the design of our application’s database. A well-designed database can be critical to the success of your database project, allowing for increased expandability and scalability, not to mention it makes for easier development. In this tutorial I will describe the steps needed to begin your application design, then move on to the design of your database, finishing up with the writing of your table creation statements.

Choosing an Application The very first thing required when creating an application is a need. Computer programs are created to fulfill needs that the application users have. In my office we have many staff members, each of whom may or may not be in the office at any given time. We currently make use of a simple application that tracks which employees are in the office and displays a short message for those who are out of the office indicating when they will return. This type of in/out tracking software is fairly common, but this type of application often comes with two limitations; many of these applications are limited to only a certain number of users, usually in the area of 50100. In addition, these applications often use flat files to store information; requiring that each user have access to a shared network directory in order to use the system. Both of these limitations can be overcome by using an SQL (pronounced “ess-que-ell”, not “sequel”) database. If the application we use at the office had its source code available, modifying it would have been an option (freedom to modify an application to suit your needs is one of the great benefits of using Open Source software and tools). Since the application in question is a relatively simple one, I have chosen to rewrite it using Visual Basic and MySQL. The idea for your application may come from a desire to “build a better mousetrap” - developing a better version of an existing application. On the other hand you may have a need for an application but cannot find a suitable existing project. Finally, the idea or need may be that of someone else; either a client or your employer. In any case, the first step when developing an application is deciding which application to develop.

Listing Requirements and Features Once you have determined what application you will be developing, it is important to then decide what specifically the application will do. You will want to define its basic functionality, along with certain features

that the software will implement. If you are developing this software for yourself, this stage in the development process can be fairly informal, but it is nevertheless very important. If you are developing software for a client or an employer then creating a list of features and requirements is vital. The list will determine what your responsibilities are as a software developer and will give a clear definition of what is required for your project to be considered complete. It should also be remembered that clients and employers have a tendency to request additional features as the project progresses while not wanting to pay any additional funds to implement them. If you have a list of features in hand you can inform your customer that your job is to implement the listed features and that any additional features will carry an additional cost. In addition to listing features and requirements, you will also want to develop a timeline and a budget. You would want to schedule significant milestones such as project completion and major phases of work being done. For example, you may wish to schedule a milestone to signify the delivery of application and database designs to the customer. You may also want a milestone for delivery of a working demonstration of the project. Often these milestones coincide with payments from the client when you work as a consultant, with the payments being defined in your budget. The Development Triangle On the subject of money and features and timelines, it is important to understand the balance that must exist between the three. Your client will undoubtedly want you to deliver as many features as possible within a small budget and a short time period. It will be important for you to realize that you may sometimes need to reject a requested feature or timeline in order to keep everything in balance. Imagine that time, features, and money are three corners of a triangle:

While you can stretch the corners of this triangle, you cannot change the area it occupies, as the area of the triangle represents your total resources. The impact of this is that every section can only increase at the expense of the other two: If you want a project to have more features you will have to either take more time or spend more money for additional tools or developers. If you want a project to cost less money, you either need to decrease the number of features or allow the project to take more time (this is because either you cut back on the number of developers or allow them to only work on the project in their spare time). Finally, if you

want the project done faster you either have to decrease the number of features or spend more money for more tools and/or developers (or at least pay overtime for your existing developers). When negotiating a feature list and timeline with your client or employer be sure to keep the triangle in mind, and even show it to your customer so they can understand the relationship between features, money, and time. If you are developing the project for your own use you benefit from having the perfect client, but still keep in mind that there is only so much you can do with your limited time. let’s take a look at the feature and requirements list for our in/out tracking software: • • • • • • • • • • • • • • • •

Login should require a username and password User should be prompted for a status message when logged in User can choose between no status message, a pre-set status message, or specify their own message when logging in or out Last two user-defined status messages should be available in a list along with pre-defined ones User should be able to login from any PC with an Internet connection, with an encrypted connection available User should be able to flag a fellow user and be notified when they check in Users should be divided into separate groups (Sales, Accounting, Programming, etc.) Main screen should show a list of all staff (or selected groups) with name, in/out status, status message, and custom fields Application should minimize to system tray User should be able to customize appearance, window size, font size, date format, name format, colors Standard users can change their status, display format, and send other users messages Receptionist users can change group users’ status (receptionists can be assigned to multiple groups) Managers can add/delete users within their groups, change their status (for their groups), and assign receptionists Administrators can create users, assign managers and administrators, and change group memberships. They have no restrictions Users should be able to choose between having the application start minimized or maximized Users should be able to choose refresh interval for open window

Your features and requirements list should be as detailed as needed for you and your client, and more complex applications should have more detailed documentation. Once your list of features, your budget, and your timeline is approved you can then move on to designing your application.

Entity Design One good approach to application design is to look at the different entities that will be involved in the application. An entity is simply an object we want to store information about. Designing an application based on its component entities is a basic principle of object oriented programming. In our application the main entity is our users. There are four different kinds of user: Regular User, Receptionist, Manager, and Administrator. There are two approaches we can take we can take to represent our different types of user. We could describe four different entities, one for each type of user, or we could just have one user entity with an attribute that defines its type. When different entities have a common base but still have major differences it can be beneficial to think of a base entity that is inherited by the other entities. let’s take vehicles as an example. While all vehicles have a

common foundation in that they all have wheels and move forwards and backwards, they can still be very unique:

As you can see, each of the vehicles have properties common to all vehicles, such as tires, steering wheels, and radios. At the same time each vehicle is also quite unique. Cars are smaller and usually carry more passengers than trucks. Trucks carry fewer passengers but have more cargo space. Taxis are similar to cars but carry farepaying passengers and have CB radios and fare calculating equipment. We could even have shown a taxi as being a sub-entity of a car, inheriting all the traits of a car and then adding special traits of its own. The only unique properties for our user entity is whether they are a manager, administrator, or receptionist. With such a small difference between the different users it is better to create only one entity with a property that designates the user’s administrative responsibilities. Other entities in our application include groups and events. let’s look at each entity in detail. Users A user is any user in our system. Users can login to our application, set their status, and view the status of others. A user has the following properties: • • • • • • • • • •

Name Phone Number Username Password Administrator (Yes/No) Creation Date Deleted (Yes/No) Custom Status Messages Groups user manages Groups user is a receptionist for

The deleted flag is used to determine whether the user should appear in lists. While we could simply delete a user from our database when they leave the company, it is better to simply mark them as deleted. While this will occupy more space in the database, it is a good record-keeping practice. You may notice that there is no In/Out status associated with the user; this is because their status will be recorded in the Events entity. Groups

A group is a set of users who share something in common. In most businesses staff members would be grouped by department; such as Accounting, Sales, and Support. It may be useful to allow users to be members of multiple groups so that a user can be in his department group and perhaps also be listed in a special group set up for an inter-departmental project. The following properties will apply to groups: • • • • • • •

Name Creation Date Description Private/Public Deleted (Yes/No) Managers Receptionists

A private group is one that cannot be viewed by those outside the group. One example would be a group of executives who do not wish their status to be known by non-executives. Events An event is simply a change in a user’s status. When a user signs out, an event will be generated that will record which user the event refers to, whether the user signed in or out, their status message, and who caused the event. While normally a user will cause his/her own event, receptionists are allowed to sign other users in and out. We will want to be able to track who signed a given user in or out. The properties of events are as follows: • • • • •

Timestamp User In/Out Status Message Creator

Once we have our entities defined, we can add them to a diagram:

In reality there are a few more entities but these three main entities are sufficient for the purposes of this tutorial.

Entity Relationships Now that we have our entities defined we need to look at the relationships between them. MySQL is a relational database and as such the data within it is defined by its relations. One way to define entity relationships is in terms of the quantities on each side of the relationship between two given entities. For example, a user can belong to one group (accounting), or many groups (accounting, year end team, Christmas party committee). This is called a one-to-many relationship. Each group will contain many users. The

relationship between the user and group entities is a many-to-many relationship (many users belonging to many groups). The relationship between users and events is different. An event will refer to one user, no more and no less. A user, on the other hand, will have many events that refer to him or her. This means that a one-to-many relationship will exist between the events and users. These two types of relationship (one-to-many and many-to-many) will be taken into consideration when designing our database tables. For now let’s just draw some arrows to identify a relationship, with the arrowheads identifying the ‘many’ side of the relationship:

Relationships can be optional when a relationship can be defined as zero-to-many, as is actually the case between the user and event entities. A new user may have never signed in or out. In this case there would be no events related to the user. Our entities will influence our software in two ways. The entities that we have designed will be used later on to lay the foundation for our database tables. In addition, these entities will also help form classes, a building block of our final application (the use of classes in application programming is referred to as object oriented programming and will be covered in an upcoming tutorial section). There are a wide variety of ways to define data entities and their relationships. The description above is simply a general overview to get you started thinking in terms of entities, and does not represent any particular established method for describing and diagramming entities. Do you need to learn one of the established methods? That depends. When working on your own at developing an application it is often enough to quickly sketch out the entities and their properties and relationships using a system that you understand and are comfortable with. If you find yourself managing a large project and working with other developers it becomes important to adopt a more formalized approach. One good system that is becoming increasingly popular is UML, or the Unified Modeling Language. There are a myriad of books on the subject of UML, some of which are even specifically directed at using UML to design Visual Basic 6 applications. An example of the latter would be Developing Applications with Visual Basic and UML by Paul Reed. A good portal to UML resources is available from IBM at http://www306.ibm.com/software/rational/uml/. Pay particular attention to the document “Introduction to the Unified Modeling Language“.

Designing The Database Once we have our entities and their relationships defined, we can move on to designing the database schema itself. When designing a database, we will want to draw our tables, with one icon per table, and include the table name and columns that make up the table. I will be using the Visio design tool by Microsoft to produce

the database diagrams, but you can use any drawing tool you have available (including the decidedly low-tech pencil). let’s start with the following:

Choosing A Primary Key We now have all our entities listed with the fields that will make up their tables. The first thing we need to add to our tables is a PRIMARY KEY. A primary key is a column in your table that will uniquely identify every row in the table. In the User table you may be tempted to use Name or perhaps Phone Number as a primary key, but your company may have more than one John Smith, or your employees may have to share phones. While it is possible to have more than one column together as a composite primary key (such as making the primary key the combination of Name and Phone Number), you have to be certain that every entry will be unique (what happens when the two John Smiths share the same phone?). So what do we use as a primary key for the user table? In this case it is probably best to introduce a new column, called User_ID. This column will be an integer and will be incremented automatically by MySQL through the use of the AUTO_INCREMENT keyword (more on AUTO_INCREMENT later). For the Group table we can use the Name column as a primary key, as each group will be required to have a different name so as to avoid confusion when choosing which group to place a user in. For the Event table we could use the Timestamp column as a primary key, but there is one caveat with this approach: the TIMESTAMP data type is only accurate down to the second, which means that if two users record an event within the same second (not unthinkable on a busy system with hundreds of users), the primary key’s requirement for uniqueness will be violated. In this case we have one of two choices: we could use a combination of the Timestamp and User columns as a primary key, safely assuming that a single user is unlikely to cause two events within the same second. By using the primary key of another table (the User_ID from the User table) as part of a table’s primary key, we would create what is known as an Identifying Relationship. An identifying relationship is one where the child table or entity cannot exist without its parent. User and Group have a Non-Identifying Relationship because a User can exist without belonging to a Group, and a Group can exist without any members. An Event, on the other hand, is meaningless without having a user to refer to. Another option is to create a new column (Event_ID), which would once again be an AUTO_INCREMENT integer value. Typically either approach would be valid, but there is one important fact we should keep in

mind: MySQL and Visual Basic use different date formats. The MySQL format of YYYY-MM-DD HH:MM:SS is often misinterpreted by Visual Basic, which uses a date format of M/D/YYYY HH:MM:SS. let’s look at the differences, first with a MySQL query and then with a simple VB example: mysql> select now(); +---------------------+ | now() | +---------------------+ | 2004-01-05 13:29:03 | +---------------------+ 1 row in set (0.02 sec)

And now in the VB Immediate window: ?now 1/5/2004 1:25:38 PM

As you can see there are some big differences in date handling. Aside from the order of the values, MySQL uses leading zeros when representing numbers smaller than ten, and uses a 24-hour clock. These differences in how dates are handled can produce serious problems for Visual Basic as VB will be expecting one format while MySQL will be expecting another. For this reason it is recommended that you NEVER use any date type columns as part of a table’s primary key. Therefore we will use an Event_ID column as the primary key of our event table. While this does not produce an identifying relationship, we shall still treat it as one within our application. let’s take a look at our diagram now that it has primary keys:

You may have noticed that I changed the Name column of the Group table to Group_Name. The reason for this is that primary keys are often referred to in database queries that involve multiple tables, and it is easier to say SELECT Group_Name FROM Group, User; rather than SELECT Group.Name FROM Group, User; trust me, its a lot easier this way. As you can see our primary key columns get moved into the center box of our table icons and have been highlighted in bold. With a Visio database diagram, all columns that form part of the primary key are displayed in the center box and have the PK identifier to their left. The boldface highlighting indicates that this is a required field and cannot be left blank (a field which does not have a value assigned is actually assigned the value of NULL, meaning undefined or no value). Choosing Field Names and Required Fields

Next we will want to clean up our field names and designate the rest of our required fields. A note on field names: while you can use spaces in a field name with MySQL, it will make your life harder as you will need to enclose the name in back ticks (“) every time you want to use it. For example, to access the Creation Date column you would need to query it like this: SELECT `Creation Date` FROM Group; I personally do not like having to use back ticks and always name my columns using underscore characters to represent spaces when referring to primary keys (User_ID), and use capitalization of distinguish names for regular columns (CreationDate). When setting field names you will want to keep readability in mind; you want column names to be short enough to type quickly when using them repeatedly, but you also want to make sure that future developers (including yourself) will be able to understand their meaning:

One-To-Many Relationships Now that we have our columns named, let’s define the relationships between the tables. First let’s look at the one-to-many relationship between an Event and a User. When dealing with a one-to-many relationship, we place the primary key of the “many” table as a field in the “one” table (I should note that column, field, and property are synonyms). In this case we already have a User column in the Event table, but let’s make it clearer by renaming it to match the definition in the User table by calling it User_ID. In addition, we can use the relationship arrow to draw our relationship (once again the arrowhead points to the “many” table):

As you can see, an FK1 identifier has been added to the left column; FK stands for Foreign Key. A foreign key is simply a column that refers to the primary key of another table. The InnoDB table handler built into MySQL handles what is known as relational integrity. Relational integrity is the requirement that foreign keys represent real values. For example, if you tried to insert a row into the Event table that had a User_ID of 10, InnoDB would check if a User_ID of 10 existed in the User table and return an error if such a parent row did not exist. Conversely, you would not be allowed to delete a row from the User table if there were rows in the Event table that referred to the row in question. The use of InnoDB is beyond the scope of a tutorial on basic MySQL usage and will receive no further coverage in this tutorial but will be the subject of a future article. Further information of InnoDB can be found at http://www.mysql.com/doc/en/InnoDB.html. Many-To-Many Relationships Now that we have established the relationship between Event and User, we can move on to defining the relationship between User and Group. As you will recall, the relationship between User and Group is a manyto-many relationship. This kind of relationship is not as simple to implement as a one-to-many relationship. In a one-to-many relationship we can place the primary key of the “many” table in a column of the “one” table, but this does not translate effectively into our many-to-many scenario: which table will hold the primary key of the other? The answer is that neither table will hold the primary key of the other. Instead, we will create a third table to join the two:

In this case the User_Group table has two foreign keys: the Group_Name and the User_ID. These two foreign keys form a Composite Primary Key, ensuring that there is only one entry in the table for each unique User/Group combination. In addition, two fields have been added that will flag whether the user is a manager or receptionist for the group, allowing us to remove the fields from both tables referring to managers and receptionists. This design allows us to have multiple managers and/or receptionists for each group and also allows a single user to manage multiple groups.

Normalizing The Database Database normalization is covered in detail in another article I wrote at http://www.vbmysql.com/articles/database-design/normalize.html and I would recommend reading it for more information on database normalization. In this tutorial I will cover the basics as they apply to our example design. Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability. This improvement is balanced against an increase in complexity and potential performance losses from the joining together of the normalized tables at query-time.

First Normal Form The normalization process involves getting our data to conform to three progressive normal forms, and a higher level of normalization cannot be achieved until the previous levels have also been achieved (there are actually five normal forms, but the last two are mainly academic and will not be discussed). The First Normal Form (or 1NF) involves removal of redundant data from horizontal rows. We want to ensure that there is no duplication of data in a given row, and that every column stores the least amount of information possible (making the field atomic). We have two violations of First Normal Form (1NF) in our database model. The first is that our Name column of the User table holds both the first and last names of our user. This does not represent the smallest amount of data possible. In addition, it makes it difficult to sort users by last name. Finally, our users will not be able to customize their lists to show names in order of first name/last name or last name/first name easily. To solve this we simply split our Name column into two columns:

The second violation of 1NF is the horizontal redundancy of the Status columns (note that while the columns appear vertically in this diagram the columns are actually horizontally arrayed when in table form. Imagine the table as a page on a ledger; with column headings across the top of the page and individual rows below). Not only will it be hard to query horizontally redundant columns (You would need a query like SELECT * FROM User WHERE Status1 = ‘My Status’ OR Status2 = ‘My Status’;), but you have just locked your users into only having two custom status messages. If a customer ever demands the ability to store three status messages you will need to add another column and rewrite the sample query I just gave. Horizontally redundant columns can be dealt with by adding another table to store the status messages. This presents us with an opportunity to also add the predefined status messages to the system:

Our new status table will hold every status message ever entered, whether the message is custom or predefined. Our event table then links to the Status table to indicate which Status message the event uses. If a message is pre-defined then the User_ID field will not be populated. If the status message is a custom message then the User_ID field will be populated, associating the status message with its author for later retrieval (remember that one of our features on the list is the retrieval of previous custom status messages for reuse). Before we declare our table to be in first normal form, there is one other adjustment we can make. Rather than store a flag for Manager and a separate one for Receptionist we can combine the two columns into one Administrative Level column. Since the two positions are mutually exclusive there is not much point in storing both fields separately, as no user will ever be a Manager AND a Receptionist for the same group:

Since a user could have no administrative privileges over the group I have made the field one that is not required. This way the field can be populated with NULL to indicate that the user has no administrative privileges. Second Normal Form Where the First Normal Form deals with redundancy of data across a horizontal row, Second Normal Form (or 2NF) deals with redundancy of data in vertical columns (where the same data appears in multiple rows). The normal forms are progressive, so to achieve Second Normal Form, your tables must already be in First Normal Form. In this case, the titles given for the administrative levels (receptionist, manager) could potentially appear in many rows, as a large company could have a lot of receptionists. We deal with vertical redundancy the same way we deal with horizontal redundancy, by adding tables to our schema:

Our new table will allow us to change the title of an administrative level (say from Manager to Supervisor) without having to make the change for every user that is a manager, as would be the case if you had the word manager in every row that it applied to. This improves ease of maintenance. In addition we may find that we eventually need to add more data regarding the rights of the administrative level. With this approach we could easily add flags to the table representing different privileges the user possesses, then allow administrators to create new administrative levels as they see fit; granting various permissions as they go. Third Normal Form In Third Normal Form we are looking for data in our tables that is not fully dependent on the primary key, but dependent on another value in the table. Take an address for example: your City and State are not really dependent on you, but on your Postal or Zip code. As such we could create a separate table with Zip Code, City, and State and only store the zip code in the User table. While such an approach may ease maintenance it can also introduce complexity when trying to assemble an address. As such, data should be moved into third normal form when deemed necessary for maintenance of information. In our case there is not really any information in the schema that would be a candidate for further normalization into third normal form.

Choosing Column Types and Writing CREATE TABLE Statements A CREATE TABLE statement is a special query we pass to MySQL to instruct it to create a new table to store our data. The syntax for CREATE TABLE can be found in the MySQL reference manual at http://www.mysql.com/doc/en/CREATE_TABLE.html. While the syntax may look complicated, it is actually quite simple. To create tables in MySQL, we mainly need a table name, column names, and the type of data that each column will hold. let’s look at the User table first. The first component of our CREATE TABLE query is the CREATE TABLE statement itself, which indicates the name of our table:

CREATE TABLE User ( The opening round bracket indicates that the lines that follow define columns in our table. When defining a field we need a few pieces of information: first we need the name of the field, taken directly from the diagram above. Second we need the data type of the field. This is where we determine whether the field will store numeric information, alphanumeric information, date and time related information, or other special data types. The full list of data types is available in the MySQL reference manual at http://www.mysql.com/doc/en/Column_types.html, and I recommend referring to the VB/MySQL data type conversion table at http://www.vbmysql.com/articles/visual-basic_mysql/datatypes.html.When choosing data types it is important to keep a balance between row size and usability. Every field will occupy a certain amount of space in your row, and will have a certain capacity for holding data. We want to choose the smallest data type that can reasonably hold our information. let’s take User_ID as an example; our User_ID field will be holding an integer value, and in theory we need one for each employee in our organization. Our choices for data type include TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. The largest numbers that can be stored in these various data types are 127, 32767, 8388607, 2147483647, and 9223372036854775807 respectively. In this case we can look at TINYINT with its capacity of 127 and consider it to be too small for our purposes. SMALLINT, with its capacity of 32767 should be more than adequate for even the largest organizations. The reason for choosing the smallest data type possible for our data is that larger data types result in longer rows, and longer rows take more time to search through when performing queries. Therefore it is always in our best interests to keep our rows as small as possible. By choosing the 16 bit SMALLINT data type over, say, the 32 bit INT data type, we have saved almost 2 gigabytes of storage over a million rows. Thus another benefit of choosing smaller data types is better storage efficiency. You can always redefine a column to larger data type later on, even once the server is in production use. The remainder of our row description is devoted to extra information about our field. First of all, when dealing with numeric data types we have the option of specifying whether the field should be allowed to handle negative numbers. The actual range of numbers that can be stored in a SMALLINT field is -32768 to 32767. In our particular application it does not make sense to have a negative User_ID value. As such it would be beneficial to declare the field UNSIGNED. Not only does this prevent the insertion of negative numbers into our table, it also increases the maximum value we can hold in the field to 65535 (this is because in binary form the leftmost bit of a number is reserved to represent the sign of the number. MySQL will use the leftmost bit to increase the maximum size of the number when the column is defined UNSIGNED). NOT NULL, DEFAULT, AUTO_INCREMENT, and PRIMARY KEY Another piece of information we can designate for this field is whether the field can hold NULL values or not. As you recall, a field that is defined as required in our design is one that must have a value when the row is created or later updated, and NULL is a keyword that indicates the field has no value. As such, any field we earlier defined as required must now have the NOT NULL keyword attached to it. NOT NULL will prevent the insertion of NULLs and make the field mandatory. In combination with NOT NULL comes the DEFAULT keyword. Just because a field is defined NOT NULL does not mean that we have to specify a value when inserting records. Any NOT NULL field that is not specified when the row is inserted will receive the value pre-defined by the DEFAULT keyword. Numeric values will automatically get a default value of zero, while strings will default to an empty string (”). You can use the DEFAULT keyword to override this behavior.

There are two final keywords that are commonly used when creating tables, but which can only be defined once. These are the AUTO_INCREMENT and PRIMARY KEY keywords. AUTO_INCREMENT indicates that if the field is not specified when inserting rows, the database will automatically generate a unique incrementing value and place it in the field defined AUTO_INCREMENT. This is very useful when using integers to identify rows, as the programmer does not have to worry about creating a unique number for each row he creates. The PRIMARY KEY keyword is used to define the field as the primary key of the table. let’s look at the definition of our first column:

CREATE TABLE User ( User_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, CHAR vs. VARCHAR Now that our primary key is defined, let’s look at the name columns of our User table. Our name columns will be handled as strings, which are allowed to hold alphanumeric values. The two basic fields for handling strings are CHAR and VARCHAR, each of which is defined in terms of the longest string it can hold (defined in characters, between 1 and 255). The difference between CHAR and VARCHAR is how they deal with unused space. let’s say you consider the longest possible last name to be 40 characters long, and a user has a name of Winstead (8 characters). In a CHAR field, the field would still be 40 bytes long (1 character = 1 byte), and the last 32 characters would be blank spaces used to pad the string to the full 40 characters (the trailing spaces will be removed when you retrieve the value). This means that no matter how long the actual string is, it will still take a full 40 characters worth of space in the table. VARCHAR on the other hand will store only the 8 characters and will only occupy 8 bytes of space in the table. Your first instinct may be to choose VARCHAR and benefit from the decreased storage space required in the table, in an effort to increase the speed of searches as I described above, but VARCHAR is an exception to this rule. When you use VARCHAR in a table (as well as certain columns intended for large text and binary values), the width of each row becomes inconsistent. One row may be 40 bytes long, another only 8. MySQL can handle this by recording the length of the row in its file system, but the server must then read the length of each row before searching it and moving on to the next row, where it must check the length again, ad nauseam. By defining text fields as CHAR we will occupy more space on the hard-drive, but there is a speed increase involved as the server will know that each and every row is exactly a given size (such as 120 bytes), allowing it to search through rows without checking their length first. As such I recommend defining all your text fields as CHAR to improve query performance, unless storage space is a strong consideration (and with the prices of today’s hard-drives this is not really that great of a concern, especially with our application). let’s look at the name columns:

CREATE TABLE User ( User_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, LastName CHAR(40) NOT NULL, FirstName CHAR(40) NOT NULL,

As you can see, each field will be a CHAR field that will hold up to 40 characters. The fields are defined NOT NULL to make them mandatory, with the standard default value of a blank string being used since we have not defined it otherwise. Storing Phone Numbers There is no one sure way to store a phone number, as the data type used can be dependant on what you plan to do with your phone numbers. If you wish to do searches based on area code you may want to store the different parts of a phone number separately, with a field for area code, a field for the first three digits, a field for the last four digits, and perhaps even an optional field for the extension. This will make it easier to search and sort phone numbers. In our case we are only storing the number for the reference of a user’s fellow staff members, so we will be storing it in a simple CHAR column:

CREATE TABLE User ( User_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, LastName CHAR(40) NOT NULL, FirstName CHAR(40) NOT NULL, Phone CHAR(10) NOT NULL, Our Username and Password fields will also be declared as CHAR columns:

CREATE TABLE User ( User_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, LastName CHAR(40) NOT NULL, FirstName CHAR(40) NOT NULL, Phone CHAR(10) NOT NULL, Username CHAR(16) NOT NULL, Password CHAR(40) BINARY NOT NULL, Handling user logins will be discussed in a future article. The BINARY keyword used with the Password field indicates that we want all comparisons with the Password field to be done in a case-sensitive manner (So that PaSSwoRD and password are treated as different strings when validating users). Strings in MySQL are treated in a case-insensitive manner unless the BINARY keyword is used. ENUM Columns For our Administrator field we will use an ENUM column. An ENUM column can be assigned only one value, chosen from a list of pre-defined values that we declare when creating the field. ENUM works well for representing value sets that will not increase in size as the database matures (for that we use a separate table as with our AdminLevels table). In this case we wish to represent a scenario that can only have two values: true or false (either you are an administrator or you are not). Assigning an ENUM involves created a commaseparated set of strings listing all possible values (you can define up to 65535 different potential values to an ENUM column, but if you need more than five or six you are probably better off creating a table to store the values):

CREATE TABLE User ( User_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, LastName CHAR(40) NOT NULL, FirstName CHAR(40) NOT NULL, Phone CHAR(10) NOT NULL, Username CHAR(16) NOT NULL, Password CHAR(20) BINARY NOT NULL, Administrator ENUM('TRUE', 'FALSE') NOT NULL, Date Columns The last two columns of our table are the Created and Deleted fields. The Deleted field is once again a true/false column and will be treated the same as the Administrator field. The Created field represents the date on which the record was created. MySQL provides four columns for handling date information, each of which is fairly self-explanatory: DATE, TIME, DATETIME, and YEAR (There is a fifth TIMESTAMP column that we will discuss later). When choosing a data type to use consider what kind of information is needed. By far the most commonly used formats are DATE and DATETIME, as there is not as much use for just the time of day or year when recording information (consider that most people do not search for time information regardless of day). For our purposes the DATE column is adequate as it is not essential to know the time of day that the row was created:

CREATE TABLE User ( User_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, LastName CHAR(40) NOT NULL, FirstName CHAR(40) NOT NULL, Phone CHAR(10) NOT NULL, Username CHAR(16) NOT NULL, Password CHAR(20) BINARY NOT NULL, Administrator ENUM('TRUE', 'FALSE') NOT NULL, Deleted ENUM('TRUE', 'FALSE') NOT NULL, Created DATETIME NOT NULL )TYPE = MyISAM; The closing round bracket indicates the end of the column definitions. The TYPE keyword is optional because the default table type is MyISAM, but we can use the keyword later to indicate that we wish to use one of the other MySQL table handlers, such as InnoDB, HEAP, or BDB to name a few. More information on table handlers can be found at http://www.mysql.com/doc/en/Table_types.html, but for the purposes of this article we will stick to the default MyISAM table handler. The semicolon character (;) tells the MySQL server that we have finished our query and that it can go ahead and execute it. Execution of queries will be covered in a future tutorial, for now we will simply be preparing the queries for future use. I recommend you create your queries in a text editor such as notepad and save them using a filename such as table_creation_statements.sql. The .sql extension will indicate that the file holds SQL queries.

TIMESTAMP Fields One special data type available with many databases is TIMESTAMP. A timestamp column is very similar to the DATETIME field but has certain special properties. If your table has a timestamp column then MySQL will automatically populate the field with the current time when a row is inserted without specifying a value for the timestamp column (or if the timestamp column is specified as being NULL). The timestamp column will also be updated to the current time whenever a row is updated with new information (once again as long as the column is either unspecified or set to NULL). One useful property of timestamp columns is their behavior when you have more than one timestamp column in a table. When you have multiple timestamp columns in a table, all timestamp fields will be assigned the current time when a new row is inserted, but only the leftmost timestamp column will be updated during subsequent updates of the table. This means that you can place two timestamp fields on a table, with the right one recording the creation date of the row and the left one tracking the last update on the row. We will use the TIMESTAMP data type to record the creation time of events in our Event table, using the name Timestamp. Typically you cannot use a data type as a column name as it is a reserved word, but the use of Timestamp as a column name is an exception in MySQL:

CREATE TABLE Event ( Event_ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, User_ID SMALLINT UNSIGNED NOT NULL, Message_ID MEDIUMINT UNSIGNED NOT NULL, Timestamp TIMESTAMP, Status ENUM('In','Out') NOT NULL, Creator SMALLINT UNSIGNED )TYPE = MyISAM; A couple of things to note is that the In/Out column was renamed Status to better reflect its meaning (as In/Out tells you what the column can hold but not what it means). In addition, the Creator column was changed from a required field to a non-required one. The logic behind this is that a NULL creator can signify that the user the event describes is the creator of the event, which could help later on when determining how often events are created by users other than the one the event describes. It is important to note that design can be an iterative process, and that changes like this are regularly made. The important thing is to remember to update your documentation when making changes:

Composite Primary Keys In the User_Group table we have a composite primary key. We can’t define both columns with the PRIMARY KEY keyword and must instead use a different syntax:

CREATE TABLE User_Group ( Group_Name CHAR(20) NOT NULL, User_ID SMALLINT UNSIGNED NOT NULL, Level_ID TINYINT UNSIGNED, PRIMARY KEY (User_ID, Group_Name) )TYPE = MyISAM; When creating a composite primary key we define the primary key after we finish defining our columns. Defining Our Remaining Tables The remaining tables in our application will follow the same principles that have been described so far:

CREATE TABLE Status ( Message_ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, User_ID SMALLINT UNSIGNED, Message CHAR(255) NOT NULL, Deleted ENUM('True','False')

)TYPE = MyISAM; CREATE TABLE Groups ( Group_Name CHAR(20) NOT NULL PRIMARY KEY, Created DATETIME NOT NULL, Scope ENUM('Public','Private'), Deleted ENUM('True','False') )TYPE = MyISAM; CREATE TABLE AdminLevels ( Level_ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Title CHAR(20) NOT NULL )TYPE = MyISAM; The full set of table definitions is available at http://www.vbmysql.com/wp-content/uploads/2006/10/vbmysql-tutorial.zip. It should be noted that once again the issue of reserved words has come up, as the Group table used a reserved word. It is possible to use a reserved word as a column or table name by wrapping it in back ticks (`Group`) every time you use it but I prefer to avoid the hassle. Instead we’ll rename the table to Groups (generally you should avoid using plural forms in your table names):

It should also be noted that just as you should use the same name for a foreign key as you use for the primary key (use User_ID in all table referring to it), you should also make sure to use the same datatype as well. So make certain that all User_ID fields are SMALLINT UNSIGNED, and that all Group_Name fields are CHAR(20). Ensure that all CHAR and VARCHAR columns are the same width (in this case 20 characters) as you do not want one table to be able to store a longer group name than another.

Conclusion We have created a list of features and requirements for our application that will not only help us establish when our project is complete, but which also helps us define our application’s entities. Our entity design will be used to define our application objects, and is also used to design our database tables. Now that our tables have been defined and designed, we can use the CREATE TABLE statements to create the tables in our MySQL server. In my next article I will cover installation of the MySQL server under Windows and then cover the basics of creating a database and its component tables.

The VB.NET-MySQL Tutorial - Part 2 Introduction In my last article, I wrote about how to design a MySQL database for use as a data store for an application to be written in Visual Basic.NET (VB.NET). The completed table definitions can be found at http://www.vbmysql.com/wp-content/uploads/2006/10/vb-mysql-tutorial.zip.In this tutorial, I am going to show you how to install MySQL on Windows, install the MySQL Query Browser, and how to load the table definitions into MySQL by using the Script Editor feature of the MySQL Query Browser. I will also show you how to enter some sample data into the database using the MySQL Query Browser. This tutorial will assume that you are using Microsoft Windows® as the host operating system for both the MySQL server and the MySQL Query Browser, and that your copy of Windows is a recent Windows NT based version like Windows 2000 or Windows XP. This tutorial will also assume that you have no previous version of MySQL installed. Upon completion of this tutorial, you should have a working MySQL installation that contains your database and some sample data, allowing you to begin developing with VB.NET in the next tutorial. At the end of this tutorial will be a link to a SQL script containing all the SQL statements used will be available. The script can be used to generate an exact clone of the database we will be building in this article.

Choosing a MySQL Version There are currently two versions of MySQL available for download from dev.mysql.com: MySQL 5.0 and MySQL 5.1. At the time of writing, MySQL 5.0 is the production versopm. This means that this version has been through extensive beta processes and has been cleared of any known bugs, with no new bugs reported within an arbitrary period. You can find out the features included in the various versions of MySQL at http://dev.mysql.com/doc/mysql/en/Roadmap.html. It is usually advisable to use the latest version of MySQL that has been declared production ready. While MySQL 5.1 will offer a greater feature set over MySQL 5.0, the fact that it is not yet released as GA means that we would have difficulty determining whether a given error is caused by our code, or a bug in the alpha version of MySQL.

Downloading and Installing MySQL MySQL 5.0 can be downloaded from http://dev.mysql.com/downloads/mysql/5.0.html. There are three versions of the Windows download available: Windows Essentials, Windows, and Without installer. In our case the Windows Essentials package will do just fine. Download the installer to your local hard-drive and when the download is complete, double-click on the install file to begin the installation (the install file will have a name like mysql-essential-5.0.26-win32.msi). The MySQL Installation Wizard Once you double-click on the install file, the Installation Wizard will be displayed. There will be three different install types made available: Typical, Complete, and Custom. In our case, the Typical installation will suit our purposes. Choose the Typical install option and click the Next button. On the confirmation screen, click the Install button to begin the installation.

MySQL will be installed to the C:\Program Files\MySQL\MySQL Server 5.0 folder, and you will be prompted to register with the MySQL web site. Registration is optional but is useful if you want to use the forums at forums.mysql.com or report bugs at bugs.mysql.com. The final screen of the MySQL Installation Wizard prompts you to start the MySQL Configuration Wizard. For more information for using the MySQL Installation Wizard, see http://dev.mysql.com/doc/mysql/en/Windows_install_wizard.html. The MySQL Configuration Wizard The MySQL Configuration Wizard will create a my.ini configuration file for you and will install MySQL as a service on your system. The full documentation for the MySQL Configuration Wizard is available at http://dev.mysql.com/doc/mysql/en/Windows_config_wizard.html, and is a good reference for advanced configurations. In this article I will cover the basics of using the MySQL Configuration Wizard, as applies to desktop developer use. The first dialog of the MySQL Configuration Wizard will prompt you to choose between a Standard Configuration and a Detailed Configuration. The Standard Configuration is fine for a desktop developer who will be the only user connecting to MySQL, where MySQL has to share resources with the rest of your desktop applications. Choose the Standard Configuration and click the Next button. The next dialog displayed allows you to configure a Windows service for MySQL. Configuring MySQL as a Windows service will allow MySQL to be started when your computer is booted, and is recommended to avoid the hassle of having to manually start MySQL every time you need to use it. I generally change the service name to the MySQL41 option so that it will play nice with other copies of MySQL on my desktop, but if you intend to stick to a single MySQL installation (which is most likely the case), you can use the default MySQL service name. Click Next to advance to the next dialog. After configuring the MySQL service you will need to set the root password for your server. Whenever you have a production MySQL server you must set the root password to ensure security. If you do not set the root password, anyone who can access your MySQL server can cause all sorts of damage to your databases. If you are the only user, and the port to the server is blocked by a firewall, you may consider not setting a root password to simplify logging into MySQL, but I do not recommend it. I recommend setting a root password, and also checking the Root may only connect from localhost option if you are installing MySQL on your desktop. Do not check the Create An Anonymous Account option. Click the Next button to advance to the confirmation dialog. On the confirmation dialog, click the Execute button to begin the configuration process. The MySQL Configuration Wizard will create a configuration file, start the MySQL server, and set the root password you specified. Once the configuration process is complete, click the Finish button to close the wizard.

Downloading and Installing the MySQL Query Browser The MySQL Query Browser is a second-generation GUI tool from MySQL AB. The MySQL Query Browser is a great tool that allows you to create and edit tables, and then easily browse the contents of the table, making changes as you go. The MySQL Query Browser is currently in beta and is already a very useful tool. The

documentation for the MySQL Query Browser can be found at http://dev.mysql.com/doc/querybrowser/en/index.html. Installing the MySQL Query Browser The MySQL Query Browser can be downloaded from http://dev.mysql.com/downloads/querybrowser/index.html. Avoid the Without Installer version. Save the install file to your local hard-drive. The install file should have a name similar to mysql-query-browser-1.1.1-gamma-win.msi. Once you have downloaded the install file, double-click it to install the MySQL Query Browser. The installer is a standard install wizard and should require no explanation. The MySQL Query Browser will be installed to C:\Program Files\MySQL\MySQL Query Browser 1.0 unless you specify a different path.

Creating The Database Now that we have installed both the MySQL database server and the MySQL Query Browser, we can begin the process of creating our database. Simply put, a database is a collection of data stored in tables made of columns and rows. A database serves as a container for the tables we created in the first tutorial. Starting the MySQL Query Browser Once the MySQL Query Browser is installed, you can start the Query Browser by clicking the Start > Programs > MySQL > MySQL Query Browser. You will then be presented with the connection dialog:

Fill in the fields with the information that is appropriate for your MySQL installation. If you installed the MySQL server and the MySQL Query Browser on the same machine, use 127.0.0.1 as the hostname. The Schema field is the default database to use for queries. Since we have not created the database for our application yet, this is set to the test database that is installed by default on all MySQL databases. Click the OK button to start the MySQL Query Browser. Creating A Database

Once the MySQL Query Browser starts successfully, you should see a window like the following:

On the right side you can see the database (Schemata) browser. This provides a list of all the databases currently residing on your MySQL server. The mysql database manages all the login information for your server and manages the permissions that users have when accessing MySQL. The test database is provided as a place to test queries and table creation statements and is accessible to all users. The test database is highlighted in bold text to indicate that the test database is currently the default database; any queries you enter will be executed against the test database by default. To create the database, right-click within the database list and choose the Create New Schema option. You will be prompted for a name for your database. At this point our application does not have a name, so we can just choose something descriptive. Since our application will essentially track who is in and out of the office, I am going to name the database in_out. The name has no capital letters to avoid problems with casesensitivity differences between Windows and Linux versions of MySQL (you will notice that I updated the SQL script from the last tutorial to match this naming convention). You can separate words with a hyphen, underscore, or nothing at all. Avoid using spaces in your database name, and remember that we want to avoid using reserved words. In my case I will be separating words with an underscore. Once you have created the database, right-click on the database and choose the Make Default Schema option. The in-out database is now the default, and our upcoming table creation statements will be applied to this database.

Creating the Tables Now that we have created the database and set it as the default, we can load the SQL script with our table creation statements. Download the script from http://www.vbmysql.com/wp-content/uploads/2006/10/vbmysql-tutorial.zip to your local hard-drive. Choose the Open Script … option from the File menu of the MySQL Query Browser.

Once the script is loaded, click the Execute button to create the tables. Once the execution completes (it should be instant), you can click on the black arrow to the left of the database name to show the tables. Click on the black arrows next to the table names to see the list of columns for the table:

Assuming you encountered no errors, you database should now be created.

Creating a User Now that our tables are in place, we will want to create a user. To create a row in a table with the MySQL Query Browser, first double-click the table (in our case the user table). This will create a SELECT * FROM user query in the top query area. Click the Execute button to run the query, and a new result tab should be created with an empty set of rows:

Click the Edit button at the bottom of the window to enable editing, then double-click in the lastname field to begin editing. You can use the tab key to move to the next field in the row as you enter your data. In my case I am entering the following: lastname: Hillyer firstname: Mike phone: 4033806535 NO HYPHENS username: mike password: 12345 administrator: TRUE deleted: FALSE created: 2004-11-27 11:41:00 The most important thing is to remember what you table expects in terms of data formatting. Your first and last name cannot be more than 40 characters. Your phone number cannot be more than 10 characters. Your username cannot me more than 16 characters and your password cannot be more than 20 characters. The values for the administrator and deleted fields must be either TRUE or FALSE, and the created field must be in the format of YYYY-MM-DD HH:MM:SS.

Once you have entered your data, click the Apply Changes button to create the new row. You can also click the Edit button to turn off row editing.

Conclusion Assuming you encountered no errors, you should now have a working copy of MySQL installed, along with the MySQL Query Browser. You should have been able to create the in_out database and populate it with the tables we designed in the previous tutorial. Once the tables were created, you should have been able to create a new row in the user table. You can also cheat and download the script at http://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-2.zip and execute it to create a database that is an exact match of the one I created. In my next tutorial I will show you how to download and install Visual Basic.NET Express Edition and the MySQL Connector/NET database driver. I’ll show you how to combine these to create a login system that will allow users to authenticate against your MySQL database.

The VB.NET-MySQL Tutorial - Part 3 Introduction This article is part three of a series of articles made to document the creation of Windows® applications using Visual Basic.NET and MySQL. In the previous articles, I have described how to design an application and database, install MySQL and the MySQL Query Browser, and how to populate our database using the Script Editor feature of the MySQL Query Browser. In this article, I will be describing the how to install Microsoft Visual Basic 2005 Express Edition, the MySQL Administrator, and MySQL Connector/NET. We will use these tools to create a login form for our tracking application.

Designing a Login System There are multiple options available when working on a login system, but typically it comes down to one of two options: 1. Build your own login system. Store usernames and passwords in a MySQL table, and use a single user account for all instances of the application that connect to MySQL. Each instance of the application will connect to one server account, then validate the user against the tables you created. 2. Use MySQL’s built-in security tables. Create MySQL users for each account, and let MySQL handle authentication and privilege management. These approaches both have their respective benefits and drawbacks. When you build your own login system, you potentially have greater control over users, groups, and privileges. You have simpler permission management because only one native MySQL user account is needed. On the other hand, there is more potential for security problems because something you create yourself does not have the review process seen in the MySQL server. In addition, you have a security weakness in the fact that every client installation has a copy of the central user account for connecting to MySQL, and that account has to have the maximum permissions needed for an administrative user. These concerns are not so great when you are developing a web application, because it is easier to manage the one web instance than a collection of desktop applications. When you use the security built-into MySQL, you inherit a robust privilege management system that has been thoroughly reviewed and tested. You can limit privileges on a fine-grained level, and even assign different permissions based on which host the user connects from. The tradeoff is one of complexity: making the most of the built-in MySQL authentication is more difficult than building one of your own, and you must add a user to the MySQL server every time you want to add a new user to your application. Because this is not a web-based application, we will be building our application using the built-in MySQL privilege system. In order to create a native MySQL user we will first install MySQL Administrator; a GUI application for server management.

Using MySQL Administrator

The MySQL Administrator is a GUI tool provided by MySQL AB for managing your MySQL server. The MySQL Administrator can be used to manage users, change server configuration, manage server databases, and monitor server status. Downloading and Installing MySQL Administrator The MySQL Administrator can be downloaded from http://dev.mysql.com/downloads/administrator/1.0.html. In our case we want the Windows version that includes an installer, avoid the ‘Without Installer’ version. Save the installer file to your hard-drive and double click the installer icon to begin the installation. The installer is a standard Windows installer and should not require any special steps. Connecting to the MySQL Server After the install is complete, there should be a MySQL Administrator icon on your desktop. Double-click the MySQL Administrator icon to display the login form.You can also start the MySQL Administrator by clicking Start > Programs > MySQL > MySQL Administrator.

The login form for the MySQL Administrator is almost identical to the login form for the MySQL Query Browser, with the exception being that MySQL Administrator does not require you to specify a default schema. Enter your root login information and click the OK button to start the MySQL Administrator. Adding A User Click the User Administration option from the left menu panel to bring up the user administration screen.

Click the New User button to create your new user account. For the MySQL User setting use the username value you set in your user row in the last tutorial. Set a password and set any Additional Information you want to specify (all information in the Additional Information section is optional). Once you have set a username and password, click the Schema Privileges tab. Click the in_out database and then the << button to assign all available privileges for the database to your user (we will refine the privilege list in the future). Click the Apply Changes button to create the new user. You can now exit the MySQL Administrator.

Connector/NET In early 2004 MySQL AB hired Reggie Burnett of ByteFX and acquired his ByteFX .NET data provider for MySQL. The provider was renamed Connector/NET and not only is it provided free under the terms of the GNU Public License, but it is one of the most feature-rich and best performing .NET providers for MySQL that is currently available. Connector/NET is written in C# and is completely managed code, allowing it to be ported to any platform that supports .NET, including Mono. One advantage Connector/NET provides over other solutions is its use of the native MySQL protocol: many other solutions wrap the MySQL C client library and suffer a performance loss as a result.

Downloading and Installing Connector/NET MySQL Connector/NET is available for download at http://dev.mysql.com/downloads/connector/net/. Download the version that includes an installer to your local hard-drive and extract the Zip file. Double-click the installer file to begin the installation process. Perform a complete install to the default directory.

Visual Basic.NET Visual Basic.NET is the new version of Visual Basic. While it shares the Visual Basic name, there are significant differences between Visual Basic 6 and Visual Basic.NET. VB.NET is now entering into its third version. The first version was Visual Basic.NET, the second was Visual Basic.NET 2003, and the new version is Visual Basic.NET 2005. Visual Basic.NET 2005 introduces a new Express version that we will use in this tutorial. The Express version of Visual Basic.NET is essentially a stripped-down version that still retains all the functionality needed to produce basic applications (no pun intended). Downloading and Installing Visual Basic.NET 2005 Express Edition VB.NET 2005 Express Edition is available for download at http://msdn.microsoft.com/vstudio/express/vb/. Look for a link to a Download Now link. Once you have downloaded the installer, double-click the installer to being the installation process. The default installation options should be sufficient. Starting Visual Basic.NET Once you have installed VB.NET 2005, look for a link in the Programs section of your Start menu named Visual Basic 2005 Express Edition Beta. Start VB.NET and click Ctrl + N to start a new project.

Enter a name for the project and choose the Windows Application template. Click the OK button to create the project. The Main VB.NET Window Once your project is created you should see a window similar to this one:

On the left is the toolbox. You can drag items from the toolbox to add them to your application. In the center is the workspace, showing your first form in design mode. On the right is the Solution Explorer, which shows all the files involved in our project. Naming The Default Form First right-click on the Form1.vb object in the Solution Explorer and choose the Properties option. This will display a properties dialog where we can rename the file.

The properties tool is used to set various properties for the objects we will deal with as we work on our project. In this case we want to change the File Name property to more accurately reflect the purpose of the form. I will be prefixing the names of my forms with a frm prefix. Do not change the extension of the form file. Once you change the file name, click the form in the workspace. The properties tool will change to reflect the properties of the form itself. Scroll down until you find the Text property and set it to something appropriate. In my case I set this to In-Out - Login. The Text property determined what appears as the title of the form, and you can see this reflected in the workspace. Adding a Reference Before we can use Connector/NET with Visual Basic, we need to add a reference to our project. By adding a reference, we are telling VB.NET where to find MySQL Connector/NET in order to access the objects and methods of Connector/NET. To add a reference, choose the Add Reference… option of the Project menu. Choose the Browse tab and browse to the Connector/NET installation, typically located at C:\Program Files\MySQL\MySQL Connector Net n.n.n\bin\.NET N.N (The path may vary depending on the version number of Connector/NET and the .NET Framework). Choose the MySql.Data.dll file and Connector/NET will be added to your project. You will also need to add a reference to System.Data.dll. Saving the Project Now that we have configured a few settings in our project, we should save the project before continuing. Choose the Save All option from the File menu. Because this is the first time we are saving the project, we are presented with the following dialog:

These settings should generally be acceptable, with the project being saved to a new folder within the My Documents/Visual Studio/Projects folder (In my case I use a custom path to My Documents). Click the Save button to save your project.

Designing The Login Form To design the login form, drag objects from the toolbox onto the form. In our first version we will need to prompt users for a server address, username, and password. Click on the TextBox object from the toolbox and drag it into the form. Once placed on the form you can widen it, move it, and generally fine-tune its size and positioning. Once you are happy with the placement of the textbox, go to the properties tool and find the Name property. Change the name to better reflect the use of the textbox (in my case I will be naming it txtServer). Place two additinal textboxes and name them txtUsername and txtPassword.

While it may be obvious to you and I what the purpose of these textboxes is, the end user will probably need some help. We will use Label objects as cues to the user to indicate what each TextBox is used for. Drag the label object onto the form and place it in-line with the first textbox. Once the label is placed, find the Text property in the properties tool and set the text to something like Server:. Repeat this process two additional times for the remaining Textbox objects. Because we will not be using the labels in actual application code there is not a real need to change the default names of the labels. The final element to add to our form will be a pair of buttons; one to start the login process and one to cancel. Drag the buttons onto the form, and set the Name property to be cmdLogin and cmdCancel. Set the Text property to be Login and Cancel, respectively. Once the process is complete your form should look something like this:

Creating an Event I’m going to start by creating an event for the Cancel button. An event is something that triggers execution of code within VB.NET. In this case, the event in question will be the clicking of the Cancel button. The simplest way to create a button click event is to double click the button on the form. When you double-click the cancel button, the code view of the form will be displayed and you will see the following code appear: Public Class frmLogin Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click End Sub End Class

The Public Class line indicates that this class describes our form, and the Private Sub line shows that this is a subfunction that handles the Click event of the cmdCancel object. The two End lines show where the code for each of these sections ends. We will add a single line to the subfunction to close the application when this button is clicked:

Application.Exit() This line instructions the application to close, and will be executed when we click the Cancel button. Your code should now look like this:

Public Class frmLogin Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click Application.Exit() End Sub End Class

Starting the Application Once you have entered the code, save the project and press the F5 key to test your application. You can also choose the Start option from the Debug menu or click the green icon on the toolbar. Your form should be displayed and if you click on the Cancel button, the form should disappear as the application is closed. If you click on the Login button nothing would happen as we have not created any code for the event of clicking on the Login button.

Importing the Connector/NET Namespace Objects in VB.NET are organized into namespaces. Namespaces are logical grouping of objects used to help organize the various objects available in VB.NET. To use a Connector/NET Connection object, you need to define it as MySql.Data.MySqlClient.MySqlConnection (more on this object later). This of course is a lot to type on a regular basis, and we can use the Imports statement to shorten this. By adding Imports MySql.Data.MySqlClient to the start of the source file, we can just refer to the Connector/NET Connection object as MySqlConnection.

Adding a MySqlConnection Object MySQL Connector/NET is essentially a collection of objects used to access a MySQL database. The first object we will use is the MySqlConnection object. The connection object serves as a broker between the other objects contained within Connector/NET and the MySQL server. The connection object handles the login process and is the object we will use to verify that a user’s login information is correct. There are two steps to adding an object. First we declare the object, then we instanciate it. When declaring an object we assign a name that we will use to refer to it, and also indicate the scope of the object, or in other words, what functions and procedures can access the object. In our case we will want to ensure that any function or procedure within the form will have access to the connection object, so we will declare the connection object first thing within the class: Imports MySql.Data.MySqlClient Public Class frmLogin Dim conn As MySqlConnection Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click Application.Exit() End Sub End Class

The Dim keyword is used when declaring objects and variables. I’m using conn as the name of my connection object. The As keyword is used to indicate what we are declaring (an object, a variable, etc). Finally, MySqlConnection is the object we are declaring.

Instanciating the MySqlConnection Object Now that we have declared the connection object, we will instanciate it. Until we instanciate an object it is not actually available for use. We will instanciate the object within the subfunction that handles the click event for the Login button. Double-click the Login button in the design view to create the subfunction. To instanciate an object, we use the New keyword: Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click conn = New MySqlConnection() End Sub

Building the Connection String The MySqlConnection object uses a connection string to know which server to connect to, which database to access, and what username and password to use to authenticate. The various properties are separated by semicolons. Here is a sample connection string:

server=localhost; user id=mike; password=12345; database=in_out Of course, we need the connection string to reflect the information our user enters into the form. To do this we shall use the & character to combine multiple strings together, and the .Text value of the TextBox objects. Additionally I will use the _ character to split our code into multiple lines: Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click conn = New MySqlConnection() conn.ConnectionString = "server=" & txtServer.Text & ";" _ & "user id=" & txtUsername.Text & ";" _ & "password=" & txtPassword.Text & ";" _ & "database=in_out" End Sub

Opening the Connection The last thing we need to do is instruct the connection object to open the connection to the MySQL server with the .Open() method of the connection object: Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click conn = New MySqlConnection() conn.ConnectionString = "server=" & txtServer.Text & ";" _ & "user id=" & txtUsername.Text & ";" _ & "password=" & txtPassword.Text & ";" _ & "database=in_out" conn.Open() End Sub

Using a MessageBox Once we have successfully opened the connection, we will want to let the user know that their username and password were correct. We can do this with the MessageBox object. In its simplest form, a MessageBox will display a message to the user, with an OK button. Here’s a simple line of code to display a MessageBox: MessageBox.Show("Connection Opened Successfully!")

Closing the Connection When we are finished with our connection object we need to close it. By closing the connection we release the resources needed to keep the connection active. It is a good practice to close connections as soon as you are finished with them. The connection is closed with the .Close() method. conn.Close()

Disposing of the MySqlConnection Object Once we are completely finished with an object, it is a good practice to dispose of it, thus minimizing resource usage in our application. When we dispose of an object, the resources it occupied are freed and the object no longer exists. We dispose of an object by calling its .Dispose() method:

conn.Dispose() Catching Errors Our code is currently only appropriate for an ideal situation. If we cannot connect to the server or if we provide the wrong username or password the connection object will return an error, also known as an exception. To handle errors, VB.NET has a special TRY … CATCH … FINALLY syntax. We place the code with the potential error after the TRY keyword and before the CATCH keyword. The CATCH keyword is used to indicate what kind of error we anticipate we might encounter (in this case the error returned will be a Connector/NET MySqlException object). Any code present after the FINALLY keyword will be executed whether there is an error or not. If an exception is encountered, the remaining code in the TRY section will not be executed. Here’s the final code for the procedure that handles the connection: Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click conn = New MySqlConnection() conn.ConnectionString = "server=" & txtServer.Text & ";" _ & "user id=" & txtUsername.Text & ";" _ & "password=" & txtPassword.Text & ";" _ & "database=in_out" Try conn.Open() MessageBox.Show("Connection Opened Successfully") conn.Close() Catch myerror As MySqlException MessageBox.Show("Error Connecting to Database: " & myerror.Message) Finally conn.Dispose()

End Try End Sub

This brings all of our connection code together and allows it to handle errors without crashing. We instanciate the connection object and assign it a connection string. Within an error handling TRY … CATCH block we attempt to open the connection to the server and, if successful, we will show a MessageBox to the user indicating our success and then close the connection. If an error occurs while connecting, the code in the CATCH block will be executed. In this case we will show the user a MessageBox with the .Message property of the MySqlException object, which contains the humanreadable error message associated with the error. In the FINALLY block we will dispose of the connection object. We do this in the FINALLY block because whether or not the connection succeeds we will want to dispose of the object.

Testing the Project Once your code is in place, save the project and press the F5 key to begin the application in debug mode. Try using the proper server address, username, and password, then try using the wrong server address, then try the wrong password. You will notice different error messages for using the wrong address and for using the wrong password. The error message for using the wrong username is the same as the error message for using the wrong password, as a seperate error message would provide a security threat in that a potential attacker would know whether they had a correct username or not.

Improvements There are a few improvements that will need to be made to our login form. First of all, our user should not be expected to enter the server address every time they want to use our application, and in the future we will cover how to store the server address in a configuration file. The second improvement will be with regards to error handling. Showing the MySQL error message is fine during development, but the production version of our application should not show the database error messages directly. We can use the .Number property of the MySqlException object to determine what kind of error we are dealing with, and then create a custom error message. Finally, we will of course need to eventually have a successful connection lead to a new form in the application. If our application consisted of a login form and nothing else, it would not be very popular.

Conclusion In this tutorial we have covered installing MySQL Administrator and created a new MySQL user account. We then installed VB.NET and MySQL Connector/NET. Finally we used VB.NET to create a login form for our application that will connect us to a MySQL server (assuming we provide the correct server address and username/password). The form is configured to handle errors during the connection by using the TRY … CATCH … FINALLY syntax. The project file created in this tutorial is available at http://www.vbmysql.com/wp-content/uploads/vb-mysqltutorial-3.zip. The project files are for Visual Basic 2005 Express Edition.

In the next tutorial we will create our first event and will create a form to display and update the current status of our users.

The VB.NET-MySQL Tutorial - Part 4 Introduction This article is part four of a series of articles on how to create a simple Windows application using Visual Basic.NET and MySQL. In previous articles we have designed our database, installed the software we need to create our application, and designed a simple login form. The previous three articles are mandatory reading before starting this article. In this article I will demonstrate how to design queries, build forms, bind data to controls, and perform queries using the MySqlCommand, MySqlDataAdapter, and DataTable classes. Later I will demonstrate how to perform parameterized queries and update data in the MySQL tables.

Adding Users, Status Messages, and Events to the Database Before we query the database to get the current status of our users, we should add extra users to the system so we have some context for our query. In addition, we will want to add some pre-defined status messages and a few events to the system. To add the data, I will be using the MySQL Query Browser. Once the Query Browser is open, double-click on the user table, then click the Execute button. Click the Edit button at the bottom of the result set area, and add two new users to the database (see Article Two for more information). The user details are not vital, just set the administrator and deleted fields to False. Once you have added two users, double-click on the status table and click Execute. We need to add some predefined status messages that our users can choose from. Click the Edit button and add three messages. Leave the user_id NULL, and set the deleted field to False. In my case I will be using the following three status messages: • • •

Gone to Lunch Gone for the Day In Meeting

Once we have created users and status messages, we can add some events to the database. Double-click on the event table and click the Execute button. The event table is the core of the tracking application and brings together the user and status tables to allow us to track the status of all our users. When a user signs in or out, the event is tracked with the appropriate user id, status message, and time. In addition, we track who created the event, in case a secretary creates an event on behalf of a user. Let’s create a few events that can later be used when querying the database:

These rows establish a chain of events of all three users going to lunch, returning to start a meeting, and then users 2 and 3 going home for the day.

Editing a Table Definition While we are looking at our data, you may wonder if a user should be able to sign in or out with no message at all. This is actually a desirable feature, but our current table definition does not allow it because message_id is defined as NOT NULL. To edit a table definition, right click on the table in the database browser and choose the Edit Table option.

As you can see, there is a check mark in the NOT NULL column for message_id. Click the check mark to remove the NOT NULL constraint and click the Apply Changes button to modify the table definition.

With the table definition modified, add a final row to the event table for user 1 with a NULL message_id, a timestamp of 2005-01-17 16:00:00, and a status of In. This shows that the meeting ended, but that user 1 has not signed out and has no status to report.

Building a Query Now that our data is loaded into the database, we need to develop queries that can be used to retrieve the data in a format that is meaningful to our users. Lets begin with a simple SELECT * query of the event table, generated by double-clicking on the table in the MySQL Query Browser:

This does not present very useful information to an end user, so lets start by removing columns that our users do not benefit from: SELECT e.user_id, e.message_id, e.timestamp, e.status FROM event e

This removed the event_id and creator columns, which are not of interest to an end user. Note that the event table is represented by an alias of e. This alias is then used as a shorthand when listing the columns to be queried. For a video demonstration on how to select individual columns, see http://www.mysql.com/products/query-browser/tutorials/build_queries.html (Macromedia Flash required). Joining Tables With an Inner Join While the previous query is a bit more readable because it eliminates unnecessary rows, most end users will not like to identify their co-workers by number. The names of our users are stored in the user table, and can be used in our query by joining the event and user tables together. To join two tables together, we have to indicate to MySQL which columns can be used to relate the tables together. In this case, the user_id column of the user table is related to the user_id column of the event table. In addition to establishing a relationship we need to select columns from the user table: SELECT u.lastname, u.firstname, e.message_id, e.timestamp, e.status FROM event e, user u WHERE e.user_id = u.user_id

In the SELECT clause we added references to the lastname and firstname columns of the user table (and used the alias u to refer to the user table). In the FROM clause we added a reference to the user table. Finally, we added a WHERE clause to indicate that the user_id columns of both tables are related. With this relationship established, MySQL returned the first and last name of each user. Concatenating Data Many users are accustomed to seeing names displayed in a format of Lastname, Firstname. This formatting can be achieved by concatenating the fields within the query using the CONCAT() function: SELECT CONCAT(u.lastname, ', ', u.firstname) AS name, e.message_id, e.timestamp, e.status FROM event e, user u WHERE e.user_id = u.user_id

Joining Tables with a Left Join We can also join the status and event tables together to provide actual status messages instead of just message_id numbers. However, if we use the syntax we used previously we will get incorrect results: SELECT CONCAT(u.lastname, ', ', u.firstname) AS name, s.message, e.timestamp, e.status FROM event e, user u, status s WHERE e.user_id = u.user_id AND e.message_id = s.message_id

While our data is more readable with the actual status messages displayed, we are now missing our final row, which has a message_id of NULL. The reason for this is that the relationship between the event and status tables is based on equality, and since the status table has no rows with NULL for a message_id, the NULL row from the event table is thrown out. This can be corrected by using a LEFT JOIN instead of an INNER JOIN. In a LEFT JOIN, a single row is returned for every row contained in the left-hand table of the LEFT JOIN. When there is a matching row in the right-hand table it is returned, otherwise a row with all values set to NULL is returned instead. In our case, we will place the event table on the left and the status table on the right of the LEFT JOIN clause: SELECT CONCAT(u.lastname, ', ', u.firstname) AS name, s.message, e.timestamp, e.status FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u WHERE e.user_id = u.user_id

Note that our NULL row has returned. When we use a LEFT JOIN, the relationship information is moved from the WHERE clause to the FROM clause, using the ON keyword. Formatting Dates While the query is getting quite readable, the date could use improvement. We can format date columns using the DATE_FORMAT() function. The function is used by passing a date value and a format string: SELECT CONCAT(u.lastname, ', ', u.firstname) AS name, s.message, DATE_FORMAT(e.timestamp,'%b %d %Y - %r'), e.status FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u WHERE e.user_id = u.user_id

Of course, most users cannot easily interpret DATE_FORMAT(e.timestamp,’%b %d %Y - %r’), so we can assign an alias to make it easier. While we are at it, we can assign aliases to the remaining columns to make the data easier to interpret: SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u WHERE e.user_id = u.user_id

Creating a Subquery Our end users will not be interested in every single event that has occurred, but will instead want to know the current status of each user. To gather this information we must determine the latest event_id value from the event table for each user.

We can determine the highest event_id value by using the MAX() function: SELECT MAX(event_id) FROM event e

This does not do us much good because we need the maximum value on a per-user basis. Aggregate functions like MAX(), MIN(), AVG(), and SUM() can be used with a GROUP BY clause to change the data that is aggregated: SELECT e.user_id, MAX(e.event_id) FROM event e GROUP BY e.user_id

We can now use this as part of a subquery to determine the current status of each user. A subquery is a query within a query, contained within parenthesis. Subqueries can be used in place of tables in a FROM clause, or to generate a list of values for use in areas such as a WHERE clause. There is an excellent discussion of subqueries at the MySQL web site at http://dev.mysql.com/techresources/articles/4.1/subqueries.html. For the purposes of our subquery we will remove the user_id reference, leaving us with a list of maximum event_id values: SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u WHERE e.user_id = u.user_id AND event_id IN ( SELECT MAX(e.event_id) FROM event e GROUP BY e.user_id )

Ordering Query Results One final tweak we can perform on this query has to do with ordering the data. Our users are accustomed to reading information in alphabetical order, and we should aim to provide our data in the order they are expecting. We can sort the data returned from our query by using the ORDER BY clause. The ORDER BY clause can take multiple column names for sorting, along with the optional DESC keyword to indicate that data should be sorted in descending order. In our case we will sort by the name column: SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u WHERE e.user_id = u.user_id AND event_id IN ( SELECT MAX(e.event_id) FROM event e GROUP BY e.user_id ) ORDER BY name

This query will now form the basis of our status form.

Creating a Status Form Now that we have created some sample data and a query that can be used to show the current status of our users, we can start building a new form to display the query. Start VB.NET and load the In-Out project. Right-click on the In-Out project name in the Solution Explorer and choose the Add > Windows Form… option. An Add New Item dialog will appear with the Windows Form template selected. Change the name of the form to frmMain.vb (we use frmMain instead of frmStatus because the is the primary form in our application) and click the Add button. Once Visual Studio successfully creates the new form, you should be presented with a new blank form to work with. Designing the Status Form In our initial version, this form will have three controls, a datagrid to display query results and two buttons to refresh the datagrid and update our status. First we will add a DataGridView control to our form; find the datagridview in the left-hand toolbox, click it, and then drag the shape you want the datagridview to be on your form (aim to have the datagridview occupy the bottom 85% of the form).

In the properties of the datagrid, change the name of the datagrid to dgvStatus, and while setting properties, set the text property of the form to In-Out - Status View. In addition, add two buttons named cmdRefresh and cmdUpdate, and set their text properties to Refresh and Update Status, respectively. Creating a Connection String Property Our Connector/NET connection string was created in frmLogin, and we will need to pass it to frmMain when we change forms so that we can continue to connect to MySQL. We will do this by creating a public property in frmMain to store the connection string. Click the View Code button to display the source code for frmMain.vb:

The View Code button is the highlighted button located at the top of the Solution Explorer. At first this should be all that is shown in the code view: Public Class frmMain End Class

Within the class definition, type the following: Public Class frmMain Private myConnString As String End Class

This string will store the connection string generated by frmLogin. The Private keyword means that other forms cannot access or modify the connection string directly. To allow access to the connection string for writing, we create a Property. Type Public WriteOnly Property connString As String after the string definition, and the following will be filled in for you: Public WriteOnly Property connectionString() As String Set(ByVal value As String) End Set End Property

This is a code template for a write only property, meaning an external form can assign the connection string, but the connection string cannot be read externally once assigned. Within the Set block we will add a single line of code:

Public Class frmMain Private myConnString As String Public WriteOnly Property connectionString() As String Set(ByVal value As String) myConnString = value End Set End Property End Class

Our form can now have a connection string assigned to it. Before we can assign a connection string, we first move the connection frmLogin connection string to a separate variable named myConnString: Dim myConnString As String myConnString = "server=" & txtServer.Text & ";" _ & "user id=" & txtUsername.Text & ";" _ & "password=" & txtPassword.Text & ";" _ & "database=in_out" conn.ConnectionString = myConnString

Instantiating and Showing the Form Now that we can assign a connection string to frmMain, we need to update the code in frmLogin for a successful login. Instead of showing a message box congratulating the user on a successful login, we will want to create an instance of frmMain, pass the connection string to it, and then close frmLogin. First we need to change the Shutdown mode of our application. By default an application closes when its startup form closes, but in our case we will be closing frmLogin early on and this will not work (there are alternatives such as having frmMain call frmLogin at startup and closing it upon a successful login). Choose the Properties option of the Project menu and make sure the Application tab is active. Change the Shutdown mode from When startup form closes to When last form closes and close the properties window. Our application will now wait until the last form closes before ending. We need to make sure that all forms are closed when we are finished with them and not just hidden. One hidden but not closed form could leave our application running when the end user thinks the application has terminated. Here is the new TRY - CATCH block for the Login button of our login form: Try conn.Open() conn.Close() Dim mainForm As New frmMain mainForm.connectionString = myConnString mainForm.Show() Me.Hide() Me.Close() Catch myerror As MySqlException MessageBox.Show("Error Connecting to Database: " & myerror.Message) conn.Dispose() End Try

First we open and close the connection to allow for an error to trigger the CATCH block. If we are still in the TRY block we instantiate the frmMain class and assign the myConnString variable.

Once the connectionString property is set, we show the main form, then hide and close the login form.

Querying Data from VB.NET We have already dealt with the MySqlConnection class when creating our login form, and we will now be introduced to three additional classes: MySqlCommand, MySqlDataAdapter and DataTable. The MySqlCommand Class The MySqlCommand class contains the query or statement we will be sending to MySQL. The MySqlCommand object helps with performance by allowing us to build queries using parameters when our queries contain static and dynamic elements. After our query is created in the MySqlCommand object, we will pass the MySqlCommand object to the MySqlDataAdapter object for execution. The MySqlDataAdapter Class The MySqlDataAdapter class is used as an adapter between the MySqlConnection class and the DataTable class. The MySqlDataAdapter can query a database, then load the resulting information into a DataTable. It can later update the database with changes that have occurred in the DataTable. The MySqlDataAdapter is what enables us to bind data to a control on our form so that the control is automatically filled with our data. The DataTable Class The DataTable class is used to hold the contents of a single query. It is filled by the MySqlDataAdapter and can then be modified or bound to a control on your form. Data Binding vs. Manual Data Loading VB.NET provides data binding as a way to easily populate components on a form by linking the components directly to the data. With data binding we can quickly and easily populate a form of controls, and any changes we make to the data in the components can easily be applied back to the database. While data binding allows for fast development, I often find that there is a greater degree of control available when I read the data and assign it to a form’s controls manually. That being said, I am first going to demonstrate data binding with the DataGridView class, and I will demonstrate manual data loading in a later article. Adding References and Import Statements The DataTable class is not part of the Connector/NET library but is part of the System.XML assembly. Because of this we need to add a new reference to our project. Choose the Add Reference… option of the Project menu. In the .NET tab, scroll down and choose the System.Xml.dll component and click the OK button. To make it easier to work with our classes, lets add a pair of Imports statements to the top of our form code: Imports MySql.Data.MySqlClient Imports System.Data

As noted previously, these allow us to reference the MySqlDataAdapter without having to use the MySql.Data.MySqlClient.MySqlDataAdapter syntax.

Declaring Our Objects We first need to declare (and instantiate) our database objects. This will be done within the Click event of the Refresh button, so start by double-clicking on the Refresh button to create a sub for the click event, then add this code: Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand Dim myAdapter As New MySqlDataAdapter Dim myData As New DataTable Dim SQL As String End Sub

We create a MySqlConnection to connect to the database, a MySqlCommand object to hold our query, a MySqlDataAdapter to run our query and load the data into a DataTable, and a SQL string to store our query. We instantiate the objects by using the New keyword so that we can use them in our code (consider a class to be a blueprint, and an object to be the home that is built from the blueprint). For the purpose of this tutorial, consider a class that is uninstantiated to be unusable (this is not entirely true, but we’ll get to that later). Entering Our Query Once we have declared our objects and variables, we can enter the SQL query we built earlier into the SQL string we declared: SQL = "SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, " _ & "DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status " _ & "FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u " _ & "WHERE(e.user_id = u.user_id) " _ & "AND event_id IN( " _ & "SELECT MAX(e.event_id) " _ & "FROM event e " _ & "GROUP BY e.user_id) " _ & "ORDER BY name"

The underscore (_) character tells the compiler that the SQL = line is being continued on the next line, allowing us to keep the code readable. The ampersand (&) character is used to join the individual string together on each line. In the end SQL is filled with our query, and we still have neat, readable code (as opposed to if we had placed the entire query on a single line, requiring a lot of horizontal scrolling). In general I try to avoid having code wider than 80 characters, making my code easy to read and easy to print out. Opening the Connection Before we can query the database, we need to open our connection to the database by using the MySqlConnection.Open method. Before we can open the connection, we need to specify the connection string (stored in the MyConnString property as you recall): conn.ConnectionString = myConnString Try conn.Open() Catch myerror As MySqlException MessageBox.Show("Error Connecting to Database: " & myerror.Message) End Try

Once again, the opening of the connection is not guaranteed to work, so we place it within a TRY - CATCH block. Configuring the MySqlCommand Object The next step in the query process is to configure the command object. For a basic, static query all we need to do is assign the query text and tell the command object which connection object to use: myCommand.Connection = conn myCommand.CommandText = SQL

Our command is now ready to be passed to the MySqlDataAdapter object. Using the MySqlDataAdapter Object Once we have configured the connection object, opened the connection, and configured the command object, we are ready to perform the actual query. First we tell the MySqlDataAdapter object which command object to use to perform the query: myAdapter.SelectCommand = myCommand

That is all the MySqlDataAdapter object needs to know to query the database. We perform the query by using the Fill method of the object, and we pass the name of our DataTable object so the adapter has something to place the query results into: myAdapter.Fill(myData)

The results of our query are now located within the myData object (of the DataTable class). This can then be used to bind to the DataGridView control on our form. Binding the Results Now that our data is locally stored in memory, we can bind the data to our DataGridView control so that our users can see the data: dgvStatus.DataSource = myData

That it, one line of code and our data is now visible to the user:

You might notice that the timestamp column is a little truncated. This is caused by each column being the exact same width (notice the status column has far too much room). We can fix this by instructing the DataGridView to resize the columns after loading the data so that each column has the proper width:

dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

The command is a bit verbose, but the auto complete feature of Visual Studio means you actually have little typing to do, and you can select the proper option from a drop-down list. The results are much nicer:

Error Catching We want to also catch errors in the code that does the query, so we will place the preceding code in a TRY CATCH block. Of course, we don’t want to try any of this if the attempt to open the connection fails, so we will actually embed this TRY - CATCH block within the previous one. Here is the entire procedure: Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand Dim myAdapter As New MySqlDataAdapter Dim myData As New DataTable Dim SQL As String SQL = "SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, " _ & "DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status " _ & "FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u " _ & "WHERE(e.user_id = u.user_id) " _ & "AND event_id IN( " _ & "SELECT MAX(e.event_id) " _ & "FROM event e " _ & "GROUP BY e.user_id) " _ & "ORDER BY name" conn.ConnectionString = myConnString Try conn.Open() Try myCommand.Connection = conn myCommand.CommandText = SQL myAdapter.SelectCommand = myCommand myAdapter.Fill(myData) dgvStatus.DataSource = myData dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells Catch myerror As MySqlException MsgBox("There was an error reading from the database: " & myerror.Message) End Try Catch myerror As MySqlException

MessageBox.Show("Error connecting to the database: " & myerror.Message) Finally If conn.State <> ConnectionState.Closed Then conn.Close() End Try End Sub

If there is an error connecting, the user will see a message box that reports an error connecting to the database. If there is an error executing the query, the user will see a message box that reports an error reading from the database.

Updating a User’s Status Now that we can see what the status of our users is, it would be good to add the ability to update our own status. To do this we need to show the user a list of possible status messages using a combo box. In addition we should add a second combo box showing the two possible status messages. Our combo boxes will look something like this:

In

Gone to Lunch

Update Status

Creating the Status and Message Combobox Controls Add the comboboxes to the form by dragging them from the toolbox. In the properties window, change the names of these comboboxes to cboStatus and cboMessage. You form should look something like the following:

On the left is cboStatus, on the right is cboMessage. We will populate these two comboboxes in the form load event, which can be created by double-clicking on the form background. We populate the cboStatus combobox with the following code: cboStatus.Items.Add("In") cboStatus.Items.Add("Out") cboStatus.SelectedIndex = 0

This adds two items (In and Out) and sets the combobox to pre-select the In item (the list starts at 0 and counts up, so In is 0 and Out is 1). We will populate the cboMessage box using data binding. The principal is the same as we covered for the data grid in the previous section, so I am just going to display the code: Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cboStatus.Items.Add("In") cboStatus.Items.Add("Out") cboStatus.SelectedIndex = 0 dgvStatus.ReadOnly = True Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand

Dim myAdapter As New MySqlDataAdapter Dim myData As New DataTable Dim SQL As String SQL = "SELECT s.message_id, s.message " _ & "FROM in_out.status s " _ & "WHERE user_id IS NULL and deleted = 'False'" conn.ConnectionString = myConnString Try conn.Open() Try myCommand.Connection = conn myCommand.CommandText = SQL myAdapter.SelectCommand = myCommand myAdapter.Fill(myData) cboMessage.DataSource = myData cboMessage.DisplayMember = "message" cboMessage.ValueMember = "message_id" Catch myerror As MySqlException MsgBox("There was an error reading from the database: " & myerror.Message) End Try Catch myerror As MySqlException MessageBox.Show("Error connecting to the database: " & myerror.Message) Finally If conn.State <> ConnectionState.Closed Then conn.Close() End Try End Sub

The primary difference is the following two lines: cboMessage.DisplayMember = "message" cboMessage.ValueMember = "message_id"

After setting the source for data binding, we first specify that the message column of our query will be shown in the combobox, and that we can identify which item was selected through the message_id column, as I will later demonstrate. This does present one problem: in its current form, the combobox will not allow us to set a NULL status, because the combobox is only populated with entries from the status table (and only those messages that have no user_id and which are not marked as deleted). For the moment we will ignore this limitation and will address it in a future article. The dgvStatus.ReadOnly = True line is added to prevent users from trying to edit the contents of the datagrid. With this code added, our comboboxes will be pre-populated and ready for us to use to update the status. Retrieving the User ID With A Parameterized Query To update a user’s status we need to create a new row in the event table by using an INSERT query. In order to build the query we need to know the user_id value associated with our user. We can do this by querying the user table, and since we may use this multiple times in a single application run, lets perform the query as part of the login form. Here is our updated login form:

Public Class frmLogin Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click Application.Exit() End Sub Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand Dim myConnString As String Dim UserID As Integer myConnString = "server=" & txtServer.Text & ";" _ & "user id=" & txtUsername.Text & ";" _ & "password=" & txtPassword.Text & ";" _ & "database=in_out" conn.ConnectionString = myConnString Try conn.Open() myCommand.Connection = conn myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username" myCommand.Parameters.Add("?Username", txtUsername.Text) UserID = myCommand.ExecuteScalar conn.Close() Dim mainForm As New frmMain mainForm.UserID = UserID mainForm.connectionString = myConnString mainForm.Show() Me.Hide() Me.Close() Catch myerror As MySqlException MessageBox.Show("Error Connecting to Database: " & myerror.Message) conn.Dispose() End Try End Sub End Class

Of interest are the following lines: myCommand.Connection = conn myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username" myCommand.Parameters.Add("?Username", txtUsername.Text) UserID = myCommand.ExecuteScalar

I added a command object to the code, set the conn object as it’s connection, and specified a query in the CommandText. This query is the first query in our application that incorporates user input, and as such it requires extra care. When we deal with data entered by the user we cannot blindly trust the user to provide information that is safe and syntactically correct, otherwise the user could compromise security through a method known as SQL Injection (see my article at http://www.vbmysql.com/articles/security/sqlinjection.html

for more information). We cannot trust user input and we need to sanitize all user input before it reaches the MySQL server. Connector/NET handles sanitizing of user input for us through the use of parameterized queries. Notice that within the query above the username is compared to a external value with the syntax WHERE BINARY username = ?Username. The ?Username element is a placeholder that we fill with a parameter that is derived from the txtUsername. When the query is executed the MySqlCommand object reads the text of the username textbox and fills in the query, sanitizing it in the process. The query is executed using the ExecuteScalar method of the MySqlCommand object, which can be used to execute queries that will return only a single value. In this case we use it for user_id, it could also be used to return a query that counts rows in a table, or any other query that returns a single value. The user id is passed to the frmMain by way of a parameter, just like the connection string. I have added a parameter to the frmMain to accommodate this: Public Class frmMain Private myConnString As String Private myUserID As Integer Public WriteOnly Property connectionString() As String Set(ByVal value As String) myConnString = value End Set End Property Public WriteOnly Property UserID() As Integer Set(ByVal value As Integer) myUserID = value End Set End Property

The new property is the same as the connection string property, except the user id is stored as an integer instead of a string. Inserting a New Row in the Event Table Now that we have the user id of the logged in user, and the status and message information from our pair of combobox controls, we can insert a new row that will update the status of our user. Here is an example of the INSERT statement we want to generate: INSERT INTO event (user_id, message_id, timestamp, status, creator) VALUES(1, 1, NOW(), 'Out', 1)

We do not specify an event_id because, as an AUTO INCREMENT column, it will be filled in automatically. The user_id is 1 in this case, along with the creator because we are setting our own event. The timestamp is assigned to NOW(), which represents the current server time. Finally, status is set to ‘Out’ to indicate the user is signing out. Of course, we cannot simply place this query into our application, otherwise everyone who ever hit the Update Status button would simply sign out user 1. Instead we will once again build a collection of parameters, bound to the controls on our form:

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand conn.ConnectionString = myConnString myCommand.Connection = conn myCommand.CommandText = "INSERT INTO event(user_id, message_id, timestamp, status, creator)" _ & "VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)" myCommand.Parameters.Add("?UserID", myUserID) myCommand.Parameters.Add("?MessageID", cboMessage.SelectedValue) myCommand.Parameters.Add("?Status", cboStatus.SelectedItem) myCommand.Parameters.Add("?Creator", myUserID) Try conn.Open() myCommand.ExecuteNonQuery() Catch myerror As MySqlException MsgBox("There was an error updating the database: " & myerror.Message) End Try End Sub

Remember when we set the ValueMember property of cboMessage to bind to the message_id of the status table? This allowed us to access the message_id through the SelectedValue property in order to use it as a parameter in our INSERT statement. cboStatus is not a bound control, so we use the SelectedItem property instead when binding it. Note that we can also variables as parameters (myUserID), and even use a variable more than once in a parameter set.

Testing the Application Now that all code is in place, you can try a quick test. Start the application, enter your server IP, username, and password. Try entering the wrong IP, username, or password and make sure you see an error message (without your application crashing). Once you are connected, click the Refresh button and make sure you can see the list of users. Choose a status and message and click the Update Status button, then click the Refresh button to see your status update.

Minor UI Tweaks After testing the application you may have noticed a couple of improvements that we can make to the UI. First, when we update your status, a refresh should be fired automatically so we instantly see the new status. This can be accomplished by adding the following line to the bottom of your update code: cmdRefresh.PerformClick()

This will fire the click event of the Refresh button, refreshing the data after the status is updated. Another improvement that could be made is with regards to keyboard shortcuts on the login form. User expect that the Escape key will cancel out of a form, while the enter key will submit form information. This requires very little coding:

Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.AcceptButton = cmdLogin Me.CancelButton = cmdCancel End Sub

By setting the AcceptButton and CancelButton properties of the form, VB.NET will now watch for Enter and Escape keystrokes and will fire the button events accordingly.

Application Improvements There are a few things that need improving in this application. First of all, we still need a less tedious login that stores the database IP address and which remembers the last username entered. Our datagrid is not particularly attractive and could use cosmetic improvements, and we need to add support for having a blank status message or a custom status message. Finally, it would be nice is the data refreshed automatically rather than requiring our users to repeatedly click the Refresh button. These will all be addressed in future articles.

Conclusion Our application is really moving along. We can now see a list of the users and their current status, and update our own status to one of the pre-defined status messages. We have populated some sample data to start off with, and added a new event through our application. In our next article, we will improve the application by adding automatic data refreshing and persistent server information so that our users need not enter their server IP and username information every time they use the application. The current project files and a dump of the database are available at http://www.vbmysql.com/wpcontent/uploads/vb-mysql-tutorial-part-4.zip.

The VB.NET-MySQL Tutorial - Part 5 Introduction This article is fifth in a series of articles describing how to create a simple Windows application that queries and updates a MySQL database. So far we have designed our application and our database, installed MySQL and VB.NET, created our database, and created a basic application. This application is capable of querying event data from the database and updating it. At the end of the fourth article of this series, I listed some improvements that could be made to our application. These included automatic refreshing of the data and storage of the MySQL server IP address in a configuration file.

Automating Data Refresh One of the drawbacks of our current application is that it requires our user to manually click the Refresh button every time they want to see the latest information. This means that a change in status can go unnoticed if the user does not click the refresh button. In addition, we cannot allow for one user to set a watch for another user to login if the first user has to regularly click the Refresh button. Well will be automating the refreshing of our status information through the use of a VB.NET Timer control. A timer fires at a configurable interval and executes a block of code. Timers are very useful in application programming and can be used for a variety of tasks. Abstracting the Refresh Code Before we create a Timer, we should make our refresh code more generic. Currently the code for refreshing the status data is located directly within the event handler for a click of the Refresh button. We can move this code to a function, whttp://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-part-5.zip vb-mysql-tutorial-part-5.ziphere it can then be called from the click event and the timer. The new function will contain all the code that was previously in the Refresh button click event, with one small change: Private Sub Dim Dim Dim Dim Dim

refreshStatus(ByRef statusView As DataGridView) conn As New MySqlConnection myCommand As New MySqlCommand myAdapter As New MySqlDataAdapter myData As New DataTable SQL As String

SQL = "SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, s.message AS Message, " _ & "DATE_FORMAT(e.timestamp,'%b %d %Y - %r') AS DateTime, e.status AS Status " _ & "FROM event e LEFT JOIN status s ON e.message_id = s.message_id, user u " _ & "WHERE(e.user_id = u.user_id) " _ & "AND event_id IN( " _ & "SELECT MAX(e.event_id) " _ & "FROM event e " _ & "GROUP BY e.user_id) " _

& "ORDER BY name" conn.ConnectionString = myConnString Try conn.Open() Try myCommand.Connection = conn myCommand.CommandText = SQL myAdapter.SelectCommand = myCommand myAdapter.Fill(myData) statusView.DataSource = myData dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells Catch myerror As MySqlException MsgBox("There was an error reading from the database: " & myerror.Message) End Try Catch myerror As MySqlException MessageBox.Show("Error connecting to the database: " & myerror.Message) Finally If conn.State <> ConnectionState.Closed Then conn.Close() End Try End Sub

In the declaration of the function we pass an argument named statusView that will point to the DataGridView on our form. In the code, we change the code that binds the DataGridView to bind against the statusView. This way we can change the name of the DataGridView without changing the code within the function. Our click event is also changed to call this function: Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click refreshStatus(dgvStatus) End Sub

Clicking the Refresh button now calls the refreshStatus function. In addition, we can change the following line of our update code: cmdRefresh.PerformClick()

to refreshStatus(dgvStatus)

This is a much better approach when the same code is executed in multiple places. In addition, add the call to refreshStatus to your frmMain_load event so that the user is presented with a view of the status from the beginning. Adding the Timer Now that our refresh code is moved, we can call it from a timer. The Timer control is located in the toolbox:

Click and drag the timer control onto your form. Unlike other controls, the Timer control does not stay on your form, but is automatically moved to a special area below the form:

This area is reserved for controls that are not visible, such as the timer and common dialogs. Change the name of the timer to tmrRefresh using the properties window and set the Enabled property to True. Choosing an Interval

The Interval property of the timer determines how often the timer will trigger an event. The Interval is measured in milliseconds, with 1000 milliseconds to a second. We do not want to fire this event too often, or we produce excessive load on our server. We also do not want to wait too long before firing our event, or our users may not see updated status information fast enough. Ideally we want to have an interval of about one minute, or 60,000 milliseconds. Set the Interval property of our timer to 60000. Catching the Tick Event When the Interval set previously is reached, the timer triggers the Tick event. We can catch the Tick event and add code to it, allowing that code to be run every time the interval is reached, or once every minute. Double-click on the timer and the following code template is generated: Private Sub tmrRefresh_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tmrRefresh.Tick End Sub

We can add a call to our refreshStatus function within this function to cause the status information to refresh automatically: Private Sub tmrRefresh_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tmrRefresh.Tick refreshStatus(dgvStatus) End Sub

This will cause our status information to be refreshed once per minute, regardless of whether the user clicks the Refresh button or not. You can test this by launching the application, then adding a row to the event table using the MySQL Query Browser. Within one minute the status display will change.

Storing Application Settings Now that we have added a timer to frmMain, let’s make some improvements to frmLogin. Currently, our users need to manually enter the MySQL server IP address every time they use our application. In addition, there is no option to remember a username for future use. To solve both of these problems we need a way to persist information for future use. There are a variety of options for persisting data, including INI files, the registry, and XML files. Most of the options mentioned above require either the use of API calls or file handling code to set and retrieve data. With VB.NET 2005 a new My.Settings syntax has been added that greatly simplifies the storage and retrieval of application settings. The new My.Settings Syntax With My.Settings, we can application and user-level settings to our application and easily access them without using any API or file handling code. Settings are stored in XML files located either with the application executable or in the Documents and Settings/Username directory, depending on the scope of the setting. Setting can have either a User or Application scope, depending on whether an setting applies to the application in general or to a specific user. To use My.Settings, we first create the setting within the VB.NET IDE, then access the setting through code.

Storing the Host IP with an Application Setting The first step in using an application-level setting is to create is using the setting designer. Choose Properties from the Project menu and choose the Settings tab. A grid with available settings is displayed:

Within this grid we create a new HostIP setting as a String, with an Application scope. Set the value to the IP address of your MySQL server machine. Once your setting is in place you can close the Project Properties window. After closing the window you will be prompted to save the application settings file, click Yes and you will be returned to the form designer. The following is added to the App.Config file: <setting name="HostIP" serializeAs="String"> 192.168.1.10

This file will be compiled into a file named in-out.exe.config file when you build your project, and will be located in the bin folder of your project.

Accessing the Host IP Information in frmLogin After the HostIP setting is stored we can access it from within our code. First change the form design by deleting the controls related to the server IP address:

Once the form is redesigned, we can modify the login code, specifically the connection string:: SQL = "server="

& My.Settings.HostIP & ";" _ & "user id=" & txtUsername.Text & ";" _ & "password=" & txtPassword.Text & ";" _ & "database=in_out"

Our login code will now use the stored IP address when connecting to the server. Storing the User Name with a User Setting Now that our host IP is taken care of, let’s look at optionally storing the username to save time for our users. We can store the username in a User scope setting, which will allow it to be read and written to a file in the Documents and Settings directory (Application scope settings are read-only though the My.Settings interface). One advantage of this approach is that even when our application is used with multiple Windows user accounts, each user can have their username stored without overwriting the previous user’s information. We create a User scope setting with the same interface as we used to create a Application scope setting. When creating the UserName setting, create it as a string, set a User scope, and leave the Value blank. The Settings section of the App.Config file will look like this: <setting name="HostIP" serializeAs="String"> 192.168.1.10 <userSettings> <setting name="Username" serializeAs="String">

Accessing the User Name in frmLogin As an added convenience to our user, let’s allow the username to be optionally stored by means of a checkbox. This will save time when the user logs in. First lets redesign our login form and add a CheckBox control:

Name the CheckBox chkRemember and leave it unchecked by default (set the text property ‘Remember Me’). First let’s add code to the form_load event to check if we have a saved username: If My.Settings.Username <> "" Then txtUsername.Text = My.Settings.Username chkRemember.Checked = True End If

This code checks for a blank username. If the Username setting is not blank, it fills the txtUsername Text property and checks chkRemember. Next we edit the login code for the form. We only save the username if chkRemember is checked and the username is successfully used to login. If chkRemember is not checked, we blank out the Username setting: Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand Dim myConnString As String Dim UserID As Integer myConnString = "server=" & My.Settings.HostIP & ";" _ & "user id=" & txtUsername.Text & ";" _ & "password=" & txtPassword.Text & ";" _ & "database=in_out" conn.ConnectionString = myConnString Try conn.Open() myCommand.Connection = conn myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username" myCommand.Parameters.Add("?Username", txtUsername.Text)

UserID = myCommand.ExecuteScalar conn.Close() If chkRemember.Checked Then My.Settings.Username = txtUsername.Text My.Settings.Save() Else My.Settings.Username = "" End If Dim mainForm As New frmMain mainForm.UserID = UserID mainForm.connectionString = myConnString mainForm.Show() Me.Hide() Me.Close() Catch myerror As MySqlException MessageBox.Show("Error Connecting to Database: " & myerror.Message) conn.Dispose() End Try End Sub

Our frmLogin class should now look like this: Public Class frmLogin Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click Application.Exit() End Sub Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand Dim myConnString As String Dim UserID As Integer myConnString = "server=" & My.Settings.HostIP & ";" _ & "user id=" & txtUsername.Text & ";" _ & "password=" & txtPassword.Text & ";" _ & "database=in_out" conn.ConnectionString = myConnString Try conn.Open() myCommand.Connection = conn myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username" myCommand.Parameters.Add("?Username", txtUsername.Text) UserID = myCommand.ExecuteScalar conn.Close() If chkRemember.Checked Then My.Settings.Username = txtUsername.Text

My.Settings.Save() Else My.Settings.Username = "" End If Dim mainForm As New frmMain mainForm.UserID = UserID mainForm.connectionString = myConnString mainForm.Show() Me.Hide() Me.Close() Catch myerror As MySqlException MessageBox.Show("Error Connecting to Database: " & myerror.Message) conn.Dispose() End Try End Sub Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.AcceptButton = cmdLogin Me.CancelButton = cmdCancel If My.Settings.Username <> "" Then txtUsername.Text = My.Settings.Username chkRemember.Checked = True End If End Sub End Class

You can test our setting code by running the application, checking the Remember Me box, and logging in. Close the application and restart it, your username should be present.

Hiding the Password One last tweak we can make before leaving frmLogin has to do with txtPassword. At present, any password entered is in plain sight, and could be read by an observer of the user. To hide the text of a textbox, add the following line to the form_load event: Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.AcceptButton = cmdLogin Me.CancelButton = cmdCancel If My.Settings.Username <> "" Then txtUsername.Text = My.Settings.Username chkRemember.Checked = True End If txtPassword.PasswordChar = "*" End Sub

All text entered is replaced with *, preventing casual observers from seeing sensitive information.

Conclusion

In this article we have tweaked our application to improve its ease of use. Specifically, we have added a Timer control to frmMain to allow for automatic refreshing of event data, and made use of the My.Settings system to store IP and username information. Username information is stored in a file that is unique to each Windows user account, allowing multiple users to store username information without overwriting each other. In our next article we will improve the event display and generation code to allow for the creation of custom status messages and to support blank status messages. We will also explore the creation of custom classes and data binding to an object. An archive of the code so far can be found at http://www.vbmysql.com/wp-content/uploads/vb-mysqltutorial-part-5.zip.

The VB.NET-MySQL Tutorial - Part 6 Introduction In the fourth article of our series, we covered basic data binding and used it to populate our VB.NET 2005 form with data from our MySQL database. While this worked well to move data from a table to a control on our form, it lacked the versatility needed to allow for blank status messages and custom status messages. In this article we will learn to use the MySqlDataReader class to populate our combobox manually, and then implement a mechanism to support custom status messages and blank status messages in our application.

Using the MySqlDataReader Class Quite often when developing database applications the use of a MySqlDataAdapter and DataSet can be overkill; many times we simply need to read a series of rows from the server with no need to store the data for future updates. In such read-only situations we can use the MySqlDataReader class. The MySqlDataReader class provides read-only access to the data in a MySQL database, reading one row at a time from the server. The MySqlDataReader class can be much more memory efficient than the MySqlDataAdapter/DataSet approach because as each row is read, the previous row is cleared from memory. Switching From a MySqlDataAdapter to a MySqlDataReader First let’s change our frmMain_Load event to use a MySqlDataReader object instead of a MySqlDataAdapter object. This change is not major, but there are small differences in object creation that we need to address. We do not instance the MySqlDataReader at the start of our function, because the MySqlCommand object has a ExecuteReader method that will return a MySqlDataReader object: Dim Dim Dim Dim

conn As New MySqlConnection myCommand As New MySqlCommand myReader As MySqlDataReader SQL As String

We have removed declarations for a MySqlDataAdapter and a DataTable and replaced them with a single declaration for a MySqlDataReader. Next we modify the code to remove reference to the old MySqlDataAdapter object and instead create our MySqlDataReader object: Try myCommand.Connection = conn myCommand.CommandText = SQL myReader = myCommand.ExecuteReader

As you can see, our MySqlDataReader object is created by the ExecuteReader method of the MySqlCommand class.

Creating a Custom Class The next challenge we face has to do with replacing the data binding employed by the cboMessage combobox: we cannot bind a MySqlDataReader as we would a DataTable. Without data binding, we need to find a way to

store not only the actual status message in our combobox (i.e. ‘Gone to Lunch’), we also need to somehow retain the message_id of a selected message. With data binding, we were able to specify a DisplayMember property(message) and a ValueMember property (message_id), but these properties are not available when manually populating a combobox. If you take a closer look at the combobox class, you will see that the Add method of the Items collection takes an object as its argument. The combobox stores these objects in the Items collection, and uses the ToString method of the object passed to determine what value to display in the actual combobox. We can use this to our advantage by creating a custom message class which will store a message and its ID. We can pass this class to the combobox.Items.Add method and later retrieve the selected message and ID value for performing updates. To create a custom class, right-click on your solution in the Solution Explorer and choose the Add > Class option from the drop-down menu. Set the name to something like clsMessage and click the Add button. You should have a simple template like the following: Public Class clsMessage End Class

Before our class can be of much use, we need to add variables, properties, a constructor, and a ToString function. Creating Variables - Public vs. Private The first thing we will do is create a couple of private variables for our class. These will be the class’s internal storage for message and ID values. We will declare these with the Private keyword so that they are inaccessible outside of the class: Public Class clsMessage Private myID As Integer Private myMessage As String End Class

Because these variables are declared Private they cannot be modified or read by other classes. This gives us greater control over the contents of the variables because we know for certain that they will not be externally modified. We can provide access to the contents of these variables through the use of properties. Creating Properties Properties work like gatekeepers that control the modification of internal variables and their presentation to external classes. In our simple application we will use them to provide direct access to our internal variables, but they can easily be expanded to provide very fine-grained control of what is allowed in and out of your class. Our properties are declared Public, and property templates can be easily created by typing a single line, in our case Public Property Message As String. When you type the line and press enter, the following template is automatically created:

Public Property Message() As String Get End Get Set(ByVal value As String) End Set End Property

The Get section of the property determines what is returned when clsMessage.Message is called to retrieve (or Get) the value, and the Set section takes an assignment from an external class and determines what is done with it. In our case we will be using a very simple pair of assignments for our property: Public Property Message() As String Get Message = myMessage End Get Set(ByVal value As String) myMessage = value End Set End Property

Our ID property will be almost identical: Public Property ID() As Integer Get ID = myID End Get Set(ByVal value As Integer) myID = value End Set End Property

With these properties in place, we can now instantiate our class, and assign the Message and ID properties. Creating a Constructor While we could now use our class by instantiating it, filling the properties, and then passing it to our combobox, we can make the process even smoother by adding a constructor to our class. Simply put, a constructor is a procedure that is executed as the object is created. We can pass the message and ID values to the constructor as we create the object, saving us the trouble of having to instantiate and then populate the object. To create a constructor, create a procedure named New: Sub New(ByVal ID As Integer, ByVal Message As String) myID = ID myMessage = Message End Sub

Our constructor accepts two arguments, ID and Message, and then passes them to the internal variables. We can call the constructor by using the New keyword: Dim myMessage As New clsMessage(12, "My Message has an ID of 12")

Overriding the ToString Method The last part of creating our class is to override the ToString method. Because our custom class actually inherits the Object class, it also inherits a ToString method. Because we do not want to have the ID value output in our combobox, we need to override this method and change it to not include the ID value: Public Overrides Function ToString() As String ToString = myMessage End Function

The Final Custom Class Here is our finished custom class: Public Class clsMessage Private myID As Integer Private myMessage As String Sub New(ByVal ID As Integer, ByVal Message As String) myID = ID myMessage = Message End Sub Public Property Message() As String Get Message = myMessage End Get Set(ByVal value As String) myMessage = value End Set End Property Public Property ID() As Integer Get ID = myID End Get Set(ByVal value As Integer) myID = value End Set End Property Public Overrides Function ToString() As String ToString = myMessage End Function End Class

Using the Custom Class Now that our custom class is created, we can use it to populate our combobox. We’ll start by creating a static item and then loop through our MySqlDataReader and load the contents of our query into the combobox. Passing the Custom Class to the ComboBox First let’s add a custom, static message that will represent no message at all. We will use ‘No Message’ as the message and -1 as the ID:

myCommand.Connection = conn myCommand.CommandText = SQL myReader = myCommand.ExecuteReader cboMessage.Items.Add(New clsMessage(-1, "No Message"))

We can also add the following line just above our Catch block to make this the default message: cboMessage.SelectedIndex = 0

Looping Through the MySqlDataReader The MySqlDataReader class contains only one row at a time, and is advanced to the next record through use of the Read method. The Read method returns True every time there is a new row to be read, and returns False when it passes the last row in the result set. Looping through the result set is as simple as using a While loop: While myReader.Read 'DO SOMETHING End While

Accessing Individual Column Data As we loop through our query results, we need to access the data contained within our columns. The most basic way to do this is to use the GetValue method of the MySqlDataReader. The GetValue method returns the contents of the specified column in the appropriate data type. GetValue expects an integer representing the column number you wish to query, in our case the message_id column is 0 and the message column is 1. I generally try to avoid hard-coding column numbers into my application: if the query were to change you would need to change the column numbers or your application may encounter errors. Instead, we can use the GetOrdinal function to dynamically retrieve the column number based on the column name. The following code loops through our query result, populating a custom message class and loading it into our combobox: While myReader.Read cboMessage.Items.Add(New clsMessage(myReader.GetValue(myReader.GetOrdinal("message_id")), _ myReader.GetValue(myReader.GetOrdinal("message")))) End While

We pass a newly created clsMessage object to the Items.Add method, and populate the constructor of the object using the GetValue method of the MySqlDataReader. We use the GetOrdinal method of the MySqlDataReader to specify the column number for the columns so that our code is more future-proof. The New frmMain_Load Procedure Here is the complete frmMain_Load procedure: Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cboStatus.Items.Add("In") cboStatus.Items.Add("Out") cboStatus.SelectedIndex = 0

dgvStatus.ReadOnly = True refreshStatus(dgvStatus) Dim Dim Dim Dim

conn As New MySqlConnection myCommand As New MySqlCommand myReader As MySqlDataReader SQL As String

SQL = "SELECT s.message_id, s.message " _ & "FROM in_out.status s " _ & "WHERE user_id IS NULL and deleted = 'False'" conn.ConnectionString = myConnString Try conn.Open() Try myCommand.Connection = conn myCommand.CommandText = SQL myReader = myCommand.ExecuteReader cboMessage.Items.Add(New clsMessage(-1, "No Message")) While myReader.Read cboMessage.Items.Add(New clsMessage(myReader.GetValue(myReader.GetOrdinal("message_id")), _ myReader.GetValue(myReader.GetOrdinal("message")))) End While cboMessage.SelectedIndex = 0 Catch myerror As MySqlException MsgBox("There was an error reading from the database: " & myerror.Message) End Try Catch myerror As MySqlException MessageBox.Show("Error connecting to the database: " & myerror.Message) Finally If conn.State <> ConnectionState.Closed Then conn.Close() End Try End Sub

You should be able to test your code by running your application and checking cboMessage to see if it is populated properly. Updating Data Using the Custom Class Now that we have changed the method used to populate our combobox, we must also modify the procedure used to update our status. First we need to add a declaration for our custom class to the cmdUpdate_Click procedure: Dim myMessage As clsMessage

We populate this object by using the SelectedItem property of the cboMessage combobox. The SelectedItem property needs to be cast back into a clsMessage object before it can be used:

myMessage = CType(cboMessage.SelectedItem, clsMessage)

Now that we have the message object for the selected message, we can use it to update the event table. We need to check whether the ID value of our message object is -1, and if so we pass a NULL value as a parameter to our update query. If the ID value of our message is not -1, we pass the ID value as a parameter to our update query: If myMessage.ID = -1 Then myCommand.Parameters.Add("?MessageID", DBNull.Value) Else myCommand.Parameters.Add("?MessageID", myMessage.ID) End If

Our cmdUpdate code is now ready to update a blank status message. Here is the new cmdUpdate_Click procedure: Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand Dim myMessage As clsMessage myMessage = CType(cboMessage.SelectedItem, clsMessage) conn.ConnectionString = myConnString myCommand.Connection = conn myCommand.CommandText = "INSERT INTO event(user_id, message_id, timestamp, status, creator)" _ & "VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)" myCommand.Parameters.Add("?UserID", myUserID) If myMessage.ID = -1 Then myCommand.Parameters.Add("?MessageID", DBNull.Value) Else myCommand.Parameters.Add("?MessageID", myMessage.ID) End If myCommand.Parameters.Add("?Status", cboStatus.SelectedItem) myCommand.Parameters.Add("?Creator", myUserID) Try conn.Open() myCommand.ExecuteNonQuery() Catch myerror As MySqlException MsgBox("There was an error updating the database: " & myerror.Message) End Try refreshStatus(dgvStatus) End Sub

Supporting Custom Status Message Entry Now that we have laid the foundation for supporting blank error messages, we can add support for custom error messages. Our status table has a column named user_id which is used to indicate that a given status

message is a custom message and also to indicate the user that the custom status message belongs to. We need to modify our application to allow for custom user input in our combobox, and then modify our update code to create a new status table entry when the user has entered a custom status message. After we have successfully created a custom status message, we need to modify our form load procedure to show the latest two custom status messages in addition to the default status messages. Modifying the ComboBox The first step in working with custom status messages is to modify our combobox control to allow our user to enter custom information. This is done by changing the DropDownStyle property of our combobox from DropDownList to DropDown. The two modes are practically the same with the exception that a DropDown combobox allows the user to manually enter information into the combobox. While the DropDownStyle property can be set at design-time by adjusting the properties of the combobox, we can also make the change at run-time in our form load event: cboMessage.DropDownStyle = ComboBoxStyle.DropDown

Determining Whether a User Chose an Item or Entered a Custom Message We can identify whether a user chose an existing status message or typed in their own status message by looking at the SelectedIndex property of the combobox. If the SelectedIndex property is set to -1, it indicates that the user has typed in their own status message. If the value is greater than -1, it indicates that the user has chosen a status message from the combobox drop-down list. We can use an If … Else … End If block to handle the different code required for custom and existing status messages: If cboMessage.SelectedIndex <> -1 Then 'IN THIS CASE WE HAVE A MESSAGE THAT WAS SELECTED FROM THE DROP-DOWN LIST myMessage = CType(cboMessage.SelectedItem, clsMessage) Else 'IN THIS CASE WE NEED TO CREATE A NEW ENTRY IN THE STATUS TABLE End If

Our code will not need any modification when working with an existing status message, so we just need to create code to insert a new status message when the SelectedIndex property is -1. Creating an INSERT Statement Once we determine that we need to add a custom status message we can build an INSERT query to add the message to the status table. We only need to specify the user_id, message and deleted values when inserting into the status table because the message_id value is an AUTO_INCREMENT value and is generated automatically. In addition, we can use the ability of MySQL Connector/NET to execute multiple statements in a batch to add a SELECT query for retrieving the auto_increment value assigned to our new row: myCommand.CommandText = "INSERT INTO status(user_id, message) VALUES(?userID, ?message);" _ & "SELECT LAST_INSERT_ID()"

The LAST_INSERT_ID() function always returns the last auto_increment value returned by your connection (in this case the message_id value for the status table). Because the function operates on a per-connection level, you can always be assured this is the last value generated by your application, regardless of how many users are inserting records into a table concurrently. We once again use parameters for the values we will be inserting, and we create these parameters in our command object: myCommand.Parameters.Add("?userID", myUserID) myCommand.Parameters.Add("?message", cboMessage.Text)

In this case we are setting the user_id column to the ID value of our logged-in user, stored in the form’s myUserID variable, and the message column to the Text property of our cboMessage combobox. Once our parameters are set, we can execute the UPDATE and SELECT queries. Because our SELECT query will be returning a single value, we can use the ExecuteScalar method of the MySqlCommand object. As you may recall from Part 4, the ExecuteScalar method will return a single value, or the first column of the first row of a query result. We can use the output of the ExecuteScalar method to create a clsMessage object, which will be used to create a new entry in cboMessage and the event table: Try conn.Open() myMessage = New clsMessage(myCommand.ExecuteScalar(), cboMessage.Text) cboMessage.Items.Insert(1, myMessage) cboMessage.SelectedIndex = 1 conn.Close() Catch myerror As MySqlException MsgBox("There was an error updating the database: " & myerror.Message) End Try

As usual, the code is placed in a TRY-CATCH block to catch any errors that may occur. We use the Items.Insert method to add an item at a specific position in our combobox and then set the SelectedIndex property to move our selection to the newly created item. Note that we explicitly close the connection object because it is reused later in the cmdUpdate_Click procedure. Up until now we have allowed VB.NET to implicitly close and dispose of our Connector/NET objects. Our Complete Update Code Here is the completed cmdUpdate_Click procedure: Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand Dim myMessage As clsMessage conn.ConnectionString = myConnString myCommand.Connection = conn If cboMessage.SelectedIndex <> -1 Then 'IN THIS CASE WE HAVE A MESSAGE THAT WAS SELECTED FROM THE DROPDOWN LIST

myMessage = CType(cboMessage.SelectedItem, clsMessage) Else 'IN THIS CASE WE NEED TO CREATE A NEW ENTRY IN THE STATUS TABLE myCommand.CommandText = "INSERT INTO status(user_id, message, deleted) VALUES(?userID, ?message, 'False');" _ & "SELECT LAST_INSERT_ID()" myCommand.Parameters.Add("?userID", myUserID) myCommand.Parameters.Add("?message", cboMessage.Text) Try conn.Open() myMessage = New clsMessage(myCommand.ExecuteScalar(), cboMessage.Text) cboMessage.Items.Insert(1, myMessage) cboMessage.SelectedIndex = 1 conn.Close() Catch myerror As MySqlException MsgBox("There was an error updating the database: " & myerror.Message) End Try End If myCommand.CommandText = "INSERT INTO event(user_id, message_id, timestamp, status, creator)" _ & "VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)" myCommand.Parameters.Add("?UserID", myUserID) If myMessage.ID = -1 Then myCommand.Parameters.Add("?MessageID", DBNull.Value) Else myCommand.Parameters.Add("?MessageID", myMessage.ID) End If myCommand.Parameters.Add("?Status", cboStatus.SelectedItem) myCommand.Parameters.Add("?Creator", myUserID) Try conn.Open() myCommand.ExecuteNonQuery() Catch myerror As MySqlException MsgBox("There was an error updating the database: " & myerror.Message) End Try refreshStatus(dgvStatus) End Sub

You should now be able to test your application code by entering a status message into the cboMessage combobox and clicking the Update Status button. Your status grid should be updated to show the new status message, and the status table should contain a new entry with your user ID and message values. Add three custom messages for use in the next part of our tutorial.

Displaying Custom Status Messages Now that we have created our custom status messages, we need to modify our application to show not only our pre-defined status messages, but the two most recent custom status messages as well. As you may recall, messages with a user_id value of NULL are pre-defined status messages that apply to all users, while messages with a non-NULL user_id belong to the specified user.

UNION Queries One way to show two sets of messages would be to perform two queries, one for the status messages with a NULL user_id, and one for the latest two messages with the same user_id as our user. This would mean two trips to the server and two result sets, and we would have to process data twice to move it to the combobox. The better solution is to use a UNION query. With a UNION query the MySQL server can combine multiple result sets from multiple queries into a single result set. The only limitation with a UNION query is that all queries must return the same number and type of columns, in the same order. Full information on using UNION can be found at http://dev.mysql.com/doc/mysql/en/union.html. The following query retrieves the latest two custom status messages for user 1, along with all pre-defined messages: (SELECT s.message_id, s.message FROM in_out.status s WHERE user_id = 1 and deleted = 'False' ORDER BY message_id DESC LIMIT 2) UNION (SELECT s.message_id, s.message FROM in_out.status s WHERE user_id IS NULL and deleted = 'False')

As you can see, each individual query can be sorted and limited independently, and if we wished, we could then sort and limit the entire result set as well. All that we need to do now is replace the existing query in our frmMain_Load procedure with the new UNION query: SQL = "(SELECT s.message_id, s.message " _ & "FROM in_out.status s " _ & "WHERE user_id = ?userID and deleted = 'False' " _ & "ORDER BY message_id DESC LIMIT 2) " _ & "UNION " _ & "(SELECT s.message_id, s.message " _ & "FROM in_out.status s " _ & "WHERE user_id IS NULL and deleted = 'False')"

Once our query is modified, we just need to add a parameter to our command object to populate the appropriate user ID value: myCommand.Parameters.Add("?userID", myUserID)

The Complete frmMain_Load Procedure Once again, here is the completed frmMain_Load procedure: Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cboMessage.DropDownStyle = ComboBoxStyle.DropDown cboStatus.Items.Add("In") cboStatus.Items.Add("Out") cboStatus.SelectedIndex = 0

dgvStatus.ReadOnly = True refreshStatus(dgvStatus) Dim Dim Dim Dim

conn As New MySqlConnection myCommand As New MySqlCommand myReader As MySqlDataReader SQL As String

SQL = "(SELECT s.message_id, s.message " _ & "FROM in_out.status s " _ & "WHERE user_id = ?userID and deleted = 'False' " _ & "ORDER BY message_id DESC LIMIT 2) " _ & "UNION " _ & "(SELECT s.message_id, s.message " _ & "FROM in_out.status s " _ & "WHERE user_id IS NULL and deleted = 'False')" conn.ConnectionString = myConnString Try conn.Open() Try myCommand.Connection = conn myCommand.CommandText = SQL myCommand.Parameters.Add("?userID", myUserID) myReader = myCommand.ExecuteReader cboMessage.Items.Add(New clsMessage(-1, "No Message")) While myReader.Read cboMessage.Items.Add(New clsMessage(myReader.GetValue(myReader.GetOrdinal("message_id")), _ myReader.GetValue(myReader.GetOrdinal("message")))) End While cboMessage.SelectedIndex = 0 Catch myerror As MySqlException MsgBox("There was an error reading from the database: " & myerror.Message) End Try Catch myerror As MySqlException MessageBox.Show("Error connecting to the database: " & myerror.Message) Finally If conn.State <> ConnectionState.Closed Then conn.Close() End Try End Sub

Changing Focus Let’s add one tweak to the login form before we go. If you have been using your application you may notice that in spite of the fact that you have the Remember Me box checked, the login form still loads with the initial focus on the Username textbox, meaning that you must press the tab key before you can enter your password. We can solve this by using the Focus() method of the textbox class.

The only challenge we face is that the Focus method can only act on visible controls, and the login form does not display until the form load event is finished. We can however use the form activated event, which fires after the form is visible and active: Private Sub frmLogin_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated If My.Settings.Username <> "" Then txtPassword.Focus() End If End Sub

Now our form will focus on the password field by default when there is a stored username value.

Conclusion Our application has become fairly usable at this point: Our users can login, they can choose between no status message, a pre-defined status message, or they can specify their own status message. Our form will show the latest two custom status messages for our user to re-use. Our login form allows a user to save their username and will fill in the username and change focus to the password field accordingly. In our next tutorial, we will develop an application installer so that we can deploy our application on multiple machines in our office. Source code for this installment can be found at http://www.vbmysql.com/wp-content/uploads/vb-mysqltutorial-part-6.zip

The VB.NET-MySQL Tutorial - Part 7 Introduction In our previous article we added the final base functionality to our application, allowing for the creation and use of custom status messages. Now that we have a basic working application we can move to creating an installer for our application, allowing it to be deployed to other PCs. There are multiple options for creating installers for a Visual Basic application, including the new one-click installer that will be included with Visual Studio.NET 2005. In this tutorial I am going to introduce you to an excellent Open Source installer called InnoSetup. InnoSetup is a script-driven installer, written in Delphi, that is very versatile and expandable. InnoSetup is free for commercial use and is available for download at www.innosetup.com. In addition to the files generated by the Visual Basic compiler, we also need the .NET framework 2.0 installer, for those machines that do not have the appropriate version of the .NET framework installed. The .NET framework redistributable package can be downloaded here.

Building the Application Before we can package the application into an installer, we need to build an executable. Open your project in VB.NET and choose the Clean option from the Build menu. The Clean option deletes all binary and intermediate files from the project, ensuring that the next build will not contain any old files. After the clean is completed, choose the Build option from the Build menu. After building the project, the bin directory will contain the In-Out.exe executable. and the In-Out.exe.config configuration file. As these are the only two files in our application the installation will be relatively simple; we must install the two built files and run the .NET framework installer.

Creating an Installation Support Directory First we need to create an installation support directory within our project directory to store the installer files and the support files. Add a \install directory with \support and \output sub-directories to your project directory. Move the .NET framework installer (dotnetfx.exe) into the \support directory. The \output directory will contain the compiled install file.

Creating the Install Script Now that we have built the executable. and prepared the support files, we are ready to create the application installer. The InnoSetup tool comes with a wizard interface that we will use to build the basic install profile. Download InnoSetup from www.innosetup.com and install it before proceeding. Once you have downloaded and installed InnoSetup, start InnoSetup and you will be presented with a file dialog:

Choose the Create a new script file using the Script Wizard option and click OK. The next dialog is an introduction to the wizard:

Make sure the Create a new empty script file option is un-checked and click Next >. Setting the Application Information

The next dialog in the wizard is for setting some basic application information:

Set the appropriate application information for your project and click Next > (if you are unsure about a piece of information just use a dummy value, you can change it later). Setting the Program Directory After setting the basic application information, we can configure the installation directory for our application:

It is generally best to install to the Program Files directory of the target machine. Set the application directory name and click Next >. Specifying Installation Files The next step is to specify the files that will be included with the installer:

Browse to the executable file you created in VB.NET and specify it as your application main executable file. After identifying your main executable, use the Add file(s) button to add the .config file for your executable. After you have specified your application files, click Next >. Creating Shortcuts and Icons After specifying installation files we move on to creating a Start menu entry and various shortcuts to our application:

Select a start menu folder name (usually your application name) and choose your desired options. I like to add an Uninstall icon to the start menu entry just to make things more convenient for my users. Once you have configured the Start menu entry, click Next >. Specifying Install Documentation The next screen of the wizard allows you to specify installation documentation files:

Of particular interest is the License file; if you specify a license file, the user will be unable to proceed with the installation without agreeing to the contents of the license file. In our case we do not have a license file, so leave the fields blank and click Next >. Specifying Output Files The final step in the InnoSetup wizard is to specify where the output files of the InnoSetup compiler will be placed:

Set the Custom compiler destination base directory to the \output subdirectory you created previously. You can change the base name of your installer if you wish, in my case I have changed the name to in-out-setup (InnoSetup will take care of the extension). After entering your output settings, click Next >. Finishing the InnoSetup Wizard On the final screen of the InnoSetup wizard click the Finish button. When prompted, click the No button to skip compiling the new script. You should be presented with a setup script similar to this one: ; Script generated by the Inno Setup Script Wizard. ; SEE THE DOCUMENTATION FOR DETAILS ON CREATING INNO SETUP SCRIPT FILES! [Setup] AppName=In-Out AppVerName=In-Out 1.0 AppPublisher=Mike Hillyer AppPublisherURL=http://www.openwin.org AppSupportURL=http://www.openwin.org AppUpdatesURL=http://www.openwin.org DefaultDirName={pf}\In-Out DefaultGroupName=In-Out OutputDir=C:\Documents and Settings\mhillyer\Desktop\in-out\install\output OutputBaseFilename=in-out-setup Compression=lzma SolidCompression=yes [Tasks] Name: "desktopicon"; Description: "{cm:CreateDesktopIcon}"; GroupDescription: "{cm:AdditionalIcons}"; Flags: unchecked Name: "quicklaunchicon"; Description: "{cm:CreateQuickLaunchIcon}"; GroupDescription: "{cm:AdditionalIcons}"; Flags: unchecked

[Files] Source: "C:\Documents and Settings\mhillyer\Desktop\in-out\In-Out\bin\In-Out.exe"; DestDir: "{app}"; Flags: ignoreversion Source: "C:\Documents and Settings\mhillyer\Desktop\in-out\In-Out\bin\In-Out.exe.config"; DestDir: "{app}"; Flags: ignoreversion ; NOTE: Don't use "Flags: ignoreversion" on any shared system files [Icons] Name: "{group}\In-Out"; Filename: "{app}\In-Out.exe" Name: "{group}\{cm:UninstallProgram,In-Out}"; Filename: "{uninstallexe}" Name: "{userdesktop}\In-Out"; Filename: "{app}\In-Out.exe"; Tasks: desktopicon Name: "{userappdata}\Microsoft\Internet Explorer\Quick Launch\In-Out"; Filename: "{app}\In-Out.exe"; Tasks: quicklaunchicon [Run] Filename: "{app}\In-Out.exe"; Description: "{cm:LaunchProgram,In-Out}"; Flags: nowait postinstall skipifsilent

This script is enough to perform a basic installation of the application in a target machine. Choose the Save option of the File menu of InnoSetup and save the script in the /install directory of your project (I named mine in-out.iss). You may want to change the various file paths in the script to relative paths, just to make it easier to move your project directory around.

Adding the .NET Framework While the installer would now successfully install our application, we need to add support for installing the .NET Framework. The dotnetfx.exe we downloaded earlier needs to be added to the installer and run as part of the installation process. This will radically increase the size of the installation (the MSI installer is 22 megabytes), but it cannot be avoided since we cannot assume that the target machine will have the .NET Framework 2.0. Adding a File to the Install Script We need to instruct InnoSetup to add dotnetfx.exe to the installer. We will want to have this file available during the installation as a temporary file that is deleted at the end of the installation. Files that are part of the installation are listed in the [Files] section of the installer script. We specify a source and destination for each file, along with special flags that dictate the behavior of the file. In our case the source is support/dotnetfx.exe, the destination is a special target called {tmp} that maps to a temporary directory on the target machine: Source: support\dotnetfx.exe; DestDir: {tmp}; Flags: deleteafterinstall

This line ensures that the MSI installer for the .NET Framework will be available to the installer. Executing the Installer Now that the installer is part of the installation package, we need to add a call to the installation script to run it before the installation completes.

We can run applications during the install process by adding them to the [Run] section of the install script. We can specify parameters to pass to the application, along with a working directory and a status message to display while the application runs: Filename: {tmp}\dotnetfx.exe; Parameters: "/q"; WorkingDir: {tmp}; StatusMsg: Installing DotNET Framework 2.0

The parameter we are passing to dotnetfx.exe instructs it to be quiet during the installation and thus not bother the user as much.

Adding Connector/NET The last file needed for our application is the assembly file for Connector/NET, named MySql.Data.dll. The assembly file can be found in the Connector/NET installation directory (typically C:\Program Files\MySQL\MySQL Connector NET 1.0.X). Copy the dll file to your \Support directory and add the following line to your install script: Source: support\MySql.Data.dll; DestDir: {app}

This will add the dll file to your application install directory.

Compiling the Installer Now that we have added the .NET Framework and Connector/NET to the installer, we can proceed with the install. Before we do, here is an example of what your install script may look like: [Setup] AppName=In-Out AppVerName=In-Out 1.0 AppPublisher=Mike Hillyer AppPublisherURL=http://www.openwin.org AppSupportURL=http://www.openwin.org AppUpdatesURL=http://www.openwin.org DefaultDirName={pf}\In-Out DefaultGroupName=In-Out OutputDir=output OutputBaseFilename=in-out-setup Compression=lzma SolidCompression=yes [Tasks] Name: desktopicon; Description: {cm:CreateDesktopIcon}; GroupDescription: {cm:AdditionalIcons}; Flags: unchecked Name: quicklaunchicon; Description: {cm:CreateQuickLaunchIcon}; GroupDescription: {cm:AdditionalIcons}; Flags: unchecked [Files] Source: Source: Source: Source:

..\In-Out\bin\In-Out.exe; DestDir: {app}; Flags: ignoreversion ..\In-Out\bin\In-Out.exe.config; DestDir: {app}; Flags: ignoreversion support\dotnetfx.exe; DestDir: {tmp}; Flags: deleteafterinstall support\MySql.Data.dll; DestDir: {app}

[Icons] Name: {group}\In-Out; Filename: {app}\In-Out.exe Name: {group}\{cm:UninstallProgram,In-Out}; Filename: {uninstallexe}

Name: {userdesktop}\In-Out; Filename: {app}\In-Out.exe; Tasks: desktopicon Name: {userappdata}\Microsoft\Internet Explorer\Quick Launch\In-Out; Filename: {app}\InOut.exe; Tasks: quicklaunchicon [Run] Filename: {tmp}\dotnetfx.exe; Parameters: "/q"; WorkingDir: {tmp}; StatusMsg: Installing DotNET Framework 2.0 Filename: {app}\In-Out.exe; Description: {cm:LaunchProgram,In-Out}; Flags: nowait postinstall skipifsilent

You can begin compiling the installer by choosing the Compile option from the Build menu of InnoSetup. The progress of the compile will be displayed in the bottom window of InnoSetup. Once the compile is complete we can test the installer.

Testing the Installer To test the installer, choose the Run option from the Run menu of InnoSetup. As you work through the installer debug information will be presented in the bottom window of InnoSetup. If your installer works without error during this test, the next step will be to test the installation on a ‘clean’ machine that you have not installed your application on before and which does not have the .NET Framework installed. If your application works properly on the clean machine you can be fairly certain that it will run properly on the target platform represented by that machine. If you expect to deploy on other versions of Windows, you may want to try installing your application on clean machines running that version of Windows.

Improving the Installer There are some minor improvements that could be made to our installer, including the addition of support for downloading the .NET Framework files only if needed, and adding different install types such as Minimal, Full, and Custom. While these changes will not be covered in this article, the InnoSetup documentation is quite good and should help you make such changes if you so desire.

Conclusion We now have a working installer for our application, allowing us to distribute the application to our users. The installer deploys our application, the .NET Framework, and Connector/NET, allowing the application to be deployed on any number of desktops, all with a versatile, Open Source installer that ships a single executable. for installation. The source code for this project is available at http://www.vbmysql.com/wp-content/uploads/vb-mysqltutorial-part-7.zip. This will be the last regular article in this tutorial series, I hope it has proven of use to you in learning the basics of working with Visual Basic.NET and MySQL. As I continue development of the application covered in these articles, I will write individual articles covering single subjects such as the system tray, creating configuration screens, and using version control.