public List <Membership> GetMembersCollection(MemberCollection mem)
{
try
{
members = new List <Membership>();
string Select = "SELECT MonthlyFee from MembershipData where MemberName='" + mem.MemberName + "'";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
tran = con.BeginTransaction();
cmd = new OleDbCommand(Select, con);
cmd.Transaction = tran;
dr = cmd.ExecuteReader();
while (dr.Read())
{
Membership m = new Membership();
Donor d = new Donor();
m.MonthlyFee = dr["MonthlyFee"] == System.DBNull.Value ? 0 : Convert.ToDecimal(dr["MonthlyFee"]);
members.Add(m);
}
dr.Close();
tran.Commit();
return(members);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// 演示 事务处理的部分。
/// </summary>
/// <param name="conn"></param>
private void Transaction(OleDbConnection conn)
{
Console.WriteLine("开始事务处理操作！");
// 开始创建一个事务.
OleDbTransaction t = conn.BeginTransaction();
try
{
Console.WriteLine("模拟插入2条相同数据，造成主键冲突的异常！");
InsertData(conn, t);
InsertData(conn, t);
// 提交事务.
t.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
// 回滚事务.
t.Rollback();
}
if (ExistData(conn))
{
Console.WriteLine("数据库中依然存在着编号为{0}的数据！", TEST_TYPE_CODE);
}
else
{
Console.WriteLine("数据库中已检索不到编号为{0}的数据！", TEST_TYPE_CODE);
}
}
public void UpdateClaimStatus(LotteryWinningBet winBet)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " UPDATE lottery_winning_bet SET claim_status = @claim_status " +
" WHERE ID = @id AND active = true";
command.Parameters.AddWithValue("@claim_status", winBet.IsClaimed());
command.Parameters.AddWithValue("@id", winBet.GetID());
command.Connection = conn;
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(String.Format(ResourcesUtils.GetMessage("lot_dao_impl_msg12"), winBet.GetID()));
}
transaction.Commit();
}
}
private bool InsertFC_CLJBXX(DataView dv, string flag, string rllxParam)
{
bool result = false;
using (OleDbConnection con = new OleDbConnection(AccessHelper.conn))
{
con.Open();
OleDbTransaction tra = null; //创建事务，开始执行事务
try
{
tra = con.BeginTransaction();
foreach (DataRow drMain in dv.Table.Rows)
{
#region 待生成的燃料基本信息数据存入燃料基本信息表
string vin = drMain["VIN"].ToString().Trim();
string sqlQueryHGSPBM = String.Format("SELECT * FROM FC_CLJBXX WHERE VIN='{0}'", vin);
string strHGSPBM = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlQueryHGSPBM, null).Tables[0].Rows[0]["HGSPBM"].ToString();
string sqlDeleteBasic = "DELETE FROM FC_CLJBXX WHERE VIN='" + vin + "'";
AccessHelper.ExecuteNonQuery(tra, sqlDeleteBasic, null);
string sqlInsertBasic = @"INSERT INTO FC_CLJBXX
( VIN,USER_ID,QCSCQY,JKQCZJXS,CLZZRQ,UPLOADDEADLINE,CLXH,CLZL,
RLLX,ZCZBZL,ZGCS,LTGG,ZJ,
TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ,
QDXS,JYJGMC,JYBGBH,HGSPBM,QTXX,STATUS,CREATETIME,UPDATETIME
) VALUES
( @VIN,@USER_ID,@QCSCQY,@JKQCZJXS,@CLZZRQ,@UPLOADDEADLINE,@CLXH,@CLZL,
@RLLX,@ZCZBZL,@ZGCS,@LTGG,@ZJ,
@TYMC,@YYC,@ZWPS,@ZDSJZZL,@EDZK,@LJ,
@QDXS,@JYJGMC,@JYBGBH,@HGSPBM,@QTXX,@STATUS,@CREATETIME,@UPDATETIME)";
DateTime clzzrqDate;
try
{
clzzrqDate = DateTime.ParseExact(drMain["CLZZRQ"].ToString().Trim(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
}
catch (Exception)
{
clzzrqDate = Convert.ToDateTime(drMain["CLZZRQ"]);
}
OleDbParameter clzzrq = new OleDbParameter("@CLZZRQ", clzzrqDate)
{
OleDbType = OleDbType.DBDate
};
DateTime uploadDeadlineDate = miutils.QueryUploadDeadLine(clzzrqDate);
OleDbParameter uploadDeadline = new OleDbParameter("@UPLOADDEADLINE", uploadDeadlineDate)
{
OleDbType = OleDbType.DBDate
};
OleDbParameter creTime = new OleDbParameter("@CREATETIME", DateTime.Now)
{
OleDbType = OleDbType.DBDate
};
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now)
{
OleDbType = OleDbType.DBDate
};
string qtxx = string.Empty;
if (dv.Table.Columns.Contains("CT_QTXX"))
{
qtxx = drMain["CT_QTXX"].ToString().Trim();
}
else if (dv.Table.Columns.Contains("QTXX"))
{
qtxx = drMain["QTXX"].ToString().Trim();
}
OleDbParameter[] param =
{
new OleDbParameter("@VIN", drMain["VIN"].ToString().Trim()),
new OleDbParameter("@USER_ID", Utils.userId),
new OleDbParameter("@QCSCQY", drMain["QCSCQY"].ToString().Trim()),
new OleDbParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString().Trim()),
clzzrq,
uploadDeadline,
new OleDbParameter("@CLXH", drMain["CLXH"].ToString().Trim()),
new OleDbParameter("@CLZL", drMain["CLZL"].ToString().Trim()),
new OleDbParameter("@RLLX", drMain["RLLX"].ToString().Trim()),
new OleDbParameter("@ZCZBZL", drMain["ZCZBZL"].ToString().Trim()),
new OleDbParameter("@ZGCS", drMain["ZGCS"].ToString().Trim()),
new OleDbParameter("@LTGG", drMain["LTGG"].ToString().Trim()),
new OleDbParameter("@ZJ", drMain["ZJ"].ToString().Trim()),
new OleDbParameter("@TYMC", drMain["TYMC"].ToString().Trim()),
new OleDbParameter("@YYC", drMain["YYC"].ToString().Trim()),
new OleDbParameter("@ZWPS", drMain["ZWPS"].ToString().Trim()),
new OleDbParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString().Trim()),
new OleDbParameter("@EDZK", drMain["EDZK"].ToString().Trim()),
new OleDbParameter("@LJ", drMain["LJ"].ToString().Trim()),
new OleDbParameter("@QDXS", drMain["QDXS"].ToString().Trim()),
new OleDbParameter("@JYJGMC", drMain["JYJGMC"].ToString().Trim()),
new OleDbParameter("@JYBGBH", drMain["JYBGBH"].ToString().Trim()),
new OleDbParameter("@HGSPBM", strHGSPBM),
new OleDbParameter("@QTXX", qtxx),
// 1：待上报，补传待上报/2：已修改未上报
new OleDbParameter("@STATUS", flag),
creTime,
upTime
};
AccessHelper.ExecuteNonQuery(tra, sqlInsertBasic, param);
#endregion
#region 插入参数信息
string sqlDelParam = "DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='" + drMain["VIN"].ToString().Trim() + "'";
AccessHelper.ExecuteNonQuery(tra, sqlDelParam, null);
var rows = dtCtnyPam.Select("FUEL_TYPE='" + rllxParam + "' and STATUS='1'");
// 待生成的燃料参数信息存入燃料参数表
foreach (DataRow drParam in rows)
{
string paramCode = drParam["PARAM_CODE"].ToString().Trim();
if (paramCode.Equals("CT_QTXX"))
{
continue;
}
string sqlInsertParam = @"INSERT INTO RLLX_PARAM_ENTITY
(PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES
(@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)";
OleDbParameter[] paramList =
{
new OleDbParameter("@PARAM_CODE", paramCode),
new OleDbParameter("@VIN", drMain["VIN"].ToString().Trim()),
new OleDbParameter("@PARAM_VALUE", drMain[paramCode].ToString().Trim()),
new OleDbParameter("@V_ID", "")
};
AccessHelper.ExecuteNonQuery(tra, sqlInsertParam, paramList);
}
#endregion
}
tra.Commit();
result = true;
}
catch (Exception ex)
{
tra.Rollback();
throw ex;
}
finally
{
tra.Dispose();
con.Close();
}
}
return(result);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="dbset">SQL链接</param>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static int ExecuteSqlTran(List <CommandInfo> cmdList)
{
using (OleDbConnection conn = new OleDbConnection(GetConnectionStringForAccess()))
{
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
OleDbCommand cmd = new OleDbCommand();
try
{
int count = 0;
//循环
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return(0);
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return(0);
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return(0);
}
continue;
}
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return(0);
}
cmd.Parameters.Clear();
}
trans.Commit();
return(count);
}
catch
{
trans.Rollback();
throw;
}
}
}
}
protected string UpdateFuelData(string vin, string status, DataRow drMain, DataTable dtPam, OleDbConnection con)
{
string message = string.Empty;
OleDbTransaction tra = null; //创建事务，开始执行事务
try
{
tra = con.BeginTransaction();
#region 更新基本信息表
string sqlUpdateBasic = @"UPDATE FC_CLJBXX SET
USER_ID=@USER_ID,QCSCQY=@QCSCQY,JKQCZJXS=@JKQCZJXS,CLXH=@CLXH,CLZL=@CLZL,
RLLX=@RLLX,ZCZBZL=@ZCZBZL,ZGCS=@ZGCS,LTGG=@LTGG,ZJ=@ZJ,
TYMC=@TYMC,YYC=@YYC,ZWPS=@ZWPS,ZDSJZZL=@ZDSJZZL,EDZK=@EDZK,LJ=@LJ,
QDXS=@QDXS,JYJGMC=@JYJGMC,JYBGBH=@JYBGBH,STATUS=@STATUS,UPDATETIME=@UPDATETIME,
HGSPBM=@HGSPBM,QTXX=@QTXX
WHERE VIN=@VIN";
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now);
upTime.OleDbType = OleDbType.DBDate;
OleDbParameter[] parameters =
{
new OleDbParameter("@USER_ID", Utils.localUserId),
new OleDbParameter("@QCSCQY", drMain["QCSCQY"].ToString()),
new OleDbParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString()),
new OleDbParameter("@CLXH", drMain["CLXH"].ToString()),
new OleDbParameter("@CLZL", drMain["CLZL"].ToString()),
new OleDbParameter("@RLLX", drMain["RLLX"].ToString()),
new OleDbParameter("@ZCZBZL", drMain["ZCZBZL"].ToString()),
new OleDbParameter("@ZGCS", drMain["ZGCS"].ToString()),
new OleDbParameter("@LTGG", drMain["LTGG"].ToString()),
new OleDbParameter("@ZJ", drMain["ZJ"].ToString()),
new OleDbParameter("@TYMC", drMain["TYMC"].ToString()),
new OleDbParameter("@YYC", drMain["YYC"].ToString()),
new OleDbParameter("@ZWPS", drMain["ZWPS"].ToString()),
new OleDbParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString()),
new OleDbParameter("@EDZK", drMain["EDZK"].ToString()),
new OleDbParameter("@LJ", drMain["LJ"].ToString()),
new OleDbParameter("@QDXS", drMain["QDXS"].ToString()),
new OleDbParameter("@JYJGMC", drMain["JYJGMC"].ToString()),
new OleDbParameter("@JYBGBH", drMain["JYBGBH"].ToString()),
new OleDbParameter("@STATUS", status),
upTime,
new OleDbParameter("@HGSPBM", drMain["HGSPBM"].ToString()),
new OleDbParameter("@QTXX", drMain["CT_QTXX"].ToString()),
new OleDbParameter("@VIN", vin)
};
AccessHelper.ExecuteNonQuery(tra, sqlUpdateBasic, parameters);
#endregion
#region 插入参数信息
// 更新燃料参数表
foreach (DataRow drParam in dtPam.Rows)
{
string paramCode = drParam["PARAM_CODE"].ToString().Trim();
string sqlUpdateParam = @"UPDATE RLLX_PARAM_ENTITY
SET PARAM_VALUE='" + drMain[paramCode] + "' WHERE VIN='" + vin + "' AND PARAM_CODE='" + paramCode + "'";
//OleDbParameter[] paramList = {
// new OleDbParameter("@PARAM_CODE",paramCode),
// new OleDbParameter("@VIN",vin),
// new OleDbParameter("@PARAM_VALUE",drMain[paramCode]),
// };
AccessHelper.ExecuteNonQuery(tra, sqlUpdateParam, null);
}
tra.Commit();
#endregion
}
catch (Exception ex)
{
tra.Rollback();
message = ex.Message + "\r\n";
}
return(message);
}
/// <summary>
/// Execute the SQL statement does not return results, such as: delete, update, insert operation
/// </summary>
/// <param name="strSQL">SQL String of a non Query Type</param>
/// <returns>success or failure</returns>
public bool UpdateOrderHeader(OrderHeaderData pOrderHeader, List <string> pOrders)
{
bool _resultState = false;
string _strSQL = CONST_ORDERUPDATEHEADER_SQL + " WHERE ";
// for all the OrderIds passed create a where clause
for (int i = 0; i < pOrders.Count - 1; i++)
{
_strSQL += " OrderID = " + pOrders[i] + " OR";
}
_strSQL += " OrderID = " + pOrders[pOrders.Count - 1];
_TrackerDbConn.Open();
OleDbTransaction _myTrans = _TrackerDbConn.BeginTransaction();
// UPDATE order CustomerId = ?, OrderDate= ?, RoastDate= ?, RequiredByDate= ?, ToBeDeliveredBy= ?, Confirmed= ?, Done= ?, Notes = ? WHERE (OrderId = ?)";
OleDbCommand _command = new OleDbCommand(_strSQL, _TrackerDbConn, _myTrans);
// add parameters in the order of the SQL command
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.CustomerID
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.OrderDate
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.RoastDate
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.RequiredByDate
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.ToBeDeliveredBy
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.Confirmed
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.Done
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.Notes
});
// _command.Parameters.Add(new OleDbParameter { Value =pOrderId});
try
{
_command.ExecuteNonQuery();
_myTrans.Commit();
_resultState = true;
}
catch
{
_myTrans.Rollback();
_resultState = false;
}
finally
{
_TrackerDbConn.Close();
}
return(_resultState);
}
//
// RoleProvider.RemoveUsersFromRoles
//
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!IsUserInRole(username, rolename))
{
throw new ProviderException("User is not in role.");
}
}
}
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand("DELETE FROM usersinroles WHERE username = ? AND rolename = ?", conn);
OleDbParameter userParm = cmd.Parameters.Add("@username", OleDbType.VarChar, 128);
OleDbParameter roleParm = cmd.Parameters.Add("@rolename", OleDbType.VarChar, 128);
OleDbTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (OleDbException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RemoveUsersFromRoles");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
//
// System.Web.Security.RoleProvider methods.
//
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.Contains(","))
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
{
throw new ProviderException("User is already in role.");
}
}
}
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand("INSERT INTO usersinroles (username, rolename) Values(?, ?)", conn);
OleDbParameter userParm = cmd.Parameters.Add("@username", OleDbType.VarChar, 128);
OleDbParameter roleParm = cmd.Parameters.Add("@rolename", OleDbType.VarChar, 128);
OleDbTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (OleDbException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "AddUsersToRoles");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
public static void SGuncelle(ESatinalma ftGuncelle)
{
OleDbCommand com, com1, com2, com3, com4;
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Veritabani\\Data.mdb;Jet OLEDB:Database Password=ayakkabi_gelisimsoft;");
//Veritabanından stok miktarını çekiyoruz.
#region VeriTabanindanStokBilgisiniCekiyoruz
//Stok miktarını çekecek ilgili OleDbDataAdapter yazıyoruz.
OleDbDataAdapter da = new OleDbDataAdapter("select miktar,stokid from satinalma WHERE ((satinalma.alisftID)=@ID)", con);
da.SelectCommand.Parameters.AddWithValue("@ID", ftGuncelle._alisftID);
DataTable dt = new DataTable();
da.Fill(dt);
double fark = Convert.ToDouble(dt.Rows[0]["miktar"].ToString());
int eskistokid = Convert.ToInt32(dt.Rows[0]["stokid"].ToString());
#endregion VeriTabanindanStokBilgisiniCekiyoruz
#region VeriTabanindanTedarikciTablosundanBakiyeyiCekiyoruz
//Toplam tutarı çekecek ilgili OleDbDataAdapter yazıyoruz.
OleDbDataAdapter da2 = new OleDbDataAdapter("select TedarikciID,geneltoplam from satinalma WHERE ((satinalma.alisftID)=@ID)", con);
da2.SelectCommand.Parameters.AddWithValue("@ID", ftGuncelle._alisftID);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
double bakiyefark = Convert.ToDouble(dt2.Rows[0]["geneltoplam"].ToString());
int eskitedarikciid = Convert.ToInt32(dt2.Rows[0]["TedarikciID"].ToString());
#endregion VeriTabanindanTedarikciTablosundanBakiyeyiCekiyoruz
//Bağlantıyı kontrol ediyoruz.Açık mı diye
//Transaction yazmaya başlıyoruz.Birden fazla store produce çalıştırabilmek için
if (con.State == ConnectionState.Closed)
{
con.Open();
}
OleDbTransaction trancim = con.BeginTransaction();
#region SatinalmaFtGuncellemeIslemleri
com = new OleDbCommand("update satinalma set alistarih=@alistarih,ftno=@ftno,birim=@birim,miktar=@miktar,tutar=@tutar,kdv=@kdv,geneltoplam=@geneltoplam,aciklama=@aciklama,TedarikciID=@TedarikciID,stokid=@stokid where alisftID=@ID", con);
com.Parameters.AddWithValue("@alistarih", ftGuncelle._alistarih);
com.Parameters.AddWithValue("@ftno", ftGuncelle._ftno);
com.Parameters.AddWithValue("@birim", ftGuncelle._birim);
com.Parameters.AddWithValue("@miktar", ftGuncelle._miktar);
com.Parameters.AddWithValue("@tutar", ftGuncelle._tutar);
com.Parameters.AddWithValue("@kdv", ftGuncelle._kdv);
com.Parameters.AddWithValue("@geneltoplam", ftGuncelle._geneltoplam);
com.Parameters.AddWithValue("@aciklama", ftGuncelle._aciklama);
com.Parameters.AddWithValue("@TedarikciID", ftGuncelle._TedarikciID);
com.Parameters.AddWithValue("@stokid", ftGuncelle._stokid);
com.Parameters.AddWithValue("@ID", ftGuncelle._alisftID);
com.Transaction = trancim;
#endregion SatinalmaFtGuncellemeIslemleri
#region StokMiktarindaIlgiliDegisiklerIcinIslemler
//Satinalma formu için gerekli store produce yazıyoruz.
//Ilk önce veritabanından daha önce girilen stok miktarını eksiltiyoruz.fark nesnesine OleDbdataadapter ile getirdiğimiz veriyi işliyoruz
com1 = new OleDbCommand("update stoklar set miktar=miktar-@fark where stokid=@stokid", con);
com1.Parameters.AddWithValue("@fark", fark);
com1.Parameters.AddWithValue("@stokid", eskistokid);
com1.Transaction = trancim;
//2.İşlem olarak yeni girilen miktarı işliyoruz.
com2 = new OleDbCommand("update stoklar set miktar=miktar+@yenigirilenmiktar where stokid=@stokid", con);
com2.Parameters.AddWithValue("@yenigirilenmiktar", ftGuncelle._miktar);
com2.Parameters.AddWithValue("@stokid", ftGuncelle._stokid);
com2.Transaction = trancim;
#endregion StokMiktarindaIlgiliDegisiklerIcinIslemler
#region TedarikciCarisiIcinGerekliStoreProduce
//Önceden satınalma menüsünden girilen tutar miktar alınarak store produce yazmak için ilgili nesneye aktarılacaktır.
//ilk girilen tutar geri çekilip siliniyor.
com3 = new OleDbCommand("update tedarikciler set bakiye=bakiye-@ilkgirilentutar where TedarikciID=@TedarikciID", con);
com3.Parameters.AddWithValue("@ilkgirilentutar", bakiyefark);
com3.Parameters.AddWithValue("@TedarikciID", eskitedarikciid);
com3.Transaction = trancim;
//2.İşlem olarak yeni girilen tutarı işliyoruz.
com4 = new OleDbCommand("update tedarikciler set bakiye=bakiye+@yenigirilentutar where TedarikciID=@TedarikciID", con);
com4.Parameters.AddWithValue("@yenigirilentutar", ftGuncelle._geneltoplam);
com4.Parameters.AddWithValue("@TedarikciID", ftGuncelle._TedarikciID);
com4.Transaction = trancim;
#endregion TedarikciCarisiIcinGerekliStoreProduce
try
{
if (com.ExecuteNonQuery() > 0)
{
if (com1.ExecuteNonQuery() > 0)
{
if (com2.ExecuteNonQuery() > 0)
{
if (com3.ExecuteNonQuery() > 0)
{
if (com4.ExecuteNonQuery() > 0)
{
MessageBox.Show(ftGuncelle._ftno + " " + "Numaralı Fatura Güncellenmiştir.", "Güncelleme Onay", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
}
trancim.Commit();
}
catch (Exception Ex)
{
MessageBox.Show(Ex.ToString(), "Hata", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}//Sonuç ne olursa olsun bağlantıyı kapatıyoruz
finally { con.Close(); }
}
/// <summary>
/// Query that returns no data but number of affected rows. This query works transactional, changes done are will be
/// rolled back in case of errors.
/// </summary>
/// <param name="sqlStatement">The SQL statement.</param>
/// <returns>
/// The result of NonQuery. -4 general exception, -3 exception on rollback, -2 rollback done, all other depend on the
/// SQL
/// statement itself.
/// </returns>
public int NonQueryWithTransaction(string sqlStatement)
{
int retryCount = 0;
int result = 0;
if (this.ConnectionType == ConnType.MSSQL)
{
while (true)
{
try
{
using (var con = new SqlConnection(this.connectionStringBuilderForSql.ConnectionString))
{
con.Open();
var adapter = new SqlDataAdapter();
SqlTransaction transaction = con.BeginTransaction();
try
{
adapter.SelectCommand = new SqlCommand(sqlStatement, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
adapter.SelectCommand.Transaction = transaction;
result = adapter.SelectCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
if (ex is SqlException &&
Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) &&
retryCount < MaxRetry)
{
retryCount++;
Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait);
continue;
}
else
{
result = -2;
this.WriteErrorLog(ex.ToString(), sqlStatement);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
throw;
}
catch (Exception ex2)
{
result = -3;
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
FileOperation.LogError(ex2, FileOperation.MaxLogFileSize);
throw;
}
}
}
}
break;
}
catch (Exception ex)
{
if (ex is SqlException &&
Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) &&
retryCount < MaxRetry)
{
retryCount++;
Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait);
continue;
}
this.WriteErrorLog(ex.ToString(), sqlStatement);
throw;
}
}
}
else if (this.ConnectionType == ConnType.ODBC)
{
using (var con = new OdbcConnection(this.connectionStringBuilderForOdbc.ConnectionString))
{
con.Open();
var adapter = new OdbcDataAdapter();
OdbcTransaction transaction = con.BeginTransaction();
try
{
adapter.SelectCommand = new OdbcCommand(sqlStatement, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
adapter.SelectCommand.Transaction = transaction;
result = adapter.SelectCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
result = -2;
this.WriteErrorLog(ex.ToString(), sqlStatement);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
throw;
}
catch (Exception ex2)
{
result = -3;
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
FileOperation.LogError(ex2, FileOperation.MaxLogFileSize);
throw;
}
}
}
}
else if (this.ConnectionType == ConnType.OLEDB)
{
using (var con = new OleDbConnection(this.connectionStringBuilderForOledb.ConnectionString))
{
con.Open();
var adapter = new OleDbDataAdapter();
OleDbTransaction transaction = con.BeginTransaction();
try
{
adapter.SelectCommand = new OleDbCommand(sqlStatement, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
adapter.SelectCommand.Transaction = transaction;
result = adapter.SelectCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
result = -2;
this.WriteErrorLog(ex.ToString(), sqlStatement);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
throw;
}
catch (Exception ex2)
{
result = -3;
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
FileOperation.LogError(ex2, FileOperation.MaxLogFileSize);
throw;
}
}
}
}
return(result);
}
private void kodbe_KeyPress(object sender, KeyPressEventArgs e)
{
bool isKey = char.IsDigit(e.KeyChar);
bool isControl = char.IsControl(e.KeyChar);
if (!isKey && !isControl)
{
e.Handled = true;
}
// Enter-re adatbevitel vége éa ellenőrzés!
if (Convert.ToInt32(e.KeyChar) == 13 && kodbe.Text != "")
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = "select * from szemely where aktiv=true and azonosito=" + kodbe.Text;
OleDbDataReader reader = command.ExecuteReader();
string azonosito = "", nev = "", beosztas = "";
DateTime datum, ido = new DateTime();
datum = DateTime.Now;
ido = DateTime.Now;
int count = 0;
while (reader.Read())
{
count++;
// Adatok kiolvasása az adatbázisból
azonosito = reader.GetValue(0).ToString();
nev = reader.GetString(1);
beosztas = reader.GetString(2);
// Adatok megjelenítése
cimke_nev.Text = nev;
cimke_beosztas.Text = beosztas;
cimke_datum.Text = datum.ToLongDateString();
cimke_ido.Text = ido.ToLongTimeString();
}
connection.Close();
// Van a szemely táblában találat
if (count == 1)
{
// Csak akkor rögzítjük ha még nem írtuk ki előzőleg (nem azonos az előző rögzítéssel)
if (azonosito != old_azonosito)
{
// Új rekord felvitele
OleDbCommand commandi = new OleDbCommand();
OleDbTransaction transaction = null;
commandi.Connection = connection;
try
{
connection.Open();
transaction = connection.BeginTransaction();
commandi.Connection = connection;
commandi.Transaction = transaction;
commandi.CommandText = "insert into mozgas (azonosito,nev,beosztas,datum) values('" + double.Parse(azonosito) + "','" + nev + "','" + beosztas + "','" + datum.ToString() + "')";
commandi.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex1)
{
//MessageBox.Show("Hiba "+ex1.Message);
try
{
transaction.Rollback();
}
catch (Exception)
{
//
}
}
connection.Close();
old_azonosito = azonosito;
// Heti munkaórák összeszámlálása start
//
DateTime startOfWeek = DateTime.Today.AddDays(-1 * (int)(DateTime.Today.DayOfWeek) + 1);
DateTime endOfWeek = startOfWeek.AddDays(+6);
// A hét első napjának meghatározása
String startOfWeekST = startOfWeek.ToString("d", DateTimeFormatInfo.InvariantInfo);
// Az adott személy leválogatása
connection.Open();
command.CommandText = "select * from mozgas where azonosito=" + azonosito + " and datum>=#" + startOfWeekST + "# order by datum";
OleDbDataReader reader2 = command.ExecuteReader();
int startDay = 0, endDay = 0;
TimeSpan startTime = new TimeSpan();
TimeSpan endTime = new TimeSpan();
TimeSpan ossz_munkaora = new TimeSpan();
ossz_munkaora = TimeSpan.Zero;
while (reader2.Read())
{
// Adatok kiolvasása az adatbázisból
azonosito = reader2.GetValue(1).ToString();
nev = reader2.GetString(2);
beosztas = reader2.GetString(3);
datum = Convert.ToDateTime(reader2.GetDateTime(4));
if (startDay == 0)
{
startDay = datum.Day;
endDay = datum.Day;
startTime = datum.TimeOfDay;
endTime = datum.TimeOfDay;
}
else
{
if (datum.Day == startDay)
{
if (startTime == TimeSpan.Zero)
{
startTime = datum.TimeOfDay;
}
endTime = datum.TimeOfDay;
if (startTime != endTime)
{
ossz_munkaora += (endTime - startTime);
startTime = TimeSpan.Zero;
}
}
else
{
endDay = datum.Day;
startTime = datum.TimeOfDay;
endTime = TimeSpan.Zero;
}
startDay = datum.Day;
}
}
// Még szükséges heti óraszám kiszámítása
TimeSpan kotelezo_oraszam = new TimeSpan(0, 32, 0, 0);
if (beosztas != "Tanár" && beosztas != "Igazgató")
{
kotelezo_oraszam = new TimeSpan(0, 40, 0, 0);
}
String listaosszST = ossz_munkaora.TotalHours.ToString();
String listaKellhST = (kotelezo_oraszam.TotalHours - ossz_munkaora.TotalHours).ToString();
String listaKellmST = (kotelezo_oraszam - ossz_munkaora).Minutes.ToString();
//
int lpoz = listaosszST.IndexOf(',');
int lkpoz = listaKellhST.IndexOf(',');
if (lpoz < 1)
{
lpoz = listaosszST.Length;
}
if (lkpoz < 1)
{
lkpoz = listaKellhST.Length;
}
cimke_hetiossz.Text = "Heti óraszám = " + listaosszST.Substring(0, lpoz) + " ÓRA " + ossz_munkaora.Minutes.ToString() + " PERC";
int xh = Int32.Parse(listaKellhST.Substring(0, lkpoz));
int xm = (kotelezo_oraszam - ossz_munkaora).Minutes;
if (xh < 0 || xm < 0)
{
cimke_Kell.ForeColor = System.Drawing.Color.Green;
cimke_Kell.Text = "A heti óraszámon felül van " + listaKellhST.Substring(1, lkpoz - 1) + " ÓRA " + listaKellmST.Substring(1) + " PERC";
}
else
{
cimke_Kell.ForeColor = System.Drawing.Color.Red;
cimke_Kell.Text = "Heti óraszámhoz még kell " + listaKellhST.Substring(0, lkpoz) + " ÓRA " + listaKellmST + " PERC";
}
connection.Close();
//
// Heti munkaórák összeszámlálása stop
}
}
else // Nem jó a kódolvasás!
{
cimke_nev.Text = "HIBÁS KÓDOLVASÁS!";
}
varakoz();
cimkek_urit();
}
catch (Exception ex2)
{
MessageBox.Show("Hiba " + ex2.Message);
}
}
}
private void ButtonSave_Click(object sender, EventArgs e)
{
byte
IdMaster,
Id;
string
Value = TextBoxValue.Text.Trim();
try
{
IdMaster = Convert.ToByte(TextBoxIdMaster.Text);
}
catch (Exception eException)
{
throw(new Exception(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace));
}
try
{
Id = Convert.ToByte(TextBoxId.Text);
}
catch (Exception eException)
{
throw(new Exception(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace));
}
OleDbConnection
connection = null;
OleDbTransaction
transaction = null;
try
{
try
{
connection = new OleDbConnection(ConnectionString);
connection.Open();
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
OleDbCommand
cmd = connection.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandType = CommandType.Text;
OleDbParameter
tmpParameter;
switch (RadioTypeOperation.SelectedValue)
{
case "insert":
{
cmd.CommandText = @"
insert into DetailsTable
(IdMaster, Id, Value)
values
(?, ?, ?)";
tmpParameter = new OleDbParameter("IdMaster", OleDbType.Numeric);
tmpParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(tmpParameter);
tmpParameter = new OleDbParameter("Id", OleDbType.Numeric);
tmpParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(tmpParameter);
tmpParameter = new OleDbParameter("Value", OleDbType.VarChar);
tmpParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(tmpParameter);
break;
}
case "update":
{
cmd.CommandText = @"
update
DetailsTable
set
Value = ?
where
(IdMaster = ?)
and (Id = ?)";
tmpParameter = new OleDbParameter("Value", OleDbType.VarChar);
tmpParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(tmpParameter);
tmpParameter = new OleDbParameter("IdMaster", OleDbType.Numeric);
tmpParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(tmpParameter);
tmpParameter = new OleDbParameter("Id", OleDbType.Numeric);
tmpParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(tmpParameter);
break;
}
}
cmd.Parameters["IdMaster"].Value = IdMaster;
cmd.Parameters["Id"].Value = Id;
cmd.Parameters["Value"].Value = Value;
cmd.ExecuteNonQuery();
transaction.Commit();
transaction = null;
LabelInfo.Text = DateTime.Now.ToString() + " Done!";
DataGridFill();
}
catch (OleDbException eException)
{
throw(new Exception(eException.GetType().FullName + Environment.NewLine + "ErrorCode: " + eException.ErrorCode.ToString() + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace: " + Environment.NewLine + eException.StackTrace));
}
catch (Exception eException)
{
throw(new Exception(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace));
}
}
finally
{
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch
{
;
}
}
if (connection != null && connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.users model)
{
int newId;
using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
{
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "users(");
strSql.Append("group_id,user_name,salt,[password],mobile,email,avatar,nick_name,sex,birthday,telphone,area,address,qq,msn,amount,point,exp,status,reg_time,reg_ip)");
strSql.Append(" values (");
strSql.Append("@group_id,@user_name,@salt,@password,@mobile,@email,@avatar,@nick_name,@sex,@birthday,@telphone,@area,@address,@qq,@msn,@amount,@point,@exp,@status,@reg_time,@reg_ip)");
OleDbParameter[] parameters =
{
new OleDbParameter("@group_id", OleDbType.Integer, 4),
new OleDbParameter("@user_name", OleDbType.VarChar, 100),
new OleDbParameter("@salt", OleDbType.VarChar, 20),
new OleDbParameter("@password", OleDbType.VarChar, 100),
new OleDbParameter("@mobile", OleDbType.VarChar, 20),
new OleDbParameter("@email", OleDbType.VarChar, 50),
new OleDbParameter("@avatar", OleDbType.VarChar, 255),
new OleDbParameter("@nick_name", OleDbType.VarChar, 100),
new OleDbParameter("@sex", OleDbType.VarChar, 20),
new OleDbParameter("@birthday", OleDbType.Date),
new OleDbParameter("@telphone", OleDbType.VarChar, 50),
new OleDbParameter("@area", OleDbType.VarChar, 255),
new OleDbParameter("@address", OleDbType.VarChar, 255),
new OleDbParameter("@qq", OleDbType.VarChar, 20),
new OleDbParameter("@msn", OleDbType.VarChar, 100),
new OleDbParameter("@amount", OleDbType.Decimal, 5),
new OleDbParameter("@point", OleDbType.Integer, 4),
new OleDbParameter("@exp", OleDbType.Integer, 4),
new OleDbParameter("@status", OleDbType.Integer, 4),
new OleDbParameter("@reg_time", OleDbType.Date),
new OleDbParameter("@reg_ip", OleDbType.VarChar, 20)
};
parameters[0].Value = model.group_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.salt;
parameters[3].Value = model.password;
parameters[4].Value = model.mobile;
parameters[5].Value = model.email;
parameters[6].Value = model.avatar;
parameters[7].Value = model.nick_name;
parameters[8].Value = model.sex;
if (model.birthday != null)
{
parameters[9].Value = model.birthday;
}
else
{
parameters[9].Value = DBNull.Value;
}
parameters[10].Value = model.telphone;
parameters[11].Value = model.area;
parameters[12].Value = model.address;
parameters[13].Value = model.qq;
parameters[14].Value = model.msn;
parameters[15].Value = model.amount;
parameters[16].Value = model.point;
parameters[17].Value = model.exp;
parameters[18].Value = model.status;
parameters[19].Value = model.reg_time;
parameters[20].Value = model.reg_ip;
DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
private void barBtnLocalDel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
this.gridView1.PostEditor();
DataView dv = (DataView)this.gridView1.DataSource;
string selectedParamEntityIds = "";
if (dv != null)
{
for (int i = 0; i < dv.Count; i++)
{
bool result = false;
bool.TryParse(dv.Table.Rows[i]["check"].ToString(), out result);
if (result) //if ((bool)dv.Table.Rows[i]["check"])
{
selectedParamEntityIds += ",'" + dv.Table.Rows[i]["VIN"] + "'";
}
}
}
if ("" == selectedParamEntityIds)
{
MessageBox.Show("请选择要删除的数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (MessageBox.Show("确定要删除吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.OK)
{
return;
}
selectedParamEntityIds = selectedParamEntityIds.Substring(1);
OleDbConnection conn = new OleDbConnection(AccessHelper.conn);
conn.Open();
OleDbTransaction ts = conn.BeginTransaction();
try
{
if ("" != selectedParamEntityIds)
{
string sql = @"delete * from FC_CLJBXX where vin in (" + selectedParamEntityIds + ")";
string sqlentity = @"delete * from RLLX_PARAM_ENTITY where vin in (" + selectedParamEntityIds + ")";
int jbxxcount = AccessHelper.ExecuteNonQuery(ts, sql, null);
int paramcount = AccessHelper.ExecuteNonQuery(ts, sqlentity, null);
ts.Commit();
}
}
catch (Exception ex)
{
throw ex;
ts.Rollback();
}
finally
{
conn.Close();
}
int pageNum = Convert.ToInt32(txtPage.Text.Substring(0, txtPage.Text.LastIndexOf("/")));
this.SearchLocal(pageNum);
}
/// <summary>
/// 增加一条数据,及其子表数据
/// </summary>
public int Add(Model.orders model)
{
using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
{
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "orders(");
strSql.Append("order_no,trade_no,user_id,user_name,payment_id,payment_fee,payment_status,payment_time,express_id,express_no,express_fee,express_status,express_time,accept_name,post_code,telphone,mobile,email,area,address,message,remark,is_invoice,invoice_title,invoice_taxes,payable_amount,real_amount,order_amount,point,status,add_time,confirm_time,complete_time)");
strSql.Append(" values (");
strSql.Append("@order_no,@trade_no,@user_id,@user_name,@payment_id,@payment_fee,@payment_status,@payment_time,@express_id,@express_no,@express_fee,@express_status,@express_time,@accept_name,@post_code,@telphone,@mobile,@email,@area,@address,@message,@remark,@is_invoice,@invoice_title,@invoice_taxes,@payable_amount,@real_amount,@order_amount,@point,@status,@add_time,@confirm_time,@complete_time)");
OleDbParameter[] parameters =
{
new OleDbParameter("@order_no", OleDbType.VarChar, 100),
new OleDbParameter("@trade_no", OleDbType.VarChar, 100),
new OleDbParameter("@user_id", OleDbType.Integer, 4),
new OleDbParameter("@user_name", OleDbType.VarChar, 100),
new OleDbParameter("@payment_id", OleDbType.Integer, 4),
new OleDbParameter("@payment_fee", OleDbType.Decimal, 5),
new OleDbParameter("@payment_status", OleDbType.Integer, 4),
new OleDbParameter("@payment_time", OleDbType.Date),
new OleDbParameter("@express_id", OleDbType.Integer, 4),
new OleDbParameter("@express_no", OleDbType.VarChar, 100),
new OleDbParameter("@express_fee", OleDbType.Decimal, 5),
new OleDbParameter("@express_status", OleDbType.Integer, 4),
new OleDbParameter("@express_time", OleDbType.Date),
new OleDbParameter("@accept_name", OleDbType.VarChar, 50),
new OleDbParameter("@post_code", OleDbType.VarChar, 20),
new OleDbParameter("@telphone", OleDbType.VarChar, 30),
new OleDbParameter("@mobile", OleDbType.VarChar, 20),
new OleDbParameter("@email", OleDbType.VarChar, 30),
new OleDbParameter("@area", OleDbType.VarChar, 100),
new OleDbParameter("@address", OleDbType.VarChar, 500),
new OleDbParameter("@message", OleDbType.VarChar, 500),
new OleDbParameter("@remark", OleDbType.VarChar, 500),
new OleDbParameter("@is_invoice", OleDbType.Integer, 4),
new OleDbParameter("@invoice_title", OleDbType.VarChar, 100),
new OleDbParameter("@invoice_taxes", OleDbType.Decimal, 5),
new OleDbParameter("@payable_amount", OleDbType.Decimal, 5),
new OleDbParameter("@real_amount", OleDbType.Decimal, 5),
new OleDbParameter("@order_amount", OleDbType.Decimal, 5),
new OleDbParameter("@point", OleDbType.Integer, 4),
new OleDbParameter("@status", OleDbType.Integer, 4),
new OleDbParameter("@add_time", OleDbType.Date),
new OleDbParameter("@confirm_time", OleDbType.Date),
new OleDbParameter("@complete_time", OleDbType.Date)
};
parameters[0].Value = model.order_no;
parameters[1].Value = model.trade_no;
parameters[2].Value = model.user_id;
parameters[3].Value = model.user_name;
parameters[4].Value = model.payment_id;
parameters[5].Value = model.payment_fee;
parameters[6].Value = model.payment_status;
if (model.payment_time != null)
{
parameters[7].Value = model.payment_time;
}
else
{
parameters[7].Value = DBNull.Value;
}
parameters[8].Value = model.express_id;
parameters[9].Value = model.express_no;
parameters[10].Value = model.express_fee;
parameters[11].Value = model.express_status;
if (model.express_time != null)
{
parameters[12].Value = model.express_time;
}
else
{
parameters[12].Value = DBNull.Value;
}
parameters[13].Value = model.accept_name;
parameters[14].Value = model.post_code;
parameters[15].Value = model.telphone;
parameters[16].Value = model.mobile;
parameters[17].Value = model.email;
parameters[18].Value = model.area;
parameters[19].Value = model.address;
parameters[20].Value = model.message;
parameters[21].Value = model.remark;
parameters[22].Value = model.is_invoice;
parameters[23].Value = model.invoice_title;
parameters[24].Value = model.invoice_taxes;
parameters[25].Value = model.payable_amount;
parameters[26].Value = model.real_amount;
parameters[27].Value = model.order_amount;
parameters[28].Value = model.point;
parameters[29].Value = model.status;
parameters[30].Value = model.add_time;
if (model.confirm_time != null)
{
parameters[31].Value = model.confirm_time;
}
else
{
parameters[31].Value = DBNull.Value;
}
if (model.complete_time != null)
{
parameters[32].Value = model.complete_time;
}
else
{
parameters[32].Value = DBNull.Value;
}
DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
model.id = GetMaxId(conn, trans);
//订单商品列表
if (model.order_goods != null)
{
StringBuilder strSql2;
StringBuilder strSql3;
StringBuilder strSql4;
foreach (Model.order_goods modelt in model.order_goods)
{
//添加订单商品
strSql2 = new StringBuilder();
strSql2.Append("insert into " + databaseprefix + "order_goods(");
strSql2.Append("article_id,order_id,goods_no,goods_title,img_url,spec_text,goods_price,real_price,quantity,point)");
strSql2.Append(" values (");
strSql2.Append("@article_id,@order_id,@goods_no,@goods_title,@img_url,@spec_text,@goods_price,@real_price,@quantity,@point)");
OleDbParameter[] parameters2 =
{
new OleDbParameter("@article_id", OleDbType.Integer, 4),
new OleDbParameter("@order_id", OleDbType.Integer, 4),
new OleDbParameter("@goods_no", OleDbType.VarChar, 50),
new OleDbParameter("@goods_title", OleDbType.VarChar, 100),
new OleDbParameter("@img_url", OleDbType.VarChar, 255),
new OleDbParameter("@spec_text", SqlDbType.Text),
new OleDbParameter("@goods_price", OleDbType.Decimal, 5),
new OleDbParameter("@real_price", OleDbType.Decimal, 5),
new OleDbParameter("@quantity", OleDbType.Integer, 4),
new OleDbParameter("@point", OleDbType.Integer, 4)
};
parameters2[0].Value = modelt.article_id;
parameters2[1].Value = model.id; //订单的ID
parameters2[2].Value = modelt.goods_no;
parameters2[3].Value = modelt.goods_title;
parameters2[4].Value = modelt.img_url;
parameters2[5].Value = modelt.spec_text;
parameters2[6].Value = modelt.goods_price;
parameters2[7].Value = modelt.real_price;
parameters2[8].Value = modelt.quantity;
parameters2[9].Value = modelt.point;
DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
//扣减商品库存
strSql3 = new StringBuilder();
strSql3.Append("update " + databaseprefix + "article_attribute_value set ");
strSql3.Append("stock_quantity=stock_quantity-@stock_quantity where article_id=@article_id");
OleDbParameter[] parameters3 =
{
new OleDbParameter("@stock_quantity", OleDbType.Integer, 4),
new OleDbParameter("@article_id", OleDbType.Integer, 4)
};
parameters3[0].Value = modelt.quantity;
parameters3[1].Value = modelt.article_id;
DbHelperOleDb.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
}
}
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(model.id);
}
public bool ExecNoQuery(string query, CommitRoll commitRollback = CommitRoll.commitRollback, NuovaConn nuovaConn = NuovaConn.seNecessario, StrConnection strConn = null, Int32 timeOutQuery = 0, Mess logMess = null)
{
if (logMess == null)
{
logMess = new Mess(LogType.ERR, Log.main.errUserText);
}
logMess.testoDaLoggare = "";
Thread thread; Exception thrEx = null; bool esito; string testoTmp;
if (PreExec(true, query, ref strConn, nuovaConn, ref timeOutQuery, logMess) == false)
{
return(false);
}
esito = false;
thread = Thr.AvviaNuovo(() => esito = ThrENQ_Exec(out thrEx));
if (Thr.AttesaCompletamento(ref thread, timeOutQuery) == false)
{ //qua non faccio rollback poichè presumo che la connessione remota sia andata persa, se DB in locale non può andare in timeout
logMess.testoDaLoggare = "query:<" + cmd.CommandText + "> andata in timeOut:<" + timeOutQuery + ">";
Log.main.Add(logMess);
return(false);
}
if (esito == false)
{
try
{
if (commitRollback == CommitRoll.commitRollback || commitRollback == CommitRoll.soloRollback)
{
tra.Rollback();
this.DisconnettiDB();
}
}
catch (Exception ex)
{
logMess.testoDaVisual = ""; //Non visualizzo nulla poichè c'è il l'altro log sotto
logMess.testoDaLoggare = "eccezione durante rollback, ex.Mess:<" + ex.Message + ">, query:<" + query + ">";
Log.main.Add(logMess);
}
if (thrEx != null)
{
if (FiltraEccezioniQuery(thrEx) == true)
{
logMess.testoDaVisual = "";
logMess.tipo = LogType.Warn;
}
testoTmp = "ex.mess:<" + thrEx.Message + ">";
}
else
{
testoTmp = "il thread ThrENQ_Exec è ritornato false ma senza eccezioni";
}
logMess.testoDaLoggare = "query:<" + cmd.CommandText + ">, " + testoTmp;
Log.main.Add(logMess);
return(false);
}
try
{
if (commitRollback == CommitRoll.commitRollback)
{
tra.Commit();
this.DisconnettiDB();
}
}
catch (Exception ex)
{
if (FiltraEccezioniCommit(ex) == true)
{
logMess.testoDaVisual = "";
logMess.tipo = LogType.Warn;
}
logMess.testoDaLoggare = "errore durante 'sqlTra.Commit()', ex.Mess:<" + ex.Message + ">, query:<" + query + ">";
Log.main.Add(logMess);
return(false);
}
return(true);
}
public static void Ssil(ESatinalma Silme)
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Veritabani\\Data.mdb;Jet OLEDB:Database Password=ayakkabi_gelisimsoft;");
if (con.State != ConnectionState.Open)
{
con.Open();
}
#region VeriTabanindanStokBilgisiniCekiyoruz
//Stok miktarını çekecek ilgili OleDbDataAdapter yazıyoruz.
OleDbDataAdapter da = new OleDbDataAdapter("select miktar from satinalma WHERE ((satinalma.alisftID)=@ID)", con);
da.SelectCommand.Parameters.AddWithValue("@ID", Silme._alisftID);
DataTable dt = new DataTable();
da.Fill(dt);
double fark = Convert.ToDouble(dt.Rows[0]["miktar"].ToString());
#endregion VeriTabanindanStokBilgisiniCekiyoruz
#region VeriTabanindanTedarikciTablosundanBakiyeyiCekiyoruz
//Genel Toplam miktarını çekecek ilgili OleDbDataAdapter yazıyoruz.
OleDbDataAdapter da2 = new OleDbDataAdapter("select geneltoplam from satinalma WHERE ((satinalma.alisftID)=@ID)", con);
da2.SelectCommand.Parameters.AddWithValue("@ID", Silme._alisftID);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
double bakiyefark = Convert.ToDouble(dt2.Rows[0]["geneltoplam"].ToString());
#endregion VeriTabanindanTedarikciTablosundanBakiyeyiCekiyoruz
OleDbTransaction trastor = con.BeginTransaction();
OleDbCommand com = new OleDbCommand("delete from satinalma WHERE ((satinalma.alisftID)=@ID)", con);
com.Parameters.AddWithValue("@ID", Silme._alisftID);
com.Transaction = trastor;
OleDbCommand com1 = new OleDbCommand("update stoklar set miktar=miktar-@fark WHERE ((stoklar.stokid)=@stokid)", con);
com1.Parameters.AddWithValue("@fark", fark);
com1.Parameters.AddWithValue("@stokid", Silme._stokid);
com1.Transaction = trastor;
OleDbCommand com2 = new OleDbCommand("update tedarikciler set bakiye=bakiye-@ilkgirilentutar WHERE ((tedarikciler.TedarikciID)=@TedarikciID)", con);
com2.Parameters.AddWithValue("@ilkgirilentutar", bakiyefark);
com2.Parameters.AddWithValue("@TedarikciID", Silme._TedarikciID);
com2.Transaction = trastor;
try
{
if (com.ExecuteNonQuery() > 0)
{
if (com1.ExecuteNonQuery() > 0)
{
if (com2.ExecuteNonQuery() > 0)
{
MessageBox.Show(Silme._ftno + " " + "Nolu Faturanın Silme İşlemi Yapılmıştır", "Sonuc", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
trastor.Commit();
}
catch (Exception Ex)
{
trastor.Rollback();
MessageBox.Show(Ex.ToString(), "Hata", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally { con.Close(); }
}
//
// RoleProvider.DeleteRole
//
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand("DELETE FROM roles WHERE rolename = ?", conn);
cmd.Parameters.Add("@rolename", OleDbType.VarChar, 128).Value = rolename;
OleDbCommand cmd2 = new OleDbCommand("DELETE FROM usersinroles WHERE rolename = ?", conn);
cmd2.Parameters.Add("@rolename", OleDbType.VarChar, 128).Value = rolename;
OleDbTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteNonQuery();
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (OleDbException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
return(false);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return(true);
}
//method to Insert User in the database
public static bool SaveUser(string Database, string ContractNumber, string UserPassword,
string SecurityLevel)
{
bool userSaved;
OleDbTransaction myTransaction = null;
try
{
// creating connection to a database through object conn of the OleDbConnection class
OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Database);
conn.Open();
OleDbCommand command = conn.CreateCommand();
string strSQL;
// assigning BeginTransaction method to myTransaction object
myTransaction = conn.BeginTransaction();
command.Transaction = myTransaction;
// assigning SQL commands to strSQL variable in order to be written to the database
strSQL = "Insert into tblUsers " +
"(ContractNumber, UserPassword, SecurityLevel) values ('" +
ContractNumber + "', '" + UserPassword + "', '" + SecurityLevel + "')";
// setting how the command will be interpreted which is .text
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
// executes the strSQL statement to the connected database
command.ExecuteNonQuery();
// executing Commit method for myTransaction object
myTransaction.Commit();
// close command is used to close existing connection and recordsaved is assigned with true value
conn.Close();
if (SecurityLevel == "A")
{
int userID = 0;
string mySelectQuery = "SELECT MAX(UserID) FROM tblUsers";
OleDbCommand myCommand = new OleDbCommand(mySelectQuery, conn);
// assigning new OleDbConnection class to sqlConn object
conn.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
userID = myReader.GetInt32(0);
}
myReader.Close();
strSQL = "INSERT INTO tblContractManager (UserID) VALUES (" + userID + ")";
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
command.ExecuteNonQuery();
conn.Close();
}
if (SecurityLevel == "S")
{
int userID = 0;
string mySelectQuery = "SELECT MAX(UserID) FROM tblUsers";
OleDbCommand myCommand = new OleDbCommand(mySelectQuery, conn);
// assigning new OleDbConnection class to sqlConn object
conn.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
userID = myReader.GetInt32(0);
}
myReader.Close();
strSQL = "INSERT INTO tblEmployee (UserID) VALUES (" + userID + ")";
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
command.ExecuteNonQuery();
conn.Close();
}
if (SecurityLevel == "C")
{
int userID = 0;
string mySelectQuery = "SELECT MAX(UserID) FROM tblUsers";
OleDbCommand myCommand = new OleDbCommand(mySelectQuery, conn);
// assigning new OleDbConnection class to sqlConn object
conn.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
userID = myReader.GetInt32(0);
}
myReader.Close();
strSQL = "INSERT INTO tblClient (UserID) VALUES (" + userID + ")";
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
command.ExecuteNonQuery();
conn.Close();
}
userSaved = true;
}
catch (Exception ex)
{
// rolling back the transaction if it receives an input error
myTransaction.Rollback();
userSaved = false;
}
return(userSaved);
}
/// <summary>
/// Agrega la tabla CatGenerales. Definición de todos los catalogos de la aplicación
/// </summary>
/// <param name="item">Se usa CatGeneralesBE Total mente llena para insertar</param>
/// <param name="sConexionString">cadena de conexión que se obtiene de la clase ConfiguracionDA</param>
/// <returns>RespuestaComun.lstCatGenerales</returns>
public RespuestaComunBE AddCatGenerales(CatGeneralesBE item, string sConexionString)
{
RespuestaComunBE RespuestaComun = new RespuestaComunBE();
OleDbCommand Comando = new OleDbCommand();
OleDbConnection Conexion = new OleDbConnection();
OleDbTransaction Transaccion = null;
string sComando = string.Empty;
StringBuilder sMensajeError = new StringBuilder();
string sResultado = string.Empty;
RespuestaComun.itemError = new ErrorBE();
RespuestaComun.itemError.psMensaje = new StringBuilder(string.Empty);
try
{
Conexion.ConnectionString = sConexionString;
Conexion.Open();
Transaccion = Conexion.BeginTransaction();
Comando.Connection = Conexion;
Comando.Transaction = Transaccion;
sComando = "spAddCatGenerales";
Comando.CommandText = sComando;
Comando.CommandType = CommandType.StoredProcedure;
Comando.CommandTimeout = 0;
Comando.Parameters.Clear();
Comando.Parameters.Add(new OleDbParameter("NOMBRECATALOGO", item.psNOMBRECATALOGO)).Direction = ParameterDirection.Input;
Comando.Parameters.Add(new OleDbParameter("IDCATALOGO", item.psIDCATALOGO)).Direction = ParameterDirection.Input;
Comando.Parameters.Add(new OleDbParameter("DESCRIPCION", item.psDESCRIPCION)).Direction = ParameterDirection.Input;
Comando.Parameters.Add(new OleDbParameter("FILTRO", item.psFILTRO)).Direction = ParameterDirection.Input;
Comando.Parameters.Add(new OleDbParameter("ACTIVO", item.psACTIVO)).Direction = ParameterDirection.Input;
Comando.Parameters.Add(new OleDbParameter("IDCATGENERALESNEW", OleDbType.BigInt)).Direction = ParameterDirection.Output;
//[15102015][falta agregar la instruccion que obtiene el nombre del metodo]RespuestaComun.itemError.psMensaje.Append("[spGetConfigApp]");
RespuestaComun.itemError.psMensaje.Append("[spAddCatGenerales]");
Comando.ExecuteNonQuery();
RespuestaComun.psIDCONFIGAPP = Comando.Parameters["IDCATGENERALESNEW"].Value.ToString();
Transaccion.Commit();
RespuestaComun.itemError.pbFlag = true;
}
catch (Exception Ex)
{
//Generar una deccion para crear Log de errores
RespuestaComun.itemError.psMensaje.Append("[");
RespuestaComun.itemError.psMensaje.Append(Ex.Message);
RespuestaComun.itemError.psMensaje.Append("]");
RespuestaComun.itemError.pbFlag = false;
}
finally
{
Transaccion.Dispose();
Transaccion = null;
Comando.Dispose();
Comando = null;
Conexion.Close();
Conexion = null;
}
return(RespuestaComun);
}
public void commit()
{
myTrans.Commit();
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.manager_role model)
{
using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
{
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "manager_role set ");
strSql.Append("role_name=@role_name,");
strSql.Append("role_type=@role_type,");
strSql.Append("is_sys=@is_sys");
strSql.Append(" where id=@id");
OleDbParameter[] parameters =
{
new OleDbParameter("@role_name", OleDbType.VarChar, 100),
new OleDbParameter("@role_type", OleDbType.Integer, 4),
new OleDbParameter("@is_sys", OleDbType.Integer, 4),
new OleDbParameter("@id", OleDbType.Integer, 4)
};
parameters[0].Value = model.role_name;
parameters[1].Value = model.role_type;
parameters[2].Value = model.is_sys;
parameters[3].Value = model.id;
DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//先删除该角色所有权限
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from " + databaseprefix + "manager_role_value where role_id=@role_id ");
OleDbParameter[] parameters2 =
{
new OleDbParameter("@role_id", OleDbType.Integer, 4)
};
parameters2[0].Value = model.id;
DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
//添加权限
if (model.manager_role_values != null)
{
StringBuilder strSql3;
foreach (Model.manager_role_value modelt in model.manager_role_values)
{
strSql3 = new StringBuilder();
strSql3.Append("insert into " + databaseprefix + "manager_role_value(");
strSql3.Append("role_id,nav_name,action_type)");
strSql3.Append(" values (");
strSql3.Append("@role_id,@nav_name,@action_type)");
OleDbParameter[] parameters3 =
{
new OleDbParameter("@role_id", OleDbType.Integer, 4),
new OleDbParameter("@nav_name", OleDbType.VarChar, 100),
new OleDbParameter("@action_type", OleDbType.VarChar, 50)
};
parameters3[0].Value = model.id;
parameters3[1].Value = modelt.nav_name;
parameters3[2].Value = modelt.action_type;
DbHelperOleDb.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
}
}
trans.Commit();
}
catch
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
public void btnSetSchedule_Click(object sender, EventArgs e)
{
if (OnSaveSchedule != null)
{
OnSaveSchedule(sender, e);
}
else
{
System.Web.UI.WebControls.HiddenField hdMsg = (System.Web.UI.WebControls.HiddenField) this.Parent.FindControl("hdMsg");
if ((UcSchedule.ActivityID == null && UcSchedule.SpecialtyID == null) ||
(UcSchedule.ActivityID != null && UcSchedule.SpecialtyID != null))
{
//throw new Exception("参数设置错误");
hdMsg.Value = "参数设置错误!";
}
DateTime dt;
if (!string.IsNullOrEmpty(txtDeclarationBeginDate.Text) &&
DateTime.TryParse(txtDeclarationBeginDate.Text, out dt))
{
UcSchedule.DeclarationBeginDate = dt;
}
if (!string.IsNullOrEmpty(txtDeclarationEndDate.Text) &&
DateTime.TryParse(txtDeclarationEndDate.Text, out dt))
{
UcSchedule.DeclarationEndDate = dt;
}
//UcSchedule.IsDeclarationStart = cbIsDeclarationStart.Checked;
UcSchedule.IsDeclarationStart = ddlIsDeclarationStart.SelectedValue == "True";
//UcSchedule.IsDeclarationAutoStart = cbIsDeclarationAutoStart.Checked;
UcSchedule.IsDeclarationAutoStart = ddlIsDeclarationAutoStart.SelectedValue == "True";
//UcSchedule.IsDeclarationAutoEnd = cbIsDeclarationAutoEnd.Checked;
UcSchedule.IsDeclarationAutoEnd = ddlIsDeclarationAutoEnd.SelectedValue == "True";
if (!string.IsNullOrEmpty(txtPrimaryElectionBeginDate.Text) &&
DateTime.TryParse(txtPrimaryElectionBeginDate.Text, out dt))
{
UcSchedule.PrimaryElectionBeginDate = dt;
}
if (!string.IsNullOrEmpty(txtPrimaryElectionEndDate.Text) &&
DateTime.TryParse(txtPrimaryElectionEndDate.Text, out dt))
{
UcSchedule.PrimaryElectionEndDate = dt;
}
UcSchedule.IsPrimaryElectionStart = ddlIsPrimaryElectionStart.SelectedValue == "True";
//UcSchedule.IsPrimaryElectionStart = cbIsPrimaryElectionStart.Checked;
UcSchedule.IsPrimaryElectionAutoStart = ddlIsPrimaryElectionAutoStart.SelectedValue == "True";
//UcSchedule.IsPrimaryElectionAutoStart = cbIsPrimaryElectionAutoStart.Checked;
UcSchedule.IsPrimaryElectionAutoEnd = ddlIsPrimaryElectionAutoEnd.SelectedValue == "True";
//UcSchedule.IsPrimaryElectionAutoEnd = cbIsPrimaryElectionAutoEnd.Checked;
if (!string.IsNullOrEmpty(txtExpertReviewBeginDate.Text) &&
DateTime.TryParse(txtExpertReviewBeginDate.Text, out dt))
{
UcSchedule.ExpertReviewBeginDate = dt;
}
if (!string.IsNullOrEmpty(txtExpertReviewEndDate.Text) &&
DateTime.TryParse(txtExpertReviewEndDate.Text, out dt))
{
UcSchedule.ExpertReviewEndDate = dt;
}
UcSchedule.IsExpertReviewStart = ddlIsExpertReviewStart.SelectedValue == "True";
//UcSchedule.IsExpertReviewStart = cbIsExpertReviewStart.Checked;
UcSchedule.IsExpertReviewAutoStart = ddlIsExpertReviewAutoStart.SelectedValue == "True";
//UcSchedule.IsExpertReviewAutoStart = cbIsExpertReviewAutoStart.Checked;
UcSchedule.IsExpertReviewAutoEnd = ddlIsExpertReviewAutoEnd.SelectedValue == "True";
//UcSchedule.IsExpertReviewAutoEnd = cbIsExpertReviewAutoEnd.Checked;
if (!string.IsNullOrEmpty(txtDownloadBeginDate.Text) &&
DateTime.TryParse(txtDownloadBeginDate.Text, out dt))
{
UcSchedule.DownloadBeginDate = dt;
}
if (!string.IsNullOrEmpty(txtDownloadEndDate.Text) &&
DateTime.TryParse(txtDownloadEndDate.Text, out dt))
{
UcSchedule.DownloadEndDate = dt;
}
UcSchedule.IsDownloadStart = ddlIsDownloadStart.SelectedValue == "True";
//UcSchedule.IsDownloadStart = cbIsDownloadStart.Checked;
UcSchedule.IsDownloadAutoStart = ddlIsDownloadAutoStart.SelectedValue == "True";
//UcSchedule.IsDownloadAutoStart = cbIsDownloadAutoStart.Checked;
UcSchedule.IsDownloadAutoEnd = ddlIsDownloadAutoEnd.SelectedValue == "True";
//UcSchedule.IsDownloadAutoEnd = cbIsDownloadAutoEnd.Checked;
//投票
if (!string.IsNullOrEmpty(txtVoteBeginDate.Text) &&
DateTime.TryParse(txtVoteBeginDate.Text, out dt))
{
UcSchedule.VoteBeginDate = dt;
}
if (!string.IsNullOrEmpty(txtVoteEndDate.Text) &&
DateTime.TryParse(txtVoteEndDate.Text, out dt))
{
UcSchedule.VoteEndDate = dt;
}
UcSchedule.IsVoteStart = ddlIsVoteStart.SelectedValue == "True";
UcSchedule.IsVoteAutoStart = ddlIsVoteAutoStart.SelectedValue == "True";
UcSchedule.IsVoteAutoEnd = ddlIsVoteAutoEnd.SelectedValue == "True";
UcSchedule.IsReVoteStart = ddlIsReVoteStart.SelectedValue == "True";
//UcSchedule.IsReVoteStart = cbIsReVoteStart.Checked;
OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);
conn.Open();
OleDbTransaction trans = conn.BeginTransaction();
try
{
BLL.Schedule.SetSchedule(UcSchedule, conn, trans);
trans.Commit();
hdMsg.Value = "时间节点设置成功！";
}
catch (Exception ex)
{
trans.Rollback();
hdMsg.Value = ex.Message;
ViewState["ErrorMessage"] = ex.Message;
}
conn.Close();
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter[]）</param>
public static void ExecuteSqlTran(DataSet data, string filePath)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
CreateAccessDb(filePath);
string str = constr + filePath;
using (OleDbConnection conn = new OleDbConnection(str))
{
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
try
{
#region 创建表结构
string createTb = "create table {0}({1})";
foreach (DataTable dt in data.Tables)
{
string tablename = dt.TableName.Substring(dt.TableName.LastIndexOf("(") + 1).Replace(")", "");
dt.TableName = tablename;//去掉中文部分
string cols = "";
foreach (DataColumn dc in dt.Columns)
{
cols += $"{dc.ColumnName} text(200), ";
}
cmd.CommandText = string.Format(createTb, tablename, cols).Replace(", )", ")");
cmd.ExecuteNonQuery();
}
#endregion
#region 插入数据
string insertData = "INSERT INTO {0} VALUES({1})";
foreach (DataTable dt in data.Tables)
{
string tablename = dt.TableName;
foreach (DataRow dr in dt.Rows)
{
string rows = "";
foreach (DataColumn dc in dt.Columns)
{
rows += $"'{dr[dc.ColumnName]}', ";
}
cmd.CommandText = string.Format(insertData, tablename, rows).Replace(", )", ")");
cmd.ExecuteNonQuery();
}
}
#endregion
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw;
}
}
}
}
private void btnOk_Click(object sender, EventArgs e)
{
Autodesk.AutoCAD.EditorInput.Editor ed = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument.Editor;
if (Validation())
{
Atend.Base.Design.NodeTransaction _NodeTransaction = new Atend.Base.Design.NodeTransaction();
SqlConnection sConnection = null;
SqlTransaction sTransaction = null;
OleDbTransaction aTransaction = null;
OleDbConnection aConnection = null;
if (_NodeTransaction.CreateTransactionAndConnection(out sTransaction, out sConnection, out aTransaction, out aConnection, false))
{
Atend.Control.Common.DesignId = Atend.Base.Design.DDesignProfile.AccessSelect(aTransaction, aConnection).DesignId;
if (Atend.Control.Common.DesignId == 0)//یعنی طرح جدید باز شده است
{
if (MessageBox.Show("آیا طرح در پشتیبان تعریف شده است", "ذخیره سازی طرح", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
{
ed.WriteMessage("_______ 1\n");
NewDesignWithPoshtiban(aConnection, aTransaction, sConnection, sTransaction);
ed.WriteMessage("_______ 1.1\n");
}
else /////////////////////////////////////////////////////////////////////////////////
{
ed.WriteMessage("_______ 2\n");
NewDesignWithoutPoshtiban(aConnection, aTransaction, sConnection, sTransaction);
ed.WriteMessage("_______ 2.2\n");
}
}
else////////////////////////////////////// ALL DESIGN HAVE DESIGN ID ///////////////////////////////////////
{
ed.WriteMessage("_______ 3\n");
PoshtibanDesign(aConnection, aTransaction, sConnection, sTransaction);
}
}
ed.WriteMessage("_______ 4\n");
aTransaction.Commit();
sTransaction.Commit();
aConnection.Close();
sConnection.Close();
//#region DDesignProfile
//Atend.Global.Utility.UOtherOutPut Output = new Atend.Global.Utility.UOtherOutPut();
//Atend.Report.dsSagAndTension ds = Output.FillStatusReport();
//Atend.Base.Design.DDesignProfile DP = Atend.Base.Design.DDesignProfile.AccessSelect();
//if (DP.DesignId <= 0)
//{
// Atend.Base.Design.DDesign Des = new Atend.Base.Design.DDesign();
// Des.Title = DP.DesignName;
// Des.ArchiveNo = DP.DesignCode;
// Des.PRGCode = 0;
// Des.RequestType = 0;
// Des.Region = 0;
// Des.IsAtend = true;
// Des.Address = DP.Address;
// if (Des.Insert())
// {
// DP.DesignId = Des.Id;
// if (!DP.AccessUpdate())
// ed.WriteMessage("\nError In D_DesignProfile Access Update\n");
// }
// else
// ed.WriteMessage("\nError In D_Design Server Insert\n");
//}
//Atend.Base.Design.DStatusReport.DeleteByDesignId(DP.DesignId);
//for (int i = 0; i < ds.Tables["StatusReport"].Rows.Count; i++)
//{
// Atend.Base.Design.DStatusReport STR = new Atend.Base.Design.DStatusReport();
// STR.DesignId = DP.DesignId;
// if (Atend.Control.NumericValidation.Int32Converter(ds.Tables["StatusReport"].Rows[i]["Code"].ToString()))
// {
// STR.ProductCode = Convert.ToInt32(ds.Tables["StatusReport"].Rows[i]["Code"].ToString());
// }
// else
// STR.ProductCode = 0;
// //MessageBox.Show(i.ToString() +" " + );
// DataTable ExTbl = Atend.Base.Base.BEquipStatus.SelectAllX();
// STR.Existance = Convert.ToInt32(ExTbl.Select("Name = '" + ds.Tables["StatusReport"].Rows[i]["Exist"].ToString() + "'")[0]["ACode"].ToString());
// int pc = Convert.ToInt32(ds.Tables["StatusReport"].Rows[i]["ProjectCode"]);
// //int pc = Atend.Base.Base.BProjectCode.AccessSelectByCode(Convert.ToInt32(Table.Rows[i]["ProjectCode"].ToString())).AdditionalCode;
// STR.ProjectCode = pc;
// ds.Tables["StatusReport"].Rows[i]["ProjectCode"] = pc;
// ds.Tables["StatusReport"].Rows[i]["Count"] = Math.Round(Convert.ToDouble(ds.Tables["StatusReport"].Rows[i]["Count"].ToString()), 2);
// STR.Count = Convert.ToDouble(ds.Tables["StatusReport"].Rows[i]["Count"].ToString());
// if (!STR.Insert())
// {
// ed.WriteMessage("\nError In D_StatusReport Insertion\n");
// }
//}
//#endregion
DataTable dt = Atend.Base.Design.DDesign.SelectAll();
gvDesign1.AutoGenerateColumns = false;
gvDesign1.DataSource = dt;
}
else
{
AllowToClose = false;
}
}
public void CommitTrans()
{
_trans.Commit();
_inTransaction = false;
}
// 保存
private void saveParam(bool flag)
{
string strCon = AccessHelper.conn;
OleDbConnection con = new OleDbConnection(strCon);
con.Open();
OleDbTransaction tra = con.BeginTransaction(); //创建事务，开始执行事务
try
{
string strCreater = Utils.userId;
string strBah = this.tbvin.Text.Trim();
string sqlJbxx = "DELETE FROM FC_CLJBXX WHERE VIN = '" + strBah + "'";
string sqlParam = "DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='" + strBah + "'";
if (!flag)
{
AccessHelper.ExecuteNonQuery(tra, sqlJbxx, null);
AccessHelper.ExecuteNonQuery(tra, sqlParam, null);
}
string mainId = this.GetMainId(strBah);
ArrayList sqlList = new ArrayList();
#region 遍历参数,保存
foreach (Control c in this.tlp.Controls)
{
if (c is TextEdit || c is DevExpress.XtraEditors.ComboBoxEdit)
{
//Control[] lblc = clj.Controls.Find("lbl" + c.Name, true);
string paramCode = c.Name;
string paramValue = c.Text;
string strSQL = @"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES(@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)";
OleDbParameter[] paramList =
{
new OleDbParameter("@PARAM_CODE", paramCode),
new OleDbParameter("@VIN", strBah),
new OleDbParameter("@PARAM_VALUE", paramValue),
new OleDbParameter("@V_ID", "")
};
AccessHelper.ExecuteNonQuery(tra, strSQL, paramList);
}
}
#endregion
#region 保存车辆基本信息
// 保存车辆基本信息
string sqlStr = @"INSERT INTO FC_CLJBXX
( VIN,
HGSPBM,
USER_ID,
QCSCQY,
JKQCZJXS,
CLXH,
CLZL,
RLLX,
ZCZBZL,
ZGCS,
LTGG,
ZJ,
CLZZRQ,
UPLOADDEADLINE,
TYMC,
YYC,
ZWPS,
ZDSJZZL,
EDZK,
LJ,
QDXS,
STATUS,
JYJGMC,
JYBGBH,
QTXX,
CREATETIME,
UPDATETIME
) VALUES
( @VIN,
@HGSPBM,
@USER_ID,
@QCSCQY,
@JKQCZJXS,
@CLXH,
@CLZL,
@RLLX,
@ZCZBZL,
@ZGCS,
@LTGG,
@ZJ,
@CLZZRQ,
@UPLOADDEADLINE,
@TYMC,
@YYC,
@ZWPS,
@ZDSJZZL,
@EDZK,
@LJ,
@QDXS,
@STATUS,
@JYJGMC,
@JYBGBH,
@QTXX,
@CREATETIME,
@UPDATETIME)";
DateTime clzzrqDate = DateTime.Parse(this.tbclzzrq.Text.Trim());
OleDbParameter clzzrq = new OleDbParameter("@CLZZRQ", clzzrqDate);
clzzrq.OleDbType = OleDbType.DBDate;
DateTime uploadDeadlineDate = Utils.QueryUploadDeadLine(clzzrqDate);
OleDbParameter uploadDeadline = new OleDbParameter("@UPLOADDEADLINE", uploadDeadlineDate);
uploadDeadline.OleDbType = OleDbType.DBDate;
OleDbParameter creTime = new OleDbParameter("@CREATETIME", DateTime.Now);
creTime.OleDbType = OleDbType.DBDate;
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now);
upTime.OleDbType = OleDbType.DBDate;
OleDbParameter[] param =
{
new OleDbParameter("@VIN", this.tbvin.Text.Trim().ToUpper()),
new OleDbParameter("@HGSPBM", this.tbHgspbm.Text.Trim().ToUpper()),
new OleDbParameter("@USER_ID", strCreater),
new OleDbParameter("@QCSCQY", this.tbqcscqy.Text.Trim()),
new OleDbParameter("@JKQCZJXS", this.tbjkqczjxs.Text.Trim()),
new OleDbParameter("@CLXH", this.tbclxh.Text.Trim()),
new OleDbParameter("@CLZL", this.tbclzl.Text.Trim()),
new OleDbParameter("@RLLX", this.tbrllx.Text.Trim()),
new OleDbParameter("@ZCZBZL", this.tbzczbzl.Text.Trim()),
new OleDbParameter("@ZGCS", this.tbzgcs.Text.Trim()),
new OleDbParameter("@LTGG", this.tbltgg.Text.Trim()),
new OleDbParameter("@ZJ", this.tbzj.Text.Trim()),
clzzrq,
uploadDeadline,
new OleDbParameter("@TYMC", this.tbtymc.Text.Trim()),
new OleDbParameter("@YYC", this.tbyyc.Text.Trim()),
new OleDbParameter("@ZWPS", this.tbzwps.Text.Trim()),
new OleDbParameter("@ZDSJZZL", this.tbzdsjzzl.Text.Trim()),
new OleDbParameter("@EDZK", this.tbedzk.Text.Trim()),
new OleDbParameter("@LJ", this.tblj.Text.Trim()),
new OleDbParameter("@QDXS", this.tbqdxs.Text.Trim()),
new OleDbParameter("@STATUS", this.status),
new OleDbParameter("@JYJGMC", this.tbjyjgmc.Text.Trim()),
new OleDbParameter("@JYBGBH", this.tbjybgbh.Text.Trim()),
new OleDbParameter("@QTXX", this.tbQtxx.Text.Trim()),
creTime,
upTime
};
#endregion
AccessHelper.ExecuteNonQuery(tra, sqlStr, param);
tra.Commit();
strVin = strBah; //备案号
}
catch (Exception ex)
{
// MessageBox.Show("保存失败!");
tra.Rollback();
throw ex;
}
finally
{
con.Close();
}
}
private void KayitekleBtn_Click(object sender, EventArgs e)
{
DialogResult dlg = MessageBox.Show("Tahsilat Kayıt Edilsin mi?", "Tahsilat Kayıt", MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (dlg == DialogResult.Yes)
{
if (HizmetCombo.Text != "Lütfen Seçiniz" && MusteriCombo.SelectedIndex != 0 &&
UrunCombo.SelectedIndex != 0 && !string.IsNullOrEmpty(tutarbox.Text))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
OleDbTransaction trancik = con.BeginTransaction();
try
{
string Hizmet = HizmetCombo.Text;
string Urun = UrunCombo.Text;
string Tarih = Convert.ToString(DateTime.Now.ToLongDateString());
int FirmaIydim = Convert.ToInt32(MusteriCombo.SelectedValue);
var comgel =
new OleDbCommand(
"insert into Tahsilat(Hizmet,Urun,Bakiye,Tarih,Aciklama,FirmaID) values(@H,@U,@B,@D,@A,@F)",
con, trancik);
comgel.Parameters.AddWithValue("@H", Convert.ToString(HizmetCombo.Text));
comgel.Parameters.AddWithValue("@U", Convert.ToString(UrunCombo.Text));
comgel.Parameters.AddWithValue("@B", tutarbox.Text);
comgel.Parameters.AddWithValue("@D", Convert.ToString(dateTimePicker1.Value.ToLongDateString()));
comgel.Parameters.AddWithValue("@A", aciklamabox.Text);
comgel.Parameters.AddWithValue("@F", Convert.ToInt32(MusteriCombo.SelectedValue));
if (comgel.ExecuteNonQuery() > 0)
{
var comGun =
new OleDbCommand("Update Musteriler set Bakiye=Bakiye-@Bakiye where FirmaID=@ID", con,
trancik);
comGun.Parameters.AddWithValue("@Bakiye", Convert.ToDouble(tutarbox.Text));
comGun.Parameters.AddWithValue("@ID", Convert.ToInt32(MusteriCombo.SelectedValue));
if (comGun.ExecuteNonQuery() > 0)
{
trancik.Commit();
TahsilatGetirHepsi();
MessageBox.Show("Tahsilat Kayıt Edildi.", "Kayıt", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
}
catch (Exception Ex)
{
trancik.Rollback();
MessageBox.Show(Ex.ToString());
}
finally
{
con.Close();
}
}
else
{
MessageBox.Show("Lütfen Tüm Alanları Doldurunuz !", "Uyarı", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
}
private void button3_Click(object sender, EventArgs e)
{
if (drpOffice.Text.Trim().Length <= 0)
{
MessageBox.Show("Select Office/Branch");
return;
//txtcname.BackColor = System.Drawing.Color.OrangeRed;
}
else if (drpOffice.Text.Trim() == drpOffice1.Text.Trim())
{
MessageBox.Show("Select Differnet Office/Branch");
return;
}
else
{
String qry;
//"update bill set billtype=@billtype,bdate=@badate,cid=@cid,cname=@cname,cmob=@cmob,qty=@qty,total=@total,tax=@tax,discount=@discount,GTotal=@gtotal,paid=@paid,balance=@balance where bid=@bid";
//"update bill set totalamount=" + txtTotalAmount.Text + ",tax=" + txtTax.Text + ",discount=" + txtDiscount.Text + ",GTotal=" + txtGrandTotal.Text + ",paid=" + txtPaid.Text + ",balance=" + txtBalance.Text + " ,INWORDS='' where billno=" + txtbid.Text + "";
qry = "insert into StockHistory(dated,cid,issuedto,pid) values(@dated,@cid,@issuedto,@pid)";
OleDbTransaction trns1 = null;
OleDbTransaction trns = null;
try
{
OleDbCommand cmdsave = new OleDbCommand(qry, con);
cmdsave.Parameters.Add("@dated", OleDbType.Date).Value = dateTimePicker1.Text;
cmdsave.Parameters.Add("@cid", OleDbType.Numeric).Value = drpOffice1.SelectedValue;
cmdsave.Parameters.Add("@issuedto", OleDbType.Numeric).Value = drpOffice.SelectedValue;
cmdsave.Parameters.Add("@pid", OleDbType.Numeric).Value = lblpid.Text;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
// Start a local transaction with ReadCommitted isolation level.
trns = con.BeginTransaction(IsolationLevel.ReadCommitted);
cmdsave.Transaction = trns;
cmdsave.ExecuteNonQuery();
//cmdsave.Parameters.Clear();
cmdsave.Dispose();
//con.Close();
//ShowBillItems();
//Update Online Orders
//OleDbCommand cmdupdate = new OleDbCommand("Update online_orders set Flag='Y' where item_code='" + txtItem_code.Text + "'", con);
//if (con.State == ConnectionState.Closed) { con.Open(); }
//cmdupdate.ExecuteNonQuery();
if (con1.State == ConnectionState.Closed)
{
con1.Open();
}
OleDbCommand cmdTrns = con1.CreateCommand();
trns1 = con1.BeginTransaction(IsolationLevel.ReadCommitted);
cmdTrns.Transaction = trns1;
cmdTrns.Connection = con1;
cmdTrns.CommandText = "Update stock set issuedto=@issuedto where id=@id";
cmdTrns.Parameters.Add("@issuedto", OleDbType.Numeric).Value = drpOffice.SelectedValue;
cmdTrns.Parameters.Add("@pid", OleDbType.Numeric, 100).Value = lblpid.Text;
cmdTrns.ExecuteNonQuery();
trns.Commit();
trns1.Commit();
cmdTrns.Dispose();
cmdTrns.Parameters.Clear();
con.Close();
con1.Close();
drpOffice.SelectedIndex = -1;
drpOffice1.SelectedIndex = -1;
lblpid.Text = "";
listBox1.Items.Clear();
MessageBox.Show("Item Issued for Branch/Office", "Bill Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message, "Error in Transcation", MessageBoxButtons.OK, MessageBoxIcon.Error);
trns.Rollback();
trns1.Rollback();
}
}
}
