在一个命令中创建多个sql查询

dvtswwa3 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(219)

我用sqlserver存储过程编写了这段代码，我想单独运行到c代码中。问题是我不知道如何使用c#。这是存储过程的代码；

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

我将我的数据库从sqlserver切换到mysql，以便我与其他软件开发人员一起采用，因为我正在与之斗争，我需要帮助。
谢谢。

mysql c#

来源：https://stackoverflow.com/questions/50755017/create-multiple-sql-queries-in-one-command

1条答案

按热度按时间

wlwcrazw1#

在一个块中可以同时执行多个语句；每条语句都必须以分号结尾 ; . 翻译时 Stored Procedure ，输入参数不能在此块内声明；把每一个都当作 SqlParameter 与 SqlCommand 对象。
对于 Stored Procedure 在您的问题中，这将如下所示。
（因为我不能在这里执行，你可能要修改一些拼写错误；但你明白了。）

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();
    }
}

赞(0）回复(0）举报 2021-06-23

我来回答

在一个命令中创建多个sql查询

1条答案

相关问题

热门标签

最新问答