Ejemplo n.º 1
0
        public static List <Product> ExampleProductsList(List <string> pCategoryList)
        {
            _methodName = nameof(ExampleProductsList);

            SqlWhereInParameterBuilder.OnShowCommandStatementEvent += SqlWhereInParameterBuilder_OnShowCommandStatementEvent;
            var products = new List <Product>();

            // field which the WHERE IN will use
            const string parameterPrefix = "C.CategoryName";

            // Base SELECT Statement
            var selectStatement =
                "SELECT  P.CategoryID, C.CategoryName, P.ProductID, P.ProductName, P.QuantityPerUnit " +
                "FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID " +
                $"WHERE {parameterPrefix} IN ({{0}}) ORDER BY P.ProductName";

            var commandText = SqlWhereInParameterBuilder.BuildWhereInClause(selectStatement, parameterPrefix, pCategoryList);

            using (var cn = new SqlConnection {
                ConnectionString = _connectionString
            })
            {
                using (var cmd = new SqlCommand {
                    Connection = cn
                })
                {
                    cmd.CommandText = commandText;
                    cmd.AddParamsToCommand(parameterPrefix, pCategoryList);

                    try
                    {
                        cn.Open();

                        var reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            products.Add(new Product()
                            {
                                CategoryID      = reader.GetInt32(0),
                                CategoryName    = reader.GetString(1),
                                ProductID       = reader.GetInt32(2),
                                ProductName     = reader.GetString(3),
                                QuantityPerUnit = reader.GetString(4)
                            });
                        }
                    }
                    catch (Exception ex)
                    {
                        OnExceptionEvent?.Invoke(ex);
                    }
                    finally
                    {
                        SqlWhereInParameterBuilder.OnShowCommandStatementEvent -= SqlWhereInParameterBuilder_OnShowCommandStatementEvent;
                    }
                }
            }

            return(products);
        }
Ejemplo n.º 2
0
        public static DataTable ExampleCustomersDataTable1(List <string> pContactTitleList)
        {
            _methodName = nameof(ExampleCustomersDataTable1);

            SqlWhereInParameterBuilder.OnShowCommandStatementEvent += SqlWhereInParameterBuilder_OnShowCommandStatementEvent;

            var customersDataTable = new DataTable();

            // field which the WHERE IN will use
            const string parameterPrefix = "CT.ContactTitle";

            // Base SELECT Statement
            var selectStatement =
                "SELECT C.CustomerIdentifier , C.CompanyName , C.ContactName , C.ContactTypeIdentifier , " +
                "FORMAT(C.ModifiedDate, 'MM-dd-yyyy', 'en-US') AS ModifiedDate, CT.ContactTitle " +
                "FROM dbo.Customers AS C INNER JOIN dbo.ContactType AS CT ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier " +
                $"WHERE {parameterPrefix} IN ({{0}}) ORDER BY C.CompanyName";

            // Builds the SELECT statement minus values
            var commandText = SqlWhereInParameterBuilder.BuildWhereInClause(selectStatement, parameterPrefix, pContactTitleList);

            using (var cn = new SqlConnection {
                ConnectionString = _connectionString
            })
            {
                using (var cmd = new SqlCommand {
                    Connection = cn
                })
                {
                    cmd.CommandText = commandText;

                    //
                    // Add values for command parameters
                    //
                    cmd.AddParamsToCommand(parameterPrefix, pContactTitleList);


                    try
                    {
                        cn.Open();
                        customersDataTable.Load(cmd.ExecuteReader());

                        if (DisplayFormattedSql)
                        {
                            Console.WriteLine(Scripter.Format(cmd.CommandText));
                        }
                    }
                    catch (Exception ex)
                    {
                        OnExceptionEvent?.Invoke(ex);
                    }
                    {
                        SqlWhereInParameterBuilder.OnShowCommandStatementEvent -= SqlWhereInParameterBuilder_OnShowCommandStatementEvent;
                    }
                }
            }

            return(customersDataTable);
        }
