alexa-tracking
Selamat Guest, Agan dapat mencoba tampilan baru KASKUS Masih Kangen Tampilan Sebelumnya
Kategori
Kategori
Home / FORUM / All / Tech / ... / Programmer Forum /
datagridview ke database
1024
1024
KASKUS
51
244
https://www.kaskus.co.id/thread/53ffd6960f8b46be648b456d/datagridview-ke-database

datagridview ke database

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Globalization;

namespace App.EmployeeMenu
{
public partial class EmployeeScheduleForm : Form
{
private DateTime currentDateTime;
private DataTable department;
public EmployeeScheduleForm()
{
InitializeComponent();
currentDateTime = DateTime.Now;

// get departmentlist
getDepartment();
departmentSelection.SelectedIndex = 0;

// select current month
populateMonthSelection();
monthSelection.SelectedValue = currentDateTime.Month;

// create year list and select current year;
populateYearSelection();
yearSelection.SelectedIndex = 10;
}

private void getDepartment()
{
department = ApplicationSystem.sqlHelper.getDataTable("SELECT * FROM department ORDER BY name ASC");
departmentSelection.DataSource = department;
departmentSelection.DisplayMember = "name";
departmentSelection.ValueMember = "id";
}

private void populateMonthSelection()
{
Dictionary<string, int> months = new Dictionary<string, int>();
months.Add("January", 1);
months.Add("Frebruary", 2);
months.Add("March", 3);
months.Add("April", 4);
months.Add("May", 5);
months.Add("June", 6);
months.Add("July", 7);
months.Add("August", 8);
months.Add("September", 9);
months.Add("October", 10);
months.Add("November", 11);
months.Add("December", 12);

monthSelection.DataSource = new BindingSource(months, null);
monthSelection.DisplayMember = "Key";
monthSelection.ValueMember = "Value";
}

private void populateYearSelection()
{
int currentYear = currentDateTime.Year;
int lestTenYear = currentYear - 10;
int moreTenYear = currentYear + 10;
for (int i = lestTenYear; i < moreTenYear; i++)
{
yearSelection.Items.Add(i);
}
}

private void EmployeeScheduleForm_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Escape)
{
this.Close();
}
}

private void EmployeeScheduleForm_Shown(object sender, EventArgs e)
{
departmentSelection.SelectedIndexChanged += departmentSelection_SelectedIndexChanged;
monthSelection.SelectedIndexChanged += monthSelection_SelectedIndexChanged;
yearSelection.SelectedIndexChanged += yearSelection_SelectedIndexChanged;
}

private int selectedDepartment, selectedMonth, selectedYear;

void yearSelection_SelectedIndexChanged(object sender, EventArgs e)
{
selectedYear = Convert.ToInt16(yearSelection.SelectedItem.ToString());
loadEmployee();
}

void monthSelection_SelectedIndexChanged(object sender, EventArgs e)
{
selectedMonth = Convert.ToInt16(monthSelection.SelectedValue);
loadEmployee();
}

void departmentSelection_SelectedIndexChanged(object sender, EventArgs e)
{
selectedDepartment = Convert.ToInt16(departmentSelection.SelectedValue);
loadEmployee();
}

private void EmployeeScheduleForm_Load(object sender, EventArgs e)
{
selectedDepartment = Convert.ToInt16(departmentSelection.SelectedValue);
selectedMonth = Convert.ToInt16(monthSelection.SelectedValue);
selectedYear = Convert.ToInt16(yearSelection.SelectedItem.ToString());
loadEmployee();
}

private void loadEmployee()
{
string employeeSql = string.Format("SELECT id, name FROM employee WHERE active = 1 AND department_id = '{0}'", selectedDepartment);
DataTable employeeTable = ApplicationSystem.sqlHelper.getDataTable(employeeSql);
int days = DateTime.DaysInMonth(selectedYear, selectedMonth);
for (int i = 1; i <= days; i++)
{
DateTime date = new DateTime(selectedYear, selectedMonth, i);
DataColumn dateColumn = new DataColumn(date.DayOfWeek.ToString() + " " + date.ToShortDateString(), typeof(string));
employeeTable.Columns.Add(dateColumn);
}
employeeDataGridView.DataSource = employeeTable;
makeGridNotSortableAndMarkSunday();
loadScheduleFromDatabase();
}

private void loadScheduleFromDatabase()
{
foreach (DataGridViewRow scheduleRow in employeeDataGridView.Rows)
{
string employeeID = scheduleRow.Cells["id"].Value.ToString();
string loadEmployeeSchedule = string.Format("SELECT * FROM schedule WHERE employee_id = {0} AND s_month = '{1}' AND s_year = '{2}';", employeeID, selectedMonth, selectedYear);
DataTable employeeTable = ApplicationSystem.sqlHelper.getDataTable(loadEmployeeSchedule);
foreach (DataRow rowData in employeeTable.Rows)
{
int day = Convert.ToInt16(rowData["s_day"]);
int workState = Convert.ToInt16(rowData["workstate_id"]);
Color cellColor = scheduleRow.Cells[day + 2].Style.BackColor;
switch (workState)
{
case 2:
cellColor = Color.FromArgb(231, 76, 60);
break;

case 3:
cellColor = Color.FromArgb(41, 128, 185);
break;

case 4:
cellColor = Color.FromArgb(39, 174, 96);
break;

default:
cellColor = Color.Empty;
break;
}
scheduleRow.Cells[day].Style.BackColor = cellColor;
scheduleRow.Cells[day].Style.SelectionBackColor = cellColor;
}
}
}

