edvaldo b. guimarães filho

Adapting a PowerShell SharePoint Script to .NET Framework C# Using SQL Databases

Adapting a PowerShell SharePoint Script to .NET Framework C# Using SQL Databases

In this article, we will take a PowerShell script initially designed to create and manage SharePoint Online lists and adapt it for .NET Framework C# to interact with SQL Server databases instead of SharePoint lists. This scenario assumes you want to retrieve data from an external API (such as the World Bank) and store it in SQL tables rather than SharePoint.

Original Scenario Overview

The original PowerShell script connects to a SharePoint Online site, creates lists for various countries, fetches GDP data from the World Bank API, and then populates the SharePoint lists with this data. We will adapt the core logic to .NET Framework C# but modify it to work with SQL Server tables instead of SharePoint lists.

Key Steps

  1. Connect to the World Bank API to retrieve GDP data.
  2. Create SQL tables instead of SharePoint lists for each country.
  3. Insert data into SQL tables using ADO.NET.
  4. Handle data retrieval and manipulation in C# using asynchronous methods for improved performance.

Requirements

  • .NET Framework (4.7 or higher)
  • SQL Server (or any SQL database)
  • A World Bank API connection for data on Gross Domestic Product (GDP)
  • JSON parsing library (e.g., Newtonsoft.Json)

The Adapted C# Code

Below is the adapted C# code that performs the same tasks as the original PowerShell script but stores the GDP data in SQL Server tables.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Net.Http;
using System.Threading.Tasks;
using Newtonsoft.Json.Linq;

class Program
{
    static async Task Main(string[] args)
    {
        // SQL Database connection string
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        // List of countries with their codes
        var countries = new List<Country>
        {
            new Country("Brazil", "BR"),
            new Country("Argentina", "AR"),
            new Country("United States", "US"),
            new Country("China", "CN"),
            new Country("Japan", "JP"),
            new Country("Germany", "DE"),
            new Country("India", "IN"),
            new Country("United Kingdom", "GB"),
            new Country("France", "FR"),
            new Country("Italy", "IT"),
            new Country("Canada", "CA"),
            new Country("South Korea", "KR"),
            new Country("Australia", "AU"),
            new Country("Spain", "ES"),
            new Country("Mexico", "MX"),
            new Country("Indonesia", "ID"),
            new Country("Netherlands", "NL"),
            new Country("Saudi Arabia", "SA"),
            new Country("Turkey", "TR"),
            new Country("Taiwan", "TW"),
            new Country("Switzerland", "CH")
        };

        int startYear = 1989;
        int endYear = 2023;

        // Connect to SQL Database and create tables
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            foreach (var country in countries)
            {
                string tableName = $"GDP_Data_{country.Name.Replace(" ", "_")}";

                // Drop existing table
                string dropTableQuery = $"IF OBJECT_ID('{tableName}', 'U') IS NOT NULL DROP TABLE {tableName};";
                using (SqlCommand dropCommand = new SqlCommand(dropTableQuery, connection))
                {
                    dropCommand.ExecuteNonQuery();
                    Console.WriteLine($"Table '{tableName}' dropped successfully.");
                }

                // Create new table
                string createTableQuery = $@"
                    CREATE TABLE {tableName} (
                        Id INT PRIMARY KEY IDENTITY(1,1),
                        CountryCode NVARCHAR(3),
                        GDP DECIMAL(18, 2),
                        Year INT
                    );";
                using (SqlCommand createCommand = new SqlCommand(createTableQuery, connection))
                {
                    createCommand.ExecuteNonQuery();
                    Console.WriteLine($"Table '{tableName}' created successfully.");
                }

                // Get GDP data from World Bank API
                string apiUrl = $"https://api.worldbank.org/v2/country/{country.Code}/indicator/NY.GDP.MKTP.CD?date={startYear}:{endYear}&format=json";

                using (HttpClient client = new HttpClient())
                {
                    HttpResponseMessage response = await client.GetAsync(apiUrl);
                    if (response.IsSuccessStatusCode)
                    {
                        string jsonResponse = await response.Content.ReadAsStringAsync();
                        JArray jsonData = JArray.Parse(jsonResponse);

                        foreach (var item in jsonData[1])
                        {
                            string countryCode = item["countryiso3code"].ToString();
                            string gdp = item["value"]?.ToString() ?? "0";
                            string year = item["date"].ToString();

                            // Insert data into SQL table
                            string insertQuery = $@"
                                INSERT INTO {tableName} (CountryCode, GDP, Year)
                                VALUES (@CountryCode, @GDP, @Year)";
                            using (SqlCommand insertCommand = new SqlCommand(insertQuery, connection))
                            {
                                insertCommand.Parameters.AddWithValue("@CountryCode", countryCode);
                                insertCommand.Parameters.AddWithValue("@GDP", decimal.Parse(gdp));
                                insertCommand.Parameters.AddWithValue("@Year", int.Parse(year));

                                insertCommand.ExecuteNonQuery();
                            }
                        }

                        Console.WriteLine($"Data added to table '{tableName}' successfully.");
                    }
                    else
                    {
                        Console.WriteLine($"Error retrieving data from API for {country.Name}");
                    }
                }
            }
        }
    }

    class Country
    {
        public string Name { get; }
        public string Code { get; }

        public Country(string name, string code)
        {
            Name = name;
            Code = code;
        }
    }
}

