在 LINQ-To-SQL (L2SQL) 中使用临时表

• 下载源代码 - 46.6 KB

引言

您是否曾经希望使用 LINQ-To-SQL 来使用临时 SQL 表，并且发现没有直接内置的支持来处理临时表？请继续阅读，了解如何使用 Visual Studio DBML 设计器创建的 DataContext 处理临时表。

背景

多年来，我一直使用 LINQ-To-SQL 来满足我的大部分数据库需求。偶尔，需要从外部来源创建临时表，以便我可以转而将数据连接到数据库中的其他表。当需要出现时，我不得不放弃使用 LINQ-To-SQL 并退回到原生 SQL 支持。我最终找到了一种利用 LINQ 满足我的临时表需求的方法。我想与那些可能需要处理临时表的人分享我的发现。

使用代码

我附带了一个带有两个项目的 Visual Studio 2017 解决方案。第一个项目进行了简化，演示了使用 LINQ-To-Sql 使用临时表所需的最小代码量。它假设您将使用 .NET 提供的 SQL 类构建临时表，并且仅使用 DataContext 从临时表读取数据。

第二个项目是第一个项目的扩展，提供了一些辅助方法，这些方法允许您使用 DataContext 来创建和填充临时表。

请注意，在创建示例项目时，我假设临时表唯一需要的功能是创建表和创建后从表中读取数据的能力。我没有考虑更新临时表中现有记录的可能性。虽然这是可能的，但我没有提供执行记录级更新所需的代码。

创建临时表支持的第一步是向上下文定义表。我创建了一个单独的源文件，定义了一个部分类，该类扩展了 Visual Studio 设计器创建的 .DBML 对象。设计器生成的类是“TestDatabaseDataContext”。我正在创建一个将被上下文引用的表，其名称为“TempTable”。

public partial class TestDatabaseDataContext
{
    // The folling defines the temporary table to the context
    public System.Data.Linq.Table<TempTable> TempTables
    {
        get
        {
            return this.GetTable<TempTable>();
        }
    }
}

下一步是定义临时表，并告知上下文该表在数据库中的名称，即“#TempTable”。您会注意到，为了使示例代码保持最少，我只创建了一个列。

// The following attribute gives a name to the temporary table
[global::System.Data.Linq.Mapping.TableAttribute(Name = "#TempTable")
public partial class TempTable
{
    private string _column1;
    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_column1", DbType = "VarChar(50)")]
    public string Column1 { get { return _column1; } set { _column1 = value; } }
}

就是这样！您已经定义了一个名为 TempTable 的表，可以使用生成的 DataContext 引用它。上面显示的所有代码都可以在项目“LinqToSqlBasics”中的名为“TestDatabase_PartialClass.cs”的源文件中找到。

其余代码演示了如何使用临时表。以下代码可以在同一项目的 Form1.cs 文件中找到。

临时表仅在与数据库的连接打开时存在。当连接关闭时，临时表将被删除。当您使用 DataContext 对象时，每次执行 SQL 查询时都会打开和关闭连接。为了将临时表与 DataContext 对象一起使用，您必须确保与数据库的连接保持打开状态。为此，您必须在实例化上下文时向构造函数传递一个已经打开的连接。以下代码片段演示了这一点。

// Create and open connection to the database
SqlConnection theConnection = new SqlConnection(Properties.Settings.Default.TestDatabaseConnectionString);
theConnection.Open();

// Create the context
TestDatabaseDataContext theContext = new TestDatabaseDataContext(theConnection);

我们已经实例化了上下文，并且已经与数据库建立了开放连接。现在我们将创建临时表并定义我们在上面的代码片段中创建的列。

// Create the temporary table.
string createTableCommand = "CREATE TABLE #TempTable " +
                            "( " +
                            "   [Column1][varchar](50) NULL, " +
                            ") ";
theContext.ExecuteCommand(createTableCommand);

使用一些数据填充表。请注意，我将使用 DataTable 和一个 SQlBulkCopy 对象将临时表加载到数据库中。

// Populate the temporary table with data from our external source 
DataTable theTable = new DataTable("TempTable");
DataColumn theColumn = new DataColumn();
theColumn.DataType = typeof(string);
theColumn.ColumnName = "Column1";
theTable.Columns.Add(theColumn);

DataRow row1 = theTable.NewRow();
row1[0] = "Value1";
theTable.Rows.Add(row1);

DataRow row2 = theTable.NewRow();
row2[0] = "Value2";
theTable.Rows.Add(row2);

SqlBulkCopy bulkCopy = new SqlBulkCopy(theConnection);
bulkCopy.DestinationTableName = "#TempTable";
bulkCopy.WriteToServer(theTable);

bulkCopy.Close();
theTable.Dispose();

通过从临时表中读取数据并将其连接到数据库中的永久表之一来使用上下文。

// Just to show that our records are there
var tempRecords = (from tempRecord in theContext.TempTables
                   select tempRecord).ToList();

// Read the permanent table with data joined to our temporary table
var joinedRecords = (from tempRecord in theContext.TempTables
                     join permRecord in theContext.PermanentTables
                        on tempRecord.Column1 equals permRecord.Column2
                     select permRecord).ToList();

theContext.Dispose();
theConnection.Close();
theConnection.Dispose();

关注点

以上所有代码都是启用您使用 DataContext 处理临时表所需的最小代码量。虽然临时表必须从上下文中外部生成，但您将能够使用上下文读取/连接到该表。附加解决方案中的第二个示例项目 LinqToSqlInsertSupport 扩展了第一个示例项目，并演示了如何设置上下文以允许使用上下文将记录插入到临时表中。我包含了两个静态辅助方法：TestDatabaseDataContext.OpenTheContextCreateTempTable() 和 TempTable.CreateTempTable()。