Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Run complexe SQL statement within C# code

Posted on 2011-03-08
7
Medium Priority
?
444 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 45

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

916 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