private void btnRefresh_Click(object sender, EventArgs e) { try { using (SqlConnection conn = new SqlConnection("Server=JOSEPH-NOTEBOOK\\SQLEXPRESS;Database=ArsipNilai;Trusted_Connection=true")) { //open connection //conn.ConnectionString = "Server=JOSEPH-NOTEBOOK\\SQLEXPRESS;Database=ArsipNilai;Trusted_Connection=true"; conn.Open(); //Data Reader //Specify the command //the view is also stored on server SqlCommand command = new SqlCommand("SELECT * FROM AllStudents", conn); command.CommandType = CommandType.Text; //optional line using (SqlDataReader reader = command.ExecuteReader()) { DataTable dt = new DataTable(); dt.Load(reader); dgvAllData.DataSource = dt; } } } catch (SqlException sqlEx) { if (sqlEx.Number == -1) { CommonFunctions.InvokeErrorMsg("Failed establishing connection to database. Check if you are granted permission to database or SQL Server Service is running", Application.ProductName); } } }
private void btnOK_Click(object sender, EventArgs e) { #region "Validation" //check constraints if (relationName == "Student") { //validate NIM if (cboKey1.Text.Length != 10 || !CommonFunctions.IsNumeric(cboKey1.Text)) { CommonFunctions.InvokeErrorMsg("NIM should be numeric and 10 characters length!", Application.ProductName); return; } //validate not null for the three fields if (String.IsNullOrWhiteSpace(txtField1.Text) || String.IsNullOrWhiteSpace(txtField2.Text) || String.IsNullOrWhiteSpace(txtField3.Text)) { CommonFunctions.InvokeErrorMsg("The attributes cannot be empty!", Application.ProductName); return; } //validate that the EnrollYear should be numeric if (!CommonFunctions.IsNumeric(txtField3.Text)) { CommonFunctions.InvokeErrorMsg("Enroll Year should be numeric!", Application.ProductName); return; } } //TODO: Implement for other relations!!! else if (relationName == "SemesterData") { //check semester number int semester; if (!int.TryParse(cboKey2.Text, out semester) || semester < 1) { CommonFunctions.InvokeErrorMsg("The semester should be numeric and greater than zero", Application.ProductName); return; } if (!CommonFunctions.IsNumeric(txtField1.Text)) { CommonFunctions.InvokeErrorMsg("The semester year should be numeric!", Application.ProductName); return; } } else if (relationName == "Courses") { //check course code if (String.IsNullOrWhiteSpace(cboKey1.Text) || !(cboKey1.Text.Length == 8) || CommonFunctions.IsNumeric(cboKey1.Text.Substring(0, 4)) || !CommonFunctions.IsNumeric(cboKey1.Text.Substring(4, 4))) { CommonFunctions.InvokeErrorMsg("The format of course code should be AAAA0000", Application.ProductName); return; } //check other attributes, it shouldn't be empty if (string.IsNullOrWhiteSpace(cboKey2.Text) || string.IsNullOrWhiteSpace(cboKey3.Text) || string.IsNullOrWhiteSpace(txtField1.Text) || string.IsNullOrWhiteSpace(txtField2.Text) || string.IsNullOrWhiteSpace(txtField3.Text) || string.IsNullOrWhiteSpace(txtField4.Text) || string.IsNullOrWhiteSpace(txtField5.Text)) { CommonFunctions.InvokeErrorMsg("The attributes cannot be empty!", Application.ProductName); return; } //numeric validation if (!CommonFunctions.IsNumeric(txtField2.Text) || !CommonFunctions.IsNumeric(txtField3.Text) || !CommonFunctions.IsNumeric(txtField4.Text) || !CommonFunctions.IsNumeric(txtField5.Text)) { CommonFunctions.InvokeErrorMsg("The weight values should be numeric!", Application.ProductName); return; } } else if (relationName == "Scores") { //the keys must not be empty if (cboKey1.Text == string.Empty || cboKey2.Text == string.Empty || cboKey3.Text == string.Empty) { CommonFunctions.InvokeErrorMsg("The keys should not be empty!", Application.ProductName); return; } //check the textboxes, it should be numeric and in 0..100 range foreach (System.Windows.Forms.Control txtBoxes in this.Controls) { //only text box should be checked if (txtBoxes.GetType() == typeof(TextBox)) { //do not check the class field, it will be handled by table constraint, just check if empty or not if (txtBoxes.Name == "txtField1") { if (String.IsNullOrWhiteSpace(txtBoxes.Text)) { CommonFunctions.InvokeErrorMsg("The class field should not be zero", Application.ProductName); return; } else { continue; } } //check the UAP field, if practicum credit is zero then no need to check if (txtBoxes.Name == "txtField5" && !ShouldInsertUAPScore) { continue; } //check, it should be numeric short score; if (!short.TryParse(txtBoxes.Text, out score) || score < 0 || score > 100) { CommonFunctions.InvokeErrorMsg("Invalid scores!\nIt should be numeric and between 0..100", Application.ProductName); return; } } } } #endregion //insert or update if (opMode == OperationMode.Insert) { try { using (SqlConnection conn = new SqlConnection(frmMain.connectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand()) { command.Connection = conn; if (relationName == "Student") { command.CommandText = "INSERT INTO Student (NIM, Name, Program, EnrollYear) VALUES (@1, @2, @3, @4)"; command.Parameters.Add(new SqlParameter("1", cboKey1.Text)); //NIM command.Parameters.Add(new SqlParameter("2", txtField1.Text)); //Name command.Parameters.Add(new SqlParameter("3", txtField2.Text)); //Program command.Parameters.Add(new SqlParameter("4", short.Parse(txtField3.Text))); //EnrollYear try { //Execute the update command if (command.ExecuteNonQuery() > 0) { MessageBox.Show("Insert successful!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information); this.DialogResult = DialogResult.OK; this.Close(); } } catch (SqlException sqlEx) { if (sqlEx.Number == 2627) { CommonFunctions.InvokeErrorMsg("The NIM already exists!", Application.ProductName); } else if (sqlEx.Number == 547) { CommonFunctions.InvokeErrorMsg("Enroll year should be lesser than or equal to " + DateTime.Now.Year, Application.ProductName); } else { CommonFunctions.InvokeErrorMsg(String.Format( "SQL Error Code : {0}\n" + "SQL Error Number : {1}\n" + "SQL Message :\n{2}", sqlEx.ErrorCode, sqlEx.Number, sqlEx.Message ) , Application.ProductName); } } } //TODO: Implement for other relations! else if (relationName == "SemesterData") { command.CommandText = "INSERT INTO SemesterData (NIM, Semester, SemesterYear) VALUES (@1, @2, @3)"; command.Parameters.Add(new SqlParameter("1", cboKey1.Text.Substring(0, 10))); //NIM command.Parameters.Add(new SqlParameter("2", short.Parse(cboKey2.Text))); //Semester command.Parameters.Add(new SqlParameter("3", short.Parse(txtField1.Text))); //Semester Year try { if (command.ExecuteNonQuery() > 0) { MessageBox.Show("Insert successful!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information); this.DialogResult = DialogResult.OK; this.Close(); } } catch (SqlException sqlEx) { if (sqlEx.Number == 2627) { CommonFunctions.InvokeErrorMsg("The semester already exists!", Application.ProductName); } else if (sqlEx.Number == 547) { CommonFunctions.InvokeErrorMsg("Semester Year must be greater or equal to Enroll Year and less than or equal to current year", Application.ProductName); } else { CommonFunctions.InvokeErrorMsg(String.Format( "SQL Error Code : {0}\n" + "SQL Error Number : {1}\n" + "SQL Message :\n{2}", sqlEx.ErrorCode, sqlEx.Number, sqlEx.Message ) , Application.ProductName); } } } else if (relationName == "Courses") { command.CommandText = "INSERT INTO Courses (CourseCode, CourseName, TheoryCredit, PracticumCredit, TMWeight, UTSWeight, PracticumWeight, UASWeight) VALUES (@1, @2, @3, @4, @5, @6, @7, @8)"; command.Parameters.Add(new SqlParameter("1", cboKey1.Text)); command.Parameters.Add(new SqlParameter("2", cboKey2.Text)); command.Parameters.Add(new SqlParameter("3", short.Parse(cboKey3.Text))); command.Parameters.Add(new SqlParameter("4", short.Parse(txtField1.Text))); command.Parameters.Add(new SqlParameter("5", short.Parse(txtField2.Text))); command.Parameters.Add(new SqlParameter("6", short.Parse(txtField3.Text))); command.Parameters.Add(new SqlParameter("7", short.Parse(txtField4.Text))); command.Parameters.Add(new SqlParameter("8", short.Parse(txtField5.Text))); try { if (command.ExecuteNonQuery() > 0) { MessageBox.Show("Insert successful!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information); this.DialogResult = DialogResult.OK; this.Close(); } } catch (SqlException sqlEx) { if (sqlEx.Number == 2627) { CommonFunctions.InvokeErrorMsg("The course code already exists!", Application.ProductName); } else if (sqlEx.Number == 547) { CommonFunctions.InvokeErrorMsg("The total weight must be 100!", Application.ProductName); } else { CommonFunctions.InvokeErrorMsg(String.Format( "SQL Error Code : {0}\n" + "SQL Error Number : {1}\n" + "SQL Message :\n{2}", sqlEx.ErrorCode, sqlEx.Number, sqlEx.Message ) , Application.ProductName); } } } else if (relationName == "Scores") { command.CommandText = ShouldInsertUAPScore ? "INSERT INTO Scores (NIM, Semester, CourseCode, Class, TM, UTS, UAS, UAP) VALUES (@1, @2, @3, @4, @5, @6, @7, @8)" : "INSERT INTO Scores (NIM, Semester, CourseCode, Class, TM, UTS, UAS) VALUES (@1, @2, @3, @4, @5, @6, @7)"; command.Parameters.Add(new SqlParameter("1", cboKey1.Text.Substring(0, 10))); command.Parameters.Add(new SqlParameter("2", cboKey2.Text)); command.Parameters.Add(new SqlParameter("3", cboKey3.Text.Substring(0, 8))); command.Parameters.Add(new SqlParameter("4", txtField1.Text)); command.Parameters.Add(new SqlParameter("5", short.Parse(txtField2.Text))); command.Parameters.Add(new SqlParameter("6", short.Parse(txtField3.Text))); command.Parameters.Add(new SqlParameter("7", short.Parse(txtField4.Text))); if (ShouldInsertUAPScore) { command.Parameters.Add(new SqlParameter("8", short.Parse(txtField5.Text))); } try { if (command.ExecuteNonQuery() > 0) { MessageBox.Show("Insert successful!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information); this.DialogResult = DialogResult.OK; this.Close(); } } catch (SqlException sqlEx) { if (sqlEx.Number == 2627) { CommonFunctions.InvokeErrorMsg("The specified student, semester and course already exists!", Application.ProductName); } else if (sqlEx.Number == 547 || sqlEx.Number == 8152) { CommonFunctions.InvokeErrorMsg("The class code should be AA00 format", Application.ProductName); } else { CommonFunctions.InvokeErrorMsg(String.Format( "SQL Error Code : {0}\n" + "SQL Error Number : {1}\n" + "SQL Message :\n{2}", sqlEx.ErrorCode, sqlEx.Number, sqlEx.Message ) , Application.ProductName); } } } } } } catch (Exception ex) { CommonFunctions.InvokeErrorMsg(ex.Message, Application.ProductName); } } //TODO: Do for update mode as well else { } }
private void dlgEditData_Load(object sender, EventArgs e) { try { //connect to database using (SqlConnection conn = new SqlConnection(frmMain.connectionString)) { conn.Open(); /* * //get primary keys and foreign keys * List<String> primaryKeys, foreignKeys; * * //get primary key and foreign keys * primaryKeys = new List<string>(); * foreignKeys = new List<string>(); * * //obtain primary keys and foreign keys of relation * using (SqlCommand command = new SqlCommand(String.Format("select CONSTRAINT_TYPE, COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc on tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME and tc.TABLE_NAME = cc.TABLE_NAME where (CONSTRAINT_TYPE = 'PRIMARY KEY' or CONSTRAINT_TYPE = 'FOREIGN KEY') and cc.TABLE_NAME = {0}", relName), conn)) * { * using (SqlDataReader reader = command.ExecuteReader()) * { * //populate the primary keys and foreign keys * while(reader.Read()) * { * if(reader.GetString(0) == "PRIMARY KEY") * { * primaryKeys.Add(reader.GetString(1)); * } else * { * foreignKeys.Add(reader.GetString(1)); * } * } * } * } */ //we already know the keys in each relation so we can directly specify it #region "UI Processing" //check operation mode if (key1 == null) { //if key1 is null, then it is data insertion mode opMode = OperationMode.Insert; this.Text = "Insert into " + this.Text; //check relation name, it determines number of key fields to be active if (relationName == "Student") { //hide the key2 and key3 field lblKey2.Visible = lblKey3.Visible = cboKey2.Visible = cboKey2.Enabled = cboKey3.Visible = cboKey3.Enabled = false; //also the Student relation has 3 non-primary key fields so hide the last 2 fields lblField4.Visible = lblField5.Visible = txtField4.Visible = txtField5.Visible = false; //the first key is the new key, disable the dropdown button cboKey1.DropDownStyle = ComboBoxStyle.Simple; //set the attribute labels lblKey1.Text = "NIM :"; lblField1.Text = "Name :"; lblField2.Text = "Program :"; lblField3.Text = "Enroll Year :"; } //TODO: Implement for other relations!!! else if (relationName == "SemesterData") { //only 2 keys required lblKey3.Visible = cboKey3.Visible = cboKey3.Enabled = false; //used to insert new semester cboKey2.DropDownStyle = ComboBoxStyle.Simple; //only 1 field required lblField2.Visible = lblField3.Visible = lblField4.Visible = lblField5.Visible = false; txtField2.Visible = txtField3.Visible = txtField4.Visible = txtField5.Visible = false; txtField2.Enabled = txtField3.Enabled = txtField4.Enabled = txtField5.Enabled = false; //get available Students using (SqlCommand cmdGetStudent = new SqlCommand("SELECT NIM, Name FROM Student ORDER BY NIM ASC", conn)) { using (SqlDataReader reader = cmdGetStudent.ExecuteReader()) { if (reader.Read()) { cboKey1.Items.Add(reader["NIM"] + " - " + reader["Name"]); while (reader.Read()) { cboKey1.Items.Add(reader["NIM"] + " - " + reader["Name"]); } } else { MessageBox.Show("No Students available", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } } } //make it drop down only and set first item as default //cboKey1.DropDownStyle = ComboBoxStyle.DropDownList; cboKey1.SelectedIndex = 0; //set the labels lblKey1.Text = "NIM :"; lblKey2.Text = "Semester :"; lblField1.Text = "Semester Year :"; } else if (relationName == "Courses") { //all fields used, change all combo box to Text Box mode cboKey1.DropDownStyle = cboKey2.DropDownStyle = cboKey3.DropDownStyle = ComboBoxStyle.Simple; //set the labels lblKey1.Text = "Course Code :"; lblKey2.Text = "Course Name :"; lblKey3.Text = "Theory Credit :"; lblField1.Text = "Practicum Credit :"; lblField2.Text = "TM Weight :"; lblField3.Text = "UTS Weight :"; lblField4.Text = "Practicum Weight :"; lblField5.Text = "UAS Weight :"; } else if (relationName == "Scores") { //set the labels lblKey1.Text = "NIM :"; lblKey2.Text = "Semester :"; lblKey3.Text = "Course Code :"; lblField1.Text = "Class :"; lblField2.Text = "TM :"; lblField3.Text = "UTS :"; lblField4.Text = "UAS :"; lblField5.Text = "UAP :"; //key 2 and key 3 field will be disabled by default until the a student is selected //get available Students using (SqlCommand cmdGetStudent = new SqlCommand("SELECT NIM, Name FROM Student ORDER BY NIM ASC", conn)) { using (SqlDataReader reader = cmdGetStudent.ExecuteReader()) { if (reader.Read()) { cboKey1.Items.Add(reader["NIM"] + " - " + reader["Name"]); while (reader.Read()) { cboKey1.Items.Add(reader["NIM"] + " - " + reader["Name"]); } } else { MessageBox.Show("No Students available", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); } } } //disable combo box 2 because combo box 1 currently selected to nothing cboKey2.Enabled = false; //read available courses using (SqlCommand command = new SqlCommand("SELECT CourseCode, CourseName FROM Courses", conn)) { SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { cboKey3.Items.Add(reader.GetString(0) + " - " + reader.GetString(1)); while (reader.Read()) { cboKey3.Items.Add(reader.GetString(0) + " - " + reader.GetString(1)); } } else { //no courses available, cancel insertion MessageBox.Show("No courses available!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); } } } } //TODO: Implement for other relations!!!! #endregion } } catch (SqlException sqlEx) { if (sqlEx.Number == -1) { CommonFunctions.InvokeErrorMsg("Failed establishing connection to database. Check if you are granted permission to database or SQL Server Service is running", Application.ProductName); this.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Arsip Nilai", MessageBoxButtons.OK, MessageBoxIcon.Error); } }