Reloading a Form in C#

I'm been doing some work recently in C# on a Windows Form I'm creating for a pet project.  I'm creating a form that presents customer information and allows the user to scroll through each customer using "Previous" and "Next" buttons.

While working through the form, I was able to get my data into an array and then present the first customer in the form.  I then figured out how to update the index of the array for the next customer, but I couldn't figure out how to present the next data into the form.  Through my research, I found the following post:

Reloads Form in C#

Scrolling through the answers, one answer says this:

try this

this.formName_Load(this, null);

---

Kapul

I then put this code into my program right after I've updated the index of my array for the next customer.  Magically (not really) the form reloaded with my new data.

I wanted to discuss the different portions of the above code and what it does:

• this - refers to the current instance of the class.  For example, you could have multiple classes that have the same form names and each could be referenced by other classes.  "this" makes sure that you are referencing the current class.
• formName - the name of your form.  For example, my form is called "customerForm", so I would replace formName with customerForm.
• _Load - tells the form to load.  To read more on Load event, go here: Form.Load Event
• (this, null) - The "this" again refers to the current class.  null is used in place of the EventArgs class (which is used to provide data for events).  Since this is an event that doesn't contain any data, we then pass a null, which is an empty field.  To read more on the EventArgs class, go here: EventArgs Class

Calculating a Remainder in SQL

