/// <summary> /// Insert table into DB by table Name /// </summary> /// <param name="dt">DataTable</param> /// <param name="tableName">string</param> /// <returns>int</returns> public static int InsertFile(DataTable dt, string tableName) { using (var context = new DocumentDBEntities()) { using (var dbContextTransaction = context.Database.BeginTransaction()) { try { using (var bulkInsert = new SqlBulkCopy(context.Database.Connection.ConnectionString)) { bulkInsert.DestinationTableName = tableName; bulkInsert.WriteToServer(dt); } dbContextTransaction.Commit(); return(0); } catch (Exception ex) { log.ErrorFormat("==> InsertFile is error, tableName=[{0}]", tableName); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); dbContextTransaction.Rollback(); return(-1); } } } }
/// <summary> /// Load data by query clause to dataTable /// </summary> /// <param name="commandText">string</param> /// <returns>DataTable</returns> public static DataTable LoadTable(string commandText) { using (var context = new DocumentDBEntities()) { try { using (var connection = context.Database.Connection) { using (var command = connection.CreateCommand()) { command.CommandText = commandText; connection.Open(); using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess)) { using (var tbResult = new DataTable()) { tbResult.Load(reader); return(tbResult); } } } } } catch (Exception ex) { log.ErrorFormat("==> LoadTable is error, CommandText=[{0}]", commandText); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); return(null); } } }
/// <summary> /// Execute a procedure or query with query style is no query /// </summary> /// <param name="procedureName">string</param> /// <param name="list">params object[]</param> /// <returns>int</returns> public int ExecuteNoQuery(string procedureName, params object[] list) { using (var context = new DocumentDBEntities()) { using (var dbContextTransaction = context.Database.BeginTransaction()) { try { int result = context.Database.ExecuteSqlCommand(procedureName, list); dbContextTransaction.Commit(); return(result); } catch (DbEntityValidationException dbex) { string paramsList = string.Empty; log.ErrorFormat("==> ExecuteNoQuery entity is error :", dbex.Message); log.ErrorFormat("==> Store procedureName or query string =[{0}], params list=[{1}]", procedureName, String.Join(paramsList, list)); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", dbex.Message, dbex.InnerException, dbex.StackTrace); dbContextTransaction.Rollback(); return(-2); } catch (Exception ex) { string paramsList = string.Empty; log.ErrorFormat("==> ExecuteNoQuery entity is error :", ex.Message); log.ErrorFormat("==> Store procedureName or query string =[{0}], params list=[{1}]", procedureName, String.Join(paramsList, list)); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); dbContextTransaction.Rollback(); return(-2); } } } }
/// <summary> /// Delete current entity /// </summary> /// <param name="entity">TEntity</param> /// <returns>int</returns> public int Delete(params TEntity[] entitys) { using (var context = new DocumentDBEntities()) { using (var dbContextTransaction = context.Database.BeginTransaction()) { try { context.Set <TEntity>().RemoveRange(entitys); int result = context.SaveChanges(); dbContextTransaction.Commit(); return(result); } catch (DbEntityValidationException dbex) { log.ErrorFormat("==> Delete entity is error :", entitys.GetType().Name); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", dbex.Message, dbex.InnerException, dbex.StackTrace); dbContextTransaction.Rollback(); return(-2); } catch (Exception ex) { log.ErrorFormat("==> Delete entity is error :", entitys.GetType().Name); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); dbContextTransaction.Rollback(); return(-2); } } } }
/// <summary> /// Get list entity /// </summary> /// <returns>List<TEntity></returns> public IEnumerable <TEntity> Select() { try { var context = new DocumentDBEntities(); return(context.Set <TEntity>().AsNoTracking().ToList <TEntity>()); } catch (Exception ex) { log.ErrorFormat("==> Get all data [{0}] is error", typeof(TEntity).Name); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); return(null); } }
/// <summary> /// Get current datetime in server /// </summary> /// <returns></returns> public DateTime GetDate() { try { using (var context = new DocumentDBEntities()) { return(context.Database.SqlQuery <DateTime>("SELECT GetDate()").First()); } } catch (Exception ex) { log.Error("==> Get current date on server is error"); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); return(DateTime.Now); } }
/// <summary> /// Get list entity by function and predicate /// </summary> /// <param name="predicate">Expression<Func<TEntity, bool>></param> /// <returns>List<TEntity></returns> public IEnumerable <TEntity> Select(Expression <Func <TEntity, bool> > predicate) { try { var context = new DocumentDBEntities(); List <TEntity> result = context.Set <TEntity>().AsNoTracking().Where(predicate).ToList <TEntity>(); return(result); } catch (Exception ex) { string paramsList = string.Empty; log.ErrorFormat("==> Get all data [{0}] by where is error", predicate.GetType().Name); log.ErrorFormat("==> Where clause =[{0}]", predicate.Body); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); return(null); } }
/// <summary> /// Execute a procedure or a query by user definition /// </summary> /// <param name="procedureName">string</param> /// <param name="paramsValue">params</param> /// <returns>IEnumerable</returns> public IEnumerable <TEntity> Executing(string procedureName, params object[] paramsValue) { try { using (var context = new DocumentDBEntities()) { return(context.Database.SqlQuery <TEntity>(procedureName, paramsValue).ToList()); } } catch (Exception ex) { string paramsList = string.Empty; log.ErrorFormat("==> Executing procedure or query string is error :", procedureName + "- " + ex.Message); log.ErrorFormat("==> Procedure or query string =[{0}], paramlist =[{1}]", procedureName, String.Join(paramsList, paramsValue)); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); return(null); } }
/// <summary> /// Update value for entity /// </summary> /// <param name="entitys">TEntity</param> /// <returns>int</returns> public int Update(params TEntity[] entitys) { using (var context = new DocumentDBEntities()) { using (var dbContextTransaction = context.Database.BeginTransaction()) { try { foreach (var updateItem in entitys) { var update = context.Set <TEntity>().Add(updateItem); context.Entry <TEntity>(updateItem).State = System.Data.Entity.EntityState.Modified; } int result = context.SaveChanges(); dbContextTransaction.Commit(); return(result); } catch (DbUpdateConcurrencyException dbucex) { log.ErrorFormat("==> Update entity is error", entitys.GetType().Name); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", dbucex.Message, dbucex.InnerException, dbucex.StackTrace); dbContextTransaction.Rollback(); return(-2); } catch (System.Data.Entity.Validation.DbEntityValidationException dbevex) { log.ErrorFormat("==> Update entity is error", entitys.GetType().Name); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", dbevex.Message, dbevex.InnerException, dbevex.StackTrace); dbContextTransaction.Rollback(); return(-2); } catch (Exception ex) { log.ErrorFormat("==> Update entity is error", entitys.GetType().Name); log.ErrorFormat("Error is unexpected message=[{0}],\n InnerException=[{1}],\n StackTrace=[{2}]", ex.Message, ex.InnerException, ex.StackTrace); dbContextTransaction.Rollback(); return(-2); } } } }