Run complexe SQL statement within C# code

Hi Folks, I need to run a complexe SQL statement within a C# method. I tested the following with a basic sql statement:

 public ICollection<SerialNumber> GetSerialNumbersByOperation(Operation OperationLot)
        {

            ISession session = SessionFactory.OpenReadOnly();

            Collection<SerialNumber> SNList = new Collection<SerialNumber>();

            SerialNumber SN;

            SN = new SerialNumber();

            SqlDataReader reader = null;
            SqlConnection connection = null;
            SqlCommand command = null;


            string ConnectionString = "server=ES-52ZCRM1;uid=username;" +
                            "pwd=password; database=soadb";
            connection = new SqlConnection(ConnectionString);
            connection.Open();

            string CommandText = "select SOADB.dbo.MaterialLot.S95Id from SOADB.dbo.MaterialLot " +
                                 "inner join  SOADB.dbo.MaterialDefinition on SOADB.dbo.MaterialDefinition.MaterialDefinitionId = SOADB.dbo.MaterialLot.MaterialDefinitionId " +
                                 "where SOADB.dbo.MaterialLot.QuantityUnitOfMeasure like 'Component' and SOADB.dbo.MaterialDefinition.S95Id like '105E9495P001'";

            command = new SqlCommand(CommandText);
            command.Connection = connection;

            // Execute the query
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                SN.Id = reader["S95Id"].ToString();
                SNList.Add(SN);
            }
            reader.Close();


            return SNList;
            }

Select allOpen in new window

Now, I would like to replace the basic statement by this one:

use [SOADB]

declare @Part varchar(50)
declare @PartType varchar(50)
declare @ShopOrder varchar(50)
declare @Operation varchar(50)
declare @Rows int
declare @Row int

select @Part = '105E8762G005' 
select @ShopOrder = '7700555'  --'OF:200300202 SN:301230002'
select @Operation = '020'


DECLARE @lotByMaterialEquipment TABLE  (
		RowID				INT IDENTITY(1,1) not null,
		LotId				VARCHAR(50),
		MaterialLotId		VARCHAR(255),
		DataType			VARCHAR(50),
		Operation			VARCHAR(50))

INSERT INTO @lotByMaterialEquipment (LotId, MaterialLotId, DataType) 
SELECT ML.S95Id, MLP.MaterialLotId, MLP.Name FROM MaterialLotProperty MLP
		INNER JOIN MaterialLot ML ON ML.MaterialLotId = MLP.MaterialLotId
WHERE  (MLP.Name LIKE 'Work request' or MLP.Name LIKE 'Current Operation') AND MLP.Value = @ShopOrder
ORDER BY ML.S95Id


SELECT	@Rows =  COUNT(*) FROM @lotByMaterialEquipment
SELECT	@Row = 0
			
			
	If @Rows = 0
	BEGIN
		RETURN
	END
	
	WHILE @Row < @Rows
	BEGIN
		SELECT @Row = @Row + 1
				
		UPDATE @lotByMaterialEquipment set Operation = (SELECT CONVERT(varchar(50),MLP.Value) FROM MaterialLotProperty MLP
		INNER JOIN @lotByMaterialEquipment LB on LB.MaterialLotId = MLP.MaterialLotId
		WHERE  MLP.Name LIKE 'Current Operation' AND MLP.Value = @Operation AND LB.RowID = @Row)
	
	END

SELECT LotId FROM @lotByMaterialEquipment WHERE Operation LIKE @Operation order by RowID

Select allOpen in new window

Do you have some advices to do that?

Thanks in advance
Regards