Execute a stored procedure N times in c#

Question

I have a stored procedure called "VehicleCamp", this stored procedure insert data to this table:

+---------------------+-----+-------------+--------------+-----------+-----------------+
| id_campaing_control | vin | campaing_id | id_workorder | id_client | campaing_status |
+---------------------+-----+-------------+--------------+-----------+-----------------+
|                     |     |             |              |           |                 |
+---------------------+-----+-------------+--------------+-----------+-----------------+
|                     |     |             |              |           |                 |
+---------------------+-----+-------------+--------------+-----------+-----------------+

This is the code of a stored procedure:

CREATE PROCEDURE VehicleCamp(
@vin VARCHAR(100), @id_camp int, @workorder varchar (100), @id_client int, @status varchar(64)
)
AS
BEGIN
SET NOCOUNT ON;
IF(@id_client IS NULL)
    BEGIN
    INSERT INTO vehicle_campaing (vin,campaing_id,id_workorder,campaing_status) values( @vin, @id_camp, @workorder, @status);
    END
ELSE
    BEGIN
    INSERT INTO vehicle_campaing (vin,campaing_id,id_workorder,id_client,campaing_status) values( @vin, @id_camp, @workorder, @id_client, @status);
    END 
END

I need to execute this stored procedure in C#, according to the number of vehicles, vehicles can be N number of vehicles.

I 'm keeping the VIN numbers of the vehicles in an ArrayList , but I do not know how to execute the stored procedure N times and how to assign the @vin parameter to each element in the ArrayList.

This is my C# code:

public void insertVinCamp()
        {
            string connetionString = null;
            connetionString = "Server=.\\SQLEXPRESS;Database=recalls;Integrated Security=true";
            SqlConnection cnn = new SqlConnection(connetionString);
            SqlCommand sqlc = cnn.CreateCommand();
            sqlc.CommandType = CommandType.StoredProcedure;
            sqlc.CommandText = "VehicleCamp";
            String workorder = label11.Text + folio.Text + year.Text;
            String status = "PENDING";
            sqlc.Parameters.Add("@vin", SqlDbType.VarChar, 100).Value = //here need the values ​​of the arraylist
            sqlc.Parameters.Add("@id_camp", SqlDbType.Int).Value = Int32.Parse(camp.Text);
            sqlc.Parameters.Add("@workorder", SqlDbType.VarChar, 100).Value = workorder;
            sqlc.Parameters.Add("@id_client", SqlDbType.VarChar, 64).Value = label4.Text;
            sqlc.Parameters.Add("@status", SqlDbType.VarChar, 64).Value = status;
            try
            {
                cnn.Open();
                sqlc.ExecuteNonQuery();
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error!");
            }
        }

Don't be afraid to ask what do you need.

Answer 1

This will be easier if your data access code isn't reading fields directly from your UI.

First you could create a class like this:

public class VehicleCamp
{
    public string Vin {get;set;}
    public int IdCamp {get;set;}
    public string WorkOrder {get;set;}
    public string IdClient {get;set;}
    public string Statis {get;set;}
}

It would be better to have a separate class for inserting records so that every class has a single responsibility. But for now you could just modify your method:

public void InsertVinCamp(VehicleCamp vinCamp) // guessing at names
{
    ...

Now your InsertVinCamp method is getting its values from the parameter passed to it. It's not reading from UI fields. This way each method only has to do one thing. One method reads values from the form. The other method doesn't know where the values come from - it just inserts what is passed to it.

Now you can build an instance of VehicleCamp for each record you want to insert.

var vc = new VehicleCamp
    {
        IdCamp = Int32.Parse(camp.Text),
        WorkOrder = label11.Text + folio.Text + year.Text,
        // etc.
    }

If you have a list of VINs you can do this (I'm using a List instead of an ArrayList.)

public void InsertVehicleWithMultipleVins(VehicleCamp vehicle, List<string> vins)
{
    foreach(var vin in vins)
    {
        vehicle.Vin = vin;
        InsertVinCamp(vehicle);
    }
}