public int ActiveCard(APIRequest <ActiveCardRP> rp)
{
var cardDepositBLL = new CardDepositBLL(CurrentUserInfo);
var cardDeposit = PagedSearch(new GetCardRP()
{
CardNo = rp.Parameters.CardNo, UseStatus = "0"
}, CurrentUserInfo.ClientID);
int result = 0;
if (cardDeposit != null && cardDeposit.Tables.Count > 0 && cardDeposit.Tables[0].Rows.Count > 0)
{
DataRow dr = cardDeposit.Tables[0].Rows[0];
if (rp.Parameters.CardPassword == System.Text.Encoding.UTF8.GetString(cardDepositBLL.DecryptCardPassword((byte[])dr["CardPassword"])).Replace("\0", ""))
{
System.Data.SqlClient.SqlTransaction tran = GetTran();
using (tran.Connection)
{
string errorMessage = "";
VipAmountBLL vipAmountBLL = new VipAmountBLL(CurrentUserInfo);
vipAmountBLL.SetVipAmountChange(CurrentUserInfo.ClientID, 4, rp.Parameters.VipID, decimal.Parse(dr["Amount"].ToString()) + decimal.Parse(dr["Bonus"].ToString()), dr["CardDepositId"].ToString(), "Prepaid card charge", "In", out errorMessage, tran);
result = this._currentDAO.ActiveCard(rp.Parameters.CardNo, rp.Parameters.VipID, this.CurrentUserInfo.UserID, this.CurrentUserInfo.ClientID, tran);
tran.Commit();
}
}
}
return(result);
}
private bool DeleteTakeIn(int p_IdTakeIn)
{
System.Data.SqlClient.SqlConnection _sqlConnection = new System.Data.SqlClient.SqlConnection(_ConfigItem.StrConnection);;
if (_sqlConnection.State != ConnectionState.Open)
{
_sqlConnection.Open();
}
System.Data.SqlClient.SqlTransaction _sqlTransaction = _sqlConnection.BeginTransaction();
try
{
_providerSQL = new COREBASE.COMMAND.SQL.AccessSQL();
string[] arrName = new string[] { "@Id", "@Mod_By" };
object[] arrValue = new object[] { p_IdTakeIn, _ConfigItem.Login_UserName };
_providerSQL.ExecuteNonQuery(_sqlConnection, _sqlTransaction, "USP_DEL_TAKE_IN", arrName, arrValue);
_sqlTransaction.Commit();
return(true);
}
catch (Exception ex)
{
_sqlTransaction.Rollback();
AppDebug(ex);
if (_sqlConnection.State != ConnectionState.Closed)
{
_sqlConnection.Close();
}
return(false);
}
}
private bool InsertData(DataTable tbDetail, int _id_WareHouse, int _TotalAMT, int _iId_Supplier_Pk, DateTime _iTake_In_Date, int _User_Pk, string _BillNumber, string _Remark, DateTime _billdate)
{
System.Data.SqlClient.SqlConnection _sqlConnection = new System.Data.SqlClient.SqlConnection(_ConfigItem.StrConnection);
if (_sqlConnection.State != ConnectionState.Open)
{
_sqlConnection.Open();
}
System.Data.SqlClient.SqlTransaction _sqlTransaction = _sqlConnection.BeginTransaction();
try
{
int _iNumberItem = tbDetail.Rows.Count;
string[] arrName = new string[] { "@Supplier_Pk", "@Take_In_Date", "@Number_Item", "@TotalAMT", "@Crt_By", "@WareHouse_Pk", "@Remark", "@BillNumber", "@BillDate", "@User_Pk" };
object[] arrValue = new object[] { _iId_Supplier_Pk, _iTake_In_Date, _iNumberItem, _TotalAMT, _ConfigItem.Login_UserName, _id_WareHouse, _Remark, _BillNumber, _billdate, _User_Pk };
_providerSQL = new COREBASE.COMMAND.SQL.AccessSQL();
int _idMaster = _providerSQL.ExecuteInsert(_sqlConnection, _sqlTransaction, "USP_INS_TAKE_IN", arrName, arrValue);
for (int i = 0; i < _iNumberItem; i++)
{
arrName = new string[] {
"@Take_In_Pk",
"@Crt_By",
"@Remark",
"@Number_Bill",
"@Number_Real",
"@Price",
"@Vat",
"@Item_Pk",
"@UnitStyle_Pk"
};
arrValue = new object[] {
_idMaster,
_ConfigItem.Login_UserName,
string.Empty,
tbDetail.Rows[i]["Number_Bill"],
tbDetail.Rows[i]["Number_Real"],
tbDetail.Rows[i]["Price"],
tbDetail.Rows[i]["Vat"],
tbDetail.Rows[i]["Item_Pk"],
tbDetail.Rows[i]["UnitStyle_Pk"]
};
_providerSQL.ExecuteNonQuery(_sqlConnection, _sqlTransaction, "USP_INS_TAKE_IN_DETAIL", arrName, arrValue);
}
_sqlTransaction.Commit();
_providerSQL.Disconnect(_sqlConnection);
return(true);
}
catch (Exception ex)
{
_sqlTransaction.Rollback();
AppDebug(ex);
if (_providerSQL != null)
{
if (_sqlConnection != null)
{
_providerSQL.Disconnect(_sqlConnection);
}
}
return(false);
}
}
private bool DeleteItem(int[] p_selectRow, DataTable p_datasource)
{
System.Data.SqlClient.SqlConnection _sqlConnection = new System.Data.SqlClient.SqlConnection(_ConfigItem.StrConnection);;
if (_sqlConnection.State != ConnectionState.Open)
{
_sqlConnection.Open();
}
System.Data.SqlClient.SqlTransaction _sqlTransaction = _sqlConnection.BeginTransaction();
try
{
_providerSQL = new COREBASE.COMMAND.SQL.AccessSQL();
string[] arrName = new string[] { "@Id", "@Mod_By", "@Mod_Dt" };
for (int i = 0; i < p_selectRow.Length; i++)
{
object[] arrValue = new object[] { p_datasource.Rows[i]["Id"], _ConfigItem.Login_UserName, DateTime.Now };
_providerSQL.ExecuteNonQuery(_sqlConnection, _sqlTransaction, "USP_DEL_TAKE_IN_DETAIL", arrName, arrValue);
}
_sqlTransaction.Commit();
return(true);
}
catch (Exception ex)
{
_sqlTransaction.Rollback();
AppDebug(ex);
if (_sqlConnection.State != ConnectionState.Closed)
{
_sqlConnection.Close();
}
return(false);
}
}
private void btnUpdateModule_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlTransaction trans = BWS.ERP.BaseControl.ConnectSetting.SysSqlConnection.BeginTransaction();
try
{
foreach (DataRow item in dsModule.Tables[0].Rows)
{
if (item.RowState == DataRowState.Added)
{
item["ID"] = dalMenuNode.Add(item, trans);
}
if (item.RowState == DataRowState.Modified)
{
dalMenuNode.Update(item, trans);
}
if (item.RowState == DataRowState.Deleted)
{
dalMenuNode.Delete(Convert.ToInt32(item["ID", DataRowVersion.Original]), trans);
}
}
trans.Commit();
//刷新菜单
initModule();
initModuleBingding();
BWS.ERP.BaseControl.Public.SystemInfo("升级成功!");
}
catch (Exception ex)
{
trans.Rollback();
BWS.ERP.BaseControl.Public.SystemInfo("升级失败!" + ex.Message, true);
}
}
public string efetuaQuitacao(string pCodigoParcela, double pValorQuitado, string pNumeroAutorizacao)
{
System.Data.SqlClient.SqlTransaction iTransacao = bancoDados.Conexoes.conexao.BeginTransaction();
try
{
if (getExisteAutorizacao(pNumeroAutorizacao, iTransacao))
{
throw new Exception("autorizacao ja cadastrada");
}
if (setQuitacaoCartao(pCodigoParcela, pValorQuitado, pNumeroAutorizacao, iTransacao))
{
throw new Exception("O vencimento nao pode ser anterior a data atual");
}
iTransacao.Commit();
return("OK");
}
catch (Exception ex)
{
if (iTransacao.Connection != null)
{
iTransacao.Rollback();
}
throw new Exception("class:" + this.GetType().Name + "\r\n Method:" + System.Reflection.MethodBase.GetCurrentMethod().Name + "\r\n" + ex.Message);
}
finally { bancoDados.Conexoes.close(); }
}
}//
public static void trxCloser(
System.Data.SqlClient.SqlTransaction trx,
bool mustCommit // the ultimate decision.
)
{
if (mustCommit)// success
{
trx.Commit();
}
else// failure
{
if (null != trx)
{
trx.Rollback();
}// else means the the trxOpener was unable to connect.
}
if (null != trx)
{
if (null != trx.Connection)
{
if (System.Data.ConnectionState.Open == trx.Connection.State)
{
trx.Connection.Close();
} // else aready closed.
} //else Commit or Rollback succeeded and let trx.Connection==null.
} // else means the the trxOpener was unable to connect.
//--END--close transazione------
trx = null;// anyway garbage collect.
// ready
} // end trxCloser
//
//System.Web.Security.RoleProvider methods.
//
//RoleProvider.AddUsersToRoles
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
foreach (var rolename in roleNames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (var username in usernames)
{
if (username.Contains(","))
{
throw new ArgumentException("Usernames cannot contain commas.");
}
foreach (var rolename in roleNames)
{
if (IsUserInRole(username, rolename))
{
throw new ProviderException("User is already in role.");
}
}
}
System.Data.SqlClient.SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
alphaCmd.Transaction = tran;
foreach (var username in usernames)
{
foreach (var rolename in roleNames)
{
alphaCmd.CommandText = " INSERT INTO UsersInRoles " +
" (Username, Rolename, ApplicationName) " +
" Values('" + username + "', '" + rolename + "', '" + ApplicationName + "')";
alphaCmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (Exception ex)
{
try
{
tran.Rollback();
}
catch (Exception ex2) { }
}
finally
{
conn.Close();
}
}
private void continuebtn_Click(object sender, EventArgs e)
{
//Add vendor to item
System.Data.SqlClient.SqlTransaction transaction = null;
try
{
//transaction = TableAdapterHelper.BeginTransaction(itemvendTableAdapter);
//TableAdapterHelper.SetTransaction(itemvendpriceTableAdapter, transaction);
Nullable <DateTime> Qdate = null;
if (quotedchk.Checked)
{
Qdate = Quotedate.Value;
}
Nullable <short> lead = null;
if (quotedchk.Checked)
{
lead = (short)Convert.ToInt32(leadtxt.Text);
}
Nullable <decimal> cost = null;
if (quotedchk.Checked)
{
cost = Convert.ToDecimal(costtxt.Text);
}
//itemvendTableAdapter.InsertVendor(item, vendorcbo.SelectedValue.ToString(), vendoritemtxt.Text, lead, Qdate);
//itemvendpriceTableAdapter.InsertQuery(item, vendorcbo.SelectedValue.ToString(),cost);
transaction.Commit();
}
catch
{
//transaction.Rollback();
this.DialogResult = DialogResult.Cancel;
this.Close();
}
finally
{
//transaction.Connection.Close();
//transaction.Dispose();
this.Close();
this.Dispose();
}
this.Close();
}
static bool InsertBook(string id, string title, string pubyear)
{
bool result = true;
string sql = $"INSERT INTO Books(title,publishYear) VALUES('{title}',{pubyear})";
string cnstr = "server=student\\sqlexpress;database=library;integrated security=true";
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnstr))
{
cn.Open();
System.Data.SqlClient.SqlTransaction tx = cn.BeginTransaction();
System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand(sql, cn, tx);
try
{
cm.ExecuteNonQuery();
tx.Commit();
}
catch (System.Data.SqlClient.SqlException ex)
{
tx.Rollback();
result = false;
}
finally
{
cn.Close();
}
}//close using block
return(result);
}
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.");
}
}
}
System.Data.SqlClient.SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
alphaCmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
alphaCmd.CommandText = " DELETE FROM UsersInRoles " +
" WHERE Username = '******' " +
" And Rolename = '" + rolename + "' " +
" And ApplicationName = '" + ApplicationName + "' ";
alphaCmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (Exception ex)
{
try
{
tran.Rollback();
}
catch (Exception ex2) { }
throw ex;
}
finally
{
conn.Close();
}
}
public override void btnsave_Click(object sender, EventArgs e)
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells[2].Value == null)
{
MessageBox.Show("Please Fill All Field Properly!");
return;
}
}
// this changes i have done for bareli school date 10/02/2016. i put !=true and i comment all previous work//
if (btnShowAttendance.Visible != true)
{
if (DialogResult.Yes.Equals(MessageBox.Show("Are You Sure To Save Attendance Date Of \" " + txtAttandanceDate.Text + " \" ?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question)))
{
System.Data.SqlClient.SqlTransaction trn = Connection.GetMyConnection().BeginTransaction();
Connection.SqlTransection("Delete From tbl_Attendance Where AttendanceDate = '" + txtAttandanceDate.Value.Date.ToString("MM/dd/yyyy") + "'",
Connection.MyConnection, trn);
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
//foreach (DataGridViewRow r in dataGridView1.Rows)
//{
Connection.AllPerform("insert into tbl_Attendance (EmpNo, AttendanceDate, Attendance)values('" + dataGridView1.Rows[i].Cells[0].Value + "','" + txtAttandanceDate.Text + "','" + dataGridView1.Rows[i].Cells[2].Value + "')");
//Connection.SqlTransection("insert into tbl_Attendance (EmpNo, AttendanceDate, Attendance, TimeIn, TimeOut) "+
// " Values('" + r.Cells[0].Value + "','" + txtAttandanceDate.Text + "','" +
// r.Cells[2].Value + "','" + r.Cells[5].Value + "','" +
// r.Cells[6].Value + "')",
// Connection .MyConnection , trn );
//}
}
trn.Commit();
}
}
else
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
DataSet ds11 = Connection.GetDataSet("select count(*) from tbl_Attendance where AttendanceDate='" + txtAttandanceDate.Value.Date + "' and empNo='" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "'");
if (ds11.Tables[0].Rows[0][0].ToString() == "0")
{
Connection.AllPerform("insert into tbl_Attendance (EmpNo, AttendanceDate, Attendance)values('" + dataGridView1.Rows[i].Cells[0].Value + "','" + txtAttandanceDate.Text + "','" + dataGridView1.Rows[i].Cells[2].Value + "')");
}
}
}
str1 = "SELECT schoolname, schooladdress, affiliate_by, logoimage FROM tbl_school";
str1 = str1 + " SELECT tbl_Attendance.EmpNo, tbl_EmployeeInfo.EmpName, tbl_Attendance.Attendance,tbl_Attendance.AttendanceDate FROM tbl_Attendance INNER JOIN tbl_EmployeeInfo ON tbl_Attendance.EmpNo = tbl_EmployeeInfo.EmpNo WHERE (tbl_Attendance.AttendanceDate = '" + txtAttandanceDate.Value.Date + "') order by tbl_Attendance.EmpNo";
DataSet ds = Connection.GetDataSet(str1);
ds.WriteXmlSchema(@"" + Connection.GetAccessPathId() + @"Barcodes\a\TodaysAttendance.xsd");
AttendanceReport cr1 = new AttendanceReport();
cr1.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
cr1.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter;
cr1.SetDataSource(ds);
ShowAllReports s = new ShowAllReports();
s.crystalReportViewer1.ReportSource = cr1;
s.Show();
}
public void CommitTran()
{
if (tran != null)
{
tran.Commit();
tran = null;
}
}
/// <summary>
/// Inclui um novo cliente
/// </summary>
/// <param name="cliente">Objeto de cliente</param>
internal long Incluir(DML.Cliente cliente)
{
long ret = 0;
using (System.Data.SqlClient.SqlConnection conexao = Connection())
{
conexao.Open();
System.Data.SqlClient.SqlCommand comando = conexao.CreateCommand();
System.Data.SqlClient.SqlTransaction transacao = conexao.BeginTransaction("IncluirCliente");
comando.Connection = conexao;
comando.Transaction = transacao;
try
{
comando.CommandType = CommandType.StoredProcedure;
comando.CommandText = "[FI_SP_IncClienteV2]";
comando.Parameters.AddWithValue("@Nome", cliente.Nome);
comando.Parameters.AddWithValue("@Sobrenome", cliente.Sobrenome);
comando.Parameters.AddWithValue("@CPF", TextoHelper.RemoveNaoNumericos(cliente.CPF));
comando.Parameters.AddWithValue("@Nacionalidade", cliente.Nacionalidade);
comando.Parameters.AddWithValue("@CEP", cliente.CEP);
comando.Parameters.AddWithValue("@Estado", cliente.Estado);
comando.Parameters.AddWithValue("@Cidade", cliente.Cidade);
comando.Parameters.AddWithValue("@Logradouro", cliente.Logradouro);
comando.Parameters.AddWithValue("@Email", cliente.Email ?? string.Empty);
comando.Parameters.AddWithValue("@Telefone", cliente.Telefone ?? string.Empty);
var idCliente = comando.ExecuteScalar();
cliente.Id = Convert.ToInt64(idCliente);
foreach (Beneficiario beneficiario in cliente.Beneficiarios)
{
comando.Parameters.Clear();
comando.CommandText = "[FI_SP_IncBeneficiario]";
comando.Parameters.AddWithValue("@CPF", TextoHelper.RemoveNaoNumericos(beneficiario.CPF));
comando.Parameters.AddWithValue("@NOME", beneficiario.Nome);
comando.Parameters.AddWithValue("@IDCLIENTE", cliente.Id);
comando.ExecuteNonQuery();
}
ret = cliente.Id;
transacao.Commit();
}
catch (Exception ex)
{
transacao.Rollback();
}
}
return(ret);
}
/// <summary>
/// Удаляет запись из базы данных
/// </summary>
/// <param name="BUDGETPROJECT_GUID">УИ проекта</param>
/// <param name="objProfile">профайл</param>
/// <param name="strErr">сообщение об ошибке</param>
/// <returns>true - запись удалена; false - ошибка</returns>
public static System.Boolean RemoveObjectFromDataBase(System.Guid BUDGETPROJECT_GUID,
UniXP.Common.CProfile objProfile, ref System.String strErr)
{
System.Boolean bRet = false;
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlTransaction DBTransaction = null;
try
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr += ("Не удалось получить соединение с базой данных.");
return(bRet);
}
DBTransaction = DBConnection.BeginTransaction();
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.Transaction = DBTransaction;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_DeleteBudgetProject]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@BUDGETPROJECT_GUID", System.Data.SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_NUM", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_MES", System.Data.SqlDbType.NVarChar, 4000));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@BUDGETPROJECT_GUID"].Value = BUDGETPROJECT_GUID;
cmd.ExecuteNonQuery();
System.Int32 iRes = (System.Int32)cmd.Parameters["@RETURN_VALUE"].Value;
if (iRes == 0)
{
// подтверждаем транзакцию
DBTransaction.Commit();
}
else
{
DBTransaction.Rollback();
strErr += ((cmd.Parameters["@ERROR_MES"].Value == System.DBNull.Value) ? "" : (System.String)cmd.Parameters["@ERROR_MES"].Value);
}
cmd.Dispose();
bRet = (iRes == 0);
}
catch (System.Exception f)
{
DBTransaction.Rollback();
strErr += ("Не удалось удалить объект 'проект'. Текст ошибки: " + f.Message);
}
finally
{
DBConnection.Close();
}
return(bRet);
}
private void btnSave_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlTransaction trans = BWS.ERP.BaseControl.ConnectSetting.SysSqlConnection.BeginTransaction();
try
{
//重建菜单顺序
foreach (DevExpress.XtraTreeList.Nodes.TreeListNode node in tvMenu.Nodes)
{
AutoSetMenuSort(node);
}
//下次保存前将顺序号清空为0
iSort = 0;
foreach (DataRow item in dsMenu.Tables[0].Rows)
{
if (item.RowState == DataRowState.Added)
{
item["ID"] = dalMenu.Add(item, trans);
}
if (item.RowState == DataRowState.Modified)
{
dalMenu.Update(item, trans);
}
if (item.RowState == DataRowState.Deleted)
{
dalMenu.Delete(Convert.ToInt32(item["ID", DataRowVersion.Original]), trans);
}
}
//保存菜单参数表
foreach (DataRow item in dsMenuPara.Tables[0].Rows)
{
if (item.RowState == DataRowState.Added)
{
item["ID"] = dalMenuParam.Add(item, trans);
}
if (item.RowState == DataRowState.Modified)
{
dalMenuParam.Update(item, trans);
}
if (item.RowState == DataRowState.Deleted)
{
dalMenuParam.Delete(Convert.ToInt32(item["ID", DataRowVersion.Original]), trans);
}
}
trans.Commit();
//刷新菜单
initMenu();
tvMenu.OptionsBehavior.Editable = false;
BWS.ERP.BaseControl.Public.SystemInfo("保存成功!");
}
catch (Exception ex)
{
trans.Rollback();
BWS.ERP.BaseControl.Public.SystemInfo("保存失败!" + ex.Message, true);
}
}
/// <summary>
/// Commits the transaction and disposes the underlying connection.
/// </summary>
public void Commit()
{
if (m_Disposed)
{
throw new ObjectDisposedException("Transaction is disposed");
}
m_Transaction.Commit();
Dispose(true);
}
private void btnMonthAttendance_Click(object sender, EventArgs e)
{
if (btnShowAttendance.Visible)
{
if (DialogResult.Yes.Equals(MessageBox.Show("Are You Sure To Save Attendance Date Of \" " + txtAttandanceDate.Text + " \" ?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question)))
{
Connection.SetValue_0_For_MS_Access_1_For_MS_Excel = 0;
Connection.MSAccessFileInfoFullName = Convert.ToString(Connection.GetAttributeObjectFromXMLFile("Mahanta", "AttendanceMachinePath"));
DataTable dsMachineAttendance = Connection.GetDataTableFromExcelFile("Select EmployeeID, AttendanceDate, InTime, OutTime, StatusCode " +
" From AttendanceLogs Where MONTH(AttendanceDate) = MONTH(#" + txtAttandanceDate.Value.Date + "#) And " +
" YEAR(AttendanceDate) = YEAR(#" + txtAttandanceDate.Value.Date + "#)");
if (dsMachineAttendance.Rows.Count > 0)
{
DataTable dtEmployee = Connection.GetDataTable("SELECT EmpNo, EmpName, AttendanceID FROM tbl_EmployeeInfo Where IsActive=1 And DOJ<='" + txtAttandanceDate.Value.Date + "' group by EmpNo, EmpName, AttendanceID order by empNo");
if (dtEmployee.Rows.Count > 0)
{
dsMachineAttendance.Columns.Add("EmpNo", typeof(int));
foreach (DataRow r in dsMachineAttendance.Rows)
{
DataRow[] Records = dtEmployee.Select("AttendanceID = '" + r["EmployeeID"] + "'", "EmpName");
if (Records.Length > 0)
{
r["EmpNo"] = Records[0]["EmpNo"];
r["StatusCode"] = (r["StatusCode"].ToString().Contains("W")) ? "W" : (r["StatusCode"].ToString().Contains("½P")) ? "H" : r["StatusCode"];
}
dsMachineAttendance.AcceptChanges();
r.SetModified();
}
System.Data.SqlClient.SqlTransaction trn = Connection.GetMyConnection().BeginTransaction();
Connection.SqlTransection("Delete From tbl_Attendance Where MONTH(AttendanceDate) = '" +
txtAttandanceDate.Value.Month + "' And YEAR(AttendanceDate) = '" + txtAttandanceDate.Value.Year + "'",
Connection.MyConnection, trn);
foreach (DataRow r in dsMachineAttendance.Rows)
{
if (r["EmpNo"].Equals(DBNull.Value))
{
continue;
}
Connection.SqlTransection("insert into tbl_Attendance (EmpNo, AttendanceDate, Attendance, TimeIn, TimeOut) " +
" Values('" + r["EmpNo"] + "','" + r["AttendanceDate"] + "','" +
r["StatusCode"] + "','" + r["InTime"] + "','" + r["OutTime"] + "')",
Connection.MyConnection, trn);
}
trn.Commit();
MessageBox.Show("Saved...", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
}
//RoleProvider.DeleteRole
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role does not exist.");
}
//If throwOnPopulatedRole AndAlso GetUsersInRole(rolename).Length > 0 Then
// Throw New ProviderException("Cannot delete a populated role.")
//End If
alphaCmd.CommandText = " DELETE FROM Roles " +
" WHERE Rolename = '" + rolename + "' and ApplicationName = '" + ApplicationName + "' ";
var cmd2 = new System.Data.SqlClient.SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = " DELETE FROM UsersInRoles " +
" WHERE Rolename = '" + rolename + "' and ApplicationName = '" + ApplicationName + "' ";
System.Data.SqlClient.SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
alphaCmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteNonQuery();
alphaCmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
try
{
tran.Rollback();
}
catch (Exception ex2) { }
throw ex;
}
finally
{
conn.Close();
}
return(true);
}
public string setAlteraVencimento(string pCodigoParcela, double pValor, DateTime pVencimento)
{
System.Data.SqlClient.SqlTransaction iTransacao = bancoDados.Conexoes.conexao.BeginTransaction();
try
{
if (pVencimento < DateTime.Today)
{
throw new Exception("O vencimento nao pode ser anterior a data atual");
}
if (pVencimento > DateTime.Today.AddDays(15))
{
throw new Exception("O vencimento nao pode ser maior que 15 dias ");
}
bancoDados.Comandos.limpaParametros();
bancoDados.Comandos.textoComando = "Principal.Cobranca.Proc_AlteraParcela";
bancoDados.Comandos.tipoComando = System.Data.CommandType.StoredProcedure;
bancoDados.retornaDados = true;
bancoDados.Comandos.adicionaParametro("@CdParcela", System.Data.SqlDbType.BigInt, 4, pCodigoParcela);
bancoDados.Comandos.adicionaParametro("@VlParcela", System.Data.SqlDbType.Money, 4, pValor);
bancoDados.Comandos.adicionaParametro("@DtVencimento", System.Data.SqlDbType.DateTime, 8, pVencimento);
bancoDados.Comandos.adicionaParametro("@CdCobrador ", System.Data.SqlDbType.VarChar, 6, codigoCobradora);
bancoDados.Comandos.comando.Transaction = iTransacao;
System.Data.DataTable iTabela = bancoDados.execute().Tables[0];
if (Convert.ToBoolean(iTabela.Rows[0]["isEfetuada"]))
{
iTransacao.Commit();
return(CarSystem.Utilidades.Rede.HTML.tableToJson(iTabela));
}
throw new Exception(iTabela.Rows[0]["isEfetuada"].ToString());
}
catch (Exception ex)
{
if (iTransacao.Connection != null)
{
iTransacao.Rollback();
}
throw new Exception("class:" + this.GetType().Name + "\r\n Method:" + System.Reflection.MethodBase.GetCurrentMethod().Name + "\r\n" + ex.Message);
}
finally { bancoDados.Conexoes.close(); }
}
public void Delete(IObjectProxy proxy)
{
foreach (var trigger in proxy.Model.Triggers.Where(p => p.ModelTriggerType == ModelTriggerType.Delete))
{
new ModelMethodContext(this.sqlCon, this.ConFac).ExcuteOperation(proxy, trigger);
}
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this.GetSqlCon(proxy.Model).ToString()))
{
con.Open();
System.Data.SqlClient.SqlTransaction trans = con.BeginTransaction();
delete(proxy, trans);
trans.Commit();
}
}
public ActionResult NoInTrun()
{
string dateStr = Request["dateStr"];
DBModel.ApartmentCashEntities ef = new DBModel.ApartmentCashEntities();
System.Data.Entity.Database db = ef.Database;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(db.Connection.ConnectionString))
{
conn.Open();
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
string sql2 = string.Format(@"
update a
set a.UserID=b.UserID
from DateWash a
inner join DateWash b on a.DateWashID=b.DateWashID+1
where a.DateStr>'{0}'", dateStr);
System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand(sql2, conn, trans);
cmd2.ExecuteNonQuery();
string sql = string.Format("update DateWash set UserID=null where DateStr='{0}'", dateStr);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, conn, trans);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ee)
{
trans.Rollback();
json.success = false;
json.msg = ee.Message;
if (ee.InnerException != null)
{
json.msg += " " + ee.InnerException.Message;
}
}
}
json.success = true;
json.msg = "操作成功";
return(Content(json.ToString()));
}
public ActionResult CreateDateWashPerson()
{
int createCount = Request["createCount"].ToInt();
string [] personList = Request["personList"].Split(',');
string startDate = Request["startDate"];
DBModel.ApartmentCashEntities ef = new DBModel.ApartmentCashEntities();
System.Data.Entity.Database db = ef.Database;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(db.Connection.ConnectionString))
{
conn.Open();
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
for (int i = 0; i < createCount; i++)
{
string userID = personList[i % personList.Length];
string dateStr = DateTime.Parse(startDate).AddDays(i).ToString("yyyy-MM-dd");
string sql = "update DateWash set UserID=@UserID where DateStr=@DateStr";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, conn, trans);
cmd.Parameters.AddWithValue("@UserID", userID);
cmd.Parameters.AddWithValue("@DateStr", dateStr);
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ee)
{
trans.Rollback();
json.success = false;
json.msg = ee.Message;
if (ee.InnerException != null)
{
json.msg += " " + ee.InnerException.Message;
}
}
}
json.success = true;
json.msg = "生成成功";
return(Content(json.ToString()));
}
public void Save(IObjectProxy proxy)
{
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this.sqlCon.ToString()))
{
con.Open();
System.Data.SqlClient.SqlTransaction trans = con.BeginTransaction();
save(proxy, trans);
trans.Commit();
MemoryCache.StoreDbCache.UpdateOrAddDbMemoryCache(this.sqlCon,
proxy.Model, proxy.ID, proxy);
}
foreach (var trigger in proxy.Model.Triggers.Where(p => p.ModelTriggerType == ModelTriggerType.Save))
{
new ModelMethodContext(this.sqlCon, this.ConFac).ExcuteOperation(proxy, trigger);
}
}
/// <summary>
/// Obtinem o conexiune deschisa cu o tranzactie
/// </summary>
/// <param name="transac"></param>
/// <param name="DoCommit"></param>
public static void CloseTransactionOnConnection(System.Data.SqlClient.SqlTransaction transac, bool DoCommit)
{
System.Data.SqlClient.SqlConnection cn = transac.Connection;
if (cn != null)
{
if (DoCommit)
{
transac.Commit();
}
else
{
transac.Rollback();
}
transac.Dispose();
cn.Close();
cn.Dispose();
}
}
private void btnContinue_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlTransaction trnEnvelope = VPSConnection.BeginTransaction();
myPLine.UtilisationLineId = productionLineId;
myPLine.UtilisationWorkDayDate = DateTime.Now;
myPLine.UtilisationLineStarted = dtpStarted.Value.TimeOfDay;
myPLine.UtilisationLineStopped = myPLine.DefaultStopTime;
if (myPLine.Insert_Utilisation_Record(trnEnvelope) == true)
{
trnEnvelope.Commit();
this.DialogResult = DialogResult.Yes;
this.Hide();
}
else
{
trnEnvelope.Rollback();
}
}
protected override void DataAdapterUpdate(System.Data.DataSet tabela, string strNomeTabela)
{
if (m_bUserCanUpdateDB)
{
OpenConnection();
if (m_Connection.State == System.Data.ConnectionState.Open)
{
if (tabela.GetChanges() != null)
{
m_Transaction = m_Connection.BeginTransaction();
m_CommandSelect.Transaction = m_Transaction;
try
{
//HACK:Testing timeout
if (m_DataAdapter.InsertCommand != null)
{
m_DataAdapter.InsertCommand.CommandTimeout = 600;
}
if (m_DataAdapter.UpdateCommand != null)
{
m_DataAdapter.UpdateCommand.CommandTimeout = 600;
}
if (m_DataAdapter.DeleteCommand != null)
{
m_DataAdapter.DeleteCommand.CommandTimeout = 600;
}
//END
m_DataAdapter.Update(tabela, strNomeTabela);
m_Transaction.Commit();
DataPersist = false;
}
catch (System.Exception eEcp)
{
m_excError = eEcp;
ShowDialogUpdateError(strNomeTabela);
m_Transaction.Rollback();
}
}
}
CloseConnection();
}
}
public static void CreateStoredProcFromFile(string fileName,
string serverName,
string databaseName,
string spName,
string executeUser)
{
System.IO.StreamReader streamReader = new System.IO.StreamReader(fileName);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlTransaction transaction = null;
//string[] SQLStatements;
cmd.Connection = new System.Data.SqlClient.SqlConnection(CodeGenerationSupport.DatabaseSupport.GetConnectionString(serverName, databaseName));
cmd.CommandText = "sp_help '" + spName + "'";
try
{
cmd.Connection.Open();
transaction = cmd.Connection.BeginTransaction();
cmd.Transaction = transaction;
string[] statements = Regex.Split(streamReader.ReadToEnd(), "\\sgo\\s", RegexOptions.Compiled | RegexOptions.CultureInvariant | RegexOptions.IgnoreCase);
foreach (string statement in statements)
{
cmd.CommandText = statement;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
transaction.Rollback();
throw;
}
finally
{
try
{
cmd.Connection.Close();
}
catch
{
}
}
}
public bool Delete(Order item)
{
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection())
{
if (connection == null)
{
return(false);
}
connection.ConnectionString = ConnectionString;
connection.Open();
var deleteDetailCommand = connection.CreateCommand();
var deleteOrderCommand = connection.CreateCommand();
deleteDetailCommand.CommandText = $"delete from [Order Details] where OrderID = @id";
var paramId1 = SqlBuilder.Create("@id", DbType.Int32, item.Id);
deleteDetailCommand.Parameters.Add(paramId1);
deleteOrderCommand.CommandText = $"delete from Orders where OrderID = @id";
var paramId2 = SqlBuilder.Create("@id", DbType.Int32, item.Id);
deleteOrderCommand.Parameters.Add(paramId2);
System.Data.SqlClient.SqlTransaction tx = null;
try
{
tx = connection.BeginTransaction();
deleteDetailCommand.Transaction = tx;
deleteOrderCommand.Transaction = tx;
deleteDetailCommand.ExecuteNonQuery();
deleteOrderCommand.ExecuteNonQuery();
tx.Commit();
}
catch
{
tx.Rollback();
}
}
return(GetByID(item.Id) == null);
}
private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
int errorCount = 0;
Version databaseVersion = GetDatabaseVersion();
foreach (string updateFile in this.UpdateFiles)
{
Version updateVersion = new Version(updateFile);
if (updateVersion.CompareTo(databaseVersion) > 0)
{
backgroundWorker.ReportProgress(-1, "در حال به روز رسانی پایگاه داده به نسخه " + updateFile + " ...");
this.DataAccess.Connection.Open();
System.Data.SqlClient.SqlTransaction tr = this.DataAccess.Connection.BeginTransaction();
this.DataAccess.Transaction = tr;
string filePath = System.IO.Path.Combine(this.UpdateFilesPath, updateFile + "." + this.UpdateFilesExtension);
string query = System.IO.File.ReadAllText(filePath);
try
{
Njit.Sql.SqlHelper.RunQuery(query, this.DataAccess);
this.DataAccess.Execute("UPDATE [DatabaseVersion] SET [Version]=@Version", "@Version", updateVersion.ToString());
}
catch
{
tr.Rollback();
this.DataAccess.Connection.Close();
errorCount++;
backgroundWorker.ReportProgress(-2, "خطا در اجرای اسکریپ به روز رسانی نسخه " + updateFile);
e.Result = errorCount;
return;
}
tr.Commit();
this.DataAccess.Connection.Close();
backgroundWorker.ReportProgress(-1, "اسکریپ به روز رسانی نسخه " + updateFile + " با موفقیت نصب شد");
}
}
e.Result = errorCount;
}
