Bulk-Insert to SQL LocalDB not working with Dapper Plus

[syedmeesamali]

0
I am trying to populate my database column with name "StockInTable" using Dapper Plus nugget package. My stockin file is as below.

class Stockin
{
    [Key]
    public int ID { get; set; }
    public DateTime Date { get; set; }
    public string Sup_ID { get; set; }
    public string Sup_Name { get; set; }
    public string Prod_ID { get; set; }
    public string Prod_Name { get; set; }
    public DateTime Expiry { get; set; }
    public float Units { get; set; }
}

Then my SQL insert operation (using Dapper) looks something like this:

try
        {  DapperPlusManager.Entity<Stockin>().Table("StockinTable").Identity(x => x.ID);
            List<Stockin> stockin = dataGridView1.DataSource as List<Stockin>;
            if (stockin != null)
            {
                using (IDbConnection db = new SqlConnection(ConnectionString);
                { db.BulkInsert(stockin);      }
                MessageBox.Show("Purchase Data Imported successfully!");
            }   MessageBox.Show("Stockin is still null or there is some issue!");
        }  catch (Exception ex)
        { MessageBox.Show(ex.Message, "Some error occurred! Please check parameters!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

But whenever I execute my code, I get back nothing (i.e. there is no error but my list is not populated and nothing gets inserted to Database). Any help/guidance is much appreciated. BTW I am populating my "stockin" table using an excel sheet input (via EPPlus package) and that is working fine (i.e. I am displaying the stockin table to DGV which shows the list perfectly).

 

[Skydiver]

Right now, I'm even seeing how your code is compiling. Your line #6 is missing a closing parenthesis at the end. Are you sure you are running the correct version of the code?

If you are sure you are running the right version of the code, then set a breakpoint on lines #6 and #7. Is the breakpoint hit when you run the code in a debugger? What is the count of items for the list?

 

[syedmeesamali]

Hi Skydiver, Yeah that bracket was missing by mistake while pasting my code. I'm totally surprised to see that when I'm debugging the code, the stockout or stockin list is getting populated properly but once I reach the part where I need to do the bulk-insert and I check debugger it shows stockin or stockout (I have two similar procedures) as NULL. I will share more code which is relevant.

Below is my declaration of both lists:

private ObservableCollection<Stockout> stockout = new ObservableCollection<Stockout>();
private ObservableCollection<Stockin> stockin = new ObservableCollection<Stockin>();

And below is the method to populate the stockin list.

if (ws.Cells[rowPurchase, colPurchase].Value != null)
                        {
                            Stockin stockinList = new Stockin();
                            stockinList.ID = default;
                            stockinList.Prod_ID = (ws.Cells[rowPurchase, colPurchase].Value).ToString();
                            stockinList.Prod_Name = (ws.Cells[rowPurchase, colPurchase + 1].Value).ToString();
                            stockinList.Date = Convert.ToDateTime(ws.Cells[rowPurchase, colPurchase + 2].Value.ToString());
                            stockinList.Expiry = Convert.ToDateTime(ws.Cells[rowPurchase, colPurchase + 3].Value.ToString());
                            stockinList.Sup_ID = ws.Cells[rowPurchase, colPurchase + 4].Value.ToString();
                            stockinList.Sup_Name = ws.Cells[rowPurchase, colPurchase + 5].Value.ToString();
                            stockinList.Units = float.Parse(ws.Cells[rowPurchase, colPurchase + 6].Value.ToString());
                            stockin.Add(stockinList);
                        }

Can't understand what's going on?

 

[Skydiver]

I assume stockin is put into the dataGridView1.DataSource.

Well, an ObservableCollection<T> is not a List<T>. Read about the as operator. The following line will return a null.

List<Stockin> stockin = dataGridView1.DataSource as List<Stockin>;

 

[syedmeesamali]

Oh Okay. Thanks a lot. So I was using wrong Collection type. I have now re-declared it to global list (my original intent) and happily it is getting passed. My last issue is I am having error related to ID field as I am getting error that values passed to ID field can't be NULL. Though my target field is "ID AUTO-INCREMENT".

 

[syedmeesamali]

Oh Okay. Thanks a lot. So I was using wrong Collection type. I have now re-declared it to global list (my original intent) and happily it is getting passed. My last issue is I am having error related to ID field as I am getting error that values passed to ID field can't be NULL. Though my target field is "ID AUTO-INCREMENT".

Well now that is solved as well. ONLY issue was that my tables in SQL were NOT having identity (1,1) clause. When i updated that, everything got fixed.