Update all null values in column .netcore console app

Question

I've got a console app which updates a column in the database called InstagramId based off user input. I want to be able to query the database and if there's a instagramId which is null, to update it with the correct instagramID based off their instagramUsername.

I wasn't sure on the approach if I should create a fake table based off all the null instagramIds and then use those to update my column or if theres another cleaner approach. This console app is only a one time use so just a quick fix up.

  class Program
    {
        static async Task Main(string[] args)
        {
            // receive a profile name
            var tasks = new List>();
            foreach (var arg in args)
            {
                if (string.IsNullOrEmpty(arg)) continue;
                var profileName = arg;
                var url = $"https://instagram.com/{profileName}";
                tasks.Add(ScrapeInstagram(url));
            }

            try
            {
                var instagramUsers = await Task.WhenAll(tasks);
                foreach (var iu in instagramUsers)
                {
                    iu.Display();
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

        }


        public static async Task ScrapeInstagram(string url)
        {
            using (var client = new HttpClient())
            {
                var response = await client.GetAsync(url);
                if (response.IsSuccessStatusCode)
                {
                    // create html document
                    var htmlBody = await response.Content.ReadAsStringAsync();
                    var htmlDocument = new HtmlDocument();
                    htmlDocument.LoadHtml(htmlBody);

                    // select script tags
                    var scripts = htmlDocument.DocumentNode.SelectNodes("/html/body/script");

                    // preprocess result
                    var uselessString = "window._sharedData = ";
                    var scriptInnerText = scripts[0].InnerText
                        .Substring(uselessString.Length)
                        .Replace(";", "");

                    // serialize objects and fetch the user data
                    dynamic jsonStuff = JObject.Parse(scriptInnerText);
                    dynamic userProfile = jsonStuff["entry_data"]["ProfilePage"][0]["graphql"]["user"];

                    //Update database query 
                    string connectionString = @"Server=mockup-dev-db";


                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Update ApplicationUser Set InstagramId = '" + userProfile.id + "'" + "where Instagram =  '" + userProfile.username + "'", con);
                        cmd.Connection.Open();
                        cmd.ExecuteNonQuery();

                    }

                    // create an InstagramUser
                    var instagramUser = new InstagramUser
                    {
                        FullName = userProfile.full_name,
                        FollowerCount = userProfile.edge_followed_by.count,
                        FollowingCount = userProfile.edge_follow.count,
                        Id = userProfile.id,
                        url = url
                    };
                    return instagramUser;
                }
                else
                {
                    throw new Exception($"Something wrong happened {response.StatusCode} - {response.ReasonPhrase} - {response.RequestMessage}");
                }
            }
        }
    }
}

user_9343223 · Answer

It seems like there are two problems here

Retrieving a list of username from the DB which have null instagram IDs
For each of these, downloading and populating the instagram ID

It looks like you've already solved 1, so let's look at number 2:

This data can be fetched using an SQL query:

SELECT Instagram FROM ApplicationUser WHERE InstagramId IS NULL

In C# this could look like:

using (SqlConnection con = new SqlConnection(connectionString))
    {
    SqlCommand cmd = new SqlCommand("SELECT Instagram FROM ApplicationUser WHERE InstagramId IS NULL", con);
cmd.Connection.Open();
    var instagramUsernames = new List();

    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        instagramUsernames.Add(reader.GetString(0));
    }
}

Now you have all the usernames in the instagramUsernames list and can execute your existing code (with minor modifications) in a foreach loop.

Instead of looping through the args you'd now loop through the list of instagramUsernames (so the above code would come at the start of the Main method): foreach (var arg in args) Would become foreach (var arg in instagramUsernames)

Update all null values in column .netcore console app

1 Answers