0

I tried searching for a similar answer here on stackoverflow, but I might just be bad at searching. I'm relatively new to c#, and what I'm trying to do is loop through different ID's so I can use a prepared statement in SQL server to run a series of queries, looping through those different ID's. I tired using a while loop, but I may have done it incorrectly and I'm not certain. I know how to use SQL server, that's not the issue as I have that working there just fine, but I want to try to use it in C# for different testing purposes. Here is my code:

using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;

class Program
{
    static void Main()
    {
        Console.WriteLine("Executing query...");
        int i = 46556;
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
        while (true)
        {
            using (SqlConnection connection = new SqlConnection("CONNECTION_STRING"))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(
                    "SELECT TOP 100 GlobalCustomerVehicleID, GlobalCustomerID, Status, VIN, LastEditByUserID, CreatedByUserID, Year, Make, Model FROM Drop_GlobalCustomerVehicle WHERE GlobalCustomerID = @ID", connection))
                {

                    command.Parameters.Add(new SqlParameter("ID", i));
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        int GlobalCustomerVehicleID = reader.GetInt32(0);
                        int GlobalCustomerID = reader.GetInt32(1);
                        string Status = reader.GetString(2);
                        string VIN = reader.GetString(3);
                        int LastEditByUserID = reader.GetInt32(4);
                        int CreatedByUserID = reader.GetInt32(5);
                        short Year = reader.GetInt16(6);
                        string Make = reader.GetString(7);
                        string Model = reader.GetString(8);
                        Console.WriteLine("GlobalCustomerVehicleID = {0}, GlobalCustomerID = {1}, Status = {2}, VIN = {3}, LastEditByUserID = {4}, CreatedByUserID = {5}, Year = {6}, Make = {7}, Model = {8}",
                            GlobalCustomerVehicleID,
                            GlobalCustomerID,
                            Status,
                            VIN,
                            LastEditByUserID,
                            CreatedByUserID,
                            Year,
                            Make,
                            Model
                            );
                    }

                }
            }
            i = i + 10;
            if (i > 47000)
                break;
        }

       stopwatch.Stop();
       Console.WriteLine("Time elapsed: {0}", stopwatch.Elapsed);


        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }

}

I know some of this isn't exactly super secure, such as the connection string directly inside of the code, but this is only for testing purposes and will not be used outside of internal testing. Could someone tell me where I'm going wrong, or even suggest a fix? Thank you for your time.

Edit: It was a simple fix, and I apologize that my question was too generalized! The question was I was wanting it to actually loop through several times instead of just once. It worked in that it did a single query, but didn't go through the different ID's I was expecting. However, I missed a single 0, and even after searching through my code I just completely missed that very easy mistake. Thank you for pointing it out, and suggesting a more optimal way to write the while loop.

5
  • What specifically is going wrong, compared to what you wanted to achieve? Are you getting errors, or some unexpected behaviour? It's not really clear what your issue is, so it's hard to suggest what to fix. "How to" isn't a great question because potentially there are several ways (since your question is quite general). You've tried a way...what problem are you experiencing exactly? P.S. I wouldn't use while (true) by the way, just use either while (i <= 4700) or a for loop Commented Sep 26, 2018 at 15:49
  • 2
    This loop will be executed only once. i is initially 46556 and you break out of loop when it is > 4700. Commented Sep 26, 2018 at 15:50
  • Also, you should not create a new connection in every iteration of the loop. And prepared statement. The idea of prepared statement is - prepare once and set parameters for every execution. Commented Sep 26, 2018 at 15:52
  • Why dont you explain what you want to achieve here. I am guessing there will be other more effecient ways to do it Commented Sep 26, 2018 at 15:52
  • Ah, I apologize. It's what yu_sha said, it's only executing once, and I'd like to make the code loop through up until 47000. I see what happened was that I missed a 0 and it was something simple. I'll also fix the loop, adding <=47000 instead of true. Thank you two for your help that was all I needed :) Commented Sep 26, 2018 at 15:53

1 Answer 1

1

If you want to search using multiple Id then you should use the IN clause in sql.

SELECT TOP 100 GlobalCustomerVehicleID, GlobalCustomerID, Status, VIN, LastEditByUserID, CreatedByUserID, Year, Make, Model FROM Drop_GlobalCustomerVehicle WHERE GlobalCustomerID IN (ID1,ID2,ID3)
Sign up to request clarification or add additional context in comments.

1 Comment

Oh, yeah, I'm doing that in my SQL Server query in SQL management studio. I just forgot to change that when I was making the c# version, thank you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.