I came accross some issue when I had to bind a data coming from Table to a Dropdownlist using Linq to SQL data access, in this example I’m going to show you, we’ll use U.S. States as a prime subject for this tutorial. Let’s get started!
Let’s say you have a table with 2 fields
ID | State
Before you begin to call the following fields from your Linq statements , prepare a class object that you can use to hold or handle the data for instance in this example we can do the following:
Table variables was introduced by Microsoft in SQL Server 2000 as an alternative way to using temporary tables.
In most cases table variable outperforms temp table, in this post we will try to review each strength and weaknesses.
Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example:
As you may have theorize Table variables can be used in batches, stored procedures, and user-defined functions (UDFs). We can also UPDATE records in our table variable as well as DELETE records, here is an example;
SET Revenue = Revenue * 1.15
WHERE ProductID = 62
WHERE ProductID = 60
SELECT TOP 5 *FROM @ProductTotals
ORDERBY Revenue DESCSET @INDEX= CHARINDEX(@delimiter , @text)IF(@INDEX= 0)AND(LEN(@text)> 0)
INSERTINTO @Strings VALUES(@text)
INSERTINTO @Strings VALUES(LEFT(@text, @INDEX- 1))SET @text =RIGHT(@text,(LEN(@text)- @INDEX))
SET @text =RIGHT(@text,(LEN(@text)- @INDEX))
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
Personally, I find it easy to use temp table for quick and dirty solution but for more complex queries, especially reports, table variable comes in handy, as programmer you pick the right solution for your needs.