AndreLotusDev/QueryExportNET

Sample to export using PostrgreSQL, Dapper and OpenXML

This C# code leverages Dapper for object-relational mapping, Npgsql for PostgreSQL database connection, and OfficeOpenXml for creating Excel files, to perform the following operations:

1. Database Connection: It connects to two PostgreSQL databases using connection strings stored in dbOne and dbTwo.

2. Data Querying:

   • From the first database, it retrieves user details including user claims from tables "User" and "UserClaims".
   • From the second database, it retrieves access permissions between users and sales persons from tables user_sales_person_access and user_department.

3. Data Processing:

   • It groups the retrieved user data by user ID, constructing a dictionary where each key is a user ID and each value is a list of UserInfo objects containing user details and claims.
   • For each user, it also identifies which sales persons they can view, creating UserInfo objects for each corresponding sales person.

4. Excel File Creation:

   • Using the OfficeOpenXml library, the code generates an Excel file containing a list of users with their full names, claims, and the sales persons they can view.
   • The data is formatted into columns with headers "User Id", "Full Name", "Claims", and "Sales Person", and the file is saved to a specified location on the user's desktop.

5. Utility Classes:

   • UserInfo class to represent details about users including their claims and the sales persons they can view.
   • QueryUser and QuerySales classes are used by Dapper to map the database query results to objects.