Explanation of the Code

  1. Database Connection and Table Creation:
  • We define the connectionString to connect to the SQL Server database.
  • For each country, the script checks if a table already exists, drops it if necessary, and creates a new table for storing GDP data.
  1. Fetching Data from the World Bank API:
  • The World Bank API provides GDP data for each country.
  • We use HttpClient to fetch the data and Newtonsoft.Json to parse the JSON response.
  1. Inserting Data into SQL Tables:
  • The script inserts the parsed data into the SQL Server database using SqlCommand.

Links for Further Reading

Summary Table of Steps

StepCommand/Action
Define countries and codesvar countries = new List<Country> { new Country("Brazil", "BR"), ... };
SQL connection string setupstring connectionString = "your_connection_string";
Drop existing tableIF OBJECT_ID('{tableName}', 'U') IS NOT NULL DROP TABLE {tableName};
Create new tableCREATE TABLE {tableName} (Id INT PRIMARY KEY IDENTITY, CountryCode NVARCHAR(3), GDP DECIMAL, Year INT);
Fetch data from World Bank APIHttpClient client = new HttpClient(); HttpResponseMessage response = await client.GetAsync(apiUrl);
Insert data into SQL tableINSERT INTO {tableName} (CountryCode, GDP, Year) VALUES (@CountryCode, @GDP, @Year);

This article covers the key concepts involved in converting a PowerShell SharePoint script into a .NET Framework C# solution. By leveraging SQL Server for data storage and retrieval, we maintain similar functionality while adapting it to a new technology stack.

Edvaldo Guimrães Filho Avatar

Published by

Edvaldo Guimrães Filho
With over 30 years of professional experience as a System and Business Analyst, Project Manager, and Technical Marketing and Sales Support Leader, including 20 years specializing in SharePoint and 5 years in Business AI Transformation, I have worked across various industries and markets. I have had the privilege of collaborating with top 500 Brazilian companies such as DASA, Siemens, Telefónica, Heineken, K2, Comgas/Raízen/Shell, Cognizant, Coty, Hypermarcas, Banco Itaú, Cobrape, Natura, Stefanini, Braskem, Nestlé, Avanade, TIM, Infoserver, SysMap, Vivo, Apsen, Siemens, EDS, Duracell, KPMG, Petrobras, CESP, TV Record, Método Engenharia, and MSD (Merck Sharp & Dohme). Today, I manage, project, and develop solutions and integrations using the following technological platforms: SharePoint, Microsoft 365, Power Platform, Power BI, C#, SQL, Python, JavaScript, React, IoT, C/C++, CAD, and 3D Printing.
Categories: ,
Tags:

Leave a comment