Ticketing System by Brandon Vallorosi
Introduction
REPO: https://github.com/Brunts/DatabaseProject
In the world today there is one force driving almost everything we interact on a daily basis: Data. In the age of information data has become increasingly valuable, worth more than precious metals and cash. As the value of data rises so does the need for systems in which to store and manipulate the data. Data Management has proven to be one of the fastest growing fields in computer science and certainly one of the most important. Our whole world runs on data, from our phones capturing our audio input and providing us personalized ads, or going to the doctor and having your information in the system, we are all impacted by data and the greatest problem facing the data world is how to make the data relevant. Data can be largely useless on its own. It has to be put into context and used properly in order to give it value. It’s not so much about having the data but how you use it. These lessons on data can be paralleled to our everyday experiences, when you read a stop sign while driving you’re taking input data and using it in some method to produce some output which is hopefully to stop the car.
The organization of data can appear in many forms, sometimes it’s simple data storage and sometimes there are systems in place that enable easy management. Tools and languages like SQL have changed the way we organize and store data. During this age of information we have seen the advent of Data Manipulation Languages. A data manipulation language (DML) is a family of computer languages including commands permitting users to manipulate data in a database. (4) This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. With just these commands we can make so much happen it’s just a matter of designing a system specialized for handling the data. These systems take on many forms and the most important defining aspect of their design is to answer the question, “What is the data being used for?” This goes hand in hand with the question of “Who is using this data?” . There are many more important questions to ask when designing a system for managing data. When asking these questions for myself I found SQL to be the answer. Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. (5) In order to get some experience managing data I decided to create a database consisting of users and tickets in two separate relational tables and create a complimentary GUI application in C# for managing all aspects of these tables and the database.
When trying to create something practical the main rule is to make it so anybody can use it. C# was attractive for this because of the use of windows forms and the SQL compatibility available. The system I designed and developed is a ticketing database that can be used by users who have an account existing in the system or a user can create one. Basically once a user signs into the database they can go onto edit the available data tables. Making use of two separate data tables there exists a data table for users and all their account information, as well as an additional datatable to create and edit tickets/cases in regards to IT. This application is perfect for keeping track of open issues that require some action or simple documentation. When the application is opened for the first time a user can sign up and proceed to log in. Once logged in the user can edit account details and change their own information or even delete their account. Any changes made are updated to the data table in the SQL database. They’ll also be given the option to view, search for, edit, and create tickets by accessing the ticket menu. These tickets will then be updated, deleted, or created on the cases data table that exists within our SQL database. These two data tables are related in our database, their relationship is through the case owner which we define based on the user. All of these interactions and methods happen in C# which can actually run SQL queries and interface with our database in order to make changes. Creating something simple that anybody can use was the goal and through testing that has been achieved. With a simple installation process, anybody can have their own database up and ready to use in minutes with this ticketing system. Inspired by current technologies like salesforce, these systems are the infrastructure with which the future will be built.
Installation
Required Programs:
-Microsoft Visual Studio
https://visualstudio.microsoft.com/downloads/
-Microsoft SQL Server Management Studio
https://www.microsoft.com/en-us/download/details.aspx?id=42299
Step 1: Download the zip file containing the project files.
Step 2: Download Microsoft SQL Server Management Studio/Visual studio if needed
Step 3: Open SQL Server Management Studio and connect to database engine
Step 4: Once connected right click databases and select New Database
Step 5: Name the database SocialNetwork
Step 6: right click SocialNetwork database and select tasks then import data.
Step 7: click next then select Flat File Source and browse to our data table then select open
Step 8: Hit next twice then select SQL server native client and hit next 4 times then finish and it will import the data table to the data base.
Step 9: repeat steps 6-8 for the Users .csv file as well.
Step 10: open the program solution DatabaseProject.sln in visual studio and hit Start to run the application
User Manual
Sign in page:
-Enter existing Email and Password to login
-Click Create Account to make a new account if you don’t have one already
Sign up page:
-Enter name, email, password, and select a country or enter a new one then click sign up to create your account.
-Hit back to go back to the sign in page.
Main Menu Page:
-Click account detail button to edit your account information or delete account
-Click edit users to access the data table with all users for user management
-Click cases to go to the menu with the case management options
Account Information page:
-Change name, email, password, and country fields.
-Press update account info to push changes to data table and database
-Press delete account to remove your user account from the database and return to signin/sign up menu
-Menu button goes back to main menu
-Logout button goes back to sign in page
User Database Page:
-Edit user information for any available fields except for user ID by double clicking
-Click perform operation to save any changes and see a count of how many changes were made
-Menu button goes back to main menu
-Logout button goes back to sign in page.
-Delete a row by selecting the left most column and press delete on the keyboard.
Cases Menu Page:
-Click edit cases to navigate to edit cases page
-Click create case to navigate to create case page
-Click Search Cases to navigate to search cases page
-Menu button goes back to main menu
-Logout button goes back to sign in page
Edit Cases Page:
-Enter a case number
-When a case number is entered the other fields appear like in the figure below
-You can change the drop down for owner, subject, and edit description.
-You cannot change case number
-Press save changes to commit changes to ticket
-Press delete to remove case from data table
Create Ticket Page:
-Select desired subject from drop down
-Enter description of issue
-Click Create to make the ticket and add it to the data table
Search Tickets Page:
-Select which type of search you want to execute, by Subject, Owner, or case number
-When selecting owner or case number a textbox appears where you enter the case owner or case number you want to search
-When selecting subject a drop down will appear asking to select type of subject
-Press search to show all results of the search parameters
Design
This project was developed using two languages, C# and SQL. Originally the plan was to have system that would enable to user any type of query, using C# GUI options, on a given database but the practicality of such a project was lacking. I thought about turning it into a social network for users to interact but I was inspired by Salesforce to go the route of a ticketing system. This would have only used one single data table and been more C# focused however I wanted to explore the concept of a relational database. A relational database is a collection of information that organizes data in predefined relationships where data is stored in one or more tables (or "relations") of columns and rows, making it easy to see and understand how different data structures relate to each other. One tool I work with almost everyday is Salesforce for managing tickets so I decided to emulate this and focus on key design features to draw inspiration from. The essence of these tools is just database management for organizing work and improving efficiency. The most basic and key feature is user management, this is important for keeping track of who is the case owner and giving unique access to many different people. This is also important for defining information about the cases and this is where the data relation comes into play. First thing needed was a basic understanding of SQL. In order to design something that would manipulate SQL I had to understand how it was used. In order to do this I enrolled myself in a Udemy course called “SQL - MySQL for Data Analytics and Business Intelligence” which enabled me to have a good understanding of the basics of SQL and how to interact with a database. SQL works through the power of queries which act as instructions that tell the database how to behave. I was able to incorporate some basic but powerful queries: Delete, Update, Insert, and Select.
Select - The SELECT statement is used to select data from a database.
Insert - The INSERT INTO statement is used to insert new records in a table.
Update - The UPDATE statement is used to modify the existing records in a table.
Delete - The DELETE statement is used to delete existing records in a table.
Some example queries:
- SELECT DISTINCT Owner FROM Cases;
- DELETE * FROM Users WHERE Country = USA;
- UPDATE Cases SET Owner = Brandon WHERE CaseNumber = 4
- INSERT INTO Users (Name, Email, Password, Country) VALUES (Brandon, bvalloro@ramapo.edu, newpass1, USA)
These SQL query commands are the heart of the design so once I had a basic grasp of how to manipulate a database using SQL I had to start by creating my database architecture. A database is just a collection of data and is useless without some structure. I needed to have a way to store and manipulate user data as well as case data. Through my analysis of Salesforce I realized there would need to be some relation between the user data and case data so it all works in tandem and users can keep track of data more effectively. By splitting these two data sets in two separate data tables I can access each individually as well as reference one another through their shared relation. The first thing I designed was a data table for just Users then upon further development I incorporated a second data table for cases. In my original planning and design i thought one data table that contained rows for each user and associated tickets would be easy to work with and that looked something like this:
This design ended up being cumbersome and inefficient. Instead I opted to split the database into two separate data tables that contain respective information and would relate through the Users Name column to the Cases Owner column. These two data tables broken up ended up looking like:
CASES:
USERS:
With the database design complete it was time to move onto the GUI with which the users would be interacting with our database. The application was originally designed just for a database of Users and so when it came to designing the GUI there was just a sign in page that led to account details. Through the use of windows forms in C# I could construct comprehensive event based UI controls for users to interact with. These include things like buttons, labels, text boxes, drop down, message boxes, etc… The application is event driven so in order to execute queries through the UI users have to interact with these elements in the windows forms. The different UI’s for the menus and actions users can perform are pretty basic as I wanted to keep it very simple. A User can execute SELECT, DELETE, INSERT INTO, and UPDATE queries on our database all with the click of a few buttons. All of this is done through the use of the DBAccess class and the SqlClient library.
SqlClient describes a collection of classes used to access a SQL Server database in the managed space.(1) Using the SqlDataAdapter, you can fill a memory-resident DataSet that you can use to query and update the database.(3) The DBAccess class contains a few methods that are essential to the application. createConn() and closeConn() are the methods that open and close our data stream from the program to the database. They are used in all cases when we need to make a change to data or retrieve data from the database. The ExecuteDataAdapter method acts as our data setter that overwrites an existing data table, we use it when editing the users because we can make changes to the entire user data table at once in the Edit Users page. ReadThroughAdapter is used many times because it is what we use to read data from the database. It is especially effective at making SELECT queries so it is used almost every time we need to search the database such as on the search tickets page where we have a choice of a few different queries the user can make. ExecuteQuery takes an SqlCommand object made up of a query and executes it on the database and is implemented when we want to make a change on a single element using update, insert, and delete commands. This is implemented in the signup, edit tickets, create tickets, and account info pages. Within the SqlClient library there are classes like SqlCommand which represents a Transact-SQL statement or stored procedure to execute against a SQL Server database. (6) So when a user signs up or creates a new ticket what happens is an object of SqlCommand is made, which is our query, then we populate the object using insertCommand.Parameters.AddWithValue(“column name”, data) and execute the query using an object of DBAccess and the ExecuteQuery method. To remove a data row, like in the edit tickets page, on delete button press: we declare our query, create an object of SqlCommand using our query but instead of populating it we simply have to call ExecuteQuery where the parameter is equal to our new SqlCommand object. To read data from the database and a specific table we just declare our query as a string like: string query = "Select * from Cases WHERE Subject = 'Software'";
And call our readDataThroughAdapter method using a DBAccess object and we pass our query and the data table as arguments. One challenge faced when trying to get and set all this different data from different tables of potential different data types there were some conflicts. There are many exceptions and checks to ensure all data input is valid. The boolean isValid() was created just to deal with email check issues and so that is handled by a regex method. This string declares the rules an email string must follow, which is an inclusion of an @ symbol, .com, .net, .edu, .gov, or a .org email: string regex = @"^[^@\s]+@[^@\s]+\.(com|net|org|gov|edu)$"; (2)
A lot of input data comes from C# windows forms tools. I included a logout or back button on every page so the user can exit at any time. Design choices for the UI were relatively simple and straightforward. I attempted to place the elements in the most efficient way possible. The struggle with these buttons, labels, drop down boxes, data grid views was more related to their properties. Design choices for the data grid views included making the grid view in the search page as read only or making the ID column of the Users data grid view on the edit users page as read only but allowing the other columns to be editable. Additionally to avoid bugs or crashes there are elements on certain pages that are dynamic. In the search cases page a method of searching from the dropdown must be selected in order for either the textbox that takes user input to appear or another drop down containing Case Subjects. These elements in these dropdowns are populated from what's in the data table so if a new user is created you will see their name added to the edit tickets owner drop down menu. We can achieve this by running a query that searches for unique entries in the case owner column of cases. Some quality of life design choices other than the logout and back buttons include message boxes that appear when the user makes an invalid input or wants to delete something from the database. The simple style of menu button options makes it easy for any user to get accustomed to.
Conclusion
In conclusion the system takes advantage of Microsoft SQL Server Management Studio to host our database while a user interacts with the C# GUI in order to search, read from, or write to two related data tables. By using the SqlClient library the user is performing SELECT, DELETE, INSERT INTO, and UPDATE queries all with the click of buttons and entry of text boxes. The GUI is extremely easy to use, which was prevalent during testing. One of the users, who is my girlfriend, was able to make an account, edit account info, edit tickets and users, all based on intuition from interacting with the UI. The design of the UI was inspired by current ticketing systems that I have used in the past but there were some unique challenges faced with the coding aspect. Learning SQL from scratch and database design/management was a great experience and applying it practically turned out to be not so bad. Challenges such as exception handling and checks against user input ended up taking a large portion of time at the end of design. The most difficult aspect of design was implementation of the DBAccess class and using the SqlClient classes in order to manipulate the database. In the end the management of an SQL database through the use of C# is extremely intuitive especially when presented in the format of an event driven GUI.
Bibliography
- Dotnet-Bot. “System.data.SqlClient Namespace.” Microsoft Learn, https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient?view=dotnet-plat-ext-6.0.
- Malek, Piotr. “How to Validate an Email Address in C#.” Mailtrap, 26 Aug. 2022, https://mailtrap.io/blog/validate-email-address-c/.
- Dotnet-Bot. “Sqldataadapter Class (System.data.sqlclient).” (System.Data.SqlClient) | Microsoft Learn, https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter?view=dotnet-plat-ext-6.0.
- Serkin, Jessica. “What Is Data Manipulation Language (DML)? - Definition from Techopedia.” Techopedia.com, https://www.techopedia.com/definition/1179/data-manipulation-language-dml#:~:text=A%20data%20manipulation%20language%20(DML,tables%20and%20modifying%20existing%20data.
- Loshin, Peter, and Jessica Sirkin. “What Is Structured Query Language (SQL)?” SearchDataManagement, TechTarget, 7 Feb. 2022, https://www.techtarget.com/searchdatamanagement/definition/SQL.
- Dotnet-Bot. “SQLCOMMAND Class (System.data.sqlclient).” (System.Data.SqlClient) | Microsoft Learn, https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand?view=dotnet-plat-ext-6.0.
Comments