TimeToCode TimeToCode - 1 year ago 93
C# Question

Execute a stored procedure N times in c#

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:

@vin VARCHAR(100), @id_camp int, @workorder varchar (100), @id_client int, @status varchar(64)
IF(@id_client IS NULL)
INSERT INTO vehicle_campaing (vin,campaing_id,id_workorder,campaing_status) values( @vin, @id_camp, @workorder, @status);
INSERT INTO vehicle_campaing (vin,campaing_id,id_workorder,id_client,campaing_status) values( @vin, @id_camp, @workorder, @id_client, @status);

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;
catch (Exception ex)

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

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download