private Attendance_Entity loadEntity()
{
Attendance_Entity ae = new Attendance_Entity();
ae.courseAlias = select_dept_comboBox.Text;
ae.sem = int.Parse(select_sem_numericUpDown.Value.ToString());
ae.month = select_month_comboBox.Text;
ae.batchYear = int.Parse(select_batch_comboBox.Text);
return(ae);
}
//upload file to specific folder
private void UploadFile(Attendance_Entity ae)
{
string saveFilePath = Properties.Settings.Default.AttendancePath + select_dept_comboBox.Text + @"\";
string msg = "";
bool isUpload = true;
if (!Directory.Exists(Properties.Settings.Default.AttendancePath + select_dept_comboBox.Text))
{
Directory.CreateDirectory(Properties.Settings.Default.AttendancePath + select_dept_comboBox.Text);
}
if (!File.Exists(saveFilePath + ae.file + ".xls"))
{
ae.file = ae.file + ".xls";
int affected = Attendance_Methods.UploadAttendance(ae);
if (affected > 0)
{
msg = "Attendance Uploaded Successfully :)";
}
}
else
{
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
DialogResult result;
result = MessageBox.Show("This Attendance file is already exists,\r\nDo u want to override the existing file?", "File Exists!!!", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
File.Delete(saveFilePath + ae.file + ".xls");
msg = "File Overriden successfully :)";
}
else
{
isUpload = false;
}
}
if (isUpload)
{
saveDataInExcel(saveFilePath, ae);
}
if (msg != "")
{
AlertBox.ShowDialog(msg, AlertBox.AlertType.success, true);
}
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
public static string getFileName(Attendance_Entity ae)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("getFileName", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@alias", ae.courseAlias);
cmd.Parameters.AddWithValue("@sem", ae.sem);
cmd.Parameters.AddWithValue("@batch", ae.batchYear);
cmd.Parameters.AddWithValue("@month", ae.month);
conn.Open();
return(cmd.ExecuteScalar().ToString());
}
}
}
//load entity for uploadAttendance (insert)
private Attendance_Entity loadEntity()
{
Attendance_Entity ae = new Attendance_Entity();
ae.courseAlias = select_dept_comboBox.Text;
ae.sem = int.Parse(select_sem_numericUpDown.Value.ToString());
ae.month = select_month_comboBox.Text;
ae.batchYear = int.Parse(batch_year_TextBox.Text);
string subMonth = select_month_comboBox.Text.Substring(0, 3);
string fileName = ae.courseAlias + ae.batchYear + "_" + subMonth + select_sem_numericUpDown.Value.ToString();
ae.file = fileName;
return(ae);
}
public static int UploadAttendance(Attendance_Entity ae)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("UploadAttendance", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@alias", ae.courseAlias);
cmd.Parameters.AddWithValue("@sem", ae.sem);
cmd.Parameters.AddWithValue("@month", ae.month);
cmd.Parameters.AddWithValue("@file", ae.file);
cmd.Parameters.AddWithValue("@batchyear", ae.batchYear);
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
}
//Write Uploaded data to the Uploaded excel file
private void saveDataInExcel(string saveFilePath, Attendance_Entity ae)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
worksheet.Name = "AttendanceSheet";
app.DisplayAlerts = false;
System.Data.DataTable dt = (DataTable)attendance_dataGridView.DataSource;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].Caption;
}
int count = 2;
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[count, j + 1] = dt.Rows[i][j].ToString();
}
count++;
}
string uploadPath = Path.GetFullPath(saveFilePath + ae.file);
workbook.SaveAs(uploadPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.Quit();
}
//Upload button click event
private void upload_button_Click(object sender, EventArgs e)
{
Attendance_Entity ae = loadEntity();
UploadFile(ae);//upload file to specific folder
}