使用范围标识和多选复选框列表插入 [英] insert using scope identity and multiselect checkbox list

问题描述

我一直坚持如何在一键式点击事件中进行两次插入。我想要做的是一次插入，然后调用新插入的id值，以便我可以使用不同的方法将其插入到另一个表中。



所以基本上：



按钮点击 - 调用bll和bo - 将数据发送到dal和存储过程。

然后

存储程序将id发送回dal - 卡在这里...



i确定存储过程有效。当我执行它时，它返回新插入的id的值，我用表格双重检查。



所以插入的第一部分工作...它'从第一个存储过程回到dal到bll并返回到后面的代码，这样我就可以开始第二次插入'搞砸了我，我想尝试学习n层架构。



我是一般的编程新手，所以我正在学习。对于经验丰富的程序员来说，修复很简单...我不是：P



非常感谢你提供任何帮助。



这里是我迄今为止所拥有的......



代码背后：

i''ve been stuck on how to do two insertions in one button click event. what i am trying to do is one insertion, then call the newly inserted id value so that i can insert it into another table using a different method.

so basically:

button click - call bll and bo - send data to dal and stored procedure.
then
stored procedure sends id back to dal - stuck here...

i know for sure that the stored procedure works. when i execute it, it returns the value of the newly inserted id which i double checked with the table.

so the first part of the insertion works...it''s going from the first stored procedure back to the dal to the bll and back to the code behind so that i can start the second insertion that''s messing me up and i would like to try to learn the n-tier architecture.

i''m new to programming in general so i''m learning as i go. chances are the fix is simple for an experienced coder...which i''m not :P

thank you so much for any help offered.

here''s what i''ve got so far....

code behind:

protected void submitButton_Click(object sender, EventArgs e)
    {
        if (!Page.IsValid)
            return;

        int intResult = 0;

        sBLL scBLL = new sBLL();
        
        sBO scBO = new sBO();
        
        sDAL scdal = new sDAL(); //I suspect I shouldn't even have this line in here in the first place

        scBO.sName = sNameText.Text;
        scBO.orgID = 1;

        try
        {
            intResult = scBLL.Insert(scBO);
            if (intResult > 0)
            {
                lblMessage.Text = "New record inserted successfully.";
            int sID = intResult;               //now trying to insert the new ID into another table...
            mascidBLL masciBLL = new mascidBLL();
            mascidBO masciBO = new mascidBO();
            foreach (ListItem li in CheckBoxList1.Items)
                {
                    if (li.Selected)
                    {
                        masciBO.sID = sID;
                        masciBO.mID = int.Parse(li.Value); //mID belongs to another table.
                        masciBLL.Insert(masciBO);
                    }
                }
            }

            else
                lblMessage.Text = "failed";

        }
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            scBO = null;
            scBLL = null;
        }
    }

bal：

bal:

public int Insert(sBO scBO)
    {
        sDAL scDAL = new sDAL();
        try
        {
            scDAL.Insert(scBO);
            return scBO.sID;
        }
        catch
        {
            throw;
        }
        finally
        {
            scDAL = null;
        }
    }

bo：

bo:

public int sID
    {
        get
        {
            return sID_BO;
        }
        set
        {
            sID_BO = value;
        }
    }

    public string sName
    {
        get
        {
            return sName_BO;
        }
        set
        {
           sName_BO = value;
        }
    }

    public int orgID
    {
        get
        {
            return orgID_BO;
        }
        set
        {
            orgID_BO = value;
        }
    }

dal：

dal:

public int Insert(sBO scBO)
    {
        SqlConnection conn = new SqlConnection(connSTR);
        conn.Open();
        SqlCommand scIns = new SqlCommand("Insert", conn);
        scIns .CommandType = CommandType.StoredProcedure;
        try
        {
            scIns .Parameters.AddWithValue("@sName", scBO.sName);
            scIns .Parameters.AddWithValue("@orgID", scBO.orgID);
            scIns.Parameters.Add("@sID", SqlDbType.Int, 0, "sID");
            scIns.Parameters["@sID"].Direction = ParameterDirection.Output;
            
            return sBO.sID;
        }
        catch
        {
            throw;
        }
        finally
        {
            scIns.Dispose();
            conn.Close();
            conn.Dispose();
        }
    }

存储过程：

stored procedure:

ALTER PROCEDURE Insert
	(
		@sName varchar(50),
		@orgID int
	)
AS
	/* SET NOCOUNT ON */
	DECLARE @Count int
	DECLARE @sID int

		
	select @Count = Count(sName) from s WHERE sName = @sName
	
	IF @Count = 0
		BEGIN
				INSERT INTO s (sName, orgID)
				VALUES (@sName, @orgID)
										 

			SET @sID =  SCOPE_IDENTITY()
			END

									 
	RETURN @sID

推荐答案

你不能从同一个存储过程进行两次插入吗？这样你只需要一次访问数据库并让存储过程处理所有内容。



你已经获得了新创建的密钥：使用它来插入另一个表中的新记录。



如果你这样做，我也会将整个包装在一个事务中，并将成功/失败返回给调用方法，这样你就可以了可以根据结果采取一些行动。

Can''t you do both insertions from the same stored procedure? That way you only need one trip to the database and let the stored procedure handle everything.

You''re already getting the newly created key: use that to insert the new record in another table.

If you do go this way I''d also wrap the whole in a transaction and return success/failure to the calling method so you can take some action depending on the result.

这篇关于使用范围标识和多选复选框列表插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！