Become an Expert Part 1: Create a Sales Management Application using ComponentOne Studio for WinForms

Introduction

Visual Studio provides a number of standard controls, but every so often you may wish you could do something more. Most of us developers are aware that trying something complicated with standard controls means a lot of time will be spent on coding.

ComponentOne Studio for WinForms (hereafter Studio for WinForms) offers a number of useful components that implement enhanced functionalities which are not included in standard controls. These are useful as standalone components, but with a little bit of effort, one can combine the features of a number of these components and create amazing applications in minutes.

In this series, we will make use of a Sales Management application to show how easily you can "Become an Expert". The first step in this application is creating a table to display sales data. The basic structure of this application will be created using the C1FlexGrid control (included in Studio for WinForms).

Part 1 Overview

Microsoft Visual Studio contains a number of standard controls. ComponentOne Studio provides easy to use controls with enhanced features which can be used in Visual Studio along with standard controls. In this series, we make use of these controls to create a Sales Management Application. In the first part of this series, we create a table to display sales data using C1FlexGrid (a control in ComponentOne Studio for WinForms) as the base.

Target Audience

  • Those who have created or want to create Windows applications in .NET.
  • Those who find standard controls somewhat limited in their usage.
  • Those who have some preliminary knowledge of databases.

Environment

  • Visual Studio 2005 or 2008
  • SQL Server 2005

Set-up the Application

Before you start, you'll need to have ComponentOne Studio for WinForms downloaded and installed. This includes the C1FlexGrid control.

Points to Remember: This application has been created in Visual Studio 2008. Installation of .NET Framework 3.5 is a prerequisite for running the provided sample. We will also be creating a SQL Server database, however you can skip over this section to create an unbound application.

  1. Download Studio for WinForms at http://www.componentone.com/SuperProducts/StudioWinForms/.

  2. Create a WinForms application in Microsoft Visual Studio. Open Microsoft Visual Studio 2008, and select "File | New | Project" from the main menu. For this article, select C# as the language and select .NET Framework 2.0. Name the project "Sales Management App" in the Name field and click the OK button.**

    Note: C1FlexGrid** is supported under the 2.0 or 3.x Frameworks and can be used in either Visual Studio 2005 or 2008 environments. You can choose the Framework and the environment. The code in this tutorial will be in C#.

  3. Drag and drop C1FlexGrid from your Toolbox onto your page.
  4. Drag and drop a DateTimePicker to the form and place it in the top right corner of Form1.
  5. Set the Format property of the DateTimePicker to Custom and set the Custom Format property to "yyyy/MM."
  6. Set the Size of the DateTimePicker to Width=96, Height=20.
  7. Give Form1 a Title of "Sales Management" by settings its Text property.
  8. In Form1.cs, add the following line of code above the namespace. This will give us direct access to the SqlClient library for connecting to our database.

using System.Data.SqlClient;

Create the User Interface

Now let's decide on the content of our application. A Sales Management application should contain the following items:

  • Date
  • Proceeds
  • Payments (Cost)
  • Gross Margin
  • Gross Margin Rate

Let's create a table to display these 5 items.

By default, C1FlexGrid consists of 50 rows × 10 columns. Since we need only 5 columns in our application, we will set Cols.Count property (located under the Cols node) to 5 in the FlexGrid property window. We do not need any rows in the grid at the start of the application, so we will set the Rows.Count property to 1.

Setting up the Columns

We need to label each of our five columns. To do this we can simply select a column and modify the Column Caption property using the smart tag. Name the five columns Date, Proceeds, Payments, GrossMargin, and GrossMarginRate.

Setting Input Constraints

Next we want to format the "Proceeds" and "Payments" columns to allow only numeric input. To set input constraints and captions for each column, we can make the desired changes by simply selecting a column and modifying the settings using the smart tag.

Select the Proceeds column, and click the smart tag to open the C1FlexGrid Tasks menu. Locate the Format Strings textbox and click the ellipsis button. This opens the "Format String" dialog box. Select "Currency" as the "Format type".

Repeat the steps above for the Payments column.

The GrossMargin and GrossMarginRate columns will contain computed values; therefore, we won't allow editing for these columns.

