Using temporary tables with LINQ-To-SQL (L2SQL)

• Download source - 46.6 KB

Introduction

Have you ever had the desire to use a temporary SQL table using LINQ-To-SQL and found that there was no direct support built in for working with temporary tables? Read on and learn how to work with temporary tables using the DataContext created by the Visual Studio DBML designer.

Background

For years now I have used LINQ-To-SQL for the majority of my database needs. On occasion there has been a need to create a temporary table with data from an external source so that I can turn around and join the data to other tables in the database. When these needs come up, I have to abort using LINQ-To-SQL and fallback to the native SQL support. I finally uncovered a way to utilize LINQ for my temporary table needs. I thought that I would share my findings with others that may have the need to work with temporary tables.

Using the code

I have attached a Visual Studio 2017 solution with two projects. The first project is stripped down demonstrating the minimum amount of code required to utilize temporary tables with LINQ-To-Sql. It assumes that you will build the temporary table using .NET provided SQL classes and will only be using the DataContext to read from the temporary table.

The second project is an extension of the first project with some helper methods provided that will allow you to use the DataContext to create and populate the temporary table.

Note that in creating the sample projects I made the assumption that the only thing needed of a temporary table was the ability to create the table and the ability to read from the table after creation. I made no allowances for updating existing records in the temporary table. Although it is possible, I did not provide the code that is needed to perform record level updates.

The first step in creating the support for a temporary table is to define the table to the context. I created a separate source file defining a partial class which expands on the Visual Studio .DBML object created by the Visual Studio designer. The class that was generated by the designer was "TestDatabaseDataContext". I am creating a table that will be referenced by the context using the name "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>();
        }
    }
}

The next step is to define the temporary table and to inform the context the name of the table as it is known to the database, "#TempTable". You will notice that I am only creating one column in the table to keep the sample code to a minimum.

// 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; } }
}

That's it! You have defined a table with the name TempTable that can be referenced using the generated DataContext. All of the code shown above can be found in the project "LinqToSqlBasics" in the source file called "TestDatabase_PartialClass.cs".

The remainder of the code demonstrates how to make use of the temporary table. The following code can be found in the Form1.cs file of the same project.

Temporary tables only exist as long as the connection to the database is open. When the connection is closed, the temporary table is deleted. When you use a DataContext object, the connection is opened and closed each time a SQL query is executed. In order to use a temporary table with a DataContext object, you must insure that the connection to the database is left open. To do this you must pass an already opened connection to the constructor when instantiating the context. The following snippet of code demonstrates this.

// 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);

We have instantiated the context and have an open connection to the database. Now we will create the temporary table and define the column that we created in the code snippet above.

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

Populate the table with some data. Note that I am going to use a DataTable and a SQlBulkCopy object to load the temporary table in the database.

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

Use the context by reading the data from the temporary table and join it to one of the permanent tables in the database.

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

Points of Interest

All of the code above is the minimum amount of code needed to enable you to use a DataContext to work with a temporary table. Although the temporary table must be generated externally from the context, you will be able to read/join against the table using the context. The second example project in the attached solution, LinqToSqlInsertSupport, expands on the first sample project and demonstrates how you can setup the context to allow for records to be inserted into the temporary table using the context. I have included two static helper methods; TestDatabaseDataContext.OpenTheContextCreateTempTable() and TempTable.CreateTempTable().