Simple calendar reminder application in WPF and MS SQL Server 2005

• Download soursefiles - 1.04 MB

Introduction

First, talk a little about the still very new technology WPF (Windows Presentation Foundation). The main innovation is the possibility of a clear division of programming into two stages.
1. design interface (now it can do directly with the designer tools Microsoft Expression Studio (MES), possesses a rich functionality, but in Visual Studio remains the mechanism for creating / editing interface).
2. implementation of the planned functionality.
Just WPF has a new graphics core, built on DirectX, and not on GDI / GDI + as it was before, etc. More details can be found here http://msdn.microsoft.com/en-us/library/ms754130.aspx.
The program interface is made using the MES, such as Microsoft Expression Blend 3, which significantly reduced the time creating an interface, and allow maximum focus on the development of functional programs. As the data warehouse was selected Microsoft SQL Server 2005, because at the time of writing the program, I understood it to him. As an alternative - Access, which can run locally without a server, because NET Framework includes driver support tasks without installing the medium itself, or XML data representation. But more about that another time.

Preparation

The program will require:

1. database (include in zip)

2. executable program

First we need to prepare SQL Server (assumed that it is already installed in the system), for ease of interaction, I use Microsoft SQL Server Management Studio Express (build 9.00.2047.00). So, let's begin.

To get started - login.

At the time of writing was only the Russian version shell, but the layout is the same, so that the problems should not be. To simplify the search will result in a description in English. Continue. In the list on the left side, locate the folder «Databases», the context menu, choose «Restore Database».

In the dialog that opens in the place (1) specify the database name (in this case, you must enter RemembersDB), which will be restored. It will be located on the server and in the future, we will use it in the program, but more on that later. Next, we change (2) value, select the value «From device» and click (3).

Specify the path to the backup database RememberBD.bak (You can find it in the attached files). It should look like this:

We note the basis for recovery (1) and click (2). Now, after the deployment of the database on the server we have the following structure:

Using code

Now about the program. It includes 3 of the form (main, for editing and output), as well as a number of classes to connect with the base, minimize to tray, etc. Let's look in more detail on the class OperateMyDB.cs it is fundamental to the program, is responsible for the operation to the database. Let's start with the methods of the class 3: insert, delete and update rows.

/// <summary>
/// Insert row
/// <summary>
public static bool InsertRow(string text, DateTime dt_rem)
        {
            SqlConnection myConnection = new SqlConnection(Resources.ConnectionString);
            SqlCommand myCmd = new SqlCommand("Ins", myConnection);
            myCmd.CommandType = CommandType.StoredProcedure;
            myCmd.Parameters.Add(new SqlParameter("@TextRemm", SqlDbType.NVarChar, 500)).Value = text;
            myCmd.Parameters.Add(new SqlParameter("@DateITime", SqlDbType.DateTime, 8)).Value = dt_rem;
            try
            {
                myConnection.Open();
                myCmd.ExecuteNonQuery();
                myConnection.Close();
                return true;
            }
            catch { return false; }
        } 

Deleting and updating occurs on a similar scheme: the creation of the connection string (the string stored in the resource - «Data Source =. \ SQLEXPRESS; Initial Catalog = RemembersDB; Integrated Security = True»), call the appropriate command (using stored procedures, pre-configured on the server) , setting the parameters (if necessary) the query. Request for insertion on the server:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO

ALTER Procedure [dbo].[Ins]
     @TextRemm nvarchar(max),
     @DateITime datetime
AS
INSERT INTO Data (TextRemm, DateITime) values (@TextRemm, @DateITime)

The class contains three properties: get all data (in the form of DataTable), get active text (provided that the time of the event occurred) and the calculation of the number of rows. Let us examine the getting active text

/// <summary>
/// Get full data from db
/// </summary>
public static DataTable GetData
{
    get
    {
         DataTable dt = new DataTable();
         if (CountRow > 0)
         {
              SqlConnection myConnection = new SqlConnection(Resources.ConnectionString);
              SqlCommand myCommand = new SqlCommand("Read", myConnection);
              myCommand.CommandType = CommandType.StoredProcedure;
              try
              {
                  myConnection.Open();
                  SqlDataAdapter sda = new SqlDataAdapter(myCommand);
                  sda.Fill(dt);
                  myConnection.Close();
               }
               catch { }
          }
          return dt;
     }
}

/// </summary>
/// Read and play
/// </summary>
public static string ReadandPlay
{
    get
    {
         string text = String.Empty;
         DataTable dt = GetData;
         try
         {
              for (int i = 0; i < dt.Rows.Count; i++)
              {
                   DateTime data = (DateTime)dt.Rows[i]["DateITime"];
                   if ((data.Hour == DateTime.Now.Hour) && (data.Minute == DateTime.Now.Minute)
                      && (data.Second == DateTime.Now.Second) && (data.Day == DateTime.Now.Day)
                      && (data.Month == DateTime.Now.Month) && (data.Year == DateTime.Now.Year))
                    {
                        text = dt.Rows[i]["TextRemm"].ToString();
                        DeleteRowNumber((int)dt.Rows[i]["NumberID"]);
                    }
                    else { return string.Empty; }
               }
                    return text;
          }
          catch { return string.Empty; }
     }
}

Used for treatment of the column name, the structure - NumberID, TextRemm, DateITime. Principle - a timer with each second test is performed the function call ReadandPlay, which returns the text if the event occurred (then the string is removed), otherwise an empty string.

In the present program flow verification outdated reminders, implemented in the above-described functions and properties.

/// </summary>
/// Search old remembers
/// </summary>
public static void ScaningBD()
{
     ArrayList old = new ArrayList();
     DataTable data = OperateMyDB.GetData;
     //search old
     for (int i = 0; i < data.Rows.Count; i++)
     {
          DateTime dt = (DateTime)data.Rows[i]["DateITime"];
          if (dt < DateTime.Now)
               old.Add(data.Rows[i]["NumberID"]);
     }
     //delete
     if (old.Count > 0)
     {
          if (MessageBox.Show("In DB finding old records, delete?", "Info", 
                    MessageBoxButton.OKCancel, MessageBoxImage.Question) == MessageBoxResult.OK)
          {
              foreach(object obj in old)
                 OperateMyDB.DeleteRowNumber((int)obj);
                    MessageBox.Show("Records delete successful", "Info", MessageBoxButton.OK, MessageBoxImage.Information);
          }
      }
      //find next date
      if (OperateMyDB.CountRow > 0)
      {
           data = OperateMyDB.GetData;
           for (int i = 0; i < data.Rows.Count; i++)
           {
                DateTime dt = (DateTime)data.Rows[i]["DateITime"];
                if (NextActualDate > dt && dt > DateTime.Now)
                     NextActualDate = dt;
           }
       }
       else
            NextActualDate = DateTime.MinValue;
}

I hope article will be useful.

History