When doing math problems using SQL, it is a good idea to consider what type of data you are working with.  In SQL, you will often deal with a data type called Integer.  With integers, if you do any division, all your answers will come out in whole numbers (i.e. you won't get any decimal numbers back).  For example, if you run the following in SQL, the answer will be 2 instead of 2.5:

select 5 / 2

Clearly you can start to lose data if you are expecting decimal places while dividing integers.  It is often better to convert your integers to a data type that allows for decimals.  For example, you can convert your integer data types to numeric and then perform the division.

select cast(5 as numeric(2,1)) / 2

If you don't want to cast your data to another data type because of possible loss of data, you can also use the Modulo operator (sometimes called Modulus in other programming languages).  To use the Modulo operator, replace the divide sign (/) with the percent sign (%).  Using the same example from above, we will use the Modulo operator:

select 5 % 2

The answer to the above would then be 1, which is the remainder of 5 / 2.

We could then display our data using the following query:

select ' 5 / 2 is ' + cast(5 / 2 as varchar) + ' with a remainder of ' + cast(5 % 2 as varchar)

Which gives us the results of

 5 / 2 is 2 with a remainder of 1

To read more on Modulo, go here: Modulo

Arithmetic Operators in SQL

Yesterday we talked about variables.  Today we will talk about an arithmetic operators that can be applied to your variable.

An arithmetic operator is one that applies some type of math to a value.

Let's first look at an example of code.  For instance, if you wanted to add a 1 onto an integer variable, the most basic way to do this is by doing the following:

set @integer = @integer + 1

Since you want to add 1 onto the original value of your integer, you have to set the variable equal to your current value plus 1.  Using the above, however, requires unnecessary retyping.  A better way to approach this would be to use the Add Equal operator, like so:

set @integer += 1

The above will add one onto the current value of the variable @integer, while only using our variable once.  This gives us less of a chance of a spelling mistake.  The same works for the Subtract Equals (-=), Multiply Equals (*=) and Divide Equals (/=) operators.

If we create different integer variables, we can see how each works

declare @integer1 int = 1
declare @integer2 int = 1
declare @integer3 int = 2
declare @integer4 int = 2

set @integer1 += 1
print @integer1

set @integer2 -= 1
print @integer2

set @integer3 *= 2
print @integer3

set @integer4 /= 2
print @integer4

We would get the following results:

2 (1 + 1 = 2)
0 (1 - 1 = 0)
4 (2 * 2 = 4)
1 (2 / 2 = 1)

As a programmer, it is always better not to have to retype code or variables.  In this case, you are saving yourself from retyping variables.


To read more on Arithmetic Operators, go here: Arithmetic Operators

SQL Variables

It's been a while since the last time I blogged.  After a family reunion and a busy week at work, I've been slow to get back into the blogging side of life.

Today I've decided to start off with a subject that is rather easy.

In SQL, as with most programming languages, you will find something called a variable (it may be called something else in other programming languages).  A variable is a set of data that you give a name to and then can assign values to.  For example, in SQL you could have a variable called @pi (the @ symbol tells SQL that this is a variable) and assign it a value of 3.14.  If you need to use the 3.14 value throughout your program, you can then call @pi instead of 3.14 each time.

You might be thinking to yourself "why would I want to create/use a variable instead of just entering 3.14 every time I need it?"  Well, there are many ways to answer this question, but the best answer is that what would happen if you accidentally entered 3.15 in one instance instead of 3.14?  You could potentially throw off the rest of your program.  A variable allows you to use the same value over again without having to re-type it and therefore not leaving yourself open for mistakes.

There are two steps to creating variables:

1. Declare the variable.  You have to let SQL know that the variable exists and give the variable a data type (int, varchar, datetime, etc.)
2. Set a value to the variable.

So, if I wanted to create my @pi variable, I would do it like this:

DECLARE @pi numeric(5,2)

SET @pi = 3.14

You can also declare and set the variable on the same line:

DECLARE @pi numeric(5,2) = 3.14

We can then take that variable and use it:

@Circumference = 2 * @pi * radius

As you dive more into using variables, you will see they are quite useful.

To read more on SQL variables, go here: Local Variables

Using Multiple Databases in SQL

In SQL Server, you can have multiple databases on the same instance of SQL Server.  In SQL Server 2012, you can have over 32,000 (go HERE to read up more maximum capacity specifications for SQL Server) databases in one SQL Server instance.  So, the question is, how do you use/reference all these databases in the same query.

There are two main ways to accomplish this:

1. The use of USE
2. Reference the database in your FROM statement

First, we can use the word USE.  The general syntax for this is:

USE database

Where database is the database you are referencing.  If our Customer table was in the Billing database, it would look something like this:

USE Billing

SELECT *

FROM Customer

Using USE leaves you rather limited, though, because only the one database can be referenced at a time using USE.

Our other option is to specify the database in our FROM statement.  Let's say, for example, that we have our Customer table in the Billing database, while the Orders table is in the Shipping database.  If we wanted to use both tables in the same query, it would look like this

SELECT *

FROM Billing.dbo.Customer

LEFT OUTER JOIN Shipping.dbo.Orders

on Customer.CustomerNumber = Orders.CustomerNumber

In the above, we put the database, a dot (.), the user (in this case dbo, which means database owner), another dot (.) and then the table name.  Using this method, we can query multiple databases at the same time within the same query.

To read more on USE, go here: USE

To read more on selecting your database, go here: Select a Database

Wildcards while Searching Text in SQL Continued

One part of using wildcards that wasn't covered yesterday was the ability to use the keyword NOT in conjunction with LIKE.  Using an example from yesterday, this returns all customers that start with the letter L:

SELECT *
FROM Customer
WHERE CompanyName LIKE 'L%'

If we put the word NOT before LIKE, we will get all customers that do not start with an L:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE 'L%'

Likewise, we can exclude any customer that has an L in their name:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE '%L%'

The same works with another one of our examples:

SELECT *
FROM Customer

WHERE FirstName LIKE '_ean'

The above returns all customers that have a name that is four digits and ends in "ean".  The following returns any customer not with four digits AND does not end with "ean".  It could still be four digits, but cannot end with "ean".

SELECT *
FROM Customer

WHERE FirstName NOT LIKE '_ean'

Wildcards while Searching Text in SQL

Let's say you are looking for some text in SQL, but not exactly sure what the text is.  For example, you are looking for any customer where their last name starts with an L.  How would you do that in SQL?

There are two ways you can accomplish this.  The first is by using substring like the following:

SELECT *
FROM Customer
WHERE Substring(CompanyName, 1, 1) = 'L'

This would certainly work, but makes your search NonSARGable (to read up more on SARGable queries, go to this post Non-SARGable Queries)

Luckily, SQL has a logical operator called LIKE (which keeps your query SARGable), that allows you to search for text that matches a specific pattern, as follows:

SELECT *
FROM Customer
WHERE CompanyName like 'L%'

In this instance, the percent sign (%) allows for any characters before or after it, depending on where the % is placed.  The above would look for customers that started with an L.  The following would look for all customers that ended with an L:

SELECT *
FROM Customer
WHERE CompanyName like '%L'

This would look for all customers that had an L anywhere in its name:

SELECT *
FROM Customer
WHERE CompanyName like '%L%'

The % is a wild card option in SQL.  There are other wild card options to consider:

• _ (underscore)
• The underscore represents a single value.  For instance, if you are looking for a four letter name that ends in "ean", you could run the following:

SELECT *
FROM Customer

WHERE FirstName like '_ean'

• [ ]
• A single character within a range or given set.  For instance:

--Range of letters

SELECT *
FROM Customer

WHERE FirstName like '[D-S]ean'

--Set of letters

SELECT *
FROM Customer

WHERE FirstName like '[DEFGHS]ean'

• [^]
• A single character not within a range or give set

--Not in this range of letters

SELECT *
FROM Customer

WHERE FirstName like '[^D-S]ean'

--Not in this set of letters

SELECT *
FROM Customer

WHERE FirstName like '[^DEFGHS]ean'

To read more on LIKE and different wild card options, go here: LIKE