Create multiple sql queries in one command

Question

I had this code with SQLServer stored procedure which i want to run into c# code alone. The problem is i do not know how to do this with C#. This is the code for stored procedure;

USE [SSFASys]
GO
/****** Object:  StoredProcedure [dbo].[userlogs]    Script Date: 6/8/2018 2:19:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[userlogs]
@id int,
@fullname varchar(150),
@activitydetails varchar(200),
@userform int,
@datelog datetime

as
declare @userid int
declare @activityid int

begin

SELECT  @userid=id FROM endusers WHERE (fullname = @fullname) AND (id = @id)

select @activityid=id from enduseractivitydetials where activitydet=@activitydetails
If (@activityid is null)
    Begin
        INSERT INTO enduseractivitydetials VALUES (@activitydetails)
        Select @activityid =SCOPE_IDENTITY()
    end
if not exists(SELECT enduserLOGS.userid FROM enduserLOGS INNER JOIN enduseractivitydetials ON enduserLOGS.activitydetails = enduseractivitydetials.id INNER JOIN endusers ON enduserLOGS.userid = endusers.id
                WHERE (endusers.fullname = @fullname) AND (enduserLOGS.date = @datelog) AND (endusers.id = @userid) AND (enduseractivitydetials.activitydet = @activitydetails))
                begin
                    insert into enduserLOGS values (@userid,@userform,@activityid,@datelog)
                end
end

I switch my database from SQLServer to MySQL for me to adopt with other software developers, since i'm struggling with it, i need a help.

Thanks.

Do some readup on [LINQ](https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/introduction-to-linq-queries) and maybe C# itself. — Freggar, Jun 08 '18 at 07:02
can u give me some links for me follow the proper way to do it? Please? I did reading some articles but i don't know which one is preferable. — Jansen Malaggay, Jun 08 '18 at 07:10
It's not clear to me what you are trying to do in 'C# alone' or how that relates to MySQL. Please re-phrase the question adding more information about what you are trying to achieve. — DaveEP, Jun 08 '18 at 08:08
@DaveEP The code i provided above is from a stored procedure in MS SQL Server. Now what i want is to is to construct this code in C# windows forms and execute at once. The problem is, i have no idea on how to do this. I Know how to execute single sql queries but with multiple queries, that is the problem, i need a help. Thanks — Jansen Malaggay, Jun 08 '18 at 08:56
Try here: https://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program — DaveEP, Jun 08 '18 at 09:07
@DaveEP I just posted another question intitled "How to create complex queries in c#". please check it, i added some codes in it maybe you could help me with it. thanks — Jansen Malaggay, Jun 13 '18 at 02:37

score 0 · Answer 1 · answered Jun 08 '18 at 13:54

Multiple statements can be executed at once in a single block; each statement has to end with a semicolon ;. When translating a Stored Procedure, the input parameters must not be declared within this block; just pass each one as SqlParameter with the SqlCommand object.

For the Stored Procedure in your question this will look like here below.
(Because I can't execute it here, you might have to fix some typos; but you get the idea.)

const String TSQL = @"
DECLARE @userid INT;
DECLARE @activityid INT;

SELECT @userid = id FROM endusers WHERE (fullname = @fullname) AND (id = @id);

SELECT @activityid = id FROM enduseractivitydetials WHERE activitydet = @activitydetails;
IF (@activityid IS NULL)
BEGIN
    INSERT INTO enduseractivitydetials 
    VALUES (@activitydetails);
    SELECT @activityid = SCOPE_IDENTITY();
END
IF NOT EXISTS (
    SELECT enduserLOGS.userid 
    FROM enduserLOGS 
    INNER JOIN enduseractivitydetials ON enduserLOGS.activitydetails = enduseractivitydetials.id 
    INNER JOIN endusers ON enduserLOGS.userid = endusers.id
    WHERE (endusers.fullname = @fullname) 
        AND (enduserLOGS.date = @datelog) 
        AND (endusers.id = @userid) 
        AND (enduseractivitydetials.activitydet = @activitydetails)
    )
    BEGIN
        INSERT INTO enduserLOGS VALUES (@userid,@userform,@activityid,@datelog);
    END
END
"; 

using (SqlConnection cn = new SqlConnection("your connectionstring goes here"))
{
    cn.Open();
    using (SqlCommand cmd = new SqlCommand(TSQL))                
    {
        cmd.Connection = cn;
        cmd.Parameters.Add(new SqlParameter("@id", 123));
        cmd.Parameters.Add(new SqlParameter("@fullname", "John Doe"));
        cmd.Parameters.Add(new SqlParameter("@activitydetails", "Lorum ipsum ..."));
        cmd.Parameters.Add(new SqlParameter("@datelog", DateTime.Now));                    
        cmd.Parameters.Add(new SqlParameter("@userform", 456));                    
        cmd.ExecuteNonQuery();
    }
}

Create multiple sql queries in one command

1 Answers1