Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run complexe SQL statement within C# code

Posted on 2011-03-08
7
Medium Priority
?
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Technology Partners: 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!

Question has a verified solution.

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

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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 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