public static DataTable ConsultaClientePortatil(int Cliente) { SqlCommand cmd = SQLLayer.conexion.CreateCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spQJAConsultaClientePortatil"; cmd.Parameters.Clear(); if (Cliente > 0) { cmd.Parameters.Add("@Cliente", SqlDbType.Int).Value = Cliente; da.SelectCommand = cmd; try { da.Fill(dt); SQLLayer.CierraConexion(); return(dt); } catch (SqlException ex) { SQLLayer.CierraConexion(); foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } return(null); } } else { return(null); } }
public static DataTable ConsultaMotivoQuejaArea(short area, short clasificacion) { SqlCommand cmd = SQLLayer.conexion.CreateCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spQJAConsultaMotivoQuejaArea"; cmd.Parameters.Clear(); cmd.Parameters.Add("@Area", SqlDbType.TinyInt).Value = area; cmd.Parameters.Add("@Clasificacion", SqlDbType.SmallInt).Value = clasificacion; da.SelectCommand = cmd; try { da.Fill(dt); SQLLayer.CierraConexion(); return(dt); } catch (SqlException ex) { SQLLayer.CierraConexion(); foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } return(null); } }
public static void TerminaConsulta(bool abrirConexion, bool cerrarConexion) { SqlCommand cmd = new SqlCommand("set transaction isolation level read committed", SQLLayer.conexion); try { if (abrirConexion) { SQLLayer.AbreConexion(); } if (conexion.State == ConnectionState.Open) { cmd.ExecuteNonQuery(); } } catch (SqlException ex) { throw ex; } finally { if (cerrarConexion) { SQLLayer.CierraConexion(); } } }
//Texis 02/06/2015. Se agrego este metodo para obtener informacion de la nueva tabla llamada ClaseQueja y asi poder llenar elc ombo con las opciones pertinentes public static DataTable ObtieneClasesQueja() { SqlCommand cmd = SQLLayer.conexion.CreateCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spQJAObtieneClasesQueja"; cmd.Parameters.Clear(); da.SelectCommand = cmd; try { da.Fill(dt); SQLLayer.CierraConexion(); return(dt); } catch (SqlException ex) { SQLLayer.CierraConexion(); foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } return(null); } }
public static DataTable ConsultaArea(short Area, short AreaDependencia) { SqlCommand cmd = SQLLayer.conexion.CreateCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spQJAConsultaArea"; cmd.Parameters.Clear(); if (Area == 0) { cmd.Parameters.Add("@Area", SqlDbType.TinyInt).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@Area", SqlDbType.TinyInt).Value = Area; } if (AreaDependencia == 0) { cmd.Parameters.Add("@AreaDependencia", SqlDbType.TinyInt).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@AreaDependencia", SqlDbType.TinyInt).Value = AreaDependencia; } da.SelectCommand = cmd; try { da.Fill(dt); SQLLayer.CierraConexion(); return(dt); } catch (SqlException ex) { SQLLayer.CierraConexion(); foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } return(null); } }
public static DataTable CargaCombo(string IdCombo) { SqlDataAdapter da = new SqlDataAdapter(); switch (IdCombo) { case "Area": da = new SqlDataAdapter("SELECT Area, Descripcion FROM Area WHERE Area > 0 AND AreaDependencia IS NOT NULL", conexion); break; case "GravedadQueja": da = new SqlDataAdapter("SELECT GravedadQueja, Descripcion FROM GravedadQueja", conexion); break; case "Celula": if (int.Parse(QuejasLibrary.Public.Global.Parametros.ValorParametro("CelulasUsuario")) == 0) { da = new SqlDataAdapter("SELECT Celula, UPPER(Descripcion) AS Descripcion FROM Celula WHERE Comercial = 1 AND CelulaAdmin = 0", conexion); } else { da = new SqlDataAdapter("spSEGUsuarioCelulaConsulta @Usuario = " + QuejasLibrary.Public.Global.Usuario.IdUsuario, conexion); } break; case "Ruta": da = new SqlDataAdapter("SELECT Ruta, UPPER(Descripcion) AS Descripcion FROM Ruta", conexion); break; } DataTable dt = new DataTable(); try { da.Fill(dt); SQLLayer.CierraConexion(); } catch (SqlException ex) { foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } } return(dt); }
public static void CancelarTrasaccion(SqlTransaction Trasaccion) { try { Trasaccion.Rollback(); } catch (SqlException ex) { SQLLayer.CierraConexion(); foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level " + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } } finally { SQLLayer.CierraConexion(); } }
public static SqlTransaction IniciaTrasaccion() { try { SQLLayer.AbreConexion(); sqlTransaccion = conexion.BeginTransaction(); //return sqlTransaccion; } catch (SqlException ex) { SQLLayer.CierraConexion(); foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level " + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } sqlTransaccion = null; } // finally // { return(sqlTransaccion); // } }
public static DataTable ExisteQueja(int Cliente, string PersonaQueja, string Telefono, bool EsClientePortatil) { SqlCommand cmd = SQLLayer.conexion.CreateCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spQJAExisteQueja"; cmd.Parameters.Clear(); if (EsClientePortatil == false) { if (Cliente == 0) { cmd.Parameters.Add("@Cliente", SqlDbType.Int).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@Cliente", SqlDbType.Int).Value = Cliente; } } else { if (Cliente == 0) { cmd.Parameters.Add("@ClientePortatil", SqlDbType.Int).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@ClientePortatil", SqlDbType.Int).Value = Cliente; } } if (PersonaQueja == string.Empty) { cmd.Parameters.Add("@PersonaQueja", SqlDbType.VarChar).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@PersonaQueja", SqlDbType.VarChar).Value = PersonaQueja; } if (Telefono == string.Empty) { cmd.Parameters.Add("@Telefono", SqlDbType.VarChar).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@Telefono", SqlDbType.VarChar).Value = Telefono; } da.SelectCommand = cmd; try { da.Fill(dt); SQLLayer.CierraConexion(); return(dt); } catch (SqlException ex) { SQLLayer.CierraConexion(); foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } return(null); } }
public static DataTable ConsultaQueja(short Area, short AreaDependencia, short SinArea, string Status, string NumeroQueja, DateTime FIniQueja, DateTime FFinQueja, short Celula, short Ruta, short GravedadQueja, short Tipo) { SqlCommand cmd = SQLLayer.conexion.CreateCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spQJAConsultaQueja"; cmd.Parameters.Clear(); cmd.Parameters.Add("@AREA", SqlDbType.TinyInt).Value = Area; cmd.Parameters.Add("@AREADEPENDENCIA", SqlDbType.TinyInt).Value = AreaDependencia; cmd.Parameters.Add("@SINAREA", SqlDbType.TinyInt).Value = SinArea; if (Status == string.Empty) { cmd.Parameters.Add("@STATUS", SqlDbType.VarChar).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@STATUS", SqlDbType.VarChar).Value = Status; } if (NumeroQueja == string.Empty) { cmd.Parameters.Add("@NUMEROQUEJA", SqlDbType.VarChar).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@NUMEROQUEJA", SqlDbType.VarChar).Value = NumeroQueja; } cmd.Parameters.Add("@FECHAINIQUEJA", SqlDbType.DateTime).Value = FIniQueja; cmd.Parameters.Add("@FECHAFINQUEJA", SqlDbType.DateTime).Value = FFinQueja; if (Celula == -1) { cmd.Parameters.Add("@CELULA", SqlDbType.TinyInt).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@CELULA", SqlDbType.TinyInt).Value = Celula; } if (Ruta == -1) { cmd.Parameters.Add("@RUTA", SqlDbType.SmallInt).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@RUTA", SqlDbType.SmallInt).Value = Ruta; } if (GravedadQueja == -1) { cmd.Parameters.Add("@GravedadQueja", SqlDbType.TinyInt).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@GravedadQueja", SqlDbType.TinyInt).Value = GravedadQueja; } if (Tipo == 0) { cmd.Parameters.Add("@tipocliente", SqlDbType.TinyInt).Value = System.DBNull.Value; } else { cmd.Parameters.Add("@tipocliente", SqlDbType.TinyInt).Value = Tipo; } da.SelectCommand = cmd; try { da.Fill(dt); SQLLayer.CierraConexion(); return(dt); } catch (SqlException ex) { SQLLayer.CierraConexion(); foreach (SqlError er in ex.Errors) { MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information); } return(null); } }