How to improve this code

Question

            // conn is read from handydrive
            //conn2 read from C:\

this code is designed to write a new record to the database in C: \ by checking for the presence of the first. my problem is too slow for many posts. and how to improve it to be faster ...

        SqlCeCommand cmd1 = new SqlCeCommand("Select * from bill_discount  ", conn);

        SqlCeDataReader dr1 = cmd1.ExecuteReader();

        while (dr1.Read() != false)
        {
            SqlCeCommand cmd4 = new SqlCeCommand("Select * from bill_discount where bill_no='" + dr1.GetInt32(0) + "' AND bill_shopdc='" + dr1.GetString(2) + "'  ", conn2);

            SqlCeDataReader dr4 = cmd4.ExecuteReader();
            if (dr4.Read() == false)
            {
                SqlCeCommand cmd2 = new SqlCeCommand("INSERT INTO bill_discount (bill_no,bill_value,bill_shopdc) VALUES ('" + dr1.GetInt32(0) + "','" + dr1.GetDouble(1) + "','" + dr1.GetString(2) + "') ", conn2);

               // SqlCeDataReader dr2 = cmd2.ExecuteReader();
                cmd2.ExecuteNonQuery();

            }

        }
        //-------------------------------------------------------------------

Answer 1

I would look at the SqlBulkCopy class :

Allows you to efficiently load SQL Server table with data from another source.

BTW . In the above code, selecting the whole table is bill_discountnot a good idea, especially if the table is large.

[In addition, it looks like you can only execute one TSQL statement, not a loop through each row and a round trip to the database.]

: SqlBulkCopy - SQL- - ADO.NET 2.0

Answer 2

, . :

SqlCeCommand getAllBills = new SqlCeCommand("select * from bill_discount", primaryConnection);
SqlCeDataReader allBillsReader = getAllBills.ExecuteReader();
while (allBillsReader.Read())
{
    SqlCeCommand getBill = new SqlCeCommand("select * from bill_discount where bill_no = '" + allBillsReader.GetInt32(0) + "' and bill_shopdc = '" + allBillsReader.GetString(2) + "'  ", secondaryConnection);
    SqlCeDataReader billReader = getBill.ExecuteReader();
    if (!billReader.Read())
    {
        SqlCeCommand addMissingBill = new SqlCeCommand("insert into bill_discount (bill_no, bill_value, bill_shopdc) values ('" + allBillsReader.GetInt32(0) + "', '" + allBillsReader.GetDouble(1) + "', '" + allBillsReader.GetString(2) + "')", secondaryConnection);
        addMissingBill.ExecuteNonQuery();
    }
}

. .

SQL-.

, : - , , .

using (SqlCeCommand getAllBills = new SqlCeCommand("select bill_no, bill_value, bill_shopdc from [bill_discount]", primaryConnection))
{
    using (SqlCeDataReader allBillsReader = getAllBills.ExecuteReader())
    {
        while (allBillsReader.Read())
        {
            using (SqlCeCommand getBill = new SqlCeCommand("if exists(select * from [bill_discount] where [bill_no] = @billNumber and bill_shopdc = @billShop) select 1 else select 0", secondaryConnection))
            {
                getBill.Parameters.AddWithValue("@billNumber", allBillsReader["bill_no"]);
                getBill.Parameters.AddWithValue("@billShop", allBillsReader["bill_shopdc"]);

                bool billExists = Convert.ToBoolean(getBill.ExecuteScalar());
                if (!billExists)
                {
                    using (SqlCeCommand addMissingBill = new SqlCeCommand("insert into [bill_discount] ([bill_no], [bill_value], [bill_shopdc]) values (@billNumber, @billValue, @billShop)", secondaryConnection))
                    {
                        addMissingBill.Parameters.AddWithValue("@billNumber", allBillsReader["bill_no"]);
                        addMissingBill.Parameters.AddWithValue("@billValue", allBillsReader["bill_value"]);
                        addMissingBill.Parameters.AddWithValue("@billShop", allBillsReader["bill_shopdc"]);

                        int countAffectedRows = addMissingBill.ExecuteNonQuery();
                        Debug.Assert(countAffectedRows == 1, "The data was not inserted.");
                    }
                }
            }
        }
    }
}

, .

. , SQL- . , , , : , .

Answer 3

I see that you are using SqlCe, which has a number of limitations when inserting massive data. The main limitation is the actual SqlCe engine. However, you can get around this using direct table inserts:

using (var command = connection.CreateCommand())
                     {
                         command.Transaction = transaction;
                         command.CommandType = CommandType.TableDirect;
                         command.CommandText = TABLE_NAME_IN_SQL;

                         using (var rs = command.ExecuteResultSet(ResultSetOptions.Updatable))
                         {
                             var rec = rs.CreateRecord();
                             rec.SetInt32(0, value0); // the index represents the column numbering
                             rec.SetString(1, value1);
                             rec.SetInt32(2, value2);
                             rs.Insert(rec);
                         }
}