Solved

Run complexe SQL statement within C# code

Posted on 2011-03-08
7
425 Views
Last Modified: 2012-08-13
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;
            }

Open 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

Open in new window


Do you have some advices to do that?

Thanks in advance
Regards
0
Comment
Question by:Foudebach
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the advice is to put that code into a stored procedure in the relevant database, and then run the procedure simply by name + parameters
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Trying to run that as an inline SQL statement isn't going to happen ;)

You need to create that as a stored procedure on your SQL Server and execute it like:
            string CommandText = "Your_SP_Name";

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

            // etc, etc

Open in new window

0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
Off question - don't put user name/password or other sensitive information into questions.  Anyone could read this.
I'd suggest you edit (or ask for a moderator to do so) the sensitive information away from the question.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Accepted Solution

by:
Foudebach earned 0 total points
Comment Utility
Hi, I don't know how edit again my question to remove sensitive data...
Concerning my question, I put the sql statement within a string by using the right way. Maybe I will move the sql statement into a stored procedure.
Thanks a lot for your help.
Regards,
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
to note that, removing the "use [SOADB]" for the sql, it can actually be executed from C# like that.
however, adding a "SET NOCOUNT ON" will avoid empty data reader() ...
good luck
0
 

Author Closing Comment

by:Foudebach
Comment Utility
Great job
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now