Inserting value from API to database

Question

I created a ConsoleApplication that gets information from an API and then is supposed to put one of the values in a table from the database. Everything works fine (like getting the values and filling the object data with them) but I can't seem to figure out a way to push it to the database.

class Program
{
static void Main(string[] args)
{
    HttpClient cons = new HttpClient();
    cons.BaseAddress = new Uri("APPURL");
    cons.DefaultRequestHeaders.Accept.Clear();
    cons.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
    MyAPIGet(cons).Wait();
}

static async Task MyAPIGet(HttpClient cons)
{

    using (cons)
    {
        HttpResponseMessage res = await cons.GetAsync("");
        res.EnsureSuccessStatusCode();
        if (res.IsSuccessStatusCode)
        {
            VirtualProduct virtualProduct = await res.Content.ReadAsAsync<VirtualProduct>();
            Console.WriteLine("\n");
            Console.WriteLine("---------------------Calling Get Operation------------------------");
            Console.WriteLine("\n");
            Console.WriteLine("Id    Name          ");
            Console.WriteLine("-----------------------------------------------------------");
            Console.WriteLine("{0}\t{1}\t", virtualProduct.Id, virtualProduct.Header);
            Console.ReadLine();

            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
            {
                string insertQuery = @"UPDATE VirtualProduct SET ProductImageId = @Id WHERE Name='@Header';";

                var result = db.Execute(insertQuery, virtualProduct);
            }

        }
    }
}
}

After running this, everything works fine but my database does not get updated. Upon debugging, I discovered that using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString)) has the following error:

ServerVersion = '((System.Data.SqlClient.SqlConnection)db).ServerVersion' threw an exception of type 'System.InvalidOperationException'

The connection string is perfectly fine, I double checked the app.config which looks like this:

 <?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add connectionString="Server= SERVER.database.windows.net;Database=SERVER_dev;User Id=user; Password=asd;" name="DefaultConnectionString" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Possible duplicate of [How to fix "'ServerVersion' threw an exception of type 'System.InvalidOperationException'"?](https://stackoverflow.com/questions/33282511/how-to-fix-serverversion-threw-an-exception-of-type-system-invalidoperatione) — Matt, Sep 12 '19 at 09:41
Possible duplicate of [C# console application Invalid Operation Exception](https://stackoverflow.com/questions/15566277/c-sharp-console-application-invalid-operation-exception) — Andrei Dragotoniu, Sep 12 '19 at 09:47
Post the *full* exception, not just parts of it. You can get the full exception, including the call stack, easily with `Exception.ToString()`. There's no `IDbConnection.Insert` method which means you're using some other library, Dapper perhaps? — Panagiotis Kanavos, Sep 12 '19 at 10:11
After putting the `db.Open()` thingy after the `using`, the error got fixed. However, though, the database still doesn't get updated. Do you have any thoughts on what am I still doing wrong? — Questieme, Sep 12 '19 at 10:31
@Questieme Did my answer helped you? Please comment under it if it helped you to solve your issue. It will be easier to track what you are doing. Also, which value do you get in you `result`? — Matt, Sep 12 '19 at 11:25

Matt · Accepted Answer · 2019-09-12T11:23:44.023

2

As stated in this answer, try this:

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
{
    string insertQuery = @"UPDATE VirtualProduct SET ProductImageId = @Id WHERE Name='@Header';";
    if (db.State==ConnectionState.Closed)
    {                      
        db.Open();   
    }
    var result = db.Execute(insertQuery, virtualProduct);
}

After using using statement, everything declared inside using block of code gets disposed.

edited Sep 12 '19 at 11:23

answered Sep 12 '19 at 09:46

Matt

1,245
2
17
32

@AndreiDragotoniu Thanks for suggestion, but I also included edited OP's code too. – Matt Sep 12 '19 at 09:49
You should have marked this as a duplicate then. In any case don't copy code that you don't understand - you may get the downvotes intended for the original asnwer. That answer is simply wrong. A new connection is closed, there's *no* reason to check its state. – Panagiotis Kanavos Sep 12 '19 at 10:07

Inserting value from API to database

1 Answers1