how to improve this code

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

this code is for write new record in to DB in C:\ by check exist first. my problem is too slow for alot of records. 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)
        {
            SqlCeComm开发者_运维知识库and 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();

            }

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

---

I would take a look at the SqlBulkCopy Class:

Lets you efficiently bulk load a SQL Server table with data from another source.

BTW: In your code above, selecting the entire bill_discount table is not really a good idea, especially if the table is large.

[Also, it appears you could perform a single TSQL statement rather than looping through each row and round-tripping to the database.]

This example should be of help: SqlBulkCopy - Copy Table Data Between SQL Servers at High Speeds - ADO.NET 2.0 New Feature

---

Let's start by make the code more readable. Here's the result:

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

Disposable objects must be disposed. Let's do it.

Let's also remove SQL Injections.

Finally, let's optimize the second query: you don't need to select something and executing the reader if you just want to check if the value exists in the database.

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.");
                    }
                }
            }
        }
    }
}

So here we are.

Now, it's still a low performance solution. To be more effective, you might want to do the same thing in a single SQL query with joins. Since two tables are probably situated on different servers, you may look at linked servers: a feature that enables to execute a single query over several tables from several servers.

---

I see you are using SqlCe, which has number of limitations when inserting bulk data. The main limitation is the actual SqlCe Engine. You can however bypass this by 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);
                         }
}