Solved

Run complexe SQL statement within C# code

Posted on 2011-03-08
7
432 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35067050
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
ID: 35067059
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
ID: 35067096
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
Foudebach earned 0 total points
ID: 35073196
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35080119
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
ID: 35120690
Great job
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
abstract class C# 1 36
Where is this file? 3 33
Problem to Office 1 40
WPF Select Items 3 25
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

735 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