In this post i will demonstrate how to start creating a C# application with SQL Server Database. For this you will need Visual Studio and SQL Server. For both programs there are free editions available on the Microsoft websites:
- Visual Studio Community 2015: https://www.visualstudio.com/
- SQL Server Express Edition: https://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx
For learning purposes we will create a very simple application: the users will input Name, no. of Extra-Hours worked and Date into a Form. The Hours field will accept only integer values which are not greater than 8. Then, they will press the Save button and the data will be inserted into a SQL Server Database Table.
Step 1: Create the SQL Server Database and the Table:
- Open and login to SQL Server, then from the left menu, right click on the “Databases” and click “Create Database”. Then name it: “Test_app”
- Create a new Table named “ExtraHours” into the newly created database with the following query:
Use Test_app Go Create TABLE ExtraWork ( ID int IDENTITY(1,1) PRIMARY KEY, Name varchar(255) NOT NULL, Hours int Not Null, Date Date not null )
Step 2: Create in Visual Studio a new Windows Form Application and a Form with 4 controls:
- Open Visual Studio, select File–> New Project–> Windows Forms Application:
- In the Form1.cs [Design] file create into the Form the 4 controls: A) Textbox “txtName” for name input, B) Textbox “txtHours” for hours input, C) DateTimePicker “dateOverTime” for selecting the date when the extra-hours occured, D) button1 labeled “Save Record” for saving the record to the database:
Step 3: Create C# Code for Connection to SQL Server + checking and adding the data into the database:
- In the Form1.cs file insert the following C# code:
using System; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsFormsApplication2 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; connetionString = @"Data Source=yourServerName;Initial Catalog=Test_app;User ID=yourWindowsID;Integrated Security=SSPI"; 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 { //do nothing, the value of hours is correct and assigned. This else statement can be deleted ! } } 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(); MessageBox.Show("The hours where added ! "); cnn.Close(); } catch (Exception ex) { MessageBox.Show("Cannot open connection ! "); } } } }
Step 4: Run the Windows Forms application and check if the data is correctly inserted into the SQL Server Database:
- If the user tries to insert into “Hours” not integer values or integer values which are greater than 8, the data is not inserted to the database:
- If else, the data is inserted to database after the user presses the “Save Record” button:
- Check in SQL Server the inserted data:
This was a very simple example of how to combine Microsoft Visual Studio, C# and SQL Server for application development.
–> In PART 2 of this post I present how we can visualize the records from the SQL Database in the Windows Forms interface.