public bool InsertRowofSpecialList(SpecialList special) { try { string sqlQuerry = ""; sqlQuerry += "insert into m_SpecialList (No, IDName,Name, Department, fromDate, toDate,Status,Update_datetime ) values( '"; sqlQuerry += special.No + "', '" + special.ID + "', '" + special.Name + "', '" + special.Department + "', '" + special.from + "', '" + special.to + "', '" + special.status + "',GETDATE() )"; sqlCON sqlCON = new sqlCON(); return(sqlCON.sqlExecuteNonQuery(sqlQuerry, false)); } catch (Exception ex) { return(false); } }
public List <SpecialList> GetSpecialLists(string pathExcelFile) { List <SpecialList> specialLists = new List <SpecialList>(); try { //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(pathExcelFile); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; //iterate over the rows and columns and print to the console as it appears in the file //excel is not zero based!! for (int i = 4; i <= 100; i++) { SpecialList special = new SpecialList(); special.No = (xlRange.Cells[i, 1].Value != null) ? xlRange.Cells[i, 1].Value.ToString() : ""; special.ID = (xlRange.Cells[i, 2].Value != null) ? xlRange.Cells[i, 2].Value.ToString() : ""; special.Name = (xlRange.Cells[i, 3].Value != null) ? xlRange.Cells[i, 3].Value.ToString() : ""; special.Department = (xlRange.Cells[i, 4].Value != null) ? xlRange.Cells[i, 4].Value.ToString() : ""; special.from = (xlRange.Cells[i, 5].Value != null) ? xlRange.Cells[i, 5].Value.ToString() : ""; special.to = (xlRange.Cells[i, 6].Value != null) ? xlRange.Cells[i, 6].Value.ToString() : ""; special.status = (xlRange.Cells[i, 7].Value != null) ? xlRange.Cells[i, 7].Value.ToString() : ""; if (special.ID.Trim() != "") { DataControl dataControl = new DataControl(); dataControl.InsertRowofSpecialList(special); specialLists.Add(special); } } //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); } catch (Exception ex) { Logfile.Output(StatusLog.Error, "List<SpecialList> GetSpecialLists(string pathExcelFile)", ex.Message); } return(specialLists); }