16. Some Industrial Query and NPOI

Task 1: Multiple CTEs (Common Table Expressions)

Scenario: Find the total number of orders per customer and the top 3 customers by order count.

Query:

WITH OrderCountCTE AS (
    SELECT 
        CustomerID, 
        COUNT(OrderID) AS TotalOrders
    FROM 
        Orders
    GROUP BY 
        CustomerID
),
TopCustomersCTE AS (
    SELECT 
        CustomerID, 
        TotalOrders,
        ROW_NUMBER() OVER (ORDER BY TotalOrders DESC) AS RowNum
    FROM 
        OrderCountCTE
)
SELECT 
    c.CustomerName, 
    t.TotalOrders
FROM 
    TopCustomersCTE t
JOIN 
    Customers c ON t.CustomerID = c.CustomerID
WHERE 
    t.RowNum <= 3;

Explanation:

OrderCountCTE: Counts total orders per customer.
TopCustomersCTE: Ranks customers by total orders.
JOIN: Retrieves customer names for the top 3 customers.

Task 2: Unique Values from Multiple Joined Tables

Scenario: We have multiple tables (Products, Suppliers, Categories) and want unique combinations.

Query:

WITH UniqueProductsCTE AS (
    SELECT 
        p.ProductID, 
        p.ProductName, 
        s.SupplierName, 
        c.CategoryName,
        ROW_NUMBER() OVER (PARTITION BY p.ProductName, s.SupplierName ORDER BY p.ProductID) AS rn
    FROM 
        Products p
    JOIN 
        Suppliers s ON p.SupplierID = s.SupplierID
    JOIN 
        Categories c ON p.CategoryID = c.CategoryID
)
SELECT 
    ProductID, 
    ProductName, 
    SupplierName, 
    CategoryName
FROM 
    UniqueProductsCTE
WHERE 
    rn = 1;

Explanation:

ROW_NUMBER() with PARTITION BY: Assigns a rank to each combination of product and supplier.
WHERE rn = 1: Keeps only the first record for each combination, ensuring uniqueness.

Task 3: Create a Simple View

Scenario: Create a view to see all active customers with their recent order details.

Query:

CREATE VIEW vw_ActiveCustomersWithOrders AS
SELECT 
    c.CustomerID, 
    c.CustomerName, 
    o.OrderID, 
    o.OrderDate, 
    od.ProductID, 
    od.Quantity
FROM 
    Customers c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID
JOIN 
    OrderDetails od ON o.OrderID = od.OrderID
WHERE 
    c.Status = 'Active';

Explanation:

View Creation: A reusable query that shows active customers and their order details.
JOIN: Combines customer, order, and order detail information.

Task 4: Use a Variable to Update Another Table

Scenario: Set a product’s category based on a provided category name.

Query:

DECLARE @CategoryID INT;
 
-- Get the category ID for 'Electronics'
SELECT @CategoryID = CategoryID 
FROM Categories 
WHERE CategoryName = 'Electronics';
 
-- Update products to this category
UPDATE Products
SET CategoryID = @CategoryID
WHERE ProductName LIKE 'Smartphone%';

Explanation:

DECLARE: Creates a variable.
SELECT INTO: Assigns a value to the variable.
UPDATE: Uses the variable to update another table.

Task 5: Use `IN` in a Query

Scenario: Find all orders placed by customers from specific countries.

Query:

SELECT 
    OrderID, 
    CustomerID, 
    OrderDate
FROM 
    Orders
WHERE 
    CustomerID IN (
        SELECT CustomerID 
        FROM Customers 
        WHERE Country IN ('USA', 'Canada', 'UK')
    );

Explanation:

IN: Matches customers from the selected countries.
Subquery: Finds all customer IDs from Customers.

Task 6: Use `EXISTS`, `NOT EXISTS`, and `IF NOT EXISTS`

Scenario:

