In PART 1 of this post I presented how to start creating a simple application with a small database in Visual Studio and SQL Server. Now I would like to continue by adding some new features to our sample application.
- As presented in Part 1, the app will store the Extrahours worked by company’s employees. After the users are adding these hours, they would like to be able to visualize in the graphic interface the hours from the database without having to query the database in SQL Server.
Considering this, we can add to our form a DataGridView and link it to the SQL Server database to display the information that we want to see.
Step 1: Add the DataGridView to Form1 and name it: dataGridView1:
Step 2: Add 2 buttons: button2 with text “All Records” and button3 with text “Summarization”. The Form1 will look like this:
Step 3: Add new C# methods in order to link the DataGridView and the new 2 buttons to the SQL DataBase. My new code is the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 |
using System; using System.Windows.Forms; using System.Data.SqlClient; using System.Data; namespace WindowsFormsApplication2 { public partial class Form1 : Form { public string connetionString = @"Data Source=yourServerName;Initial Catalog=Test_app;User ID=yourUserID;Integrated Security=SSPI"; public Form1() { InitializeComponent(); allRecords(); } private void button1_Click(object sender, EventArgs e) { SqlConnection cnn = new SqlConnection(connetionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = cnn; cmd.CommandText = ("INSERT into ExtraWork VALUES (@Name,@Hours,@Date)"); //take and check the hours ! int hours; if (int.TryParse(txtHours.Text,out hours)) { if (hours > 8){ MessageBox.Show("The number of hours cannot be greater than 8 ! Please correct the entered value !"); return; } } else { MessageBox.Show("Please insert an integer into Hours textbox !"); return; } //insert ok data into SQL Table cmd.Parameters.AddWithValue("@Name", txtName.Text); cmd.Parameters.AddWithValue("@Hours", hours); cmd.Parameters.AddWithValue("@Date", dateOverTime.Value); try { cnn.Open(); cmd.ExecuteNonQuery(); allRecords(); MessageBox.Show("The hours where added ! "); cnn.Close(); txtName.Text = ""; txtHours.Text = ""; } catch (Exception ex) { MessageBox.Show("Cannot open connection ! " + ex.Message); } } private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) { } void load_table(SqlCommand cmdDataBase) { try { SqlDataAdapter sd = new SqlDataAdapter(); sd.SelectCommand = cmdDataBase; DataTable dbDataSet = new DataTable(); sd.Fill(dbDataSet); BindingSource bSource = new BindingSource(); bSource.DataSource = dbDataSet; dataGridView1.DataSource = bSource; sd.Update(dbDataSet); dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; //set columns to readonly: foreach (DataGridViewColumn column in dataGridView1.Columns) { dataGridView1.Columns[column.Name].ReadOnly = true; } } catch { MessageBox.Show("An error occured ! "); } } private void button2_Click(object sender, EventArgs e) { allRecords(); } public void allRecords() { SqlConnection conn = new SqlConnection(connetionString); SqlCommand cmdDataBase = new SqlCommand("Select Name, Hours, Date from ExtraWork ;", conn); load_table(cmdDataBase); } public void summarization() { SqlConnection conn = new SqlConnection(connetionString); SqlCommand cmdDataBase = new SqlCommand("Select Name, Sum(Hours) as [Sum of Hours], Year(Date) as Year, Month(Date) as Month "+ "from ExtraWork Group by Name, Month(Date), Year(Date) Order by Name asc, Year desc, Month desc; ", conn); load_table(cmdDataBase); } private void button3_Click(object sender, EventArgs e) { summarization(); } } } |
Step 4: Run the App and see in the DataGridView All database Records or a Summarization by Name, Year and Month, by pressing the corresponding buttons. The DataGridView is also filled and refreshed when the application starts or after a new record is added:
- The SQL simple Database looks like this:
Of course, if we need, we can add more fields, tables or features and create a “real app”, but I think this is a good start which presents a few methods that we can use to link the Windows Forms interface to a SQL Server Database.
Great tutorial. Both, part 1 and 2.
It’s a good starting point for someone like me just learning the basics.
Thank you for sharing.
Do you perhaps have a tutorial similar to this, that’s more in line with a Windows Form for an equipment sign-out register?.
Good informative example. Ver well explained.
Thanks for sharing Sir !