C# Tutorial - Task Management App with CRM Database and Excel Funcionality

Ever wanted to try your hand at building a task manager? Today I’ll be showing you how to program your own task management app with a CRM database in C#, it even integrates with excel to make it even easier to keep track of everything!

First up, we need to launch Visual Studio 2015, I’m using the community version for this project and if you don’t have it installed already you can find it here.

Once you’re in the development environment, you’ll need to click ‘New Project’ then select Templates -> Visual C# and Windows Forms Application. This should take you to a blank form design page. Our app will consist of two forms, one for the login screen and another to contain the main controls. Our main form will have a tab control which houses the 3 main sections of the app.

The first thing to do once you’re in design view is the head over to the ‘Solution Explorer’ panel, right click on the project and select ‘Add’ then choose the Windows Forms option. Name this file Main.cs then rename Form1.cs to Login.cs.

As promised in the above video, the controls and coordinates required for each page can be found here.

Ok, so now you’ve hopefully got a layout similar to what you see in the video, the next step is to program the business logic that makes the application run.

First head to Login.cs [Design] and double click on the Submit button. You will be taken to the code view for this form, at the top, insert the namespace:

Using System.Data.SqlClient;

Open up SQL Management Studio (download link can be found here) and create a new database called TicketManager, right click on the database once generated and create a new table called users with the columns userID (as int data type and primary key), username (as varchar) and password (also as varchar), right click on the users table and select ‘Edit Rows’, insert a suitable username and password and hit save.

Now head back to Visual studio and within the btnSubmit_Click event which has been generated we need to write a function which queries our SQL database. To do so, type the following code between the curly braces:

SqlConnection sqlCon = new SqlConnection(@”Your connection string goes here”);

String query = “select * from users where username = ‘” + cbUsername.Text.Trim() + “’ and password = ‘” + tbPassword.Text.Trim() + “’”;

SqlDataAdapter sda = new SqlDataAdapter(query, sqlcon);

DataTable dtbl = new DataTable();

sda.Fill(dtbl);

if(dtbl.Rows.Count == 1)

{

MessageBox.Show(“Welcome to Ticket Manager “ + cbUsername.Text);

Main objMain = new Main;

this.Hide();

objMain.Show();

}

else

{

MessageBox.Show(“Incorrect username or password entered!”);

}

This essentially passes a query to our data table asking it to check if the values inserted into out combobox and textbox correspond with any of the values in the users table, if a match is found then we display the first message welcoming the user to the application, if no match is found then the user instead sees an error message requesting they check their login credentials.

Next we will head into the Main.cs designer and navigate to the Customer Database tab, we need to populate the datagrid with customer information again stored in SQL, we will then run a similar query this time populating our form input fields with information if a valid Client ID is found.

First double click on the save button in the Customer Database Tab, add the following namespaces:

Using System.Data.SqlClient;

Using System.Configuration

Using Microsoft.Office.Interop.Excel;

For the last namespace you will need to open up the NuGet package manager console and type the following command: PM> Install-Package Microsoft.Office.Interop.Excel

Next create a new connection to SQL Server by adding the this line before the main entry point for the application:

SqlConnection con = new SqlConnection(@”Your connection string goes here”);

If you need any assistance finding your connection string please see the video, it can be found by accessing your server properties in the Server Explorer panel.

Go back to SQL server and create another table with the following columns:

ID (primary key, int)

client (varchar)

contact (varchar)

address (varchar)

postcode (varchar)

phone (varchar)

email (varchar)

website (varchar)

Save the table as customerDB and head to the btnSaveClient_Click event in Visual Studio and insert the following:

con.Open();

SqlCommand cmd = con.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = “insert into CustomerDB(ID, client, contact, address, postcode, phone, email, website) Value(@ID, @client, @contact, @address, @postcode, @phone, @email, @website)”;

cmd.Parameters.AddWithValue(“@ID”, tbClientID.Text);

cmd.Parameters.AddWithValue(“@client”, tbClientName.Text);

cmd.Parameters.AddWithValue(“@contact”, tbClientContact.Text);

cmd.Parameters.AddWithValue(“@address”, tbClientAddress.Text);

cmd.Parameters.AddWithValue(“@postcode”, tbClientPostcode.Text);

cmd.Parameters.AddWithValue(“@phone”, tbClientPhone.Text);

cmd.Parameters.AddWithValue(“@email”, tbClientEmail.Text);

cmd.Parameters.AddWithValue(“@website”, tbClientWebsite.Text);

cmd.ExecuteNonQuery();

con.Close();

disp_data();

MessageBox.Show(“Record added”);

tbClientID.Text = “”;

tbClientName.Tex = “”);

tbClientContact.Text = “”;

tbClientAddress.Text = “”;

tbClientPostcode.Text = “”;

tbClientPhone.Text = “”;

tbClientEmail.Text = “”;

tbClientWebsite.Text = “”;

The first line here opens the connection we established before the main entry point, we then create a command which injects the specified values into the specified cells in our data table. We state that these values are to be taken from the user input text boxes, otherwise known as defining the command parameters. After this the connection is closed and the datagrid is refreshed, a message appears to let the user know the record has been added to the table. The last block of code is simply to wipe the values from the textboxes to save the user having to do this manually each time.

We still must write a function which updates the datasource of our datagrid dgvCustomerDatabase and reflects any changes to the table in the datagrid, before doing so it is a good idea to bind the combobox cbTech in the Live Tickets tab to a datasource in order to generate a loader for our Main.cs form so that we can declare disp_data, create another table in the SQL database called techs, add a ID column as primary key and int and a name column as varchar, now insert a name into this table and save.

