/// <summary>
/// 增加一条数据
/// </summary>
public int Add(DataRow dr, SqlTransaction trans)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO hrCompanyShopInfo(");
strSql.Append("MainID,sShopID,sShopCName,sShopEName,sRemark,sUserID)");
strSql.Append(" VALUES (");
strSql.Append("@MainID,@sShopID,@sShopCName,@sShopEName,@sRemark,@sUserID)");
strSql.Append(";SELECT @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@MainID", SqlDbType.Int,4),
new SqlParameter("@sShopID", SqlDbType.VarChar,30),
new SqlParameter("@sShopCName", SqlDbType.VarChar,50),
new SqlParameter("@sShopEName", SqlDbType.VarChar,50),
new SqlParameter("@sRemark", SqlDbType.VarChar,200),
new SqlParameter("@sUserID", SqlDbType.VarChar,30)};
parameters[0].Value = dr["MainID"];
parameters[1].Value = dr["sShopID"];
parameters[2].Value = dr["sShopCName"];
parameters[3].Value = dr["sShopEName"];
parameters[4].Value = dr["sRemark"];
parameters[5].Value = dr["sUserID"];
object obj = DbHelperSQL.GetSingle(strSql.ToString(), trans, parameters);
if (obj == null)
{
return -1;
}
else
{
return Convert.ToInt32(obj);
}
}
public static int InsertBySQL(List <string> SQL)
{
System.Data.SqlClient.SqlConnection oracleConnection = new System.Data.SqlClient.SqlConnection(constr);
System.Data.SqlClient.SqlCommand oracleCommand = new System.Data.SqlClient.SqlCommand();
oracleCommand.Connection = oracleConnection;
int rowsAffected = 0;
oracleConnection.Open();
System.Data.SqlClient.SqlTransaction tran = oracleConnection.BeginTransaction();
try
{
foreach (string s in SQL)
{
oracleCommand.Transaction = tran;
oracleCommand.CommandText = s;
rowsAffected += oracleCommand.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw new NotSupportedException(ex.Message);
}
finally
{
oracleConnection.Close();
}
return(rowsAffected);
}
public void Import(string connectionString, StatbookModel statbook, bool assumeATeams)
{
_connection = new SqlConnection(connectionString);
try
{
_connection.Open();
_transaction = _connection.BeginTransaction();
// insert leagues
LeagueGateway leagueGateway = new LeagueGateway(_connection, _transaction);
var leagues = leagueGateway.GetAllLeagues();
League homeLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.HomeTeam.LeagueName.ToLower());
League awayLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.AwayTeam.LeagueName.ToLower());
int maxID = leagues.Select(l => l.ID).Max();
if(homeLeague == null)
{
homeLeague = leagueGateway.GetLeague(maxID + 1, statbook.HomeTeam.LeagueName, statbook.Date, false);
maxID++;
}
if(awayLeague == null)
{
awayLeague = leagueGateway.GetLeague(maxID + 1, statbook.AwayTeam.LeagueName, statbook.Date, false);
maxID++;
}
// insert teams
TeamGateway teamGateway = new TeamGateway(_connection, _transaction);
Team homeTeam, awayTeam;
if (assumeATeams)
{
homeTeam = teamGateway.GetATeam(homeLeague.ID);
awayTeam = teamGateway.GetATeam(awayLeague.ID);
}
else
{
homeTeam = teamGateway.GetTeam(statbook.HomeTeam.Name, homeLeague.ID, "A", false);
awayTeam = teamGateway.GetTeam(statbook.AwayTeam.Name, awayLeague.ID, "A", false);
}
// insert bout
BoutGateway boutGateway = new BoutGateway(_connection, _transaction);
if(!boutGateway.DoesBoutExist(homeTeam.ID, awayTeam.ID, statbook.Date))
{
Bout bout = boutGateway.GetBout(homeTeam.ID, awayTeam.ID, statbook.Date);
BoutDataImport(statbook, bout, homeTeam, awayTeam);
}
else
{
// bout already exists
Console.WriteLine(string.Format("Bout between {0} and {1} on {2} already exists.", homeTeam.Name, awayTeam.Name, statbook.Date));
}
_transaction.Commit();
}
finally
{
_connection.Close();
}
}
public void Add(SqlTransaction transaction)
{
_orderInsertDataParameters = new OrderInsertDataParameters(Orders);
DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName);
object id = dbHelper.RunScalar(transaction, _orderInsertDataParameters.Parameters);
Orders.OrderId = int.Parse(id.ToString());
}
/// <summary>
/// Get insert cmd of GENERAL_AUT_USERROLE table
/// </summary>
/// <param name="con"></param>
/// <param name="trans"></param>
/// <returns></returns>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public SqlCommand GetInsertCommand(SqlConnection con, SqlTransaction trans)
{
SqlCommand cmd = new SqlCommand("INSERT INTO GENERAL_AUT_USERROLE(UROLE_ID, ROLE_NAME) VALUES(@UROLE_ID, @ROLE_NAME)", con, trans);
cmd.Parameters.Add("@UROLE_ID", SqlDbType.VarChar, 14, "UROLE_ID");
cmd.Parameters.Add("@ROLE_NAME", SqlDbType.VarChar, 255, "ROLE_NAME");
return cmd;
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(DataRow dr, SqlTransaction trans)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO sysMenuParam(");
strSql.Append("MenuID,sParamName,sParamValue,sUserID)");
strSql.Append(" VALUES (");
strSql.Append("@MenuID,@sParamName,@sParamValue,@sUserID)");
strSql.Append(";SELECT @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@MenuID", SqlDbType.Int,4),
new SqlParameter("@sParamName", SqlDbType.VarChar,50),
new SqlParameter("@sParamValue", SqlDbType.VarChar,50),
new SqlParameter("@sUserID", SqlDbType.VarChar,30)};
parameters[0].Value = dr["MenuID"];
parameters[1].Value = dr["sParamName"];
parameters[2].Value = dr["sParamValue"];
parameters[3].Value = dr["sUserID"];
object obj = DbHelperSQL.GetSingle(strSql.ToString(), trans, parameters);
if (obj == null)
{
return -1;
}
else
{
return Convert.ToInt32(obj);
}
}
/// <summary>
/// 查找不存在的图片并删除已删除的图片及数据
/// </summary>
public void DeleteList(SqlConnection conn, SqlTransaction trans, List<ArticleImagesSizeInfo> models, int category_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (var modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string id_list = Utils.DelLastChar(idList.ToString(), ",");
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,height,width,category_id from " + databaseprefix + "article_images_size where category_id=" + category_id);
if (!string.IsNullOrEmpty(id_list))
{
strSql.Append(" and id not in(" + id_list + ")");
}
DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
DbHelperSQL.ExecuteSql(conn, trans, "delete from " + databaseprefix + "article_images_size where id=" + dr["id"].ToString()); //删除数据库
}
}
/// <summary>
/// Delete values from MarcControl by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="marcControlID"></param>
/// <returns>true if successful otherwise false.</returns>
public bool MarcControlDeleteAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int marcControlID)
{
return MarcControlDeleteAuto( sqlConnection, sqlTransaction, "BHL", marcControlID );
}
private bool disposed = false; //Disposeを何度も呼び出させない
public MyModel()
{
// 接続文字列を生成する
string connectString =
"Data Source = " + Settings.DatabaseServer
+ ";Initial Catalog = " + Settings.DatabaseName
+ ";User ID = " + Settings.DatabaseUser
+ ";Password = "******"データベース接続エラー " + ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlConn.Close();
Application.Exit();
}
}
public bool Guardar(System.Data.SqlClient.SqlTransaction transaccion)
{
/*using (SqlConnection conexion = new SqlConnection())
* {
* SqlTransaction transaccion = null;
* conexion.ConnectionString = Config.ConnectionString;
* try
* {
* conexion.Open();
* transaccion = conexion.BeginTransaction();*/
foreach (GuiaComprobanteCobro comprobanteCobro in this.guiaComprobantesCobros)
{
if (!comprobanteCobro.Guardar(transaccion))
{
transaccion.Rollback();
return(false);
}
}
//transaccion.Commit();
return(true);
/*}
* catch (Exception ex)
* {
* transaccion.Rollback();
* throw ex;
* }
* }*/
}
public void ExecuteSqlTran(Hashtable SQLStringList)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
{
sqlConnection.Open();
using (System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
try
{
foreach (DictionaryEntry dictionaryEntry in SQLStringList)
{
string cmdText = dictionaryEntry.Key.ToString();
System.Data.SqlClient.SqlParameter[] cmdParms = (System.Data.SqlClient.SqlParameter[])dictionaryEntry.Value;
DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, sqlTransaction, cmdText, cmdParms);
int num = sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear();
}
sqlTransaction.Commit();
}
catch
{
sqlTransaction.Rollback();
throw;
}
}
}
}
public int ExecuteSqlTran(List <string> SQLStringList)
{
int result;
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
{
sqlConnection.Open();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
sqlCommand.Connection = sqlConnection;
System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
try
{
int num = 0;
for (int i = 0; i < SQLStringList.Count; i++)
{
string text = SQLStringList[i];
if (text.Trim().Length > 1)
{
sqlCommand.CommandText = text;
num += sqlCommand.ExecuteNonQuery();
}
}
sqlTransaction.Commit();
result = num;
}
catch
{
sqlTransaction.Rollback();
result = 0;
}
}
return(result);
}
/// <summary>
/// 保存数据表所做的更改（带事务）
/// </summary>
/// <param name="conn">SqlTransaction</param>
/// <param name="trans">SqlTransaction</param>
/// <param name="strSQL">需要更新的表的SQL语句</param>
/// <param name="dt">需要更新的DataTable</param>
public static void SaveChangesOnTrans(SDC.SqlConnection conn, SDC.SqlTransaction trans, string strSQL, SD.DataTable dt)
{
//this.Validate();
try
{
using (sqlcmd = new SDC.SqlCommand(strSQL, conn, trans))
{
using (sqladp = new SDC.SqlDataAdapter(sqlcmd))
{
//sqladp.InsertCommand.Transaction = trans;
using (sqlcmdbd = new SDC.SqlCommandBuilder(sqladp))
{
sqlcmdbd.ConflictOption = SD.ConflictOption.CompareRowVersion;
sqladp.InsertCommand = sqlcmdbd.GetInsertCommand();
sqladp.UpdateCommand = sqlcmdbd.GetUpdateCommand();
sqladp.DeleteCommand = sqlcmdbd.GetDeleteCommand();
sqladp.Update(dt);
}
}
}
}
catch (SDC.SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public bool Guardar(System.Data.SqlClient.SqlTransaction transaccion, int UsuarioID)
{
try
{
if (this.CotizacionClienteUVentaModalidadOrigenDestinoID == 0)
{
this.CotizacionClienteUVentaModalidadOrigenDestinoID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "CotizacionClienteUVentaModalidadOrigenDestinoINS", this.CotizacionClienteUVentaModalidad.CotizacionClienteUVentaModalidadID,
this.Aprobado, Utiles.BaseDatos.FechaToSql(this.FechaAlta), Utiles.BaseDatos.IntToSql(this.ClienteOrigen.ClienteOrigenID), Utiles.BaseDatos.IntToSql(this.AgrupacionDestino.AgrupacionDestinoID), Utiles.BaseDatos.IntToSql(this.ClienteDestino.ClienteDestinoID),
Utiles.BaseDatos.IntToSql(this.ClienteZona.ClienteZonaID), Utiles.BaseDatos.StrToSql(this.DiaHoraRetiro), Utiles.BaseDatos.StrToSql(this.DiaHoraEntrega), Utiles.BaseDatos.IntToSql(this.TarifarioClienteFleteID), Utiles.BaseDatos.IntToSql(this.TarifarioFleteID),
Utiles.BaseDatos.DoubleToSql(this.ImporteFacturacionMensual), Utiles.BaseDatos.IntToSql(this.MovimientoMensual), Utiles.BaseDatos.StrToSql(this.EsIdaVuelta), Utiles.BaseDatos.IntToSql(this.TarifarioVentaReDespachoID), UsuarioID));
}
else
{
Config.Conexion.EjecutarSinResultados(transaccion, "CotizacionClienteUVentaModalidadOrigenDestinoUPD", this.CotizacionClienteUVentaModalidadOrigenDestinoID, this.CotizacionClienteUVentaModalidad.CotizacionClienteUVentaModalidadID,
this.Aprobado, Utiles.BaseDatos.IntToSql(this.ClienteOrigen.ClienteOrigenID), Utiles.BaseDatos.IntToSql(this.AgrupacionDestino.AgrupacionDestinoID), Utiles.BaseDatos.IntToSql(this.ClienteDestino.ClienteDestinoID),
Utiles.BaseDatos.IntToSql(this.ClienteZona.ClienteZonaID), Utiles.BaseDatos.StrToSql(this.DiaHoraRetiro), Utiles.BaseDatos.StrToSql(this.DiaHoraEntrega), Utiles.BaseDatos.IntToSql(this.TarifarioClienteFleteID),
Utiles.BaseDatos.IntToSql(this.TarifarioFleteID), Utiles.BaseDatos.DoubleToSql(this.ImporteFacturacionMensual), Utiles.BaseDatos.IntToSql(this.MovimientoMensual), Utiles.BaseDatos.StrToSql(this.EsIdaVuelta), Utiles.BaseDatos.IntToSql(this.TarifarioVentaReDespachoID), UsuarioID);
}
return(true);
}
catch (Exception ex)
{
//No largo la excepcion. Supongo que se trata de un error que he largado en el SP.
this.HayError = true;
this.ErrorDescrip = ex.Message;
throw ex;
//return false;
}
}
public bool Guardar(System.Data.SqlClient.SqlTransaction transaccion)
{
this.GuiaEstadoHisID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "GuiaEstadoHistoricoDatosINS",
this.GuiaID, Convert.ToInt32(this.GuiaEstadoHisID), Utiles.BaseDatos.IntToSql(this.estadoGuiaDatosID),
Utiles.BaseDatos.StrToSql(this.Valor)));
return(true);
}
/// <summary>
/// Constructs an instance of an atom transaction scope.
/// </summary>
/// <param name="kind">Type of transaction scope.</param>
/// <param name="conn">Connection to use for the transaction scope.</param>
protected internal SqlStoreTransactionScope(StoreTransactionScopeKind kind, SqlConnection conn)
{
this.Kind = kind;
_conn = conn;
switch (this.Kind)
{
case StoreTransactionScopeKind.ReadOnly:
SqlUtils.WithSqlExceptionHandling(() =>
{
_tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
});
break;
case StoreTransactionScopeKind.ReadWrite:
SqlUtils.WithSqlExceptionHandling(() =>
{
_tran = conn.BeginTransaction(IsolationLevel.RepeatableRead);
});
break;
default:
// Do not start any transaction.
Debug.Assert(this.Kind == StoreTransactionScopeKind.NonTransactional);
break;
}
}
public static DataTable ExcuteDataTableByTextWithTrans(SqlConnection connection, SqlTransaction tran, string sqlText, IDataParameter[] paramers)
{
DataTable dt = new DataTable("");
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
SqlCommand cmd = new SqlCommand(sqlText, connection, tran);
cmd.CommandTimeout = 300;
cmd.CommandType = CommandType.Text;
if (null != paramers)
{
foreach (SqlParameter param in paramers)
{
cmd.Parameters.Add(param);
}
}
////Add parameter to receive return value of stored procedure
//SqlParameter pr = new SqlParameter("ReturnValue", SqlDbType.Int);
//pr.Direction = ParameterDirection.ReturnValue;
//cmd.Parameters.Add(pr);
SqlDataAdapter ada = new SqlDataAdapter(cmd);
ada.Fill(dt);
return dt;
//return pr.Value;
}
public bool Guardar(System.Data.SqlClient.SqlTransaction transaccion)
{
this.ChoferID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "ChoferINS", Utiles.BaseDatos.StrToSql(this.ChoferEmpresaSITT),
Utiles.BaseDatos.StrToSql(this.LegajoSITT), Utiles.BaseDatos.StrToSql(this.Nombre), Utiles.BaseDatos.FechaToSql(this.FechaAlta), this.UnidadNegocioID));
return(true);
}
/// <summary>
/// 查找不存在的文件并删除已删除的附件及数据
/// </summary>
public void DeleteList(SqlConnection conn, SqlTransaction trans, List<Model.download_attach> models, int article_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (Model.download_attach modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string id_list = Utils.DelLastChar(idList.ToString(), ",");
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,file_path from dt_download_attach where article_id=" + article_id);
if (!string.IsNullOrEmpty(id_list))
{
strSql.Append(" and id not in(" + id_list + ")");
}
DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
int rows = DbHelperSQL.ExecuteSql(conn, trans, "delete from dt_download_attach where id=" + dr["id"].ToString()); //删除数据库
if (rows > 0)
{
Utils.DeleteFile(dr["file_path"].ToString()); //删除文件
}
}
}
public static bool ExecuteQueryTrans(string cmdtext, params SqlParameter[] parameters)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(connStr))
{
System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
cm.Connection = conn;
conn.Open();
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
cm.CommandText = cmdtext;
cm.Parameters.AddRange(parameters);
cm.Transaction = trans;
i = cm.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
conn.Close();
trans.Dispose();
conn.Dispose();
}
}
return(i > 0);
}
public static DataSet ExecuteDataset(SqlConnection cn, SqlTransaction trans, string cmdText, string tableName, params SqlParameter[] sqlParams)
{
DataSet data = new DataSet();
SqlCommand cmd = new SqlCommand(cmdText, cn);
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
if (sqlParams != null)
{
AttachParameters(cmd, sqlParams);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
if (tableName != null && tableName != string.Empty)
{
adapter.Fill(data, tableName);
}
else
{
adapter.Fill(data);
}
adapter.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
return data;
}
public bool UpdateAdjustmentPromotion(System.Data.SqlClient.SqlTransaction trans, string strReceiptNo, string strItemCode, string strPromotionCode, string strBranchCode, int nEntryID, string panel)
{
//DataSet ds = null;
SqlParameter param1 = new SqlParameter("strPromotionCode", strPromotionCode);
SqlParameter param2 = new SqlParameter("strItemCode", strItemCode);
SqlParameter param3 = new SqlParameter("strReceiptNo", strReceiptNo);
SqlParameter param4 = new SqlParameter("strBranchCode", strBranchCode);
SqlParameter param5 = new SqlParameter("nEntryID", nEntryID);
SqlParameter param6 = new SqlParameter("panel", panel);
try
{
int query = SqlHelper.ExecuteNonQuery(trans, "SP_UpdateAdjustmentPromotion", param1, param2, param3, param4, param5, param6);
if (query > 0)
{
return(true);
}
}
catch (Exception err)
{
UI.ShowErrorMessage(null, err.Message, "Error");
return(false);
}
return(false);
}
/// <summary>
/// Delete values from PDFPage by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="pdfPageID"></param>
/// <returns>true if successful otherwise false.</returns>
public bool PDFPageDeleteAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int pdfPageID)
{
return PDFPageDeleteAuto( sqlConnection, sqlTransaction, "BHL", pdfPageID );
}
public bool Guardar(System.Data.SqlClient.SqlTransaction transaccion)
{
this.GuiaComprobanteCobroID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "GuiaComprobanteCobroINS", this.GuiaID,
this.GuiaFacturaDetalleID, this.Banco, Utiles.BaseDatos.IntToSql(this.ChequeNro), Utiles.BaseDatos.IntToSql(this.BoletaDepositoNro), this.Importe));
return(true);
}
/// <summary>
/// 批量增加数据
/// </summary>
public void BatchDelete(List<int> list, SqlTransaction trans)
{
foreach (int i in list.ToArray())
{
Delete(i, trans);
}
}
public Transaction BeginTransaction()
{
System.Data.SqlClient.SqlTransaction tr = Conn.BeginTransaction();
Transaction result = new Transaction(tr);
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.ServerVariables["REQUEST_METHOD"] == "POST")
{
strPost = CCLib.Common.Strings.GetFormString("post");
if (strPost == "1" && Session["RegionAdminInfo"] != null)
{
DataRow drAdminInfo = (DataRow)Session["RegionAdminInfo"];
//CCLib.Common.DataAccess.ExecuteDb("update RRS_Login_View set Agreement=1 where RegionId='" + drAdminInfo["RegionId"].ToString().Replace("'", "''") + "' and Password='******'", "''") + "'");
//
this.strPassword = drAdminInfo["Password"].ToString().Replace("'", "''");
this.strRegionID = drAdminInfo["RegionId"].ToString();
this.strConnectionStringToSLX = WebConfigurationManager.AppSettings["strConSLX"];
this.strConnectionStringToCC = WebConfigurationManager.AppSettings["strConLocal"];
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Ssl3;
System.Net.ServicePointManager.CertificatePolicy = new CareerCruisingWeb.CCLib.SLXIntegration.TrustAllCertificatePolicy();
this.wsSLXIntegration = new WSCCIntegrationWithSLX.IntegrationWithSLX();
this.userCredential = new System.Net.NetworkCredential("WSCCTOSLX", "Next_20");
this.wsSLXIntegration.Credentials = userCredential;
this.wsSLXIntegration.PreAuthenticate = true;
this.cnnCC = new SqlConnection(this.strConnectionStringToCC);
if (this.cnnCC.State == ConnectionState.Closed)
{
this.cnnCC.Open();
}
this.trsCC = this.cnnCC.BeginTransaction();
this.objDistrictUserID = SqlHelper.ExecuteScalar(this.trsCC, CommandType.Text, this.GetCCSQLSelectDistrictUserID(this.strPassword, this.strRegionID));
this.strDistrictUserID = this.objDistrictUserID != null ? this.objDistrictUserID.ToString() : null;
this.intReturnValue1 = CareerCruisingWeb.CCLib.SLXIntegration.IntegrationWithSLX.ExecuteNoQuery(this.GetCCSQLUpdateRRSUsers(this.strDistrictUserID), this.cnnCC, this.trsCC);
if (this.intReturnValue1 > 0)
{
this.intReturnValue2 = this.wsSLXIntegration.ExecuteNoQuery(this.GetSLXSQLUpdateConRRSUsers(this.strDistrictUserID), this.strConnectionStringToSLX);
if (this.intReturnValue2 > 0)
{
this.trsCC.Commit();
this.ReleaseObjects();
}
else
{
this.trsCC.Rollback();
this.ReleaseObjects();
}
}
else
{
this.trsCC.Rollback();
this.ReleaseObjects();
}
//
Response.Redirect("https://" + ConfigurationManager.AppSettings["strServerName"] + "/Region/Home.aspx");
}
}
}
public bool Guardar(System.Data.SqlClient.SqlTransaction transaccion, IUsuarios usuario)
{
try
{
if (this.AgrupacionID == 0) //agregar
{
fechaAlta = DateTime.Now;
this.AgrupacionID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "AgrupacionAgenciaINS",
Utiles.BaseDatos.StrToSql(this.AgrupacionNombre), Utiles.BaseDatos.FechaToSql(FechaAlta), usuario.UsuarioID, this.EsPublica
));
}
else //modificar
{
this.FechaUltModif = DateTime.Now;
Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "AgrupacionAgenciaUPD",
Utiles.BaseDatos.IntToSql(this.AgrupacionID),
Utiles.BaseDatos.StrToSql(this.AgrupacionNombre),
this.EsPublica,
Utiles.BaseDatos.IntToSql(usuario.UsuarioID),
Utiles.BaseDatos.FechaToSql(this.FechaUltModif)));
}
return(this.AgrupacionID > 0); //Si se Si pudo guardar, devuelve un número mayor a cero (devuelve el Id que insertó o el ID del servicio al que le hizo UPD)
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Delete values from Item by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="itemID"></param>
/// <returns>true if successful otherwise false.</returns>
public bool ItemDeleteAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int itemID)
{
return ItemDeleteAuto( sqlConnection, sqlTransaction, "BHL", itemID );
}
/// <summary>
/// 增加多条数据
/// </summary>
public void Add(List <Hi.Model.BD_DisPriceInfo> l, System.Data.SqlClient.SqlTransaction Tran)
{
foreach (Hi.Model.BD_DisPriceInfo model in l)
{
dal.Add(model, Tran);
}
}
public SqlCommand CreateAddProductCommand(SqlConnection conn, SqlTransaction transaction)
{
var cmd = new SqlCommand(_sql, conn, transaction);
CreateParameters(cmd);
return cmd;
}
// /// <summary>
// /// Método para dar de baja lógicamente a una zona de un tarifario.
// /// </summary>
// /// <returns>Retorna un booleano indicando el éxito de la operacion.</returns>
// public bool Eliminar()
// {
// using (SqlConnection conexion = new SqlConnection())
// {
// SqlTransaction transaccion = null;
// conexion.ConnectionString = Config.ConnectionString;
//
// try
// {
//
// conexion.Open();
// transaccion = conexion.BeginTransaction();
// Config.Conexion.EjecutarSinResultados(transaccion,"TarifarioFleteZonaDEL", this.TarifarioFleteZonaID);
// //Se instancia un objeto de TarifarioFlete para por utilizar el metodo validar (Bety)
// TarifarioFlete oTari= new TarifarioFlete();
// oTari.TarifarioFleteID=this.TarifarioFleteID;
// oTari.ValidarTarifario(transaccion);
// transaccion.Commit();
// return true;
// }
// catch(Exception)
// {
// transaccion.Rollback();
// return false;
// }
// }
// }
public bool Eliminar(int usuarioID, System.Data.SqlClient.SqlTransaction transaccion)
{
using (SqlConnection conexion = new SqlConnection())
{
//SqlTransaction transaccion = null;
conexion.ConnectionString = Config.ConnectionString;
try
{
conexion.Open();
transaccion = conexion.BeginTransaction();
Config.Conexion.EjecutarSinResultados(transaccion, "TarifarioFleteZonaDEL", this.TarifarioFleteZonaID, usuarioID);
//Se instancia un objeto de TarifarioFlete para por utilizar el metodo validar (Bety)
TarifarioFlete oTari = new TarifarioFlete();
oTari.TarifarioFleteID = this.TarifarioFleteID;
oTari.ValidarTarifario(transaccion);
transaccion.Commit();
return(true);
}
catch (Exception)
{
transaccion.Rollback();
return(false);
}
}
}
public static bool Insert_Callstack(SqlConnection conn, ref SqlTransaction tran, int project_uid, byte[] signature, out int callstack_uid)
{
callstack_uid = 0;
try
{
SqlCommand cmd1 = new SqlCommand("usp_insert_callstack", conn, tran);
cmd1.CommandType = CommandType.StoredProcedure;
SqlParameter returned = cmd1.CreateParameter();
returned.Direction = ParameterDirection.ReturnValue;
cmd1.Parameters.Add(returned);
cmd1.Parameters.AddWithValue("@project_uid", project_uid);
cmd1.Parameters.AddWithValue("@signature", signature);
cmd1.ExecuteNonQuery();
callstack_uid = (int)returned.Value;
}
catch (System.Exception e)
{
Console.WriteLine(e.Message);
return false;
}
return true;
}
private DataSet GetGuiasEnPlanillaRemitoConEstadoEnPlanilla(System.Data.SqlClient.SqlTransaction transaccion)
{
DataSet ds = Config.Conexion.EjecutarDataSet(transaccion,
"GetGuiasConEstadoEnPlanillaRemito", this.PlanillaRemitoClienteID);
return(ds);
}
public bool Update_ControlPresupuestal(ControlPresupuestal_VO pControlPresupuestal, SqlTransaction TransControlPresupuestal)
{
bool b_ControlPresupuestal;
try
{
SqlCommand cmdComand = new SqlCommand("USP_U_SICOP_CONTROLPRESUPUESTAL");
cmdComand.CommandType = System.Data.CommandType.StoredProcedure;
cmdComand.Connection = TransControlPresupuestal.Connection;
cmdComand.Transaction = TransControlPresupuestal;
cmdComand.Parameters.Clear();
cmdComand.Parameters.AddWithValue("@CPRE_INT_IDCONTROLPRESUPUESTAL", pControlPresupuestal.CPRE_INT_IDCONTROLPRESUPUESTAL);
cmdComand.Parameters.AddWithValue("@EGAS_VCH_IDESPECIFICADEGASTO", pControlPresupuestal.EGAS_VCH_IDESPECIFICADEGASTO);
cmdComand.Parameters.AddWithValue("@DOCU_INT_IDDOCUMENTO", pControlPresupuestal.DOCU_INT_IDDOCUMENTO);
cmdComand.Parameters.AddWithValue("@CPRE_VCH_NRODOCUMENTO", pControlPresupuestal.CPRE_VCH_NRODOCUMENTO);
cmdComand.Parameters.AddWithValue("@CPRE_DAT_FECHAINGRESO", pControlPresupuestal.CPRE_DAT_FECHAINGRESO);
cmdComand.Parameters.AddWithValue("@CPRE_VCH_NROCOMPROBANTEPAGO", pControlPresupuestal.CPRE_VCH_NROCOMPROBANTEPAGO);
cmdComand.Parameters.AddWithValue("@CPRE_VCH_NROSIAF", pControlPresupuestal.CPRE_VCH_NROSIAF);
cmdComand.Parameters.AddWithValue("@CPRE_DAT_FECHAGIRO", pControlPresupuestal.CPRE_DAT_FECHAGIRO);
cmdComand.Parameters.AddWithValue("@CPRE_DAT_FECHAPAGO", pControlPresupuestal.CPRE_DAT_FECHAPAGO);
cmdComand.Parameters.AddWithValue("@CPRE_VCH_CONCEPTOESPECIFICADEGASTO", pControlPresupuestal.CPRE_VCH_CONCEPTOESPECIFICADEGASTO);
cmdComand.Parameters.AddWithValue("@CPRE_VCH_DETALLEESPECIFICADEGASTO", pControlPresupuestal.CPRE_VCH_DETALLEESPECIFICADEGASTO);
cmdComand.Parameters.AddWithValue("@CPRE_VCH_PARTIDACONTABLE", pControlPresupuestal.CPRE_VCH_PARTIDACONTABLE);
b_ControlPresupuestal = cmdComand.ExecuteNonQuery() > 0;
}
catch (Exception e)
{
if (e.Source != null)
Console.WriteLine("IOException source: {0}", e.Source);
throw;
}
return b_ControlPresupuestal;
}
private DataSet GetGuiasPendientesDeArchivar(System.Data.SqlClient.SqlTransaction transaccion)
{
DataSet ds = Config.Conexion.EjecutarDataSet(transaccion,
"GetGuiasPendientesDeArchivar", this.PlanillaRemitoClienteID);
return(ds);
}
/// <summary>
/// Executes the script.
/// </summary>
/// <remarks>
/// Use script.Execute(transaction) to do the work. We will also pull the
/// status of our script exection from here.
/// </remarks>
/// <param name="scriptName">Name of the script.</param>
/// <param name="transaction">The current transaction.</param>
/// <param name="dbUserName">Name of the DB owner.</param>
public static void ExecuteScript(string scriptName, SqlTransaction transaction, string dbUserName)
{
SqlScriptRunner scriptRunner = new SqlScriptRunner(UnpackEmbeddedScript(scriptName), Encoding.UTF8);
if(!string.IsNullOrEmpty(dbUserName))
scriptRunner.TemplateParameters.SetValue("dbUser", dbUserName);
scriptRunner.Execute(transaction);
}
public bool Guardar(System.Data.SqlClient.SqlTransaction transaccion, int estadoMovimiento, int estadoEstatico, bool asignada, int agencia, int usuario)
{
try
{
if (this.PlanillaTrasbordoDetalleID == 0) //agregar
{
int asign = 0;
if (asignada)
{
asign = 1;
}
this.PlanillaTrasbordoDetalleID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "PlanillaTrasbordoDetalleINS",
Utiles.BaseDatos.IntToSql(this.PlanillaTrasbordoID),
Utiles.BaseDatos.IntToSql(this.GuiaID), estadoMovimiento, estadoEstatico, asign, agencia, usuario,
Utiles.BaseDatos.IntToSql(this.ObservacionID)));
}
// else//modificar
return(this.PlanillaTrasbordoDetalleID > 0); //Si se Si pudo guardar, devuelve un número mayor a cero
}
catch (Exception ex)
{
throw ex;
}
}
public void Close()
{
try
{
if (this.mConnection != null)
{
this.mConnection.Close();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (!Information.IsNothing(this.mTransaction))
{
this.mTransaction.Dispose();
this.mTransaction = null;
}
if (!Information.IsNothing(this.mConnection))
{
SqlConnection.ClearPool(this.mConnection);
this.mConnection.Dispose();
this.mConnection = null;
}
}
}
public bool Modificar(System.Data.SqlClient.SqlTransaction transaccion, int estadoMovimiento, int estadoEstatico, bool recepcionada,
int usuario, int agencia, int nroPlanilla)
{
try
{
if ((this.PlanillaTrasbordoID != 0) && (this.GuiaID != 0)) //modificar
{
string ob = "";
int recepcion = 0;
if (recepcionada)
{
recepcion = 1;
ob = Utiles.BaseDatos.StrToSql("Planilla Trasbordo: " + nroPlanilla).ToString();
}
this.PlanillaTrasbordoDetalleID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "PlanillaTrasbordoDetalleRecepcionUPD",
Utiles.BaseDatos.IntToSql(this.PlanillaTrasbordoID),
Utiles.BaseDatos.IntToSql(this.GuiaID),
estadoMovimiento, estadoEstatico,
recepcion, //para que le llegue 1 no true
usuario, agencia, ob,
Utiles.BaseDatos.IntToSql(this.ObservacionID)));
}
return(this.PlanillaTrasbordoDetalleID > 0); //Si se Si pudo guardar, devuelve un número mayor a cero
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Delete values from TitleIdentifier by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="titleIdentifierID"></param>
/// <returns>true if successful otherwise false.</returns>
public bool TitleIdentifierDeleteAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int titleIdentifierID)
{
return TitleIdentifierDeleteAuto( sqlConnection, sqlTransaction, "BHL", titleIdentifierID );
}
public static int EjecutarNonQuery(string CommandString, System.Data.SqlClient.SqlTransaction tran)
{
SqlCommand Comando = new SqlCommand();
PrepararConexion(Comando, tran);
try
{
object aux;
//Adecua la sentencia Segun el Tipo de Dato:
Comando.CommandText = CommandString;
//Comando.Connection = Conexion
Comando.CommandType = CommandType.Text;
if (Comando.Connection.State != ConnectionState.Open)
{
Comando.Connection.Open();
}
aux = EjecutarNonQuery(Comando);
return(Convert.ToInt32(aux));
}
catch (Exception ex)
{
throw ex;
}
}
//-------------------------------------------------------------------------------------------
public void DeleteCheckedItems(SqlTransaction transaction, Control parent)
{
//foreach (Control control in parent.Controls)
//{
// if (control.GetType() == typeof(CheckBox))
// {
// CheckBox item = (CheckBox)control;
// if (item.Checked)
// {
// Guid itemId = new Guid(item.Attributes["datakey"]);
// DataRowLookup reverseLookup = DatabaseHelper.Session.Get<DataRowLookup>(itemId);
// if (reverseLookup != null)
// {
// SqlCommand command = new SqlCommand("delete from " + reverseLookup.TableName + " where id=@Id", wvvrdb.MSSQLDB);
// command.Parameters.AddWithValue("Id", reverseLookup.RowId);
// command.Transaction = transaction;
// command.ExecuteNonQuery();
// }
// string sql = "delete from system_datalinks where (object1=@object1 and object2=@object2) ";
// sql += "or (object1=@object2 and object2=@object1)";
// SqlCommand dataLinks = new SqlCommand(sql, wvvrdb.MSSQLDB);
// dataLinks.Parameters.AddWithValue("Object1", itemId);
// dataLinks.Parameters.AddWithValue("Object2", new Guid(Request["id"]));
// dataLinks.Transaction = transaction;
// dataLinks.ExecuteNonQuery();
// }
// }
// if (control.HasControls())
// {
// DeleteCheckedItems(transaction, control);
// }
//}
}
public static int GetNextId(string Tabla, string NombreClave, System.Data.SqlClient.SqlTransaction tran)
{
try
{
SqlCommand Comando = new SqlCommand();
object Aux;
int ret;
PrepararConexion(Comando, tran);
//Armo la Query
Comando.CommandType = CommandType.Text;
Comando.CommandText = "SELECT MAX(" + NombreClave + ") " + "FROM [" + Tabla + "] ";
//Obtengo el Nuevo Id
Aux = EjecutarScalar(Comando);
//Si el resultado es un numero, le sumo 1 y lo devuelvo.
//Sino, devuelvo 1.
if ((bool)int.TryParse(Aux.ToString(), out ret))
{
return(ret + 1);
}
else
{
return(1);
}
}
catch (Exception ex)
{
throw new Exception("Error en: DataLibrary - GetNextId", ex);
}
}
/// <summary>
/// Delete values from PDFStatus by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="pdfStatusID"></param>
/// <returns>true if successful otherwise false.</returns>
public bool PDFStatusDeleteAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int pdfStatusID)
{
return PDFStatusDeleteAuto( sqlConnection, sqlTransaction, "BHL", pdfStatusID );
}
public System.Data.SqlClient.SqlTransaction SaveTruck(GINTruckInfo truck, System.Data.SqlClient.SqlTransaction transaction)
{
IGINProcess originalGIN = new GINProcessBLL(currentGINProcess.GINProcessInformation.TransactionId);
currentGINProcess.SaveTruck(truck, transaction);
//ConfirmNoConcurrency(Truck, Load, Weight)
var truckChanges = (from oTruck in originalGIN.GINProcessInformation.Trucks
where oTruck.TruckId == truck.TruckId
select new object[] { oTruck, truck, AuditTrailWrapper.ExistingRecord });
try
{
AuditTrailWrapper atw = new AuditTrailWrapper(AuditTrailWrapper.TruckRegistration, truckChanges.ToArray(), "GIN Process");
if (!atw.Save())
{
transaction.Rollback();
throw new Exception("Failed to save audit trail!");
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
return(transaction);
}
/// <summary>
/// Выполнение набора команд в одной транзакции
/// </summary>
/// <param name="commands">Список команд для выполнения</param>
/// <param name="level">Уровень изоляции транзакции</param>
/// <exception cref="System.ArgumentNullException"></exception>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="System.InvalidOperationException"></exception>
/// <exception cref="System.Data.SqlClient.SqlException"></exception>
/// <exception cref="System.Exception"></exception>
public void ExecuteTransaction(List <SqlCommand> commands, System.Data.IsolationLevel level = System.Data.IsolationLevel.RepeatableRead)
{
if (commands == null)
{
throw new ArgumentNullException("commands");
}
System.Data.SqlClient.SqlTransaction transaction = null;
try
{
connection.Open();
// Start a local transaction.
transaction = connection.BeginTransaction(level);
foreach (SqlCommand command in commands)
{
//command.Transaction = transaction;
ExecuteTransaction(command, transaction);
}
// Attempt to commit the transaction.
// connection.Close();
transaction.Commit();
}
catch (SqlException ex)
{
// Attempt to roll back the transaction.
transaction.Rollback();
throw ex;
}
finally
{
connection.Close();
}
}
public System.Data.SqlClient.SqlTransaction SaveGIN(Guid truckId, System.Data.SqlClient.SqlTransaction transaction)
{
IGINProcess originalGIN = new GINProcessBLL(currentGINProcess.GINProcessInformation.TransactionId);
currentGINProcess.SaveGIN(truckId, transaction);
//ConfirmNoConcurrency(GIN)
var ginChanges = (from oGIN in originalGIN.GINProcessInformation.Trucks
join cGIN in currentGINProcess.GINProcessInformation.Trucks
on oGIN.TruckId equals cGIN.TruckId
where oGIN.TruckId == truckId
select new object[] { oGIN, cGIN, AuditTrailWrapper.ExistingRecord });
try
{
AuditTrailWrapper atw = new AuditTrailWrapper(AuditTrailWrapper.TruckLoading, ginChanges.ToArray(), "GIN Process");
if (!atw.Save())
{
transaction.Rollback();
throw new Exception("Failed to save audit trail!");
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
return(transaction);
}
/// <summary>
/// 更新多条数据
/// </summary>
public void Update(List <Hi.Model.BD_DisPrice> l, System.Data.SqlClient.SqlTransaction Tran)
{
foreach (Hi.Model.BD_DisPrice model in l)
{
dal.Update(model, Tran);
}
}
public int Insert(System.Data.SqlClient.SqlConnection sqlConnection, System.Data.SqlClient.SqlTransaction sqlTran)
{
System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand("Insert into ASBLOB(FileContent,FileName,FileLength,FileType,FileExtName,AddUserNo,AddDate,IPAddress) Values(@FileContent,@FileName,@FileLength,@FileType,@FileExtName,@AddUserNo,@AddDate,@IPAddress);", sqlConnection);
sqlcmd.Transaction = sqlTran;
List <SqlParameter> _p = new List <SqlParameter>();
_p.Add(new SqlParameter("@FileContent", this.FileContent));
_p.Add(new SqlParameter("@FileName", this.FileName));
_p.Add(new SqlParameter("@FileLength", this.FileLength));
_p.Add(new SqlParameter("@FileType", this.FileType));
_p.Add(new SqlParameter("@FileExtName", this.FileExtension));
_p.Add(new SqlParameter("@AddUserNo", this.AddUser));
_p.Add(new SqlParameter("@AddDate", this.AddDate));
_p.Add(new SqlParameter("@IPAddress", this.IPAddress));
sqlcmd.Parameters.AddRange(_p.ToArray());
try
{
sqlcmd.ExecuteNonQuery();
sqlcmd.CommandText = "select @@IDENTITY";
int blobId = Convert.ToInt32(sqlcmd.ExecuteScalar());
return(blobId);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 金额变更
/// </summary>
/// <param name="CustomerId">客户标识</param>
/// <param name="AmountSourceId">来源</param>
/// <param name="VipId">会员标识</param>
/// <param name="Amount">金额</param>
/// <param name="ObjectId">对象来源</param>
/// <param name="remark">说明</param>
/// <param name="InOut">添加还是消费：In 或者 Out</param>
/// <param name="strError">错误输出</param>
/// <param name="tran">批处理</param>
/// <returns></returns>
public bool SetVipAmountChange(string CustomerId
, int AmountSourceId
, string VipId
, decimal Amount
, string ObjectId
, string Remark
, string InOut
, out string strError
, System.Data.SqlClient.SqlTransaction tran = null
)
{
try
{
strError = "Success!";
bool bReturn = true;
Loggers.Debug(new DebugLogInfo()
{
Message = string.Format("soureId: {0}, customerId: {1}, vipId: {2}, objectId: {3}, point: {4}", AmountSourceId, CustomerId, VipId, ObjectId, Amount)
});
#region
if (VipId == null || VipId.Equals(""))
{
strError = "没有会员标识.";
return(false);
}
if (Amount.Equals(""))
{
strError = "没有金额.";
return(false);
}
if (InOut == null || InOut.Equals(""))
{
strError = "没有说明是累加还是支出.";
return(false);
}
#endregion
#region
string result = "0";
result = this._currentDAO.SetVipAmountChange(CustomerId, AmountSourceId, VipId, Amount, ObjectId, Remark, tran, InOut, out strError) ?? "0";
if (result.Equals("0"))
{
return(bReturn);
}
else
{
return(false);
}
#endregion
}
catch (Exception ex)
{
strError = ex.ToString();
return(false);
}
}
public int getModelMapping(Int64 TractorNo, SqlTransaction objTrans)
{
int ModelMappingID = 0;
DataAccessLayer objDataLayer = new DataAccessLayer();
SqlCommand objCmd = new SqlCommand();
try
{
objCmd.CommandText = "usp_getModelMappingID";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.AddWithValue("@TRACTOR_NO", TractorNo);
objCmd.Parameters.AddWithValue("@ModelMappingID", SqlDbType.Int);
objCmd.Parameters["@ModelMappingID"].Direction = ParameterDirection.Output;
objCmd.Transaction = objTrans;
objCmd.Connection = objTrans.Connection;
objDataLayer.Command = objCmd;
objDataLayer.ExecQuery();
ModelMappingID = Convert.ToInt32(objCmd.Parameters["@ModelMappingID"].Value);
}
catch (Exception ex)
{
throw ex;
}
return ModelMappingID;
}
/// <summary>
/// ²éÕÒ²»´æÔÚµÄͼƬ²¢É¾³ýÒÑɾ³ýµÄͼƬ¼°Êý¾Ý
/// </summary>
public void DeleteList(SqlConnection conn, SqlTransaction trans, List<Model.article_albums> models, int article_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (Model.article_albums modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string id_list = Utils.DelLastChar(idList.ToString(), ",");
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,thumb_path,original_path from " + databaseprefix + "article_albums where article_id=" + article_id);
if (!string.IsNullOrEmpty(id_list))
{
strSql.Append(" and id not in(" + id_list + ")");
}
DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
int rows = DbHelperSQL.ExecuteSql(conn, trans, "delete from " + databaseprefix + "article_albums where id=" + dr["id"].ToString()); //ɾ³ýÊý¾Ý¿â
if (rows > 0)
{
Utils.DeleteFile(dr["thumb_path"].ToString()); //ɾ³ýËõÂÔͼ
Utils.DeleteFile(dr["original_path"].ToString()); //ɾ³ýÔͼ
}
}
}
/// <summary>
/// Delete values from TitleLanguage by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="titleLanguageID"></param>
/// <returns>true if successful otherwise false.</returns>
public bool TitleLanguageDeleteAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int titleLanguageID)
{
return TitleLanguageDeleteAuto( sqlConnection, sqlTransaction, "BHL", titleLanguageID );
}
internal static void ExecuteNonQueryProcedure(SqlConnection connection, SqlTransaction transaction, string procName, Dictionary<string, object> parameters)
{
using (var command = PrepareCommandForProcedure(connection, transaction, procName, parameters))
{
command.ExecuteNonQuery();
}
}
internal static object ExecuteScalarProcedure(SqlConnection connection, SqlTransaction transaction, string procName, Dictionary<string, object> parameters)
{
using (var command = PrepareCommandForProcedure(connection, transaction, procName, parameters))
{
return command.ExecuteScalar();
}
}
/// <summary>
/// 新增機台保養
/// </summary>
/// <param name="model">model</param>
/// <param name="tran">SqlTransaction</param>
/// <returns></returns>
public bool AddMachineMaintainRecord(MachineMaintainRecord model, SqlTransaction tran)
{
try
{
MappingHelper.HandleNullProperty(model);
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into MaintainRecord_mtr(");
strSql.Append("mtr_RecordID,mtr_cMachineID,mtr_iPlanID,mtr_MOMID,mtr_cDescription,mtr_cRemark,mtr_dBeginTime,mtr_dEndTime,mtr_cStatus,mtr_lDeleted,mtr_dAddDate,mtr_dLastDate,mtr_lSync)");
strSql.Append(" values (");
strSql.Append("@mtr_RecordID,@mtr_cMachineID,@mtr_iPlanID,@mtr_MOMID,@mtr_cDescription,@mtr_cRemark,@mtr_dBeginTime,@mtr_dEndTime,@mtr_cStatus,@mtr_lDeleted,@mtr_dAddDate,@mtr_dLastDate,@mtr_lSync)");
SqlParameter[] parameters = {
DbHelperSQL.MakeInParam("@mtr_RecordID", SqlDbType.UniqueIdentifier,16, model.RecordID),
DbHelperSQL.MakeInParam("@mtr_cMachineID", SqlDbType.NVarChar,20, model.mtr_cMachineID),
DbHelperSQL.MakeInParam("@mtr_iPlanID", SqlDbType.Int,4, model.mtr_iPlanID),
DbHelperSQL.MakeInParam("@mtr_MOMID", SqlDbType.Int,4, model.mtr_MOMID),
DbHelperSQL.MakeInParam("@mtr_cDescription", SqlDbType.NVarChar,200, model.mtr_cDescription),
DbHelperSQL.MakeInParam("@mtr_cRemark", SqlDbType.NVarChar,500, model.mtr_cRemark),
DbHelperSQL.MakeInParam("@mtr_dBeginTime", SqlDbType.DateTime, 0, model.mtr_dBeginTime),
DbHelperSQL.MakeInParam("@mtr_dEndTime", SqlDbType.DateTime, 0, model.mtr_dEndTime),
DbHelperSQL.MakeInParam("@mtr_cStatus", SqlDbType.Int,4, model.mtr_cStatus),
DbHelperSQL.MakeInParam("@mtr_lDeleted", SqlDbType.Bit,1, 0),//model.mtr_lDeleted;
DbHelperSQL.MakeInParam("@mtr_dAddDate", SqlDbType.DateTime, 0, model.AddDate),
DbHelperSQL.MakeInParam("@mtr_dLastDate", SqlDbType.DateTime, 0, model.LastDate),
DbHelperSQL.MakeInParam("@mtr_lSync", SqlDbType.Bit,1, 0)};//model.mtr_lSync;
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, strSql.ToString(), parameters);
return true;
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
conn.ConnectionString = this.GetConnectString();
command.Connection = conn;
command.CommandType = System.Data.CommandType.Text;
try
{
conn.Open();
}
catch (SqlException ex)
{
this.Err = "数据库连接失败!" + ex.Message;
this.ErrCode = "-1";
this.WriteErr();
throw ex;
}
try
{
trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
command.Transaction = trans;
}
catch (SqlException ex)
{
this.Err = "数据库连接失败!" + ex.Message;
this.ErrCode = "-1";
this.WriteErr();
throw ex;
}
}
/// <summary>
/// Delete values from Item by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="itemID"></param>
/// <returns>true if successful otherwise false.</returns>
public bool ItemDeleteAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
int itemID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings(connectionKeyName), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("ItemDeleteAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("ItemID", SqlDbType.Int, null, false, itemID),
CustomSqlHelper.CreateReturnValueParameter("ReturnCode", SqlDbType.Int, null, false)))
{
int returnCode = CustomSqlHelper.ExecuteNonQuery(command, "ReturnCode");
if (transaction == null)
{
CustomSqlHelper.CloseConnection(connection);
}
if (returnCode == 0)
{
return true;
}
else
{
return false;
}
}
}
public bool Guardar(System.Data.SqlClient.SqlTransaction transaccion) ////
{
this.LineaTransporteID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "LineaTransporteINS", Utiles.BaseDatos.StrToSql(this.LineaEmpresaSITT),
Utiles.BaseDatos.StrToSql(this.LineaCodigoSITT), Utiles.BaseDatos.StrToSql(this.LineaTransporteDescrip), this.UnidadNegocioID));
return(true);
}