Select the GrossMargin column, and click the smart tag to open the C1FlexGrid Tasks menu. Uncheck the "Allow Editing" option. Repeat the steps for the GrossMarginRate column.

To sum up, only numeric values can be entered in "Proceeds" and "Payments" columns while input is disabled in other columns.

So by simply setting a few properties, we can restrict the entry of certain type values in some columns and prohibit entering any values in other columns.

Calculating Values

Next, let's calculate the values of GrossMargin and GrossMarginRate columns. To do this, add C1FlexGrid's AfterEdit event to the application. This event fires after the cell has been edited. Add the following code:

double proceeds = this.c1FlexGrid1[e.Row, 1] == null ? 0 : double.Parse(this.c1FlexGrid1[e.Row, 1].ToString()); double payments = this.c1FlexGrid1[e.Row, 2] == null ? 0 : double.Parse(this.c1FlexGrid1[e.Row, 2].ToString()); double grossMargin = proceeds - payments; this.c1FlexGrid1[e.Row, 3] = grossMargin; this.c1FlexGrid1[e.Row, 4] = proceeds != 0 ? grossMargin / proceeds : 0;

Values of Proceeds and Payments columns are converted to Double to calculate the GrossMargin. Null value is converted to 0. Subtract Payments from Proceeds to get GrossMargin amount (GrossMargin = Proceeds - Payments). GrossMarginRate is calculated by dividing GrossMargin with Proceeds (GrossMarginRate = GrossMargin/Proceeds). When the value in the Proceeds column is 0, GrossMarginRate is also changed to 0.

This is how we calculate GrossMargin and GrossMarginRate after the values in Proceeds and Payments columns have been entered.

Setting Date

Now let's see how we can set dates in the fixed column for each row. Of course it is possible to enter the date every time, but for our convenience, let's try setting the date automatically. Month and year are set to display the date on a monthly basis.

Let's use the DateTimePicker to set the date and month. Add the following code to dynamically change the row count of FlexGrid and values of the fixed column to the ValueChanged event of DateTimePicker.

This.setDateCell();

Now we need to add the setDateCell method which deletes all rows excep the fixed row and adds rows with the selected date.

`private void setDateCell()
{
this.c1FlexGrid1.Rows.RemoveRange(1, this.c1FlexGrid1.Rows.Count - 1);

DateTime startDate =
new DateTime(this.dateTimePicker1.Value.Year,
this.dateTimePicker1.Value.Month,
1);

DateTime value = startDate;

while (value.Month == startDate.Month)
{
this.c1FlexGrid1.AddItem(value.ToShortDateString());
value = value.AddDays(1);
}
}`

By using this simple piece of code, we can add the date in the heading of each row and also add and delete rows.

Let's add a couple lines of code to the Form's Load event so that the C1FlexGrid is displaying some dates before we even select one.

this.dateTimePicker1.Value = DateTime.Now; this.setDateCell();

Loading and Registering Data

Now let's try registering the entered data and loading this registered data into the grid. A 'Sales' table has to be created for registering the sales data. You can use the DBScript that's included with the downloaded sample to generate the sales table.

Add a button to the form with the text "Register," and in its click event write the method for registering the data to the database.

The method of registering data to the database and loading it to the grid has been summarized below (implement steps 1-7 in order). The following code should be placed in the Register button's click event.

  1. Create an instance of SqlConnection`

    SqlConnection connection = new SqlConnection(this.getConnectionString());`

  2. Open database connection`

    connection.Open();
    `

  3. Create an instance of SqlCommand

    SqlCommand command = new SqlCommand();

Set SQL command command.Connection = connection; command.CommandType = CommandType.Text;

  1. Delete data by executing SQL commands

