1
Posted August 13, 2012 by George in C#
 
 

How to Code a Personal Scheduler in C#

schedule
schedule

It is often hard to keep your schedule on track, but with a few coding know-how you will be able to keep your schedule and tasks up to date. I will be teaching you today how to make a simple Personal Scheduler using the C# programming language.

Now before I start teaching you any codes, you should have a Microsoft Visual C# 2010 Express, or a Microsoft Visual Studio program installed on your computer. Now as for the data to be stored, you should have a Microsoft Access Database tool included in you Microsoft Office package.

First, create a database on Microsoft Access and name it scheduler. Add a new table and name it notes with two columns, namely dates and tasks. Save that file and proceed into the Microsoft Visual C# window and create a new Windows Forms Application.

Drag a monthCalendar object, a ListView object and 3 buttons each with a name Add, Delete and Exit.

To connect the database, Add a New Data Source and make sure that you pick Microsoft Access Database File as your Data Connection. Pick your Database file and allow it to be included into the project folder so that we can access it without specifying a file path. Include all the tables we have created and you are now ready to start coding.

To be able to use your data sources and modify it using the objects in Form1, you should first specify it in the source code. The great thing about using C# is that there are a lot of available tools that we can use. Keywords and their functions are already defined in many namespaces and in this case the keywords we will be using are defined inside the OleDb namespace.


//Add this just below using System.Windows.Forms;

using System.Data.OleDb;

Then define these variables in Form1 so that we will be able to use them universally. Also these variables should be defined in any Form where you plan to retrieve, modify, and use the data inside the database.


//This should be above public Form1()

private OleDbConnection mycon;

private OleDbDataReader dr = null;

private OleDbCommand cmd = null;

 

//And below that we will be specifying the connection we’ll be using

mycon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=scheduler.accdb");

          

 

The mycon variable is the connection we will be using throughout the project so keep this in mind. As By specifying the data source like this Data Source=scheduler.accdb we let the program know that the database file is included in the project folder. This will also prevent any errors to pop-up once we publish the program.

So let’s start using these variable.

First, we should let the user Add a new task on a specified date. So double click the Add button and paste the following code.


mycon.Open();

 

DateTime dateselected = Convert.ToDateTime(monthCalendar1.SelectionRange.Start);

string cmdstr = "SELECT * FROM notes WHERE dates = #" + dateselected + "# ";

            cmd = new OleDbCommand(cmdstr, mycon);

            dr = cmd.ExecuteReader();

            string[] tasks = new string[3];

           

            while (dr.Read())

            {

                tasks[1] = (dr["tasks"].ToString());

              

            }

            dr.Close();

            mycon.Close();

 

            Form2 window = new Form2(dateselected, tasks[1]);

            window.Show();

We open the connection by mycon.Open(); and close it by replacing Open(); with Close();. This is important to note since we will not be able to add, delete, or update anything from the database if the connection is closed. So before we execute any SQL Statement, which are the SQL codes used to modify the contents of the database, we should first open the connection.

So to let the user input their tasks into a particular date, we should first specify what date the user has selected, DateTime dateselected = Convert.ToDateTime(monthCalendar1.SelectionRange.Start);, and use that to see if there are any pre-defined tasks in that date through checking it in the database. So to do that, We must “select” it from the database and since the connection is already open, we can go ahead and use the other pre-defined OleDb variables. The variable cmd is the command used to manipulate the database and we specify what kind of manipulations are done through the cmdstr, which is the string of commands, particularly the SQL statement, “SELECT * FROM notes WHERE dates = #” + dateselected + “# “;. So before the datareader, dr, executes the commands we should prepare a container where we will save the retrieved data so we can use it without accidentally modifying the database contents, string[] tasks = new string[3]. While the datareader is reading the contents of the database, it is using the SQL statement to retrieve only the pieces of data that we need, which are the tasks, if there are any, on that specified date and store it in string tasks so we can pass it on to the next windows forms. Make sure that you close the datareader and connection before doing anything else once you retrieved all the data that you need.

Create the next windows forms and drag in a dateTimePicker and a richTextBox as well as two buttons named UPDATE and EXIT.

So to pass it from Form1 to Form2 we must define what data types are to be passed onto Form2. Define the date and task displayed onto the next form so you’ll be able to see if there are any tasks defined on the specified date and modifies them.


//Define these above public Form2

DateTime datedisplayed;

string tasksdisplayed;

 

//then add DateTime dateselected, string tasks inside the parenthesis on public Form2()

public Form2(DateTime dateselected, string tasks)

{

            datedisplayed = dateselected;

            tasksdisplayed = tasks;

mycon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=scheduler.accdb");

 

}

This would let you accept the data from Form1, and display them on Form2, through declaring the data on the dateTimePicker and richTextBox once Form2 loads.


  private void Form2_Load(object sender, EventArgs e)

        {

            dateTimePicker1.Enabled = false;

            dateTimePicker1.Text = datedisplayed.ToShortDateString();

            richTextBox1.Text = tasksdisplayed;

        }

We disabled the dateTimePicker so that any user won’t mess up and pick another date which would be a problem is we want to update or add any task on another date.

So now you can see the tasks on a specified date if there are any. If you would like to update or add any task, double click the UPDATE button and add the following code:


mycon.Open();

            string cmdstr = "SELECT * FROM notes WHERE dates = #" + Convert.ToDateTime(dateTimePicker1.Text) + "#";

            cmd = new OleDbCommand(cmdstr, mycon);

            dr = cmd.ExecuteReader();

           if (dr.HasRows == false)

                {

                    string cmdstr3 = "INSERT INTO notes([dates],[tasks]) VALUES(#" + Convert.ToDateTime(dateTimePicker1.Text) + "#,'" + richTextBox1.Text + "')";

                    cmd = new OleDbCommand(cmdstr3, mycon);

                    cmd.ExecuteNonQuery();

                  

                }

 

            if (dr.HasRows == true)

                {

                    string cmdstr2 = "UPDATE notes SET tasks = '" + richTextBox1.Text + "' WHERE dates = #" + Convert.ToDateTime(dateTimePicker1.Text) + "#";

                    cmd = new OleDbCommand(cmdstr2, mycon);

                    cmd.ExecuteNonQuery();

                  

                }

                dr.Close();

                mycon.Close();

As you can see, we have two other SQL statements used which are UPDATE and INSERT. Respectively, we will use INSERT if there are no tasks on the specified date and UPDATE it to add more tasks. So now we need to SELECT the date on the dateTimePicker to let the program know what to do if there are tasks or not. The code dr.HasRows checks if the selected date from the database is already in the database since it will not exist in the database if it wasn’t added. It is a Boolean method which returns true if there are two or more rows retrieved from the database and otherwise if none. By using this we let the program know the actions to be done on the database if there are tasks on a date. Unlike SELECT, we don’t need a datareader to execute the INSERT and UPDATE commands so we only need to Execute it as a Non Query, cmd.ExecuteNonQuery();.

After this, the user can exit Form2 and get back to Form1 through clicking the EXIT button. Add these codes on the Exit Button:


this.Close();

Getting back to Form1, we should allow the program to display the tasks on the current date as Form1 Loads.


private void Form1_Load(object sender, EventArgs e)

        {

            mycon.Open();

            string cmdstr2 = "SELECT * FROM notes WHERE dates = #" + monthCalendar1.TodayDate + "#";

            cmd = new OleDbCommand(cmdstr2, mycon);

            dr = cmd.ExecuteReader();

            while (dr.Read())

            {

                tasks[1] = (dr["dates"].ToString());

                tasks[2] = (dr["tasks"].ToString());

                this.listView1.Items.Add(new ListViewItem(new string[] { tasks[2] }));

            }

            dr.Close();

            mycon.Close();

        }

 

We should also let the user know which dates already has schedules/tasks defined in them.


//Paste these codes before you close dr.

string cmdstr = "SELECT * FROM notes";

            cmd = new OleDbCommand(cmdstr, mycon);

            dr = cmd.ExecuteReader();

            string[] tasks = new string[3];

 

            while (dr.Read())

            {

                if (dr.HasRows)

                {

                    tasks[1] = (dr["dates"].ToString());

                    tasks[2] = (dr["tasks"].ToString());

                    DateTime dates = Convert.ToDateTime(tasks[1]);

                    monthCalendar1.AddBoldedDate(dates);

 

                }

            }

The code monthCalendar1.AddBoldedDate(dates); uses the dates selected from the database that have predefined tasks, but this method only accepts DateTime variables so we first have to convert the string dates into DateTime through DateTime dates = Convert.ToDateTime(tasks[1]);

Next, for a more interactive feature, we let the user see what tasks are defined in those bolded dates. So select the monthCalendar object and click the Events button beside the properties button on the lower right portion of the screen. Then, look for DateChanged, which represents the event when the user range of dates defined on the monthCalendar control changes due to user selection. Double click that and add the following code


mycon.Open();

            DateTime dateselected = Convert.ToDateTime(monthCalendar1.SelectionRange.Start);

            string cmdstr = "SELECT * FROM notes WHERE dates = #" + dateselected + "# ";

            cmd = new OleDbCommand(cmdstr, mycon);

            dr = cmd.ExecuteReader();

            string[] tasks = new string[3];

            listView1.Clear();

            while (dr.Read())

            {

                tasks[1] = (dr["tasks"].ToString());

                this.listView1.Items.Add(new ListViewItem(new string[] { tasks[1] }));

 

            }

            dr.Close();

            mycon.Close();

 

This code, monthCalendar1.SelectionRange.Start, specifies the beginning date that the user has selected. Now the program would use that date to select the tasks from the database and display it on the ListView through this.listView1.Items.Add(new ListViewItem(new string[] { tasks[1] }));.

Lastly, the user should be allowed to delete the entire task list for a day easily from Form1. So double click the DELETE button and add the following code which would use the date selected by the user and delete the entire row of data on the database which contains the specified date.


mycon.Open();

            DateTime dateselected = Convert.ToDateTime(monthCalendar1.SelectionRange.Start);

            string cmdstr = "DELETE * FROM notes WHERE dates =#" + dateselected + "# ";

            cmd = new OleDbCommand(cmdstr, mycon);

            cmd.ExecuteNonQuery();

            mycon.Close();

 

This sums it all up for this tutorial. Hopefully, you have followed the simple steps and successfully made the personal scheduler on C#. Now if you would want a more intensive scheduler with Time, you should add a new column on the database as well as the variables included in the ListView objects and the variables used on the database and I leave it to your expertise to improve this simple scheduler.


George