public static bool OpenDatabase(string dbFileName)
{
try
{
Log.log("Opening sqlite database connection: "+dbFileName);
string cs = string.Format("Version=3,uri=file:{0}", dbFileName);
connection = new SqliteConnection(cs);
connection.Open();
trans = connection.BeginTransaction();
return true;
}
catch (Exception e)
{
return false;
}
}
/// <summary>
/// Initialize the command definition
/// </summary>
public CommandDefinition(string commandText, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null,
CommandType? commandType = null, CommandFlags flags = CommandFlags.Buffered
#if ASYNC
, CancellationToken cancellationToken = default(CancellationToken)
#endif
)
{
CommandText = commandText;
Parameters = parameters;
Transaction = transaction;
CommandTimeout = commandTimeout;
CommandType = commandType;
Flags = flags;
#if ASYNC
CancellationToken = cancellationToken;
#endif
}
private void BtnStart_Click(object sender, EventArgs e)
{
try
{
if (!checke1 || !checke2)
{
MessageBox.Show("请先检查出差方案，请重试");
return;
}
var dr = MessageBox.Show("确认开始吗？请提前备份好数据再开始", "确认", MessageBoxButtons.YesNo);
if (dr == DialogResult.Yes)
{
if (dal.Connection.State == System.Data.ConnectionState.Closed)
{
dal.Connection.Open();
}
tran = dal.Connection.BeginTransaction();
string strSourceSolutionId = labelFromSolutionId.Text;
string strToSolutionId = labelToSolutionID.Text;
var areaAll = (from ent in dal.T_OA_AREADIFFERENCE.Include("T_OA_TRAVELSOLUTIONS")
where ent.T_OA_TRAVELSOLUTIONS.TRAVELSOLUTIONSID == strSourceSolutionId//神州通集团出差方案
select ent).ToList();
var solutionitem = (from ent in dal.T_OA_TRAVELSOLUTIONS
where ent.TRAVELSOLUTIONSID == strToSolutionId
select ent).FirstOrDefault();
//复制城市分类
foreach (var area in areaAll)
{
StarCopy(solutionitem, area, strSourceSolutionId);
}
dal.SaveChanges();
tran.Commit();
}
}
catch (Exception ex)
{
tran.Rollback();
MessageBox.Show(ex.ToString());
}
}
public void BeginTransaction()
{
tran = DBContext.BeginTransaction(this.lbc);
}
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseReader"/> class from a DbTransaction.
/// </summary>
/// <param name="transaction">The transaction</param>
public DatabaseReader(System.Data.Common.DbTransaction transaction) :
this(transaction.Connection.GetType().Namespace, transaction.Connection.ConnectionString, new SchemaParameters(transaction))
{
}
public int Delete(string tableName, System.Collections.Hashtable ht, System.Data.Common.DbTransaction isOpenTrans)
{
throw new NotImplementedException();
}
/// <summary>
/// Update the specified field that matches the Where expression with the new data value
/// </summary>
/// <param name="select">The field to update</param>
/// <param name="where">The expression that determines the records selected</param>
/// <param name="newValue">The new value to set the specified field in all matching records</param>
/// <param name="connection">An open database connection</param>
/// <param name="transaction">The database connection transaction</param>
/// <returns>The number of records affected</returns>
public static int UpdateData <TSource>(Expression <Func <Gravitybox.Datastore.EFDAL.MachineQuery, TSource> > select, Expression <Func <Gravitybox.Datastore.EFDAL.MachineQuery, bool> > where, TSource newValue, System.Data.IDbConnection connection, System.Data.Common.DbTransaction transaction)
{
return(BusinessObjectQuery <Gravitybox.Datastore.EFDAL.Entity.Machine, Gravitybox.Datastore.EFDAL.MachineQuery, TSource> .UpdateData(select : select, where : where, newValue : newValue, leafTable : "Machine", getField : GetDatabaseFieldName, hasModifyAudit : false, startup : null, connection : connection, transaction : transaction));
}
public ReturnType UpdateUser(User user, List <Role> roles, List <Shop> shops, List <StockHouse> houses)
{
System.Data.Common.DbTransaction tran = null;
using (AladingEntities alading = new AladingEntities(AppSettings.GetConnectionString()))
{
try
{
alading.Connection.Open();
tran = alading.Connection.BeginTransaction();
User u1 = alading.User.FirstOrDefault(c => c.UserCode == user.UserCode);
if (u1 != null)
{
//更新用户
alading.Attach(u1);
alading.ApplyPropertyChanges("User", user);
//更新用户角色
var result1 = alading.UserRole.Where(c => c.UserCode == u1.UserCode);
foreach (UserRole s1 in result1)
{
alading.DeleteObject(s1);
}
alading.SaveChanges();
foreach (var i1 in roles)
{
UserRole e1 = new UserRole
{
UserRoleCode = Guid.NewGuid().ToString(),
UserCode = u1.UserCode,
RoleCode = i1.RoleCode,
RoleType = i1.RoleType,
};
alading.AddToUserRole(e1);
}
alading.SaveChanges();
//更新用户和店铺的关联
var return2 = alading.UserShop.Where(c => c.UserCode == u1.UserCode);
foreach (UserShop s2 in return2)
{
alading.DeleteObject(s2);
}
alading.SaveChanges();
foreach (var i2 in shops)
{
UserShop e2 = new UserShop
{
UserCode = u1.UserCode,
ShopId = i2.sid,
};
alading.AddToUserShop(e2);
}
alading.SaveChanges();
//更新用户和仓库的关联
var return3 = alading.UserStockHouse.Where(c => c.UserCode == u1.UserCode);
foreach (UserStockHouse s3 in return3)
{
alading.DeleteObject(s3);
}
alading.SaveChanges();
foreach (var i3 in houses)
{
UserStockHouse e3 = new UserStockHouse
{
UserCode = u1.UserCode,
StockHouseCode = i3.StockHouseCode,
};
alading.AddToUserStockHouse(e3);
}
alading.SaveChanges();
tran.Commit();
return(ReturnType.Success);
}
else
{
return(ReturnType.NotExisted);
}
}
catch (System.Exception ex)
{
if (tran != null)
{
tran.Rollback();
}
return(ReturnType.SaveFailed);
}
finally
{
if (alading != null && alading.Connection.State != System.Data.ConnectionState.Closed)
{
alading.Connection.Close();
}
}
}
}
/// <summary>
/// Inserts an entity into table "Ts" and returns identity id or number if inserted rows if inserting a list.
/// </summary>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToInsert">Entity to insert, can be list of entities</param>
/// <param name="transaction">The transaction to run under, null (the defualt) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Identity of inserted entity, or number of inserted rows if inserting a list</returns>
public static long Insert <T>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var isList = false;
var type = typeof(T);
if (type.IsArray)
{
isList = true;
type = type.GetElementType();
}
else if (type.IsGenericType())
{
isList = true;
type = type.GetGenericArguments()[0];
}
var name = GetTableName(type);
var sbColumnList = new StringBuilder(null);
var allProperties = TypePropertiesCache(type);
var keyProperties = KeyPropertiesCache(type).Where(k => k.PropertyType != typeof(Guid)); // We set Guids ourselves
var computedProperties = ComputedPropertiesCache(type);
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
var adapter = GetFormatter(connection);
for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++)
{
var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
adapter.AppendColumnName(sbColumnList, property.Name); //fix for issue #336
if (i < allPropertiesExceptKeyAndComputed.Count - 1)
{
sbColumnList.Append(", ");
}
}
var sbParameterList = new StringBuilder(null);
for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++)
{
var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
sbParameterList.AppendFormat("@{0}", property.Name);
if (i < allPropertiesExceptKeyAndComputed.Count - 1)
{
sbParameterList.Append(", ");
}
}
int returnVal;
var wasClosed = connection.State == ConnectionState.Closed;
if (wasClosed)
{
connection.Open();
}
if (!isList) //single entity
{
returnVal = adapter.Insert(connection, transaction, commandTimeout, name, sbColumnList.ToString(),
sbParameterList.ToString(), keyProperties, entityToInsert);
}
else
{
//insert list of entities
var cmd = $"insert into {name} ({sbColumnList}) values ({sbParameterList})";
returnVal = connection.Execute(cmd, entityToInsert, transaction, commandTimeout);
}
if (wasClosed)
{
connection.Close();
}
return(returnVal);
}
/// <summary>
/// Delete entity in table "Ts".
/// </summary>
/// <typeparam name="T">Type of entity</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToDelete">Entity to delete</param>
/// <param name="transaction">The transaction to run under, null (the defualt) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>true if deleted, false if not found</returns>
public static bool Delete <T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
if (entityToDelete == null)
{
throw new ArgumentException("Cannot Delete null Object", nameof(entityToDelete));
}
var type = typeof(T);
if (type.IsArray)
{
type = type.GetElementType();
}
else if (type.IsGenericType())
{
type = type.GetGenericArguments()[0];
}
var keyProperties = KeyPropertiesCache(type).ToList(); //added ToList() due to issue #418, must work on a list copy
var explicitKeyProperties = ExplicitKeyPropertiesCache(type);
if (!keyProperties.Any() && !explicitKeyProperties.Any())
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var name = GetTableName(type);
keyProperties.AddRange(explicitKeyProperties);
var sb = new StringBuilder();
sb.AppendFormat("delete from {0} where ", name);
var adapter = GetFormatter(connection);
for (var i = 0; i < keyProperties.Count; i++)
{
var property = keyProperties.ElementAt(i);
adapter.AppendColumnNameEqualsValue(sb, property.Name); //fix for issue #336
if (i < keyProperties.Count - 1)
{
sb.AppendFormat(" and ");
}
}
var deleted = connection.Execute(sb.ToString(), entityToDelete, transaction, commandTimeout);
return(deleted > 0);
}
public int Delete(T entity, System.Data.Common.DbTransaction isOpenTrans)
{
throw new NotImplementedException();
}
//Import All Selected Child of school, Dt: 11-April-2013, DB: A
public static bool ImportAllSelectedChild(Guid ChildDataId, Guid ChildFamilyId, Guid SchoolYearId)
{
DayCarePL.Logger.Write(DayCarePL.LogType.INFO, DayCarePL.ModuleToLog.clChildSchoolYear, "ImportAllSelectedChild", "Execute ImportAllSelectedChild Method", DayCarePL.Common.GUID_DEFAULT);
clConnection.DoConnection();
DayCareDataContext db = new DayCareDataContext();
System.Data.Common.DbTransaction tran = null;
try
{
DayCarePL.Logger.Write(DayCarePL.LogType.DEBUG, DayCarePL.ModuleToLog.clChildSchoolYear, "ImportAllSelectedChild", "Debug ImportAllSelectedChild Method", DayCarePL.Common.GUID_DEFAULT);
//ChildSchoolYear
if (db.Connection.State == System.Data.ConnectionState.Closed)
{
db.Connection.Open();
}
tran = db.Connection.BeginTransaction();
db.Transaction = tran;
//ChildFamilySchoolYear
bool IsActive = true;
ChildFamilySchoolYear DBChildFamilySchoolYear = db.ChildFamilySchoolYears.FirstOrDefault(i => i.ChildFamilyId.Equals(ChildFamilyId) && i.SchoolYearId.Equals(SchoolYearId));
if (DBChildFamilySchoolYear == null)
{
DBChildFamilySchoolYear = new ChildFamilySchoolYear();
DBChildFamilySchoolYear.Id = Guid.NewGuid();
DBChildFamilySchoolYear.ChildFamilyId = ChildFamilyId;
DBChildFamilySchoolYear.SchoolYearId = SchoolYearId;
DBChildFamilySchoolYear.active = true;
db.ChildFamilySchoolYears.InsertOnSubmit(DBChildFamilySchoolYear);
db.SubmitChanges();
}
else
{
IsActive = DBChildFamilySchoolYear.active == null ? false : DBChildFamilySchoolYear.active.Value;
}
ChildSchoolYear DBChildSchoolYear = db.ChildSchoolYears.FirstOrDefault(i => i.ChildDataId.Equals(ChildDataId) && i.SchoolYearId.Equals(SchoolYearId));
if (DBChildSchoolYear == null)
{
DBChildSchoolYear = new ChildSchoolYear();
DBChildSchoolYear.Id = Guid.NewGuid();
DBChildSchoolYear.ChildDataId = ChildDataId;
DBChildSchoolYear.SchoolYearId = SchoolYearId;
DBChildSchoolYear.active = IsActive;
db.ChildSchoolYears.InsertOnSubmit(DBChildSchoolYear);
db.SubmitChanges();
}
tran.Commit();
return(true);
}
catch (Exception ex)
{
DayCarePL.Logger.Write(DayCarePL.LogType.EXCEPTION, DayCarePL.ModuleToLog.clChildSchoolYear, "ImportAllSelectedChild", ex.Message.ToString(), DayCarePL.Common.GUID_DEFAULT);
if (tran != null)
{
tran.Rollback();
}
return(false);
}
finally
{
if (db.Connection.State == System.Data.ConnectionState.Open)
{
db.Connection.Close();
}
}
}
public bool InsertUser(UserInfo user, System.Data.Common.DbTransaction tran)
{
return(processor.Insert <UserInfo>(user, tran));
}
public int Insert(List <T> entity, System.Data.Common.DbTransaction isOpenTrans)
{
throw new NotImplementedException();
}
public int ExecuteByProc(string procName, System.Data.Common.DbParameter[] parameters, System.Data.Common.DbTransaction isOpenTrans)
{
throw new NotImplementedException();
}
public int ExecuteBySql(StringBuilder strSql, System.Data.Common.DbParameter[] parameters, System.Data.Common.DbTransaction isOpenTrans)
{
throw new NotImplementedException();
}
public int Delete(string propertyName, object[] propertyValue, System.Data.Common.DbTransaction isOpenTrans)
{
throw new NotImplementedException();
}
public void UploadFile(DetailProblem problem)
{
logger.DebugFormat("Begin UploadFile , WorkZoneId = {0}, workZOneDetailId id = {1}", problem.WorkZoneId, problem.WorkZoneDetailId);
WorkZone wk = WorkZone.GetWorkZone(problem.WorkZoneId);
string pathFolderProblem = Path.Combine(System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath, Common.GetFolderWorkZoneProblem(wk.CompanyId, wk.CompanyName, wk.FactoryId, wk.FactoryName, wk.WorkZoneId, wk.Name));
logger.Debug("PhysicalPath pathFolder =" + pathFolderProblem);
string uploadFilePath = "";
try
{
using (DBContext db = new DBContext())
{
using (System.Data.Common.DbTransaction tran = db.UseTransaction())
{
try
{
if (problem.FileId == 0) // case insert New
{
logger.Debug(" Case Insert new problem");
WorkZoneDetail d = WorkZoneDetail.GetWorkZoneDetail(problem.WorkZoneId, problem.WorkZoneDetailId);
d.Status = 1; // Has Problem
d.ModifiedAccount = problem.CreateAccount;
d.Update();
problem.FileId = DetailProblem.GetNextFileID(d.WorkZoneId, d.WorkZoneDetailId);
if (problem.Base64Data != null)
{
problem.ImageFile = string.Format("{0}_{1}.png", d.WorkZoneDetailId, problem.FileId);
}
problem.ModifiedAccount = problem.CreateAccount;
problem.Insert();
logger.Debug(" Update problem and WorkZone Detail");
if (!Directory.Exists(pathFolderProblem))
{
Directory.CreateDirectory(pathFolderProblem);
}
if (!string.IsNullOrEmpty(problem.Base64Data))
{
uploadFilePath = Path.Combine(pathFolderProblem, problem.ImageFile);
logger.Debug("Case Create file problem = " + uploadFilePath);
Byte[] data = Convert.FromBase64String(problem.Base64Data);
File.WriteAllBytes(uploadFilePath, data);
problem.Base64Data = null;
}
}
else if (problem.FileId != 0) // Update or Delete
{
DetailProblem p = DetailProblem.GetDetailProblem(problem.WorkZoneId, problem.WorkZoneDetailId, problem.FileId);
if (string.IsNullOrEmpty(problem.ImageFile))
{
//delete
logger.Debug("Case Delete detail Problem");
p.Delete();
uploadFilePath = Path.Combine(pathFolderProblem, p.ImageFile);
logger.Debug("Case Delete file uploadFilePath = " + uploadFilePath);
if (File.Exists(uploadFilePath))
{
File.Delete(uploadFilePath);
}
}
else
{
logger.Debug("Case update detail Problem");
p.Comment = problem.Comment;
p.ModifiedAccount = problem.CreateAccount;
p.Update();
}
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
logger.Error("Error UploadFile", ex);
throw ex;
}
}
}
logger.Debug("End UploadFile");
}
catch (Exception ex)
{
logger.Error("Error UploadFile ", ex);
throw ex;
}
}
public override void Execute(System.Data.Common.DbTransaction tran, string WorkflowCode, string FromWorkflowStepCode, string ToWorkflowStepCode, string EntityID, string Comment, string UserName)
{
//base.Execute(tran);
DataCommandService dataCommandDB = DataCommandService.GetInstance();
DataCommand command = DataCommand.GetDataCommand(this.ExecuteCommand);
if (command == null)
{
throw new ApplicationException(String.Format("DataCommand {0} could not be found in configuration", this.ExecuteCommand));
}
List <ScreenDataCommandParameter> parameters = new List <ScreenDataCommandParameter>();
ScreenDataCommandParameter parameter = null;
if (!String.IsNullOrEmpty(this.WorkflowCodeParameter))
{
parameter = new ScreenDataCommandParameter();
parameter.Name = this.WorkflowCodeParameter;
parameter.Value = WorkflowCode;
parameters.Add(parameter);
}
if (!String.IsNullOrEmpty(this.FromWorkflowStepCodeParameter))
{
parameter = new ScreenDataCommandParameter();
parameter.Name = this.FromWorkflowStepCodeParameter;
parameter.Value = FromWorkflowStepCode;
parameters.Add(parameter);
}
if (!String.IsNullOrEmpty(this.ToWorkflowStepCodeParameter))
{
parameter = new ScreenDataCommandParameter();
parameter.Name = this.ToWorkflowStepCodeParameter;
parameter.Value = ToWorkflowStepCode;
parameters.Add(parameter);
}
if (!String.IsNullOrEmpty(this.EntityIDParameter))
{
parameter = new ScreenDataCommandParameter();
parameter.Name = this.EntityIDParameter;
parameter.Value = EntityID;
parameters.Add(parameter);
}
if (!String.IsNullOrEmpty(this.CommentParameter))
{
parameter = new ScreenDataCommandParameter();
parameter.Name = this.CommentParameter;
parameter.Value = Comment;
parameters.Add(parameter);
}
if (!String.IsNullOrEmpty(this.UsernameParameter))
{
parameter = new ScreenDataCommandParameter();
parameter.Name = this.UsernameParameter;
parameter.Value = UserName;
parameters.Add(parameter);
}
//execute command with transaction
//CommandType type = (command.Type == DataCommandCommandType.StoredProcedure) ? CommandType.StoredProcedure : CommandType.Text;
object retVal = dataCommandDB.ExecuteCommand(tran, command, parameters, command.Text);
}
/// <summary>
/// Initialize the command for inserting PHRP data
/// </summary>
/// <param name="dbTransaction"></param>
public bool InitPHRPInsertCommand(out System.Data.Common.DbTransaction dbTransaction)
{
return(mDatabaseConnection.InitPHRPInsertCommand(out dbTransaction));
}
public bool InsertRequesition(string usr, ref string draftID, string voucherID, string sampleName, string LotNo,
string purpose, string sampleFrom, DateTime requireDate, DateTime sendDate, DateTime getDate,
string[] profiles)
{
bool NeedDraft = false;
if (draftID.Trim().Length < 11)
{
int count = draftCount(voucherID.Trim());
if (count < 0)
{
return(false);
}
else
{
// draftID = voucherID.Substring(1, 8) + count.ToString().PadLeft(3, '0');
draftID = voucherID.Substring(1, 8) + count.ToString().PadLeft(4, '0');
}
NeedDraft = true;
}
//
bool result = false;
string createDraft = @"INSERT INTO Draft VALUES(@DraftID,@Owner,GETDATE())";
//
string createDraftCommand = @"INSERT INTO ReqInDraft VALUES(@DraftID,@VoucherID,@Stamp)";
//
string createRequCommand = @"INSERT INTO Requisition(VoucherID,SampleName,LOT_NO,Purpose,SampleFrom,RequireDate,SendDate,GetDate) VALUES(@VoucherID,@SampleName,@LOT_NO,@Purpose,@SampleFrom,@RequireDate,@SendDate,@GetDate)";
System.Data.Common.DbTransaction tran = acQC.BeginTransaction();
//
try
{
if (NeedDraft)
{
acQC.ExecuteNonQuery(createDraft, new object[] { draftID, usr });
}
acQC.ExecuteNonQuery(createRequCommand, new object[] { voucherID, sampleName, LotNo, purpose, sampleFrom, requireDate, sendDate, getDate });
acQC.ExecuteNonQuery(createDraftCommand, new object[] { draftID, voucherID, System.DateTime.Now });
//
int i = 1;
foreach (string propertyName in profiles)
{
string sqlProfile = @"INSERT INTO Profile(VoucherID,Item,SampleName,PropertyName,Result,State) VALUES(@VoucherID,@Item,@SampleName,@PropertyName,@Result,@State)";
acQC.ExecuteNonQuery(sqlProfile, new object[] { voucherID, i, sampleName, propertyName, DBNull.Value, "0" });
i++;
}
tran.Commit();
result = true;
}
catch (Exception e)
{
tran.Rollback();
log.Error(e);
throw e;
}
return(result);
}
/// <summary>
/// Updates entity in table "Ts", checks if the entity is modified if the entity is tracked by the Get() extension.
/// </summary>
/// <typeparam name="T">Type to be updated</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToUpdate">Entity to be updated</param>
/// <param name="transaction">The transaction to run under, null (the defualt) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>true if updated, false if not found or not modified (tracked entities)</returns>
public static bool Update <T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var proxy = entityToUpdate as IProxy;
if (proxy != null)
{
if (!proxy.IsDirty)
{
return(false);
}
}
var type = typeof(T);
if (type.IsArray)
{
type = type.GetElementType();
}
else if (type.IsGenericType())
{
type = type.GetGenericArguments()[0];
}
var keyProperties = KeyPropertiesCache(type).ToList(); //added ToList() due to issue #418, must work on a list copy
var explicitKeyProperties = ExplicitKeyPropertiesCache(type);
if (!keyProperties.Any() && !explicitKeyProperties.Any())
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var name = GetTableName(type);
var sb = new StringBuilder();
sb.AppendFormat("update {0} set ", name);
var allProperties = TypePropertiesCache(type);
keyProperties.AddRange(explicitKeyProperties);
var computedProperties = ComputedPropertiesCache(type);
var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
var adapter = GetFormatter(connection);
for (var i = 0; i < nonIdProps.Count; i++)
{
var property = nonIdProps.ElementAt(i);
adapter.AppendColumnNameEqualsValue(sb, property.Name); //fix for issue #336
if (i < nonIdProps.Count - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(" where ");
for (var i = 0; i < keyProperties.Count; i++)
{
var property = keyProperties.ElementAt(i);
adapter.AppendColumnNameEqualsValue(sb, property.Name); //fix for issue #336
if (i < keyProperties.Count - 1)
{
sb.AppendFormat(" and ");
}
}
var updated = connection.Execute(sb.ToString(), entityToUpdate, commandTimeout: commandTimeout, transaction: transaction);
return(updated > 0);
}
private void bbiLuu_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
gbList.FocusedRowHandle += 1;
gbList.UpdateCurrentRow();
if (!Kiem_Tra())
{
return;
}
Data_QLKDataContext database = new Data_QLKDataContext(SqlHelper.ConnectionString);
try
{
database.Connection.Open();
System.Data.Common.DbTransaction transaction = database.Connection.BeginTransaction();
database.Transaction = transaction;
if (tinh_trang == "them")
{
CHUNG_TU chung_tu = new CHUNG_TU
{
Ma_Chung_Tu = txtMaChungTu.Text,
Ma_Hoa_Don = txtMaHoaDon.Text,
Ngay = txtNgay.DateTime,
Loai_Chung_Tu = 1, //1 la nhap kho 2 la xuat kho
Ma_Khach_Hang = string.IsNullOrEmpty(txtKhachHang.Text) ? "" : txtKhachHang.EditValue.ToString(),
Dia_Chi = txtDiaChi.Text,
Ly_Do = txtLyDo.Text,
VAT_Phan_Tram = txtVATPhanTram.Value,
VAT_Thanh_Tien = txtVATThanhTien.Value,
Chiet_Khau_Phan_Tram = txtChietKhauPhanTram.Value,
Chiet_Khau_Thanh_Tien = txtChietKhauThanhTien.Value,
Thanh_Tien = txtThanhTien.Value,
Hinh_Thuc_Cong_No = Convert.ToInt32(txtHinhThucCongNo.EditValue),
Hinh_Thuc_Thu_Chi = Convert.ToInt32(txtHinhThucThuChi.EditValue),
Thanh_Toan = txtThanhToan.Value,
Ghi_Chu = txtGhiChu.Text,
So_Thu_Tu = 0,
Nhan_Vien = string.IsNullOrEmpty(txtNhanVien.Text) ? "" : txtNhanVien.EditValue.ToString()
};
database.CHUNG_TUs.InsertOnSubmit(chung_tu);
}
else
{
var chung_tu = (from ct in database.CHUNG_TUs
where ct.Ma_Chung_Tu == txtMaChungTu.Text
select ct).FirstOrDefault();
chung_tu.Ma_Chung_Tu = txtMaChungTu.Text;
chung_tu.Ma_Hoa_Don = txtMaHoaDon.Text;
chung_tu.Ngay = txtNgay.DateTime;
chung_tu.Loai_Chung_Tu = 1; //1 la nhap kho 2 la xuat kho
chung_tu.Ma_Khach_Hang = string.IsNullOrEmpty(txtKhachHang.Text) ? "" : txtKhachHang.EditValue.ToString();
chung_tu.Dia_Chi = txtDiaChi.Text;
chung_tu.Ly_Do = txtLyDo.Text;
chung_tu.VAT_Phan_Tram = txtVATPhanTram.Value;
chung_tu.VAT_Thanh_Tien = txtVATThanhTien.Value;
chung_tu.Chiet_Khau_Phan_Tram = txtChietKhauPhanTram.Value;
chung_tu.Chiet_Khau_Thanh_Tien = txtChietKhauThanhTien.Value;
chung_tu.Thanh_Tien = txtThanhTien.Value;
chung_tu.Hinh_Thuc_Cong_No = Convert.ToInt32(txtHinhThucCongNo.EditValue);
chung_tu.Hinh_Thuc_Thu_Chi = Convert.ToInt32(txtHinhThucThuChi.EditValue);
chung_tu.Thanh_Toan = txtThanhToan.Value;
chung_tu.Ghi_Chu = txtGhiChu.Text;
chung_tu.Nhan_Vien = string.IsNullOrEmpty(txtNhanVien.Text) ? "" : txtNhanVien.EditValue.ToString();
chung_tu.So_Thu_Tu = 0;
}
if (Luu_Chi_Tiet(database, 0) == "OK")
{
database.SubmitChanges();
database.Transaction.Commit();
RaiseReloadEventHander();
Close();
}
else
{
database.Transaction.Rollback();
}
}
catch (Exception ex)
{
database.Transaction.Rollback();
MessageBox.Show(ex.ToString());
}
}
private void bbiLuu_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
gbList.FocusedRowHandle += 1;
gbList.UpdateCurrentRow();
if (!Kiem_Tra())
{
return;
}
Data_QLKDataContext database = new Data_QLKDataContext(SqlHelper.ConnectionString);
try
{
database.Connection.Open();
System.Data.Common.DbTransaction transaction = database.Connection.BeginTransaction();
database.Transaction = transaction;
long chungTuId = 0;
if (tinh_trang == "them")
{
CHUNG_TU chung_tu = new CHUNG_TU();
chung_tu.Ma_Chung_Tu = txtMaChungTu.Text;
chung_tu.Ngay = txtNgay.DateTime;
chung_tu.Loai_Chung_Tu = this.loai; //1 la nhap kho 2 la xuat kho
chung_tu.Ly_Do = txtLyDo.Text;
chung_tu.Ghi_Chu = txtGhiChu.Text;
chung_tu.CreateDate = DateTime.Now;
chung_tu.IsDeleted = false;
if (loai == 1)
{
chung_tu.Dau_Ky = cbNhapDauKy.Checked;
}
database.CHUNG_TUs.InsertOnSubmit(chung_tu);
database.SubmitChanges();
chungTuId = chung_tu.Id;
}
else
{
var chung_tu = (from ct in database.CHUNG_TUs
where ct.Id == this.id
select ct).FirstOrDefault();
chung_tu.Ma_Chung_Tu = txtMaChungTu.Text;
chung_tu.Ngay = txtNgay.DateTime;
chung_tu.Loai_Chung_Tu = this.loai; //1 la nhap kho 2 la xuat kho
chung_tu.Ly_Do = txtLyDo.Text;
chung_tu.Ghi_Chu = txtGhiChu.Text;
chung_tu.ModifiedDate = DateTime.Now;
if (loai == 1)
{
chung_tu.Dau_Ky = cbNhapDauKy.Checked;
}
chungTuId = chung_tu.Id;
}
if (Luu_Chi_Tiet(database, chungTuId) == "OK")
{
database.SubmitChanges();
database.Transaction.Commit();
RaiseReloadEventHander();
Close();
}
else
{
database.Transaction.Rollback();
}
}
catch (Exception ex)
{
database.Transaction.Rollback();
XtraMessageBox.Show(this, JsonConvert.SerializeObject(ex), "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public IDisposable BeginTransaction(IsolationLevel isolation)
{
NuoDBClientTransaction = NuoDBClientConnection.BeginTransaction(isolation);
return(NuoDBClientTransaction);
}
public bool ImportAllActiveChildFamily(Guid SchoolYearId, Guid SchoolId, Guid OldCurrentSchoolYearId, System.Data.Common.DbTransaction tran, DayCareDataContext dbold)
{
DayCarePL.Logger.Write(DayCarePL.LogType.INFO, DayCarePL.ModuleToLog.clChildFamily, "ImportAllActiveChildFamily", "Execute ImportAllActiveChildFamily Method", DayCarePL.Common.GUID_DEFAULT);
clConnection.DoConnection();
DayCareDataContext db = dbold;
db.Transaction = tran;
ChildFamilySchoolYear DBChildFamilySchoolYear = null;
try
{
this.ImportActiveChildFamilyCount = 0;
DayCarePL.Logger.Write(DayCarePL.LogType.DEBUG, DayCarePL.ModuleToLog.clChildFamily, "ImportAllActiveChildFamily", "Debug ImportAllActiveChildFamily Method", DayCarePL.Common.GUID_DEFAULT);
//Guid currentschoolyearid = (from sy in db.SchoolYears
// where sy.CurrentId.Equals(true)
// select sy.Id).SingleOrDefault();
//List<Guid> lstChildFamily = (from c in db.ChildDatas
// join cf in db.ChildFamilies on c.ChildFamilyId equals cf.Id
// where cf.SchoolId.Equals(SchoolId) && c.Active.Equals(true)
// && !(from cy in db.ChildSchoolYears
// where cy.SchoolYearId.Equals(SchoolYearId)
// select cy.ChildDataId).Contains(c.Id)
// select c.Id).ToList();
List <Guid> lstChildFamily = (from cf in db.ChildFamilies
join cfsy in db.ChildFamilySchoolYears on cf.Id equals cfsy.ChildFamilyId
where cf.SchoolId.Equals(SchoolId) && cfsy.active.Equals(true) && cfsy.SchoolYearId.Equals(OldCurrentSchoolYearId) &&
!(from cy in db.ChildFamilySchoolYears
where cy.SchoolYearId.Equals(SchoolYearId)
select cy.ChildFamilyId).Contains(cf.Id)
select cf.Id).ToList();
//var Data = db.spGetChildIdNotInChildSchoolYear(SchoolId, SchoolYearId);
foreach (Guid ChildFamilyId in lstChildFamily)
{
try
{
this.ImportActiveChildFamilyCount = lstChildFamily.Count;
DBChildFamilySchoolYear = new ChildFamilySchoolYear();
DBChildFamilySchoolYear.Id = Guid.NewGuid();
DBChildFamilySchoolYear.ChildFamilyId = ChildFamilyId;
DBChildFamilySchoolYear.SchoolYearId = SchoolYearId;
DBChildFamilySchoolYear.active = true;
db.ChildFamilySchoolYears.InsertOnSubmit(DBChildFamilySchoolYear);
db.SubmitChanges();
}
catch
{ }
}
return(true);
}
catch (Exception ex)
{
DayCarePL.Logger.Write(DayCarePL.LogType.EXCEPTION, DayCarePL.ModuleToLog.clChildFamily, "ImportAllActiveChildFamily", ex.Message.ToString(), DayCarePL.Common.GUID_DEFAULT);
return(false);
}
}
public DatabaseHelper(System.Data.Common.DbConnection connection, System.Data.Common.DbTransaction transaction)
{
this.dataAccess = new DataAccess(connection, transaction);
}
public static List <dynamic> DynamicSqlQuery(this Database database, string sql, System.Data.Common.DbTransaction transaction = null, params object[] parameters)
{
TypeBuilder builder = createTypeBuilder(
"MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");
using (System.Data.IDbCommand command = database.Connection.CreateCommand())
{
try
{
if (database.Connection.State != ConnectionState.Open)
{
database.Connection.Open();
}
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandText = sql;
command.CommandTimeout = command.Connection.ConnectionTimeout;
foreach (var param in parameters)
{
command.Parameters.Add(param);
}
using (System.Data.IDataReader reader = command.ExecuteReader())
{
var schema = reader.GetSchemaTable();
foreach (System.Data.DataRow row in schema.Rows)
{
string name = (string)row["ColumnName"];
//var a=row.ItemArray.Select(d=>d.)
Type type = (Type)row["DataType"];
if (type != typeof(string) && (bool)row.ItemArray[schema.Columns.IndexOf("AllowDbNull")])
{
type = typeof(Nullable <>).MakeGenericType(type);
}
createAutoImplementedProperty(builder, name, type);
}
}
}
finally
{
database.Connection.Close();
command.Parameters.Clear();
}
}
Type resultType = builder.CreateType();
return(database.SqlQuery(resultType, sql, parameters).Cast <dynamic>().ToList());
}
/// <summary>
/// Rolls back a transaction.
/// </summary>
/// <param name="transaction">The transaction.</param>
public new void RollbackTransaction(System.Data.Common.DbTransaction transaction)
{
base.RollbackTransaction(transaction);
}
/// <summary>
/// Commits a transaction.
/// </summary>
/// <param name="transaction">The transaction.</param>
public new void CommitTransaction(System.Data.Common.DbTransaction transaction)
{
base.CommitTransaction(transaction);
}
/// <summary>
/// 标记为配货并出库
/// </summary>
/// <param name="houseProductList">数量即为出库数量</param>
/// <param name="customtid">交易的唯一标识</param>
/// <returns></returns>
public ReturnType AllocationAndOutput(List <StockHouseProduct> houseProductList, string customtid)
{
System.Data.Common.DbTransaction tran = null;
using (AladingEntities alading = new AladingEntities(AppSettings.GetConnectionString()))
{
try
{
alading.Connection.Open();
tran = alading.Connection.BeginTransaction();
StockInOut stockInOut = new StockInOut();
stockInOut.InOutCode = System.Guid.NewGuid().ToString();//GUID??
foreach (StockHouseProduct shp in houseProductList)
{
StockProduct stockProduct = alading.StockProduct.FirstOrDefault(c => c.SkuOuterID == shp.SkuOuterID);
stockProduct.SkuQuantity -= shp.Num;
StockItem item = alading.StockItem.FirstOrDefault(c => c.OuterID == stockProduct.OuterID);
item.TotalQuantity -= shp.Num;
StockHouseProduct stockHouseProduct = alading.StockHouseProduct.FirstOrDefault(c => c.SkuOuterID == shp.SkuOuterID && c.HouseCode == shp.HouseCode && c.LayoutCode == shp.LayoutCode);
stockHouseProduct.Num -= shp.Num;
#region StockDetail
StockDetail stockDetail = new StockDetail();
stockDetail.DetailRemark = "销售自动出库";
stockDetail.DetailType = (int)DetailType.TaobaoSaleOut;
stockDetail.DurabilityDate = DateTime.Now;
stockDetail.HouseName = stockHouseProduct.HouseName;
stockDetail.InOutCode = stockInOut.InOutCode;//////////
stockDetail.LayoutName = stockHouseProduct.LayoutCode;
stockDetail.Price = float.Parse(stockProduct.SkuPrice.ToString());
stockDetail.ProductSkuOuterId = stockProduct.SkuOuterID;
stockDetail.Quantity = shp.Num; //出库数量
stockDetail.SearchText = string.Empty; //搜索字段
stockDetail.StockDetailCode = System.Guid.NewGuid().ToString(); //
stockDetail.StockHouseCode = stockHouseProduct.HouseCode;
stockDetail.StockLayOutCode = stockHouseProduct.LayoutCode;
stockDetail.Tax = item.Tax;//税率
stockDetail.TotalFee = float.Parse((stockProduct.SkuPrice * shp.Num).ToString());
alading.AddToStockDetail(stockDetail);
#endregion
stockInOut.DiscountFee += stockDetail.TotalFee;//这么算可对？
}
Trade trade = alading.Trade.FirstOrDefault(c => c.CustomTid == customtid);
trade.LocalStatus = LocalTradeStatus.AssortedNotSent;
stockInOut.AmountTax = 0.0f; //税率怎么算？
stockInOut.DueFee = stockInOut.DiscountFee; //??
stockInOut.FreightCode = string.Empty;
stockInOut.FreightCompany = string.Empty;
stockInOut.HouseCodeIn = string.Empty;
stockInOut.HouseCodeOut = string.Empty;
stockInOut.HouseNameIn = string.Empty;
stockInOut.HouseNameOut = string.Empty;
stockInOut.IncomeTime = DateTime.Now;
stockInOut.InOutStatus = (int)InOutStatus.AllSend;
stockInOut.InOutTime = DateTime.Now;
stockInOut.InOutType = (int)InOutType.SaleOut;
stockInOut.IsSettled = true;
stockInOut.OperatorCode = string.Empty; //操作人编码？？？
stockInOut.OperatorName = string.Empty; //操作人姓名？？
stockInOut.PayTerm = 0;;
stockInOut.PayThisTime = stockInOut.DueFee;
stockInOut.PayType = (int)PayType.ALIPAY;
stockInOut.SearchText = string.Empty;//搜索字段
stockInOut.TradeOrderCode = trade.CustomTid;
stockInOut.TransportCode = string.Empty;
alading.AddToStockInOut(stockInOut);
alading.SaveChanges();
tran.Commit();
return(ReturnType.Success);
}
catch (System.Exception ex)
{
if (tran != null)
{
tran.Rollback();
}
return(ReturnType.SaveFailed);
}
finally
{
if (alading != null && alading.Connection.State != System.Data.ConnectionState.Closed)
{
alading.Connection.Close();
}
}
}
}
public void BeginTransaction()
{
_dbTrans = DataContext.Database.Connection.BeginTransaction();
}
internal new bool Edit(BO.ORCID.Person person, System.Data.Common.DbTransaction trans)
{
return(base.Edit(person, trans));
}
public static IDataReader ExecuteReaderSql(this IDbConnection connection, string sql, object parameters = null,
IDbTransaction transaction = null, int? commandTimeout = null)
{
return SqlMapper.ExecuteReader(connection, sql, parameters, transaction, commandTimeout, CommandType.StoredProcedure);
}
public static int UpdateData(
Expression <Func <K, P> > select,
Expression <Func <K, bool> > where,
P newValue,
string leafTable,
GetDatabaseFieldNameDelegate getField,
bool hasModifyAudit,
ContextStartup startup,
IDbConnection connection,
System.Data.Common.DbTransaction transaction
)
{
if (startup == null)
{
startup = new ContextStartup(null);
}
using (var dc = new DataContext(connection))
{
var template = dc.GetTable <K>();
using (var cmd = BusinessEntityQuery.GetCommand <K, P>(dc, template, select, where))
{
if (!startup.DefaultTimeout && startup.CommandTimeout > 0)
{
cmd.CommandTimeout = startup.CommandTimeout;
}
else
{
cmd.CommandTimeout = DEFAULTTIMEOUT;
}
if (transaction != null)
{
cmd.Transaction = transaction;
}
var parser = LinqSQLParser.Create(cmd.CommandText, LinqSQLParser.ObjectTypeConstants.Table);
var fieldName = parser.GetSelectClause();
var sql = "UPDATE [" + parser.GetTableAlias(fieldName, leafTable) + "]\r\n";
sql += "SET [" + parser.GetTableAlias(fieldName, leafTable) + "].[" + fieldName + "] = @newValue\r\n";
if (hasModifyAudit && (fieldName != "ModifiedBy"))
{
sql += ", [" + parser.GetTableAlias(fieldName, leafTable) + "].[ModifiedBy] = NULL\r\n";
}
if (hasModifyAudit && (fieldName != "ModifiedDate"))
{
sql += ", [" + parser.GetTableAlias(fieldName, leafTable) + "].[ModifiedDate] = sysdatetime()\r\n";
}
sql += parser.GetFromClause(new QueryOptimizer()) + "\r\n";
sql += parser.GetWhereClause();
sql += ";select @@rowcount";
sql = "set ansi_nulls off;" + sql;
cmd.CommandText = sql;
if (newValue == null)
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("newValue", System.DBNull.Value));
}
else
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("newValue", newValue));
}
object p = cmd.ExecuteScalar();
return((int)p);
}
}
}