`DateTime selectedMonth
= new DateTime(this.dateTimePicker1.Value.Year, this.dateTimePicker1.Value.Month, 1);

command.CommandText =
string.Format("DELETE FROM Sales WHERE Date >= '{0}' AND Date < '{1}'",
selectedMonth,
selectedMonth.AddMonths(1));
command.ExecuteNonQuery();

for (int i = 1; i < this.c1FlexGrid1.Rows.Count; i )
{
if (this.c1FlexGrid1[i, 1] != null
|| this.c1FlexGrid1[i, 2] != null)
{
string insert =
string.Format("INSERT Sales (Date, Proceeds, Payments, GrossMargin, GrossMarginRate) VALUES ('{0}', {1}, {2}, {3}, {4})",
this.c1FlexGrid1[i, 0],
this.c1FlexGrid1[i, 1] == null ? 0 : this.c1FlexGrid1[i, 1],
this.c1FlexGrid1[i, 2] == null ? 0 : this.c1FlexGrid1[i, 2],
this.c1FlexGrid1[i, 3],
this.c1FlexGrid1[i, 4]);

command.CommandType = CommandType.Text;
command.CommandText = insert;
command.ExecuteNonQuery();
}
}
`

  1. Dispose command

command.Dispose();

  1. Close database connection

`connection.Close();
connection.Dispose();

MessageBox.Show("Data has been written to the database.");`

We used the following SQL command to delete records.

DELETE FROM Sales WHERE Date >= 'Start date of the month' AND Date < 'Start date of the next month'

The range of dates selected in the table for sales data are specified in the WHERE clause. By setting a single month for the dates in the WHERE clause, we prevented registering data on a date that already existed in the table.

We registered the sales data entered in C1FlexGrid using the following SQL command.

INSERT Sales (Date, Proceeds, Payments, GrossMargin, GrossMarginRate) VALUES ('Date', 'Proceeds', 'Payments', 'GrossMargin', 'GrossMarginRate')

The data from C1FlexGrid will be set as it is. Null values are acceptable in the "Proceeds" and "Payment" columns. They are registered as 0 in the database.

Getting Records

Let's check the registered data. We will add another button to our Form and call it "Load." In the Load button's click event, add the following code:

`SqlConnection connection = new SqlConnection(this.getConnectionString());

// Open database connection.
connection.Open();

// Generate an instance of SqlCommand from SqlConnection.
SqlCommand command = connection.CreateCommand();

DateTime selectedMonth
= new DateTime(this.dateTimePicker1.Value.Year, this.dateTimePicker1.Value.Month, 1);

string selectSQL
= string.Format("SELECT Date, Proceeds, Payments, GrossMargin, GrossMarginRate FROM Sales"
" WHERE Date >= '{0}' AND Date < '{1}'"
" ORDER BY Date", selectedMonth, selectedMonth.AddMonths(1));

// Set the SQL command to be executed.
command.CommandText = selectSQL;

// Execute the specified SQL command and create a SQLDataReader.
SqlDataReader reader = command.ExecuteReader();

// Dispose command.
command.Dispose();

// Move to the next record.
while (reader.Read())
{
DateTime dbDate = Convert.ToDateTime(reader["Date"]);

this.c1FlexGrid1[dbDate.Day, 1] = reader["Proceeds"];
this.c1FlexGrid1[dbDate.Day, 2] = reader["Payments"];
this.c1FlexGrid1[dbDate.Day, 3] = reader["GrossMargin"];
this.c1FlexGrid1[dbDate.Day, 4] = reader["GrossMarginRate"];
}

// Close reader.
reader.Close();

// Close database connection.
connection.Close();
connection.Dispose();

MessageBox.Show("Data has been loaded to the grid.");`

Here we are creating a SqlConnection just as we did before when registering the data. We are using the following SQL statement for getting the records.

SELECT Date, Proceeds, Payments, GrossMargin, GrossMarginRate FROM Sales WHERE Date >= 'Start date of the month' AND Date < 'Start date of the next month' ORDER BY Date

Only the date is added initially, and then the corresponding data for a particular date is added in the other columns. Therefore, data retrieved is based on the sorted date column.

The following screenshot shows the sample application for registering the data.

Conclusion

In Part 1 we have introduced Studio for WinForms and have explained the usage and features of C1FlexGrid. Use C1FlexGrid to create a simple data list display. Although the sample shown here is a Sales Management Application, with a few minor changes it can also be used as an accounting application.

In Part 2, we will use more features from C1FlexGrid and use the grid with other ComponentOne controls to create a feature rich application.

Download Part 1 Sample Project

Acknowledgements

ComponentOne Product Manager Greg Lutz

Greg Lutz

comments powered by Disqus