Join query in a SQL Server database and a DBF in C #

Question

Can you help me with the following?

I have two databases: one in SQL Server making the connection with ADO.NET; the other is a DBF database which makes a class for the connection of the same. I want to make a comparison of data from one table to the other, since they have the same structure, and I want to see what data is missing from both the SQL Server and the DBF.

namespace log
{
    static class Program
    {
        private static ECEntities1 dc = new ECEntities1();

        public static void Main()
        {
            // Query in BDD SQL Server
            var query = 
                from HInvoice in dc.HInvoice
                where HInvoice.DOB == '2020-03-01'
                select HInvoice.DOB
                
            // Print data
            foreach (var item in query)
            {
                Console.WriteLine(item);
            }

            // Query in BDD BDF
            string path = AppDomain.CurrentDomain.BaseDirectory + @"db";
            DataTable dt = DBF.ObtenerDatos(path, "GNDITEM.Dbf");

            // Print Data
            foreach (DataRow dtRow in dt.Rows)
            {
                // On all tables' columns
                foreach (DataColumn dc in dt.Columns)
                {
                    var field1 = dtRow[dc].ToString();
                    Console.WriteLine(field1);
                }
            }
        }
    }
}

Hi, welcome to SO. Please read https://stackoverflow.com/help/how-to-ask. You need to be more specific with your question: include examples of what you want as a result, explain what you have tried and what about it isn't working for you, etc. — Connor Low, Mar 07 '21 at 02:34
Please can you indicate as one the two queries to compare data between the two tables? — Santiago Trujillo Terán, Mar 07 '21 at 03:18
See : https://stackoverflow.com/questions/7517968/how-to-compare-2-datatables — jdweng, Mar 07 '21 at 12:09

score 1 · Accepted Answer · edited Mar 11 '21 at 05:58

I will answer myself, I am new so the question was how could I be focused, but that was not the way so I changed the focus what I did was the following:

I created a new project where I sent the DBF table to my SQL Server database through a stored procedure and from there make the comparison

Create the connection to the Dbf database.

OleDbConnection cnn = new OleDbConnection();
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommand cmd;
string PathArchivo = (@"\\ruta_archivo");
string NombreArchivo = "GNDITEM.Dbf";

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @PathArchivo + ";Extended Properties=dBASE IV;User ID=;Password=";
cmd = new OleDbCommand("select * from " + NombreArchivo, cnn);
da.SelectCommand = cmd;
cnn.Close();

var dt = new DataTable();
da.Fill(dt);

Pass the parameters to a DataTable

var dtParametro = new DataTable();
dtParametro.Columns.Add("parametro1", typeof(string));
dtParametro.Columns.Add("parametro2", typeof(string));
dtParametro.Columns.Add("parametro3", typeof(string));
dtParametro.Columns.Add("parametro4", typeof(string));
dtParametro.Rows.Add(drAdd);
// On all tables' columns

Then with a For we fill the DataTable with the information from the DBF table

foreach (DataRow dr in dt.Rows)
{
    drAdd["TYPE"] = dr["parametro1"].ToString(); 
    drAdd["EMPLOYEE"] = dr["parametro2"].ToString(); 
    drAdd["CHECK"] = dr["parametro3"].ToString();
    drAdd["ITEM"] = dr["parametro4"].ToString();
           
    dtParametro.Rows.Add(drAdd);
}

Finally I connect to the SQL Server database and call the stored procedure

string cadena1 = @"conexionabdd...";

var cmda = new SqlCommand("Insertar_tbl_GNDITEM1", new SqlConnection(cadena));
cmda.CommandType = CommandType.StoredProcedure;
cmda.Parameters.Add("@param", SqlDbType.Structured).Value = dtParametro;

cmda.Connection.Open(); // abrimos la conexion
cmda.ExecuteNonQuery(); // lo ejecutamso
cmda.Connection.Close(); // cerramos la conexion

We review the database and see that the data is already in the same

PS: In addition to this, you must create the database in the SQL Server, also create a datatype for that table and the stored procedure, here is the video that will guide me, I hope it will be helpful for someone else Video

Join query in a SQL Server database and a DBF in C #

1 Answers1