如何获取作为存储过程中列表的局部变量？

我在 C# 中有一些代码，我正在尝试移动到存储过程 - Sql Azure 不喜欢多次调用。所以一切都需要转移到过程中。

这是 C# 代码：

    List<string> addList = new List<string>(EmailDomains);
    List<string> deleteList = new List<string>();
    using (IDbCommand cmd = conn.CreateCommand())
    {
        cmd.Transaction = transaction;
        cmd.CommandText = "select * from CompanyDomains where CompanyDomains.CompanyId = @CompanyId";
        DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
        using (IDataReader reader = cmd.ExecuteReader())
        {
            int ordinal = reader.GetOrdinal("EmailDomain");
            while (reader.Read())
            {
                string domain = reader.GetString(ordinal).Trim();
                if (! addList.Remove(domain))
                {
                    deleteList.Add(domain);
                }
            }
        }
    }
    foreach (string domain in deleteList)
    {
        using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.Transaction = transaction;
            cmd.CommandText =
                "delete from CompanyDomains where CompanyDomains.CompanyId = @CompanyId and CompanyDomains.EmailDomain = @EmailDomain";
            DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
            DbUtilities.AddParam(cmd, "@EmailDomain", domain);
            cmd.ExecuteNonQuery();
        }
    }

    // deleted domains get added/updated to DeletedCompany
    foreach (string domain in deleteList)
    {
        DeletedCompany.InsertOrUpdate(conn, transaction, domain, CompanyName);
    }

    foreach (string domain in addList)
    {
        using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.Transaction = transaction;
            cmd.CommandText = "insert into CompanyDomains (CompanyId, EmailDomain) " +
                              "values (@CompanyId, @EmailDomain)";
            DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
            DbUtilities.AddParam(cmd, "@EmailDomain", domain.Trim());

            cmd.ExecuteNonQuery();
        }
    }

    // added domains get removed from DeletedCompany
    foreach (string domain in addList)
    {
                    using (IDbCommand cmd = conn.CreateCommand())
    {
        if (transaction != null)
            cmd.Transaction = transaction;
        cmd.CommandText = "delete from DeletedCompany where DeletedCompany.Domain = @Domain";
        DbUtilities.AddParam(cmd, "@Domain", domain);
        cmd.ExecuteNonQuery();
    }

    }

我完全停止的是我没有看到列表的任何等价物是 SQL。有没有办法在列表中建立所有这些？

或者我可以在选择使用我传入的列表的地方执行删除/插入/更新吗？我以定义的类型（表）的形式传递一个列表，它是新的域列表。从该列表中：

传入列表中不在 CompanyDomains 中的任何域都将添加到 CompanyDomains。

CompanyDomains 表中不在传入列表中的任何域 1) 从 CompanyDomains 中删除，以及 2) 添加/更新到 DeletedCompany 表。

liast 和 DeletedCompany 表中的任何域都将从 DeletedCompany 表中删除。

DeletedCompany.Domain 列在该表中是唯一的。所以添加到它必须是一个 upsert。

CompanyDomains 表也有一个 CompanyId 列，CompanyDomains.CompanyId = @CompanyId（将参数传递给 proc）。

这适用于 Sql Server 2008 和 Sql Azure。它不需要在其他任何东西上运行。

谢谢 - 戴夫