/// <summary>
/// Convert Excel- Sheet 1
/// </summary>
/// <param name="excelSheetRecords"></param>
/// <returns></returns>
public bool Save(ExcelSheetRecords excelSheetRecords)
{
try
{
DataTable data = new DataTable();
data.Columns.Add(AccountNumber, typeof(string));
data.Columns.Add(TicketNumber, typeof(string));
data.Columns.Add(TicketAssignedTo, typeof(string));
data.Columns.Add(CreateDateTime, typeof(string));
data.Columns.Add(CompleteDateTime, typeof(string));
data.Columns.Add(WorkTypeName, typeof(string));
data.Columns.Add(NumberOfWorkEntries, typeof(string));
data.Columns.Add(IssueType, typeof(string));
data.Columns.Add(SubIssueType, typeof(string));
data.Columns.Add(Source, typeof(string));
data.Columns.Add(UDFDeferredForResolution, typeof(string));
data.Columns.Add(Priority, typeof(string));
data.Columns.Add(TicketStatus, typeof(string));
data.Columns.Add(TotalWorkedHours, typeof(string));
data.Columns.Add(TicketDurationDays, typeof(string));
data.Columns.Add(TicketDurationHours, typeof(string));
data.Columns.Add(TicketQueue, typeof(string));
DataTable dtData = excelSheetRecords.ReadExcel();
dtData.Columns["F1"].ColumnName = AccountNumber;
dtData.Columns["F2"].ColumnName = TicketNumber;
dtData.Columns["F3"].ColumnName = TicketAssignedTo;
dtData.Columns["F4"].ColumnName = CreateDateTime;
dtData.Columns["F5"].ColumnName = CompleteDateTime;
dtData.Columns["F6"].ColumnName = WorkTypeName;
dtData.Columns["F7"].ColumnName = NumberOfWorkEntries;
dtData.Columns["F8"].ColumnName = IssueType;
dtData.Columns["F9"].ColumnName = SubIssueType;
dtData.Columns["F10"].ColumnName = Source;
dtData.Columns["F11"].ColumnName = UDFDeferredForResolution;
dtData.Columns["F12"].ColumnName = Priority;
dtData.Columns["F13"].ColumnName = TicketStatus;
dtData.Columns["F14"].ColumnName = TotalWorkedHours;
dtData.Columns["F15"].ColumnName = TicketDurationDays;
dtData.Columns["F16"].ColumnName = TicketDurationHours;
dtData.Columns["F17"].ColumnName = TicketQueue;
//remove empty row, where whole columns (row) contains null in excel document
dtData = dtData.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is DBNull || string.IsNullOrWhiteSpace(field as string))).CopyToDataTable();
//We should merge datatype as string for bulk upload
//Other datatypes to string
foreach (DataRow iData in dtData.Rows)
{
data.Rows.Add(iData[AccountNumber], iData[TicketNumber], iData[TicketAssignedTo], iData[CreateDateTime],
iData[CompleteDateTime], iData[WorkTypeName], iData[NumberOfWorkEntries], iData[IssueType],
iData[SubIssueType], iData[Source], iData[UDFDeferredForResolution], iData[Priority],
iData[TicketStatus], iData[TotalWorkedHours], iData[TicketDurationDays], iData[TicketDurationHours],
iData[TicketQueue]);
}
string InsertQuery = "INSERT INTO AllTicketsCreated " +
"(" + AccountNumber + ", " + TicketNumber + ", " + TicketAssignedTo + "," +
CreateDateTime + ", " + CompleteDateTime + ", " + WorkTypeName + "," +
NumberOfWorkEntries + ", " + IssueType + ", " + SubIssueType + "," +
Source + ", " + UDFDeferredForResolution + ", " + Priority + "," +
TicketStatus + ", " + TotalWorkedHours + ", " + TicketDurationDays + "," +
TicketDurationHours + ", " + TicketQueue + ")" +
" VALUES (@" + AccountNumber + " , @" + TicketNumber + ", @" + TicketAssignedTo +
", @" + CreateDateTime + " , @" + CompleteDateTime + ", @" + WorkTypeName +
", @" + NumberOfWorkEntries + " , @" + IssueType + ", @" + SubIssueType +
", @" + Source + " , @" + UDFDeferredForResolution + ", @" + Priority +
", @" + TicketStatus + " , @" + TotalWorkedHours + ", @" + TicketDurationDays +
", @" + TicketDurationHours + " , @" + TicketQueue + " )";
using (var connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + _dbConnection.ConnectionString() + ""))
{
connection.Open();
var adapter = new OleDbDataAdapter();
adapter.InsertCommand = new OleDbCommand(InsertQuery, connection);
adapter.InsertCommand.Parameters.Add("@" + AccountNumber, OleDbType.VarChar, 255, AccountNumber);
adapter.InsertCommand.Parameters.Add("@" + TicketNumber, OleDbType.VarChar, 255, TicketNumber);
adapter.InsertCommand.Parameters.Add("@" + TicketAssignedTo, OleDbType.VarChar, 255, TicketAssignedTo);
adapter.InsertCommand.Parameters.Add("@" + CreateDateTime, OleDbType.VarChar, 25, CreateDateTime);
adapter.InsertCommand.Parameters.Add("@" + CompleteDateTime, OleDbType.VarChar, 25, CompleteDateTime);
adapter.InsertCommand.Parameters.Add("@" + WorkTypeName, OleDbType.VarChar, 255, WorkTypeName);
adapter.InsertCommand.Parameters.Add("@" + NumberOfWorkEntries, OleDbType.VarChar, 10, NumberOfWorkEntries);
adapter.InsertCommand.Parameters.Add("@" + IssueType, OleDbType.VarChar, 100, IssueType);
adapter.InsertCommand.Parameters.Add("@" + SubIssueType, OleDbType.VarChar, 255, SubIssueType);
adapter.InsertCommand.Parameters.Add("@" + Source, OleDbType.VarChar, 100, Source);
adapter.InsertCommand.Parameters.Add("@" + UDFDeferredForResolution, OleDbType.VarChar, 10, UDFDeferredForResolution);
adapter.InsertCommand.Parameters.Add("@" + Priority, OleDbType.VarChar, 100, Priority);
adapter.InsertCommand.Parameters.Add("@" + TicketStatus, OleDbType.VarChar, 100, TicketStatus);
adapter.InsertCommand.Parameters.Add("@" + TotalWorkedHours, OleDbType.VarChar, 10, TotalWorkedHours);
adapter.InsertCommand.Parameters.Add("@" + TicketDurationDays, OleDbType.VarChar, 10, TicketDurationDays);
adapter.InsertCommand.Parameters.Add("@" + TicketDurationHours, OleDbType.VarChar, 10, TicketDurationHours);
adapter.InsertCommand.Parameters.Add("@" + TicketQueue, OleDbType.VarChar, 100, TicketQueue);
//Bulk upload
adapter.Update(data);
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}