How to avoid distinct records from database while inserting data using asp.net?

Question

I'm building a program that imports data from an Excel file into a database, or by writing into textboxes (by an insert in asp.net) or both, but I want to give a warning if the user tries to enter the same record that is already in database (duplicated).

And I would like to do that by coding or setting something in SQL, to be easier to the user...

Here's my table:

Mov. Date     Value Date    description of the movement     Value in EUROS
---------------------------------------------------------------------------
12-12-2001    12-12-2001    DEPOSITO EM NUMERARIO 222       200,01
12-12-2001    12-12-2001    DEPOSITO EM NUMERARIO 223       200,01
12-12-2001    12-12-2001    DEPOSITO EM NUMERARIO 224       200,02

if the user enters a record equal to any of these, it says a warning and the user can try again

Here's the code (asp.net C#):

protected void Upload_Click(object sender, EventArgs e)
{
    string excelPath = Server.MapPath("~/Nova pasta/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    string filepath = Server.MapPath("~/Nova pasta/") + Path.GetFileName(FileUpload1.FileName);
    string filename = Path.GetFileName(filepath);

    FileUpload1.SaveAs(excelPath);

    string ext = Path.GetExtension(filename);

    string strConnection = @"Data Source=PEDRO-PC\SQLEXPRESS;Initial Catalog=costumizado;Persist Security Info=True;User ID=sa;Password=1234";
    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";

    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

    OleDbCommand cmd = new OleDbCommand("Select * from [rptListaMovs_4$A15:D75]", excelConnection);

    excelConnection.Open();
    cmd.ExecuteNonQuery();

    OleDbDataReader dReader;
    dReader = cmd.ExecuteReader();

    DataTable dtFail = new DataTable();

    using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
    {
        sqlBulk.ColumnMappings.Add("Mov. Date", "Mov. Date");
        sqlBulk.ColumnMappings.Add("Value Date", "Value Date");
        sqlBulk.ColumnMappings.Add("description of the movement", "description of the movement");
        sqlBulk.ColumnMappings.Add("Value in EUROS", "Value in EUROS");
        sqlBulk.DestinationTableName = "Dados";

        var temp = dtFail.AsEnumerable().Distinct();

        dtFail = temp.CopyToDataTable();

        sqlBulk.WriteToServer(dReader);
    }

    excelConnection.Close();
}

You can add a Unique constraint on the columns, or select * and perform the validation in memory — Daniel, May 08 '19 at 14:59
@Daniel Can't do the unique constraint on the columns or else I can't have two equal values in differents recrods. I just dont want that my program lets the user enter two exacly rows by mistake... what is that second thing tou are talking about ? — , May 08 '19 at 15:02
Just a hunch but you are calling Linqs `.Distinct` on a collection of DataTables. This probably uses reference equality and fails. I think the easiest way to do this would be to parse your form data into a collection of value type structs then use something like `.GroupBy(x => x) .Where(g => g.Count() > 1)` to check if there are any duplicates. — Marie, May 08 '19 at 15:02
If you do SQL you could simply do a merge statement, if it matches then update, it is not matched insert. The other thing you could do, would be to use the SQL Output functionality to get the id entered and remove it from the list before continuing. — Greg, May 08 '19 at 15:04
@Marie I will need more information about that, I'm in training this is so new to me, sorry... but that thing that you said to do the user have to do it? cause I want my program to be able to do that without user being bother by it — , May 08 '19 at 15:04
@Greg the program will read that always when something is inserted on the database? or I have to do that command always? Im using SQL btw just dont know the commands — , May 08 '19 at 15:07
How do you want it to behave? If they try to insert 5 records and 1 is a duplicate, do you want to insert none and let them know? Or do you want to insert 4 and tell them that there was one duplicate? If it's an exact duplicate and they are trying to insert something that's already there, is that actually a problem? But what if the dates are the same but the value is different - is that a duplicate, or are they trying to update what was previously entered? Do you want to prevent that? — Scott Hannen, May 08 '19 at 15:09
@ScottHannen "insert 4 and tell them that there was one duplicate" and the duplicate not be inserted... the full row can't be exacly the same — , May 08 '19 at 15:14
@ScottHannen exemple: date|value|descrp|valuein|---- --------------------------------- a a a a --------------------------------------------- a a a b — , May 08 '19 at 15:17
@PedroPereira You would call SQL, the Stored Procedure or the hand written query you use to execute would basically do the merge. The merge will search the table via the index and if it finds a match it'll update, if no match exists it will insert. The OUTPUT $action, inserted.*. would return a log of all the data modified in the query. — Greg, May 08 '19 at 15:18
@Greg Sorry for being ignorant... So you are saying that the user does not need to write that statement? sql will do it by himself after I do the first time? — , May 08 '19 at 15:22
Yes, the user does not enter any data. You consume and send a series of parameters and the code would execute. — Greg, May 08 '19 at 16:46
Do not delete your question when you get your answer. See https://meta.stackoverflow.com/questions/378440/caveat-emptor-making-students-aware-they-cannot-delete-their-homework-questions. — , May 10 '19 at 03:19

b_stil · Answer 1 · 2019-05-08T15:59:01.567

SqlBulkCopy would not be the right tool to use for conditional inserting. You would need to use a merge statement or IF NOT EXISTS (SELECT ....) INSERT ELSE UPDATE.

If you need to submit a large quantity of data look into using a Table Type Table-Valued Parameter and a stored procedure to encapsulate the conditional insert/update logic.

If need to display a message to the user when a match is found then adding a column to contain the hash value of all data in the row may be helpful so you can perform a lookup and message or filter out matches. Or as mentioned early use the OUTPUT SQL clause to create a list of matches to return.

CREATE TYPE <MyRecord> (
    <add columns to match the table as if it was a CREATE TABLE>
)

Bind the DataTable as a parameter to a stored procedure

...
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "<MyRecord>";
command.ExecuteNonQuery();
...

Example in existing post Pass table value parameter

but in doing I do not have to always write this? As this is for a costumer, he will not be able to use Sql for this, I need to be programming this for him... via web app — , May 08 '19 at 15:47
That is the purpose of a stored procedure. It encapsulates the database logic and is initiated through code. [Example](https://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) — b_stil, May 08 '19 at 15:54

How to avoid distinct records from database while inserting data using asp.net?

1 Answers1