public GRVImport Insert(GRVImport ins) { //...Get User and Date Data... string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now); string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]); string strTrx = "GRVImportIns_" + EmployeeId; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); //...Command Interface... SqlCommand cmdI = con.CreateCommand(); SqlTransaction trx; trx = con.BeginTransaction(strTrx); cmdI.Connection = con; cmdI.Transaction = trx; try { //...Insert Record... cmdI.CommandText = StoredProcedures.GRVImportInsert; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@FileName", ins.FileName); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); ins.BatchId = (int)cmdI.ExecuteScalar(); trx.Commit(); cmdI.Connection.Close(); } catch (SqlException ex) { if (trx != null) { trx.Rollback(); } } finally { //Check for close and respond accordingly if (con.State != ConnectionState.Closed) { con.Close(); } //Clean up con.Dispose(); cmdI.Dispose(); trx.Dispose(); } return(ins); }
public List <GRVImport> GetAllGRVImports() { //...Create New Instance of Object... List <GRVImport> list = new List <GRVImport>(); GRVImport ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT * FROM t_GRVImport", con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new GRVImport(); ins.BatchId = Convert.ToInt32(drI["BatchID"]); ins.FileName = Convert.ToString(drI["FileName"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return(list); }
public ActionResult ImportFromExcel(FormCollection ins) { if (Request != null) { HttpPostedFileBase file = Request.Files["grv"]; string extension = System.IO.Path.GetExtension(file.FileName); string path1 = string.Format("{0}/{1}", Server.MapPath("~/Includes/Files/"), Request.Files[0].FileName); if (System.IO.File.Exists(path1)) System.IO.File.Delete(path1); Request.Files[0].SaveAs(path1); var excel = new ExcelQueryFactory(path1); excel.AddMapping<GRVExcel>(x => x.InvNo, "Inv No"); excel.AddMapping<GRVExcel>(x => x.REF, "REF"); excel.AddMapping<GRVExcel>(x => x.Typ, "Typ"); excel.AddMapping<GRVExcel>(x => x.Number, "Number"); excel.AddMapping<GRVExcel>(x => x.Seq, "SeqNo"); excel.AddMapping<GRVExcel>(x => x.GRVBook, "GRV Book"); excel.AddMapping<GRVExcel>(x => x.GRVDate, "GRV Date"); excel.AddMapping<GRVExcel>(x => x.InvDate, "Inv Date"); excel.AddMapping<GRVExcel>(x => x.SupplierName, "Supplier Name"); excel.AddMapping<GRVExcel>(x => x.ExclVAT, "Excl VAT"); excel.AddMapping<GRVExcel>(x => x.VAT, "VAT"); excel.AddMapping<GRVExcel>(x => x.InclVAT, "Incl VAT"); var grvlist = from c in excel.Worksheet<GRVExcel>() select c; //...Insert Batch... GRVImport imp = new GRVImport(); imp.FileName = file.FileName; imp = GRVRep.Insert(imp); //...Set Data to Model List List<GRVList> list = GRVRep.setData(grvlist, imp.BatchId); //...Save List to Database... for (int i = 0; i < list.Count; i++) { list[i] = GRVRep.InsertTemp(list[i]); } //...Display Imported Values return RedirectToAction("GRVImportBatch", new { BatchId = imp.BatchId }); } return RedirectToAction("GRVLists"); //return RedirectToAction("MemberImport"); }
public List<GRVImport> GetAllGRVImports() { //...Create New Instance of Object... List<GRVImport> list = new List<GRVImport>(); GRVImport ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT * FROM t_GRVImport", con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new GRVImport(); ins.BatchId = Convert.ToInt32(drI["BatchID"]); ins.FileName = Convert.ToString(drI["FileName"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return list; }
public GRVImport Insert(GRVImport ins) { //...Get User and Date Data... string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now); string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]); string strTrx = "GRVImportIns_" + EmployeeId; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); //...Command Interface... SqlCommand cmdI = con.CreateCommand(); SqlTransaction trx; trx = con.BeginTransaction(strTrx); cmdI.Connection = con; cmdI.Transaction = trx; try { //...Insert Record... cmdI.CommandText = StoredProcedures.GRVImportInsert; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@FileName", ins.FileName); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); ins.BatchId = (int)cmdI.ExecuteScalar(); trx.Commit(); cmdI.Connection.Close(); } catch (SqlException ex) { if (trx != null) trx.Rollback(); } finally { //Check for close and respond accordingly if (con.State != ConnectionState.Closed) { con.Close(); } //Clean up con.Dispose(); cmdI.Dispose(); trx.Dispose(); } return ins; }