コード例 #1
0
        static public List <Library.Cheque> FindAdvanced(params Library.Classes.QItem[] args)
        {
            SqlDataReader rdr     = null;
            SqlConnection conexao = null;
            SqlCommand    comando = null;

            try
            {
                conexao = new SqlConnection(global::Connection.Connection.String());

                comando = new SqlCommand();

                string query = "SELECT * FROM Cheque AS cq " +
                               "LEFT JOIN Venda AS v ON cq.idVenda = v.id " +
                               "LEFT JOIN Servico AS s ON cq.idServico = s.id ";
                //"INNER JOIN Venda AS v ON cq.idVenda = v.id " +
                //"INNER JOIN Servico AS s ON cq.idServico = s.id ";

                int    p   = 0;
                string pre = "";
                foreach (Library.Classes.QItem qi in args)
                {
                    if (p == 0)
                    {
                        pre = "WHERE ";
                    }
                    else
                    {
                        pre = "AND ";
                    }

                    p++;

                    switch (qi.Campo)
                    {
                    case "cq.id":
                        query += pre + "cq.id = @id";
                        comando.Parameters.AddWithValue("@id", qi.Objeto);
                        break;

                    case "cq.data varchar":
                        query += pre + "CONVERT(varchar, cq.data, 103) = @data";
                        comando.Parameters.AddWithValue("@data", qi.Objeto);
                        break;

                    case "v.id":
                        query += pre + "v.id = @idVenda";
                        comando.Parameters.AddWithValue("@idVenda", qi.Objeto);
                        break;

                    case "s.id":
                        query += pre + "s.id = @idServico";
                        comando.Parameters.AddWithValue("@idServico", qi.Objeto);
                        break;

                    case "ORDER BY":
                        query += " ORDER BY " + qi.Objeto;
                        break;
                    }
                }

                comando.CommandText = query;

                comando.Connection = conexao;

                conexao.Open();

                rdr = comando.ExecuteReader();

                List <Library.Cheque> cheques = new List <Library.Cheque>();

                while (rdr.Read())
                {
                    Library.Cheque cheque = new Cheque();
                    cheque.Id     = (long)rdr["id"];
                    cheque.Data   = (DateTime)rdr["data"];
                    cheque.Valor  = (decimal)rdr["valor"];
                    cheque.Numero = rdr["numero"].ToString();
                    cheque.Pago   = (int)rdr["pago"];

                    if (!string.IsNullOrEmpty(rdr["idVenda"].ToString()))
                    {
                        cheque.Venda = Library.VendaBD.FindById((long)rdr["idVenda"]);
                    }
                    else
                    {
                        cheque.Venda = null;
                    }

                    cheques.Add(cheque);
                }

                return(cheques);
            }
            catch (Exception ex)
            {
                Library.Diagnostics.Logger.Error(ex);
            }
            finally
            {
                conexao.Close();
                comando.Dispose();
            }
            return(null);
        }