//Lista de clientes por ID public List <Customers> obtenerClientePorID(string id) { if (conexion.Conectar() == "True") { List <Customers> listaCustomer = new List <Customers>(); Customers customer; using (SqlCommand comando = new SqlCommand("SELECT CustomerID, CompanyName, ContactName, City, Phone FROM Customers WHERE CustomerID LIKE '%" + id + "%' AND b_logiv = 0", conexion.getConn())) { using (IDataReader reader = comando.ExecuteReader()) { while (reader.Read()) { customer = new Customers(); customer.CustomerID = reader.IsDBNull(0) ? "" : reader.GetValue(0).ToString(); customer.CompanyName = reader.IsDBNull(1) ? "" : reader.GetValue(1).ToString(); customer.ContactName = reader.IsDBNull(2) ? "" : reader.GetValue(2).ToString(); customer.City = reader.IsDBNull(3) ? "" : reader.GetValue(3).ToString(); customer.Phone = reader.IsDBNull(4) ? "" : reader.GetValue(4).ToString(); listaCustomer.Add(customer); } } } return(listaCustomer); } else { return(null); } }
public string AgregarAnio(string DESCRIPCION) { cn = cnx.Conectar(); string mensaje = string.Empty; SqlTransaction tr = null; SqlCommand cmd = new SqlCommand("USP_ANIO_INSERTAR", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@DESCRIPCION", DESCRIPCION); int q = 0; try { cn.Open(); tr = cn.BeginTransaction(IsolationLevel.Serializable); cmd.Transaction = tr; q = cmd.ExecuteNonQuery(); tr.Commit(); mensaje = "Registro creado"; } catch (Exception ex) { tr.Rollback(); mensaje = ex.Message; } finally { cn.Close(); } return(mensaje); }
public string RegistrarAlumno(string dni, string nombre, string apepat, string apetmat, byte[] foto) { cn = cnx.Conectar(); string mensaje = string.Empty; SqlTransaction tr = null; SqlCommand cmd = new SqlCommand("sp_registraralumno", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@dni", dni); cmd.Parameters.AddWithValue("@nombre", nombre); cmd.Parameters.AddWithValue("@apepat", apepat); cmd.Parameters.AddWithValue("@apetmat", apetmat); cmd.Parameters.AddWithValue("@foto", foto); int q = 0; try { cn.Open(); tr = cn.BeginTransaction(IsolationLevel.Serializable); cmd.Transaction = tr; q = cmd.ExecuteNonQuery(); tr.Commit(); mensaje = "Registro creado"; } catch (Exception ex) { tr.Rollback(); mensaje = ex.Message; } finally { cn.Close(); } return(mensaje); }
//Consultar Pedidos public List <Orders> consultarPedidos() { List <Orders> listaPedidos = new List <Orders>(); Orders pedido; if (conexion.Conectar() == "True") { StringBuilder builder = new StringBuilder(); builder.Append(" SELECT TOP(50) od.OrderID, od.OrderDate, od.RequiredDate, od.ShippedDate, od.Freight, c.CompanyName "); builder.Append(" FROM Orders od, Customers c WHERE estado = 0 AND od.CustomerID = c.CustomerID ORDER BY OrderID DESC"); using (SqlCommand comando = new SqlCommand(builder.ToString(), conexion.getConn())) { using (IDataReader reader = comando.ExecuteReader()) { while (reader.Read()) { pedido = new Orders(); pedido.OrderID = int.Parse(reader.IsDBNull(0) ? "" : reader.GetValue(0).ToString()); pedido.OrderDate = reader.IsDBNull(1) ? new DateTime() : DateTime.Parse(reader.GetValue(1).ToString()); pedido.RequiredDate = reader.IsDBNull(2) ? new DateTime() : DateTime.Parse(reader.GetValue(2).ToString()); pedido.ShippedDate = reader.IsDBNull(3) ? new DateTime() : DateTime.Parse(reader.GetValue(3).ToString()); pedido.Freight = decimal.Parse(reader.IsDBNull(4) ? "0" : reader.GetValue(4).ToString()); pedido.CustomerCompany = reader.IsDBNull(5) ? "" : reader.GetValue(5).ToString(); listaPedidos.Add(pedido); } } } foreach (Orders order in listaPedidos) { StringBuilder builder2 = new StringBuilder(); builder2.Append(" SELECT od.OrderID, od.ProductID, od.UnitPrice, od.Quantity, p.ProductName FROM[Order Details] od, Products p "); builder2.Append(" WHERE od.OrderID = @id AND od.ProductID = p.ProductID "); using (SqlCommand comando2 = new SqlCommand(builder2.ToString(), conexion.getConn())) { Order_Details lineas; order.Order_Details = new List <Order_Details>(); comando2.Parameters.AddWithValue("@id", order.OrderID); using (IDataReader reader2 = comando2.ExecuteReader()) { while (reader2.Read()) { lineas = new Order_Details(); lineas.OrderID = int.Parse(reader2.IsDBNull(0) ? "" : reader2.GetValue(0).ToString()); lineas.ProductID = int.Parse(reader2.IsDBNull(1) ? "" : reader2.GetValue(1).ToString()); lineas.UnitPrice = decimal.Parse(reader2.IsDBNull(2) ? "": reader2.GetValue(2).ToString()); lineas.Quantity = short.Parse(reader2.IsDBNull(3) ? "" : reader2.GetValue(3).ToString()); lineas.ProductName = reader2.IsDBNull(4) ? "" : reader2.GetValue(4).ToString(); order.Order_Details.Add(lineas); } } } } } return(listaPedidos); }
//----------------------------------------------------------------------------- //LISTADO public DataTable Listar() { cn = cnx.Conectar(); DataTable dtb = new DataTable(); SqlDataAdapter dap = new SqlDataAdapter ("USP_CARPETA_LISTAR", cn); dap.SelectCommand.CommandType = CommandType.StoredProcedure; dap.Fill(dtb); return(dtb); }
public string IngresarSujetoProcesal(string NOMBRES) { cn = cnx.Conectar(); string mensaje = string.Empty; SqlTransaction tr = null; SqlCommand cmd = new SqlCommand("USP_SUJETOPROCESAL_INGRESAR", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@NOMBRES", NOMBRES); int q = 0; try { cn.Open(); tr = cn.BeginTransaction(IsolationLevel.Serializable); cmd.Transaction = tr; q = cmd.ExecuteNonQuery(); tr.Commit(); mensaje = "Sujeto Procesal Ingresado"; } catch (Exception ex) { tr.Rollback(); mensaje = ex.Message; } finally { cn.Close(); } return(mensaje); }
/*@NOMBRE VARCHAR (50) * ,@SIGLAS VARCHAR(9) * ,@CELULAR VARCHAR(9) * ,@EMAIL VARCHAR(50) * ,@CASILLAELECTRONICA VARCHAR(50) * ,@DISTRITOFISCAL VARCHAR(50) * ,@FISCALIA VARCHAR(50) * ,@MODULO VARCHAR(50) * ,@DESPACHO VARCHAR(50) * ,@ABREVIATURA VARCHAR(50) * ,@DIRECCIONPRO VARCHAR(300)*/ public string ActualizarFiscal( string NOMBRE , string SIGLAS , string CELULAR , string EMAIL , string CASILLAELECTRONICA , string DISTRITOFISCAL , string FISCALIA , string MODULO , string DESPACHO , string ABREVIATURA , string DIRECCIONPRO ) { cn = cnx.Conectar(); string mensaje = string.Empty; SqlTransaction tr = null; SqlCommand cmd = new SqlCommand("USP_FISCAL_ACTUALIZAR", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@NOMBRE", NOMBRE); cmd.Parameters.AddWithValue("@SIGLAS", SIGLAS); cmd.Parameters.AddWithValue("@CELULAR", CELULAR); cmd.Parameters.AddWithValue("@EMAIL", EMAIL); cmd.Parameters.AddWithValue("@CASILLAELECTRONICA", CASILLAELECTRONICA); cmd.Parameters.AddWithValue("@DISTRITOFISCAL", DISTRITOFISCAL); cmd.Parameters.AddWithValue("@FISCALIA", FISCALIA); cmd.Parameters.AddWithValue("@MODULO", MODULO); cmd.Parameters.AddWithValue("@DESPACHO", DESPACHO); cmd.Parameters.AddWithValue("@ABREVIATURA", ABREVIATURA); cmd.Parameters.AddWithValue("@DIRECCIONPRO", DIRECCIONPRO); int q = 0; try { cn.Open(); tr = cn.BeginTransaction(IsolationLevel.Serializable); cmd.Transaction = tr; q = cmd.ExecuteNonQuery(); tr.Commit(); mensaje = "Datos actualizados"; } catch (Exception ex) { tr.Rollback(); mensaje = ex.Message; } finally { cn.Close(); } return(mensaje); }
//Metodos public string Codigo_Boleta() { cn = cnx.Conectar(); string correlativo = string.Empty; SqlCommand cmd = new SqlCommand ("USP_CODIGO_BOLETA", cn); cmd.CommandType = CommandType.StoredProcedure; try { cn.Open(); correlativo = cmd.ExecuteScalar().ToString(); } finally { cn.Close(); } return(correlativo); }
//Metodos public string CodigoRequerimiento(string IDCARPETA) { cn = cnx.Conectar(); string codigo = string.Empty; SqlCommand cmd = new SqlCommand ("USP_REQUERIMIENTO_CODIGO", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@IDCARPETA", IDCARPETA); try { cn.Open(); codigo = cmd.ExecuteScalar().ToString(); } finally { cn.Close(); } return(codigo); }
//Metodos public string CodigoDisposicion(string IDCARPETA) { cn = cnx.Conectar(); string codigo = string.Empty; SqlCommand cmd = new SqlCommand ("USP_DISPOSICION_CODIGO", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@IDCARPETA", IDCARPETA); try { cn.Open(); codigo = cmd.ExecuteScalar().ToString(); } finally { cn.Close(); } return(codigo); }