How do I insert an excel in a sql server table without inserting repeated data in the table?

Question

How do I insert an excel in a sql server table without inserting repeated data in the table?

Navigation

#1 by (1 votes)

1

someone could help me with something that has been stuck for some days, what happens is that I have to insert the records of a document in excel in a table in sql server, but I have to verify that the records that I insert, do not repeat with the ones I already have in the table, create a Datareader to fill a list with the records of the base, of the table where I insert and then pass the excel to a dataGridView, to go through this with two foreach and get the comparison of the records, but now, suppose I have 50 records in the base and 3 in the excel and only one is repeated, it brings me 149 record, but it is wrong because they should be 49 to insert, I do not know what to do, if someone could guide me.

string excelConectionConfig;
excelConectionConfig = "Provider=Microsoft.Jet.OleDb.4.0; ";
excelConectionConfig += "Data Source =" + openUrlExcel.FileName + "; ";
excelConectionConfig += "Extended Properties=\"Excel 8.0; HDR=YES\" ";

OleDbConnection excelConnection = default(OleDbConnection);
excelConnection = new OleDbConnection(excelConectionConfig);

OleDbCommand filterRows = default(OleDbCommand);
filterRows = new OleDbCommand("Select * From [Hoja1$]", excelConnection);

DataSet ds = new DataSet();

try
{
    OleDbDataAdapter adaptador = default(OleDbDataAdapter);
    adaptador = new OleDbDataAdapter();
    adaptador.SelectCommand = filterRows;
    adaptador.Fill(ds);
    dataGridView1.DataSource = ds.Tables[0];
    try
    {
        SqlConnection conexion_destino = new SqlConnection();
        conexion_destino.ConnectionString = mi_conexion;

        SqlCommand command = new SqlCommand("SELECT rfc FROM prefecto;", conexion_destino);
        conexion_destino.Open();

        //Ejecutar el comando SQL
        SqlDataReader reader = command.ExecuteReader();
        List<string> resultado = new List<string>();
        resultado.Clear();
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                resultado.Add(reader["rfc"].ToString());
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
        Console.WriteLine("---------------------------------------------------------");
        //Mostrar los datos de la tabla
        foreach (String rfc in resultado)
        {
            foreach (DataTable table in ds.Tables)
            {
                foreach (DataRow row in table.Rows)
                {   
                    if (row[0].ToString().Equals(rfc))
                    {
                        Console.WriteLine(row[0].ToString());
                        //aqui insertaria
                    }
                }
            }
        }
            conexion_destino.Close();
            MessageBox.Show("Registros insertados con exito!");
    }
    catch (Exception)
    {
        MessageBox.Show("Error con la conexión de base de datos.");
    }
}
catch (Exception)
{
    MessageBox.Show("Formato de excel invalido");
}

c# .net sql-server azure-sql

asked by Karla Huerta 03.01.2018 в 23:43

source

1 answer

How can I export data from a table separated by; instead of, Why are not the fields validated? JQuery Validation

score 1 · Answer 1

You have too many cycles, that I can not understand them. Actually you only need one cycle:

foreach (DataRow row in ds.Tables[0].Rows)
{   
    string rfc = row[0].ToString();
    if (!resultado.Contains(rfc))
    {
        Console.WriteLine(rfc);
        //aqui insertaria
    }
}

And, optionally, if you want better performance, you can change:

List<string> resultado = new List<string>();

... at a HashSet<string> :

var resultado = new HashSet<string>();