Ejemplo n.º 3
0
        public static List <Orders> ExampleOrderDates(List <string> pDatesList)
        {
            _methodName = nameof(ExampleOrderDates);

            SqlWhereInParameterBuilder.OnShowCommandStatementEvent += SqlWhereInParameterBuilder_OnShowCommandStatementEvent;

            var orders = new List <Orders>();

            const string parameterPrefix = "O.OrderDate";

            var selectStatement =
                "SELECT O.OrderID, O.CustomerIdentifier, O.OrderDate, O.RequiredDate, O.ShipCity, O.ShipPostalCode  " +
                "FROM Orders AS O  " +
                $"WHERE {parameterPrefix} IN ({{0}})";

            var commandText = SqlWhereInParameterBuilder.BuildWhereInClause(selectStatement, parameterPrefix, pDatesList);

            using (var cn = new SqlConnection {
                ConnectionString = _connectionString
            })
            {
                using (var cmd = new SqlCommand {
                    Connection = cn
                })
                {
                    cmd.CommandText = commandText;
                    cmd.AddParamsToCommand(parameterPrefix, pDatesList);


                    try
                    {
                        cn.Open();

                        var reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            orders.Add(new Orders()
                            {
                                OrderID = reader.GetInt32(0), OrderDate = reader.GetNullableDateTime("OrderDate")
                            });
                        }

                        if (DisplayFormattedSql)
                        {
                            OnShowFormattedStatementEvent?.Invoke(Scripter.Format(cmd.CommandText));
                        }
                    }
                    catch (Exception ex)
                    {
                        OnExceptionEvent?.Invoke(ex);
                    }
                    finally
                    {
                        SqlWhereInParameterBuilder.OnShowCommandStatementEvent -= SqlWhereInParameterBuilder_OnShowCommandStatementEvent;
                    }
                }
            }


            return(orders);
        }
Ejemplo n.º 4
0
        public static List <Customer> ExampleCustomersList1(List <string> pContactTitleList)
        {
            _methodName = nameof(ExampleCustomersList1);

            SqlWhereInParameterBuilder.OnShowCommandStatementEvent += SqlWhereInParameterBuilder_OnShowCommandStatementEvent;

            var customers = new List <Customer>();

            // field which the WHERE IN will use
            const string parameterPrefix = "CT.ContactTitle";

            // Base SELECT Statement
            var selectStatement =
                "SELECT C.CustomerIdentifier , C.CompanyName , C.ContactName , C.ContactTypeIdentifier , " +
                "FORMAT(C.ModifiedDate, 'MM-dd-yyyy', 'en-US') AS ModifiedDate, CT.ContactTitle " +
                "FROM dbo.Customers AS C INNER JOIN dbo.ContactType AS CT ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier " +
                $"WHERE {parameterPrefix} IN ({{0}}) ORDER BY C.CompanyName";

            var commandText = SqlWhereInParameterBuilder.BuildWhereInClause(selectStatement, parameterPrefix, pContactTitleList);

            using (var cn = new SqlConnection {
                ConnectionString = _connectionString
            })
            {
                using (var cmd = new SqlCommand {
                    Connection = cn
                })
                {
                    cmd.CommandText = commandText;
                    cmd.AddParamsToCommand(parameterPrefix, pContactTitleList);

                    try
                    {
                        cn.Open();

                        var reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            customers.Add(new Customer()
                            {
                                CustomerIdentifier    = reader.GetInt32(0),
                                CompanyName           = reader.GetString(1),
                                ContactName           = reader.GetString(2),
                                ContactTypeIdentifier = reader.GetInt32(3),
                                ModifiedDate          = reader.GetString(4),
                                ContactTitle          = reader.GetString(5)
                            });
                        }

                        if (DisplayFormattedSql)
                        {
                            Console.WriteLine(Scripter.Format(cmd.CommandText));
                        }
                    }
                    catch (Exception ex)
                    {
                        OnExceptionEvent?.Invoke(ex);
                    }
                    {
                        SqlWhereInParameterBuilder.OnShowCommandStatementEvent -= SqlWhereInParameterBuilder_OnShowCommandStatementEvent;
                    }
                }
            }

            return(customers);
        }