private void makeGridNotSortableAndMarkSunday()
{
employeeDataGridView.EnableHeadersVisualStyles = false;
foreach (DataGridViewColumn column in employeeDataGridView.Columns)
{
string columnName = column.Name;
DataGridViewColumn col = employeeDataGridView.Columns[columnName];
col.SortMode = DataGridViewColumnSortMode.NotSortable;

// ganti background minggu jadi merah
bool val = System.Text.RegularExpressions.Regex.IsMatch(columnName, @"\d");
if (val)
{
col.Width = 70;
}
if (columnName.StartsWith("Sunday"))
{
col.HeaderCell.Style.BackColor = Color.FromArgb(231, 76, 60);
}
}
}

private void employeeDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex < 0)
{
return;
}

if (e.ColumnIndex > 1)
{
DataGridViewCell selectedCell = employeeDataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex];
DataGridViewCellStyle selectedCellStyle = selectedCell.Style;

if (selectedCellStyle.BackColor == Color.Empty)
{
// DAY OFF
selectedCellStyle.BackColor = Color.FromArgb(231, 76, 60);
selectedCellStyle.SelectionBackColor = Color.FromArgb(231, 76, 60);
}
else if (selectedCellStyle.BackColor == Color.FromArgb(231, 76, 60))
{
// ANNUAL LEAVE
selectedCellStyle.BackColor = Color.FromArgb(41, 128, 185);
selectedCellStyle.SelectionBackColor = Color.FromArgb(41, 128, 185);
}
else if (selectedCellStyle.BackColor == Color.FromArgb(41, 128, 185))
{
// PUBLIC HOLIDAY
selectedCellStyle.BackColor = Color.FromArgb(39, 174, 96);
selectedCellStyle.SelectionBackColor = Color.FromArgb(39, 174, 96);
}
else if (selectedCellStyle.BackColor == Color.FromArgb(39, 174, 96))
{
// WORK
selectedCellStyle.BackColor = Color.Empty;
selectedCellStyle.SelectionBackColor = Color.Empty;
}

}
}

private void updateWorkScheduleToDatabase()
{
List<Dictionary<string, string>> schedulesList = new List<Dictionary<string, string>>();
foreach (DataGridViewRow scheduleRow in employeeDataGridView.Rows)
{
string employeeID = scheduleRow.Cells["id"].Value.ToString();
for (int i = 2; i < scheduleRow.Cells.Count; i++)
{
Dictionary<string, string> scheduleDic = new Dictionary<string, string>();
scheduleDic.Add("employee_id", employeeID);
scheduleDic.Add("s_day", i.ToString());
scheduleDic.Add("s_month", selectedMonth.ToString());
scheduleDic.Add("s_year", selectedYear.ToString());
scheduleDic.Add("create_date", DateTime.Now.ToString());
scheduleDic.Add("create_user_id", ApplicationSystem.userProperties.id.ToString());

string state = "";

Color cellBackColor = scheduleRow.Cells[i].Style.BackColor;
if (cellBackColor == Color.Empty)
{
state = "1"; // WORK
}
else if (cellBackColor == Color.FromArgb(231, 76, 60))
{
state = "2"; // DAY OFF
}
else if (cellBackColor == Color.FromArgb(41, 128, 185))
{
state = "3"; // ANNUAL LEAVE
}
else if (cellBackColor == Color.FromArgb(39, 174, 96))
{
state = "4";
}
scheduleDic.Add("workstate_id", state);
schedulesList.Add(scheduleDic);
}
}

foreach (Dictionary<string, string> scheduleDic in schedulesList)
{
string employeeId, workstate, day, month, year;
string dbid, dbEmployeeId, dbWorksate, dbDay, dbMonth, dbYear;
scheduleDic.TryGetValue("employee_id", out employeeId);
scheduleDic.TryGetValue("workstate_id", out workstate);
scheduleDic.TryGetValue("s_day", out day);
scheduleDic.TryGetValue("s_month", out month);
scheduleDic.TryGetValue("s_year", out year);

string databaseSchedule = string.Format("SELECT * FROM schedule where employee_id = '{0}' AND s_day = '{1}' AND s_month = '{2}' AND s_year = '{3}';", employeeId, day, month, year);
DataTable employeeSchedule = ApplicationSystem.sqlHelper.getDataTable(databaseSchedule);
if (employeeSchedule.Rows.Count > 0)
{
dbid = employeeSchedule.Rows[0]["id"].ToString();
dbEmployeeId = employeeSchedule.Rows[0]["employee_id"].ToString();
dbWorksate = employeeSchedule.Rows[0]["workstate_id"].ToString();
dbDay = employeeSchedule.Rows[0]["s_day"].ToString();
dbMonth = employeeSchedule.Rows[0]["s_month"].ToString();
dbYear = employeeSchedule.Rows[0]["s_year"].ToString();

if (workstate.Equals("1"))
{
ApplicationSystem.sqlHelper.deleteData("schedule", "id = " + dbid);
}
else
{
ApplicationSystem.sqlHelper.updateData("schedule", scheduleDic, "id = " + dbid);
}
}
else
{
if (!workstate.Equals("1"))
{
ApplicationSystem.sqlHelper.insertData("schedule", scheduleDic);
}
}
}
}

private void updateButton_Click(object sender, EventArgs e)
{
updateWorkScheduleToDatabase();
}
}
}


mau nanya gan..
ini kenapa yah sewaktu gw update ke database lama amat yah?
kayak not responding gitu.. appnya berjalan lancar tapi prosesnya lama...
apakah ada cara yg lebih efisien dari ini?

kira2 hasilnya nanti seperti ini gan

datagridview ke database
Diubah oleh anditsung
ga ada yg bs bantu?


×
GDP Network
Copyright © 2018, Kaskus Networks, PT Darta Media Indonesia.
Ikuti KASKUS di