Find all customers who have placed orders (EXISTS).
Find customers with no orders (NOT EXISTS).
Insert a record if it doesn’t already exist (IF NOT EXISTS).

Query:

-- 1. Use EXISTS: Find customers who placed orders
SELECT 
    CustomerID, 
    CustomerName
FROM 
    Customers c
WHERE 
    EXISTS (
        SELECT 1 
        FROM Orders o 
        WHERE o.CustomerID = c.CustomerID
    );
 
-- 2. Use NOT EXISTS: Find customers without orders
SELECT 
    CustomerID, 
    CustomerName
FROM 
    Customers c
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM Orders o 
        WHERE o.CustomerID = c.CustomerID
    );
 
-- 3. Use IF NOT EXISTS: Insert a new product if it doesn't exist
IF NOT EXISTS (
    SELECT 1 
    FROM Products 
    WHERE ProductName = 'New Gadget'
)
BEGIN
    INSERT INTO Products (ProductName, CategoryID, Price)
    VALUES ('New Gadget', 1, 99.99);
END;

Explanation:

EXISTS: Returns records if a subquery finds a match.
NOT EXISTS: Returns records if a subquery finds no matches.
IF NOT EXISTS: Conditional insertion to avoid duplicates.

Task 7: `SELECT` and `INSERT` Together

Definition:
Using INSERT ... SELECT allows We to insert data from one table or query result directly into another table without manually entering values. This is useful for migrating or copying data.

Scenario:
Insert active customers from the Customers table into the CustomerArchive table, including only those with at least one order.

Query:

WITH ActiveCustomersCTE AS (
    SELECT 
        c.CustomerID, 
        c.CustomerName, 
        c.Email, 
        o.OrderDate,
        ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC) AS rn
    FROM 
        Customers c
    JOIN 
        Orders o ON c.CustomerID = o.CustomerID
    WHERE 
        c.Status = 'Active'
)
INSERT INTO CustomerArchive (
    CustomerID, 
    CustomerName, 
    Email, 
    LastOrderDate
)
SELECT 
    CustomerID, 
    CustomerName, 
    Email, 
    OrderDate
FROM 
    ActiveCustomersCTE
WHERE 
    rn = 1;

Explanation:

WITH CTE: Retrieves active customers and their latest order using ROW_NUMBER().
INSERT INTO ... SELECT: Inserts selected customers into the archive table.
Why Use INSERT ... SELECT: Useful for batch operations, migrations, and backups.

Task 8: Reseed Identity Column

Definition:
Reseeding resets the identity column’s auto-increment counter. It’s often used after deleting records to restart numbering.

Scenario:
Reset the identity counter for the Orders table to 100.

Query:

DBCC CHECKIDENT ('Orders', RESEED, 100);

⚠️ Caution:

Permission Issues: We may need ALTER or DBCC permissions.
Data Overwrite Risk: If new records overlap with existing identity numbers, errors may occur.

Task 9: Use `CASE` Statement

Definition:
The CASE statement provides conditional logic, similar to IF-ELSE in programming. It’s useful for categorizing or transforming data.

Scenario:
Classify orders by amount into categories: Low, Medium, or High.

Query:

SELECT 
    OrderID, 
    CustomerID, 
    OrderAmount,
    CASE 
        WHEN OrderAmount < 100 THEN 'Low'
        WHEN OrderAmount BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'High'
    END AS OrderCategory
FROM 
    Orders;

Explanation:

CASE: Evaluates conditions sequentially.
AS OrderCategory: Creates a new column with the result.
Result: Classifies each order based on its amount.

Task 10: SQL Data Read into C#

Scenario: Retrieve employee attendance records from SQL and store them in a C# list object.

string query = @"
    SELECT 
        EmployeeID AS Emp_ID,
        AttendanceDate AS Attendance_Date,
        InTime AS In_Time,
        OutTime AS Out_Time,
        Status AS Attendance_Status
    FROM 
        [HRDB].[dbo].[t_Attendance]
    WHERE 
        AttendanceDate >= DATEADD(DAY, -7, GETDATE()) 
    ORDER BY AttendanceDate DESC";
 
