How to Create Inclusive Dates Report in C#

Submitted by janobe on Wednesday, December 5, 2018 - 18:48.

This time, I will teach you how to create inclusive dates report in c#. I based this on my last tutorial which is how to create a Daily, Weekly and Monthly Report in C#. When you create a report this program will help you a lot because this will retrieve the previous months transactions. And this can also retrieve the previous weeks and specific dates of transactions. So let's begin:

Creating Database

Create a database named “dbtransaction”. Execute the following query for creating table and adding data in the table that you have created.

CEATE TABLE `tbltransaction` (
  `TRANSID` int(11) NOT NULL,
  `ORNO` int(30) NOT NULL,
  `TRANSDATE` date NOT NULL,
  `AMOUNTSALE` double NOT NULL,
  `CASHIER` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `tbltransaction`
--
 
INSERT INTO `tbltransaction` (`TRANSID`, `ORNO`, `TRANSDATE`, `AMOUNTSALE`, `CASHIER`) VALUES
(1, 70004, '2018-12-03', 385, 'Janno Palacios'),
(2, 70005, '2018-12-03', 385, 'Janno Palacios'),
(3, 70002, '2018-12-01', 385, 'Janno Palacios'),
(4, 70001, '2018-12-01', 385, 'Janno Palacios'),
(5, 70006, '2018-12-04', 69, 'Janno Palacios'),
(6, 70007, '2018-12-04', 69, 'Janno Palacios'),
(7, 70003, '2018-12-02', 138, 'Janno Palacios');

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application for c#. psFormDates

Step 2

Do the form just like shown below. psFROMDates1

Step 3

Add a namespace to access MySQL libraries.

using MySql.Data.MySqlClient;

Step 4

Create a connection between C# and MySQL database. After that, initialize all the classes and variables that are needed.

 MySqlConnection con = new MySqlConnection("server=localhost;user id=root;password=;database=dbtransaction;sslMode=none");
 MySqlCommand cmd;
 MySqlDataAdapter da;
 DataTable dt;
 string sql;

Step 5

Create a method for retrieving data in the database.

private void loadData(string sql)
        {
            try
            {
                con.Open();
                cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                dt = new DataTable();
                da.Fill(dt);
 
                dtg_list.DataSource = dt;
 
 
            } catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                da.Dispose();
            }
        }

Step 6

Double click the “Search” button and do the following codes for the inclusive dates report.

private void btn_search_Click(object sender, EventArgs e)
{
            sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE DATE(`TRANSDATE`) BETWEEN '" + dtp_from .Text  + "' AND '" + dtp_to .Text   + "'";
            loadData(sql);
            lblTitle.Text = "Report Inclusive Dates from " + dtp_from .Text + " to " + dtp_to .Text ;
 }

Note: The database file is included inside the folder. The complete source code is included. You can download it and run it on your computer For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.

Add new comment

437 views