CODEWITHSUNDEEP
c#asp.netsql-serverexcel
user11442425
user11442425

Reputation:

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

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();
}

Upvotes: 0

Views: 112

Answers (1)

b_stil
b_stil

Reputation: 2625

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

Upvotes: 1

Related Questions