Once done, return to Live Tickets Tab and select cbTech, click on the arrow in the right and corner and tick the box to make the control data bound, display the tech name in the combobox.

We must then write the following in Main_Load event in the code view for Main.cs:

disp_data();

cbTech.Text = “”;

Now insert the following declaring a new class:

public void disp_data();

{

con.Open();

SqlCommand cmd = con.CreatCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = “select * from customerDB”;

system.Data,DataTable dt = new system.Data.DataTable();

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(dt);

dgvCustomerDB.DataSource = dt;

con.Close();

}

Now we want to go to Live Tickets Tab in the design viewer and click on the search button underneath our form, go to the auto generated event and add this code:

private void btnSearch_Click(object sender, EventArgs e)

{

string sql;

sql = "Select * from customerDB where ID = '" + tbID.Text + "'";

SqlCommand com = new SqlCommand(sql, con);

con.Open();

DataSet data = new DataSet();

var adapter = new SqlDataAdapter(com);

adapter.Fill(data);

int count = data.Tables[0].Rows.Count;

con.Close();

if (count > 0)

{

tbClient.Text = data.Tables[0].Rows[0]["client"].ToString();

tbAddress.Text = data.Tables[0].Rows[0]["address"].ToString();

tbPostcode.Text = data.Tables[0].Rows[0]["postcode"].ToString();

tbPhoneNo.Text = data.Tables[0].Rows[0]["phone"].ToString();

}

else

{

MessageBox.Show("Invalid ID", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

Again we make a connection to SQL and pass a query, this time we check the values in the ID column in customerDB, find the one which is a match and pull the data we want, in this case name, address, postcode and phone number into our textbox fields. This is known as auto-fill. Now we need a way to save the values into our datagrid in the Live Tickets tab, double click on save and insert the following into the event:

dgvLiveTickets.Rows.Add(tbID.Text, tbClient.Text, tbAddress.Text, tbPostcode.Text, tbPhoneNo.Text, cbTech.Text, rtbDescription.Text, tbEstimate.Text, monthCalendar1.SelectionRange.Start.ToShortDateString());

tbID.Text = "";

tbClient.Text = "";

tbAddress.Text = "";

tbPostcode.Text = "";

tbPhoneNo.Text = "";

rtbDescription.Text = "";

cbTech.Text = "";

tbEstimate.Text = "";

}

All we’re doing here is converting the textbox values to suitable data types for the datagrid then adding them as a row, we then clear the form.

Now we need a way to complete tasks on our list and pass the rows over to the datagrid in the second tab, Closed tickets. This is a fairly straightforward procedure however we also want to calculate the totals from each task and input this amount in a texbox. Add this to the btnClose_Click event:

foreach (DataGridViewRow selRow in dgvLiveTickets.SelectedRows.OfType<DataGridViewRow>().ToArray())

{

dgvLiveTickets.Rows.Remove(selRow);

dgvClosedTickets.Rows.Add(selRow);

tbTotal.Text = (from DataGridViewRow row in dgvClosedTickets.Rows

where row.Cells[7].FormattedValue.ToString() != string.Empty

select Convert.ToDouble(row.Cells[7].FormattedValue)).Sum().ToString();

}

}

Now double click on the delete button in Live Tickets tab and copy the following into the event in order to delete the selected row in the datagrid on button click:

foreach (DataGridViewRow selRow in dgvLiveTickets.SelectedRows.OfType<DataGridViewRow>().ToArray())

{

dgvLiveTickets.Rows.Remove(selRow);

}

Almost done, now all we have left to do is to allow our app to communicate with excel and generate a worksheet with values from our closed tickets datagrid and then program our delete and log out buttons. I have only tested this with WPS Excel however I presume it will work fine for most spreadsheet programs including Microsoft Excel of course.

Double click on the Excel button in the closed tickets tab and type in the following in the event handler:

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

Microsoft.Office.Interop.Excel._Worksheet

worksheet = null;

app.Visible = true;

worksheet = workbook.Sheets["Sheet1"];

worksheet = workbook.ActiveSheet;

worksheet.Name = "Exported from Ticket Manager";

for(int i = 1; i < dgvClosedTickets.Columns.Count + 1; i++)

{

worksheet.Cells[1, i] = dgvClosedTickets.Columns[i - 1].HeaderText;

}

for(int i = 0; i < dgvClosedTickets.Rows.Count - 1; i++)

{

for(int j = 0; j < dgvClosedTickets.Columns.Count; j++)

{

worksheet.Cells[i + 2, j + 1] = dgvClosedTickets.Rows[i].Cells[j].Value.ToString();

}

}

workbook.SaveAs("c:\\ClosedTickets.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing,

Type.Missing, Type.Missing);

}

Here we declare that we will be using an excel application and generating a new workbook and worksheet upon button click. We then specify the file name and layout of the cells and tell the program where to take the data from.

To finish up, add functionality to the delete button in the closed tickets tab the same way we did before for the live tickets tab obviously this time we will be changing the datagridview from dgvLiveTickets to dgvClosedTickets.

Now we just need to program our log out buttons by adding some simple code in each button click event:

this.Hide();

Login loginPg = new Login();

loginPg.Show();

And there you have it! A fully functioning task manager with a CRM database and Excel functionality! Join me next time where I’ll be doing a series on web design fundamentals covering HTML, CSS & JavaScript, I’ll even be showing you how to build a stylish landing page for your site! Happy debugging!

View the source code on GitHub.

#CTutorial #programming #programmingtutorials #programminglesons #C #visualstudio #visualstudio2015 #beginnersC #Taskmanagementapp #taskmanager #CRMDatabase #Excel #SQL #SQLserver #NETFramework #computers #computing #technology #windowsformsapplication #windowsforms