Please rate it 1-10 where 10 is the highest. I only learned VB.NET from school and only have self-taught C# so most of the logic here I got from open-source projects and some throught. Took me hours to refine this but I still feel I can do better (I know how to use constructors, accessors, and such but I didn't use them here).
It is a simple INSERT/UPDATE/DELETE form with datagridview/listbox display.
MainForm:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace Workers {
public partial class ManageUserRecords : Form {
string getID { get; set; }
string Emp_ID, Username, Password, Type, FirstName, MInitial, LastName, FullName, Address, Phone, Email;
public ManageUserRecords() {
InitializeComponent();
}
private void ManageUserRecords_Load(object sender, EventArgs e) {
load_Dgv();
hide_GridColumns();
load_lb();
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e) {
set_TxtBox();
}
private void dataGridView1_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) {
set_TxtBox();
}
private void dataGridView1_KeyDown(object sender, KeyEventArgs e) {
if (e.KeyCode == Keys.Up || e.KeyCode == Keys.Down) {
set_TxtBox();
}
}
private void btnInsert_Click(object sender, EventArgs e) {
if ((string)btnInsert.Tag == "Add New") {
btnInsert.Tag = "Save Add";
btnInsert.Text = "Save";
clear_All();
txtFName.Select();
} else if ((string)btnInsert.Tag == "Save Add") {
if (txtFName.TextLength < 1 || txtLName.TextLength < 1) {
MessageBox.Show("First Name & Last Name is required");
return;
} else if (txtUsername.TextLength < 1 || txtPassword.TextLength < 1) {
MessageBox.Show("Username & Password is required");
return;
} else if (cbType.Text == "") {
MessageBox.Show("Account Type is required");
return;
}
btnInsert.Tag = "Add New";
btnInsert.Text = "Add New";
Username = txtUsername.Text;
Password = txtPassword.Text;
Type = cbType.Text;
FirstName = txtFName.Text;
MInitial = txtMInitial.Text;
LastName = txtLName.Text;
FullName = FirstName + " " + MInitial + " " + LastName;
Address = txtAddress.Text;
Phone = txtPhone.Text;
Email = txtEmail.Text;
new FormWorkers().insert_Record(FullName, Username, Password, Type, FirstName, MInitial, LastName, Address, Phone, Email);
string userInfo = String.Format("Details:{0} Name : {1}{0} Address : {2}{0} Phone : {3}{0} Email : {4}{0}{0}Account:{0} Username : {5}{0} Type : {6}", Environment.NewLine, FullName, Address, Phone, Email, Username, Type);
MessageBox.Show(String.Format("Record Added!{0}{0}" + userInfo, Environment.NewLine));
load_Dgv();
load_lb();
clear_All();
}
}
private void btnUpdate_Click(object sender, EventArgs e) {
int count = dgvEmployees.SelectedRows.Count;
if (count == 1) {
if ((string)btnUpdate.Tag == "Update New") {
btnUpdate.Tag = "Save";
btnUpdate.Text = "Save";
accessControls();
} else if ((string)btnUpdate.Tag == "Save") {
btnUpdate.Tag = "Update New";
btnUpdate.Text = "Update";
Emp_ID = getID;
Password = txtPassword.Text;
Type = cbType.Text;
FirstName = txtFName.Text;
MInitial = txtMInitial.Text;
LastName = txtLName.Text;
FullName = FirstName + " " + MInitial + " " + LastName;
Address = txtAddress.Text;
Phone = txtPhone.Text;
Email = txtEmail.Text;
new FormWorkers().update_Info(Emp_ID, FullName, Password, Type, FirstName, MInitial, LastName, Address, Phone, Email);
MessageBox.Show("Record updated");
load_Dgv();
load_lb();
clear_All();
}
} else {
MessageBox.Show("Select an item from the list to update.");
}
}
private void btnDelete_Click(object sender, EventArgs e) {
int count = dgvEmployees.SelectedRows.Count;
if (count == 1) {
Emp_ID = getID;
new FormWorkers().delete_Record(Emp_ID);
MessageBox.Show("Record deleted");
load_Dgv();
load_lb();
clear_All();
} else {
MessageBox.Show("Select an item from the list to delete.");
}
}
void accessControls() {
foreach (Control txt in this.Controls) {
if (txt is TextBox) {
txt.Enabled = true;
}
}
foreach (Control txt in groupBox1.Controls) {
if (txt is TextBox) {
txt.Enabled = true;
}
cbType.Enabled = true;
}
}
void load_Dgv() {
new FormWorkers().load_Db();
dgvEmployees.DataSource = FormWorkers.sqldt;
}
void load_lb() {
listBox1.Items.Clear();
new FormWorkers().load_List();
foreach (DataRow dtrows in FormWorkers.lbdt.Rows) {
FormWorkers.dtrow = dtrows;
listBox1.Items.Add("(" + FormWorkers.dtrow["Emp_ID"] + ") " +
"Name: " + FormWorkers.dtrow["Name"]);
}
}
void set_TxtBox() {
DataGridViewRow CurrentRow = dgvEmployees.CurrentRow;
getID = CurrentRow.Cells[0].Value.ToString();
txtFName.Text = CurrentRow.Cells[5].Value.ToString();
txtMInitial.Text = CurrentRow.Cells[6].Value.ToString();
txtLName.Text = CurrentRow.Cells[7].Value.ToString();
txtAddress.Text = CurrentRow.Cells[2].Value.ToString();
txtPhone.Text = CurrentRow.Cells[3].Value.ToString();
txtEmail.Text = CurrentRow.Cells[4].Value.ToString();
txtUsername.Text = CurrentRow.Cells[8].Value.ToString();
cbType.Text = CurrentRow.Cells[9].Value.ToString();
}
void hide_GridColumns() {
int colcount = dgvEmployees.Columns.Count - 1;
for (int i = 5; i <= colcount; i++) {
dgvEmployees.Columns[i].Visible = false;
}
}
void clear_All() {
foreach (Control txt in this.Controls) {
if (txt is TextBox || (string)btnDelete.Tag != "Delete") {
txt.Text = "";
if (txt.Enabled == true) {
txt.Enabled = false;
} else {
txt.Enabled = true;
}
}
}
foreach (Control txt in groupBox1.Controls) {
if (txt is TextBox || (string)btnDelete.Tag != "Delete") {
txt.Text = "";
if (txt.Enabled == true) {
txt.Enabled = false;
cbType.Enabled = false;
} else {
txt.Enabled = true;
cbType.Enabled = true;
}
}
cbType.SelectedIndex = 0;
}
dgvEmployees.ClearSelection();
}
}
}
MainForm Class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace Workers {
class FormWorkers {
SqlCommand cmdSelect, sqlInsert, sqlUpdate, sqlDelete;
SqlDataAdapter sqlSelect;
SqlDataReader rdr;
public static DataRow dtrow;
public static DataTable lbdt;
public static DataTable sqldt;
public static string ID;
private static FormWorkers _main = new FormWorkers();
public static FormWorkers Main {
get {
return _main;
}
}
public void load_List() {
string connStr = @"Data Source=.\SQLEXPRESS_KEVIN;Database=Test;Integrated Security=true";
using (SqlConnection conn = new SqlConnection(connStr)) {
using (SqlDataAdapter sqlSelect = new SqlDataAdapter("SELECT Emp_ID,Name FROM Employees INNER JOIN Accounts ON Employees.Acct_ID=Accounts.Acct_ID WHERE Emp_ID >= 1", conn)) {
lbdt = new DataTable();
sqlSelect.Fill(lbdt);
dtrow = null;
}
}
}
public void load_Db() {
try {
string connStr = @"Data Source=.\SQLEXPRESS_KEVIN;Database=Test;Integrated Security=true";
using (SqlConnection conn = new SqlConnection(connStr)) {
using (sqlSelect = new SqlDataAdapter("SELECT Emp_ID AS 'ID',Accounts.Name,Emp_Address AS 'Address',Emp_Contact AS 'Phone',Emp_Email AS 'Email',Emp_FName,Emp_MName,Emp_LName,Username,Type FROM Employees INNER JOIN Accounts ON Employees.Acct_ID=Accounts.Acct_ID", conn)) {
sqldt = new DataTable();
sqlSelect.Fill(sqldt);
}
}
} catch (SqlException err) {
MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void insert_Record(string FullName, string Username, string Password, string Type, string FirstName, string MInitial, string LastName, string Address, string Phone, string Email) {
try {
string connStr = @"Data Source=.\SQLEXPRESS_KEVIN;Database=Test;Integrated Security=true";
using (SqlConnection conn = new SqlConnection(connStr)) {
using (sqlInsert = new SqlCommand(
"INSERT INTO Accounts (Name,Username,Password,Type) VALUES (@name,@username,@password,@type);" +
"INSERT INTO Employees (Acct_ID,Emp_FName,Emp_MName,Emp_LName,Emp_Address,Emp_Contact,Emp_Email) " +
"SELECT TOP 1 Acct_ID,@FName,@MName,@LName,@Address,@Phone,@Email FROM Accounts ORDER BY Acct_ID DESC", conn)) {
sqlInsert.Parameters.AddWithValue("@name", FullName);
sqlInsert.Parameters.AddWithValue("@username", Username);
sqlInsert.Parameters.AddWithValue("@password", Password);
sqlInsert.Parameters.AddWithValue("@type", Type);
sqlInsert.Parameters.AddWithValue("@FName", FirstName);
sqlInsert.Parameters.AddWithValue("@MName", MInitial);
sqlInsert.Parameters.AddWithValue("@LName", LastName);
sqlInsert.Parameters.AddWithValue("@Address", Address);
sqlInsert.Parameters.AddWithValue("@Phone", Phone);
sqlInsert.Parameters.AddWithValue("@Email", Email);
conn.Open();
sqlInsert.ExecuteNonQuery();
}
}
} catch (SqlException err) {
MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void update_Info(string Emp_ID, string FullName, string Password, string Type, string FirstName, string MInitial, string LastName, string Address, string Phone, string Email) {
try {
string connStr = @"Data Source=.\SQLEXPRESS_KEVIN;Database=Test;Integrated Security=true";
using (SqlConnection conn = new SqlConnection(connStr)) {
conn.Open();
using (sqlUpdate = new SqlCommand(
"UPDATE Accounts SET Name=@name,Password=@password,Type=@type FROM Accounts INNER JOIN Employees ON Accounts.Acct_ID=Employees.Acct_ID WHERE Emp_ID=@Emp_ID;" +
"UPDATE Employees SET Emp_FName=@FName,Emp_MName=@MName,Emp_LName=@LName,Emp_Address=@Address,Emp_Contact=@Phone,Emp_Email=@Email WHERE Emp_ID=@Emp_ID", conn)) {
sqlUpdate.Parameters.AddWithValue("@name", FullName);
sqlUpdate.Parameters.AddWithValue("@password", Password);
sqlUpdate.Parameters.AddWithValue("@type", Type);
sqlUpdate.Parameters.AddWithValue("@FName", FirstName);
sqlUpdate.Parameters.AddWithValue("@MName", MInitial);
sqlUpdate.Parameters.AddWithValue("@LName", LastName);
sqlUpdate.Parameters.AddWithValue("@Address", Address);
sqlUpdate.Parameters.AddWithValue("@Phone", Phone);
sqlUpdate.Parameters.AddWithValue("@Email", Email);
sqlUpdate.Parameters.AddWithValue("@Emp_ID", Emp_ID);
sqlUpdate.ExecuteNonQuery();
}
}
} catch (SqlException err) {
MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void delete_Record(string Emp_ID) {
try {
string connStr = @"Data Source=.\SQLEXPRESS_KEVIN;Database=Test;Integrated Security=true";
using (SqlConnection conn = new SqlConnection(connStr)) {
using (sqlDelete = new SqlCommand(
"DELETE Accounts FROM Accounts INNER JOIN Employees ON Accounts.Acct_ID=Employees.Acct_ID WHERE Emp_ID=@ID;" +
"DELETE FROM Employees WHERE Emp_ID=@ID", conn)) {
sqlDelete.Parameters.AddWithValue("@ID", Emp_ID);
conn.Open();
sqlDelete.ExecuteNonQuery();
}
}
} catch (SqlException err) {
MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
|