DataTable dt = dbConn.ExecuteQuery(query);
List<AttendanceModel> result = new List<AttendanceModel>();
 
foreach (DataRow row in dt.Rows)
{
    AttendanceModel record = new AttendanceModel
    {
        Emp_ID = row["Emp_ID"] != DBNull.Value ? Convert.ToInt32(row["Emp_ID"]) : 0,
        Attendance_Date = row["Attendance_Date"] != DBNull.Value ? Convert.ToDateTime(row["Attendance_Date"]) : (DateTime?)null,
        In_Time = row["In_Time"]?.ToString(),
        Out_Time = row["Out_Time"]?.ToString(),
        Attendance_Status = row["Attendance_Status"]?.ToString()
    };
    result.Add(record);
}

Explanation:

SELECT query fetches attendance records from the past 7 days.
DataTable is populated from the database query result.
foreach loop maps rows to AttendanceModel objects and adds them to a list.

Task 11: Bulk Excel Upload from C# using NPOI (1 Million Records in < 2 Minutes)

Scenario: Upload Product data from an Excel file to a SQL table.

using (SqlConnection conn = new SqlConnection(ConfigurationReader.CONNECTION_STRING))
{
    conn.Open();
 
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("ProductCode", typeof(string));
    dataTable.Columns.Add("ProductName", typeof(string));
    dataTable.Columns.Add("Category", typeof(string));
    dataTable.Columns.Add("Price", typeof(decimal));
    dataTable.Columns.Add("StockQuantity", typeof(int));
    dataTable.Columns.Add("CreatedDate", typeof(DateTime));
    dataTable.Columns.Add("CreatedBy", typeof(string));
 
    using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = extension == ".xls" ? (IWorkbook)new HSSFWorkbook(stream) : new XSSFWorkbook(stream);
        ISheet sheet = workbook.GetSheetAt(0);
 
        for (int i = 1; i <= sheet.LastRowNum; i++) // Start from 1 to skip header
        {
            IRow row = sheet.GetRow(i);
            if (row != null)
            {
                string productCode = row.GetCell(0)?.ToString().Trim();
                string productName = row.GetCell(1)?.ToString().Trim();
                string category = row.GetCell(2)?.ToString().Trim();
                decimal.TryParse(row.GetCell(3)?.ToString().Trim(), out decimal price);
                int.TryParse(row.GetCell(4)?.ToString().Trim(), out int stockQty);
                DateTime createdDate = DateTime.Now;
                string createdBy = "System";
 
                dataTable.Rows.Add(
                    productCode, 
                    productName, 
                    category, 
                    price, 
                    stockQty, 
                    createdDate, 
                    createdBy
                );
            }
        }
    }
 
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
    {
        bulkCopy.DestinationTableName = "[dbo].[Products]";
        bulkCopy.BatchSize = 10000; // Improves performance
        bulkCopy.BulkCopyTimeout = 60;
 
        bulkCopy.ColumnMappings.Add("ProductCode", "ProductCode");
        bulkCopy.ColumnMappings.Add("ProductName", "ProductName");
        bulkCopy.ColumnMappings.Add("Category", "Category");
        bulkCopy.ColumnMappings.Add("Price", "Price");
        bulkCopy.ColumnMappings.Add("StockQuantity", "StockQuantity");
        bulkCopy.ColumnMappings.Add("CreatedDate", "CreatedDate");
        bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
 
        bulkCopy.WriteToServer(dataTable);
    }
}

Explanation:

NPOI Library: Reads Excel data efficiently.
DataTable: Temporarily holds records before uploading.
SqlBulkCopy: Uploads 1 million records quickly (under 2 minutes).
BatchSize: Increases performance.

Task 12: Methods to Get Results from Database

