Check DataTable records if it exists in database

Question

User-1391672913 posted

Hi,

I have a button in my webform that uploads a csv file with data, inserts the data in a database, and displays the content of the database in a gridview. I am using a TextFieldParser to read the csv file. However, I cannot seem to figure out how to add a validation in it.

I want to validate the first column (which is the SKU in my database) of the uploaded csv file data. If the data has a duplicate in the database, it will prompt a message that the action cannot be completed. If not, it will continue to insert the data in the database. I am trying to validate it in the DataTable (csvdt) but I only end up in errors.

This is my code behind:

protected void AddButton_Click(object sender, EventArgs e)
        {
            string path = @"C:\Users\hac9289\Downloads\";
            //Creating object of datatable  
            DataTable tblcsv = new DataTable();
            //creating columns
            tblcsv.Columns.Add("Stock Keeping Unit");
            tblcsv.Columns.Add("Universal Product Code");
            tblcsv.Columns.Add("Vendor Name");
            tblcsv.Columns.Add("Product Name");
            tblcsv.Columns.Add("Product Description");
            tblcsv.Columns.Add("Retail Price");
            //getting full file path of Uploaded file  
            string CSVFilePath = Path.GetFullPath(path + AddFile.PostedFile.FileName);

            if (!AddFile.HasFile)
            {
                ScriptManager.RegisterStartupScript(this, typeof(string), "Alert", "alert('File Upload Empty');", true);
            }

            else
            {
                //parse records in csv file
                using (TextFieldParser parser = new TextFieldParser(CSVFilePath))
                {
                    parser.HasFieldsEnclosedInQuotes = true;
                    parser.TextFieldType = FieldType.Delimited;
                    parser.SetDelimiters(",");

                    while (!parser.EndOfData)
                    {
                        //Processing row
                        tblcsv.Rows.Add();
                        int count = 0;
                        string[] fields = parser.ReadFields();

                        foreach (string field in fields)
                        {
                            tblcsv.Rows[tblcsv.Rows.Count - 1][count] = field;

                            count++;
                        }
                    }
                }
                InsertCSVRecords(tblcsv);
                PopulateGridView();
            }

        }

        private void InsertCSVRecords(DataTable csvdt)
        {
            string connectionString = "Data Source=102000-LSU-2216;Initial Catalog=loginDB;Integrated Security=True";

            using (SqlConnection connect = new SqlConnection(connectionString))
            {
                //I am trying to validate the csvdt here but I only end up in errors
                String checkSKU = "SELECT StockKeepingUnit FROM RetailInfo WHERE EXISTS";
                SqlDataAdapter adapter = new SqlDataAdapter(checkSKU, connect);

                connect.Open();

                //creating object of SqlBulkCopy
                using (SqlBulkCopy objbulk = new SqlBulkCopy(connect))
                {
                    //assigning Destination table name    
                    objbulk.DestinationTableName = "RetailInfo";
                    //Mapping Table column
                    objbulk.ColumnMappings.Add(0, "StockKeepingUnit");
                    objbulk.ColumnMappings.Add(1, "UniversalProductCode");
                    objbulk.ColumnMappings.Add(2, "VendorName");
                    objbulk.ColumnMappings.Add(3, "ProductName");
                    objbulk.ColumnMappings.Add(4, "ProductDesc");
                    objbulk.ColumnMappings.Add(5, "RetailPrice");

                    //inserting Datatable Records to DataBase    
                    objbulk.WriteToServer(csvdt);
                }
            }

            ScriptManager.RegisterStartupScript(this, typeof(string), "Alert", "alert('CSV Data added');", true);

        }

Any Ideas on how to do it? Thank you.

Sincerely,

NoobNoob

Answer 1

User475983607 posted

I want to validate the first column (which is the SKU in my database) of the uploaded csv file data. If the data has a duplicate in the database, it will prompt a message that the action cannot be completed. If not, it will continue to insert the data in the database. I am trying to validate it in the DataTable (csvdt) but I only end up in errors.

The code behind runs to completion and returns the results to the browser.  Basically, one request one response. 

The easiest approach is to process the entire file and return all the errors to the user.  A very common ETL procedure is loading the data in a "load" table as is.  Then doing the validation from the "load" table.  This gives you a lot of flexibility because you can use the "load" table in queries.   For example, finding matches.  Then you can return the results to the user to edit/fix.

Answer 2

User-1391672913 posted

Thank you for the advise. I will try that one.

Regards,

NoobNoob