LINQ Binding to Dropdownlist ASP.NET

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
— —–
1        AZ
2        CA
3        WA

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:

Public Class StateObj
{
public int sID {get;set;}
public string stateName {set;set;}
}

Now that we got that taken care of, it will be ready once we pass the value. So let’s build our query in Linq. Let’s create an object that extract the data.

YourDataContext db = new YourDataContext
protected List GetState()
{
List result = db.select (s = > new StateObj
{ s.sID = ID, s.stateName = State
}
).toList();
return result;
}

So after you’ve done this, all you have to do now is bind that method to your dropdownlist , let’s call our dropdownlist “ddl_state”;

ddl_state.DataSource = GetState();
ddl_state.DataBind();
ddl_state.DataTextField = “sID”;
ddl_state.DataValueField =”stateName”;

And there you are, you’re all set when you run this it will show you a dropdownlist containing the state description if you view the source html would look like this:

<input type='Dropdownlist' id='ddl_state'>
<option value='1'>AZ</option>
<option value='2'>CA</option>
<option value='3'>WA</option>
</input>

I hope this short tutorial will help you with this needs.

Happy Programming!~

Using Table Variable in SQL

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:

DECLARE @ProductTotals TABLE 
(
  ProductID int, 
  Revenue money
)

Let’s try, connect to your Northwind data-base (as my favorite test ground db), and write the following SELECT statement to populate the table variable.

INSERT INTO @ProductTotals (ProductID, Revenue)
  SELECT ProductID, SUM(UnitPrice * Quantity)
    FROM [ORDER Details]
    GROUP BY ProductID

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;

UPDATE @ProductTotals
  SET Revenue = Revenue * 1.15
WHERE ProductID = 62
 
DELETE FROM @ProductTotals
WHERE ProductID = 60
 
SELECT TOP 5 * 
FROM @ProductTotals
ORDER BY Revenue DESC
    SET @INDEX = CHARINDEX(@delimiter , @text)  
    IF (@INDEX = 0) AND (LEN(@text) > 0)  
      BEGIN   
        INSERT INTO @Strings VALUES (@text)
          BREAK  
      END  
    IF (@INDEX > 1)  
      BEGIN   
        INSERT INTO @Strings VALUES (LEFT(@text, @INDEX - 1))   
        SET @text = RIGHT(@text, (LEN(@text) - @INDEX))  
      END  
    ELSE 
      SET @text = RIGHT(@text, (LEN(@text) - @INDEX)) 
    END
  RETURN
END
 
GO
 
SET QUOTED_IDENTIFIER OFF 
GO
 
SET ANSI_NULLS ON 
GO

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.