1️⃣ Check if Table Has Any Record:

public bool IsTableNotEmpty(string tableName)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationReader.CONNECTION_STRING))
    {
        conn.Open();
        string query = $"SELECT COUNT(1) FROM [{tableName}]";
        SqlCommand cmd = new SqlCommand(query, conn);
        int count = (int)cmd.ExecuteScalar();
        return count > 0;
    }
}

2️⃣ Get Approved Countries Dictionary:

public Dictionary<string, int> GetApprovedCountries()
{
    var countries = new Dictionary<string, int>();
    using (SqlConnection conn = new SqlConnection(ConfigurationReader.CONNECTION_STRING))
    {
        conn.Open();
        string query = @"
            SELECT CountryName, EdwCountryId 
            FROM [dbo].[t_EWDCountry] 
            WHERE IsApproved = 1";
 
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    string countryName = reader["CountryName"].ToString().ToUpper();
                    int countryId = Convert.ToInt32(reader["EdwCountryId"]);
                    if (!countries.ContainsKey(countryName))
                    {
                        countries.Add(countryName, countryId);
                    }
                }
            }
        }
    }
    return countries;
}

3️⃣ Get Table Row Count:

public int GetTableRowCount(string tableName)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationReader.CONNECTION_STRING))
    {
        conn.Open();
        string query = $"SELECT COUNT(*) FROM [{tableName}]";
        SqlCommand cmd = new SqlCommand(query, conn);
        return (int)cmd.ExecuteScalar();
    }
}

4️⃣ Get Current Dollar Rate:

public decimal GetCurrentDollarRate()
{
    using (SqlConnection conn = new SqlConnection(ConfigurationReader.CONNECTION_STRING))
    {
        conn.Open();
        string query = @"
            SELECT TOP 1 FCYCURRENCY_RATE 
            FROM [dbo].[t_FCYCurrency] 
            WHERE FCYCURRENCY_STATUS = '1' 
            ORDER BY FCYCURRENCY_ID DESC";
 
        SqlCommand cmd = new SqlCommand(query, conn);
        object result = cmd.ExecuteScalar();
        return result != null && result != DBNull.Value 
            ? Convert.ToDecimal(result) 
            : 1;
    }
}

Task 13: Bulk Download Excel (Simplified Example)

string query = @"
    SELECT 
        OrderID AS Order_ID,
        CustomerName AS Customer_Name,
        ProductName AS Product_Name,
        Quantity,
        OrderDate AS Order_Date
    FROM Orders
    WHERE MONTH(OrderDate) = @selectedMonth AND YEAR(OrderDate) = @selectedYear";
 
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
DataTable dt = new DataTable();
adapter.Fill(dt);
 
string filePath = Path.Combine(_downloadPath, $"Orders-{selectedMonth}-{selectedYear}.xlsx");
 
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Orders");
 
IRow headerRow = sheet.CreateRow(0);
string[] headers = { "Order_ID", "Customer_Name", "Product_Name", "Quantity", "Order_Date" };
for (int i = 0; i < headers.Length; i++)
    headerRow.CreateCell(i).SetCellValue(headers[i]);
 
int rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
    IRow dataRow = sheet.CreateRow(rowIndex++);
    for (int i = 0; i < dt.Columns.Count; i++)
        dataRow.CreateCell(i).SetCellValue(row[i].ToString());
}
 
using (var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
    workbook.Write(fileStream);

Task 14: Bulk TXT Upload (Simplified Example)

if (File.Exists(filePath))
{
    DataTable dt = new DataTable();
    dt.Columns.Add("OrderID", typeof(int));
    dt.Columns.Add("CustomerName", typeof(string));
    dt.Columns.Add("ProductName", typeof(string));
    dt.Columns.Add("Quantity", typeof(int));
    dt.Columns.Add("OrderDate", typeof(DateTime));
 
    string[] lines = File.ReadAllLines(filePath);
    foreach (string line in lines)
    {
        string[] parts = line.Split(',');
 
        if (parts.Length == 5)
        {
            dt.Rows.Add(
                int.Parse(parts[0]),
                parts[1],
                parts[2],
                int.Parse(parts[3]),
                DateTime.Parse(parts[4])
            );
        }
    }
 
    using (SqlConnection conn = new SqlConnection(ConfigurationReader.CONNECTION_STRING))
    {
        conn.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
        {
            bulkCopy.DestinationTableName = "Orders";
            bulkCopy.ColumnMappings.Add("OrderID", "OrderID");
            bulkCopy.ColumnMappings.Add("CustomerName", "CustomerName");
            bulkCopy.ColumnMappings.Add("ProductName", "ProductName");
            bulkCopy.ColumnMappings.Add("Quantity", "Quantity");
            bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate");
 
            bulkCopy.WriteToServer(dt);
        }
    }
}

Task 15: Generate Report Using NPOI and Download into Excel (Simple Example)

This example generates a report for “Employee Attendance” and downloads it as an Excel file:

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.IO;
using System.Web;
 
public void CreateEmployeeAttendanceExcel(List<EmployeeAttendanceModel> attendances, string month)
{
    var workbook = new XSSFWorkbook();
    var sheet = workbook.CreateSheet($"Attendance Report - {month}");
 
    // Create header style
    var headerStyle = workbook.CreateCellStyle();
    var font = workbook.CreateFont();
    font.IsBold = true;
    font.FontHeightInPoints = 11;
    headerStyle.SetFont(font);
    headerStyle.Alignment = HorizontalAlignment.Center;
    headerStyle.VerticalAlignment = VerticalAlignment.Center;
 
    // Create header row
    var headerRow = sheet.CreateRow(0);
    string[] headers = { "Employee ID", "Employee Name", "Date", "Check-In", "Check-Out", "Status" };
    for (int i = 0; i < headers.Length; i++)
    {
        var cell = headerRow.CreateCell(i);
        cell.SetCellValue(headers[i]);
        cell.CellStyle = headerStyle;
        sheet.AutoSizeColumn(i);
    }
 
    // Add data rows
    int rowIndex = 1;
    foreach (var record in attendances)
    {
        var row = sheet.CreateRow(rowIndex++);
        row.CreateCell(0).SetCellValue(record.EmployeeId);
        row.CreateCell(1).SetCellValue(record.EmployeeName);
        row.CreateCell(2).SetCellValue(record.Date.ToString("yyyy-MM-dd"));
        row.CreateCell(3).SetCellValue(record.CheckIn);
        row.CreateCell(4).SetCellValue(record.CheckOut);
        row.CreateCell(5).SetCellValue(record.Status);
    }
 
    // Write to memory stream and download
    using (var stream = new MemoryStream())
    {
        workbook.Write(stream);
        var content = stream.ToArray();
 
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        HttpContext.Current.Response.AddHeader("Content-Disposition", $"attachment; filename=Attendance_Report_{month}.xlsx");
        HttpContext.Current.Response.BinaryWrite(content);
        HttpContext.Current.Response.End();
    }
}
 
public class EmployeeAttendanceModel
{
    public string EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public DateTime Date { get; set; }
    public string CheckIn { get; set; }
    public string CheckOut { get; set; }
    public string Status { get; set; }
}

Task 16: Simple SQL Query with Inline Search, THEN-ELSE Usage, and HAVING Clause

This example calculates the number of orders per customer and counts how many were delivered or pending. It also uses inline search for customer names and includes a HAVING clause to filter results:

SELECT 
    c.CustomerID,
    (SELECT Name FROM Customers WHERE CustomerID = o.CustomerID) AS CustomerName,
    COUNT(o.OrderID) AS TotalOrders,
    SUM(CASE WHEN o.Status = 'Delivered' THEN 1 ELSE 0 END) AS DeliveredOrders,
    SUM(CASE WHEN o.Status = 'Pending' THEN 1 ELSE 0 END) AS PendingOrders
FROM 
    Orders o
JOIN 
    Customers c ON o.CustomerID = c.CustomerID
GROUP BY 
    c.CustomerID
HAVING 
    SUM(CASE WHEN o.Status = 'Delivered' THEN 1 ELSE 0 END) > 5 
    OR SUM(CASE WHEN o.Status = 'Pending' THEN 1 ELSE 0 END) > 2;

Explanation of Key Concepts:

Inline Search:
(SELECT Name FROM Customers WHERE CustomerID = o.CustomerID) AS CustomerName
This is an inline subquery that fetches the customer name directly within the main query.
THEN 1 ELSE 0:
SUM(CASE WHEN o.Status = 'Delivered' THEN 1 ELSE 0 END)
This counts the number of delivered orders by adding 1 for each “Delivered” and 0 for others.
HAVING Clause:
HAVING SUM(CASE WHEN o.Status = 'Delivered' THEN 1 ELSE 0 END) > 5
The HAVING clause filters results based on aggregated values (e.g., customers with more than 5 delivered orders).

🪴 Shoyeb's Dev Diary

Explorer

16. Some Industrial Query and NPOI

Task 1: Multiple CTEs (Common Table Expressions)

Task 2: Unique Values from Multiple Joined Tables

Task 3: Create a Simple View

Task 4: Use a Variable to Update Another Table

Task 5: Use `IN` in a Query

Task 6: Use `EXISTS`, `NOT EXISTS`, and `IF NOT EXISTS`

Task 7: `SELECT` and `INSERT` Together

Task 8: Reseed Identity Column

Task 9: Use `CASE` Statement

Task 10: SQL Data Read into C#

Task 11: Bulk Excel Upload from C# using NPOI (1 Million Records in < 2 Minutes)

Task 12: Methods to Get Results from Database

1️⃣ Check if Table Has Any Record:

2️⃣ Get Approved Countries Dictionary:

3️⃣ Get Table Row Count:

4️⃣ Get Current Dollar Rate:

Task 13: Bulk Download Excel (Simplified Example)

Task 14: Bulk TXT Upload (Simplified Example)

Task 15: Generate Report Using NPOI and Download into Excel (Simple Example)

Task 16: Simple SQL Query with Inline Search, THEN-ELSE Usage, and HAVING Clause

Explanation of Key Concepts:

🪴 Shoyeb's Dev Diary

Explorer

16. Some Industrial Query and NPOI

Task 1: Multiple CTEs (Common Table Expressions)

Task 2: Unique Values from Multiple Joined Tables

Task 3: Create a Simple View

Task 4: Use a Variable to Update Another Table

Task 5: Use IN in a Query

Task 6: Use EXISTS, NOT EXISTS, and IF NOT EXISTS

Task 7: SELECT and INSERT Together

Task 8: Reseed Identity Column

Task 9: Use CASE Statement

Task 10: SQL Data Read into C#

Task 11: Bulk Excel Upload from C# using NPOI (1 Million Records in < 2 Minutes)

Task 12: Methods to Get Results from Database

1️⃣ Check if Table Has Any Record:

2️⃣ Get Approved Countries Dictionary:

3️⃣ Get Table Row Count:

4️⃣ Get Current Dollar Rate:

Task 13: Bulk Download Excel (Simplified Example)

Task 14: Bulk TXT Upload (Simplified Example)

Task 15: Generate Report Using NPOI and Download into Excel (Simple Example)

Task 16: Simple SQL Query with Inline Search, THEN-ELSE Usage, and HAVING Clause

Explanation of Key Concepts:

Task 5: Use `IN` in a Query

Task 6: Use `EXISTS`, `NOT EXISTS`, and `IF NOT EXISTS`

Task 7: `SELECT` and `INSERT` Together

Task 9: Use `CASE` Statement