switch statement in linq

Question

My code for sql connection using linq is:

var query1 = from u in dc.Usage_Computers
          where u.DomainUser == s3
          select u; // selects all feilds from table

GridView1.DataSource = query1;
GridView1.DataBind();

I have a field called "Operation" in the table "Domainuser" which has values like "1, 2, 3". When I populate these values to data grid I wanted to convert them to meaningful values like if the value of Operation is 1 then display in datagrid as "logon", if 2 then "logoff" etc...

How do i assign values for them after retrieving from database?

Answer 1

I've done something similar using TemplateFields. Using an ASP:Label bound to the property and adding an OnPreRender event handler for the control. In the event handler for the control I translate the text based on it's current value and set the new value:

protected void label_OnPreRender( object sender, EventArgs e )
{
   Label l = (Label)sender;
   switch (l.Text) {
      case "1":
         l.Text = "Logon";
         break;
       ...
      default:
         break;
   }
}

If the form is in edit mode, you'll need to handle it differently. You'll also probably need to add handlers for Inserting and Updating to the View control you are using to translate the data supplied by the page into its database representation.

Answer 2

I wanted a code for switch case for linq sql in datagrid. Not label.

Answer 3

Use a template field in your gridview:

<asp:GridView ID="gvDomain" runat="server" OnRowDataBound="gvDomain_RowDataBound">
    <Columns>
        <asp:TemplateField>
             <HeaderTemplate>
                 Operation
             </HeaderTemplate>
             <ItemTemplate>
                 <asp:Label id="lblLogon" runat="server" />
             </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Then use the gridviews RowDataBound event to discover the label and assign its text:

Protected Sub gvDomain_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvStates.RowDataBound
    Dim lblLogon As Label = DirectCast(e.Row.FindControl("lblLogon"), Label)
    Dim drv As DataRowView = DirectCast(e.Row.DataItem, DataRowView)

    If lblLogon IsNot Nothing Then
        Select Case drv("Operation").ToString()
            Case "1" 
                lblLogon.Text = "Logon"
                Break
            Case "2"
                lblLogon.Text = "Logoff"
                Break
            //etc...
        End Select
    End If
End Sub

Answer 4

static Func<int?, string> MapSqlIntToArbitraryLabel = (i =>
{
   // for performance, abstract this reference 
   //  dictionary out to a static property
   Dictionary<int, string> labels = new Dictionary<int, string>();
   labels.Add(1, "logon");
   labels.Add(2, "logoff");
   labels.Add(...);

   if (i == null) throw new ArgumentNullException();
   if (i < 1 || i > labels.Count) throw new ArgumentOutOfRangeException();

   return labels.Where(x => x.Key == i.Value)
                .Select(x.Value)
                .Single();
}

that return statement can also be expressed as:

return (from kvp in labels
        where kvp.Key == i.Value
        select kvp.Value).Single();

Then you can use call that function from your linq query like so:

var query1 = from u in dc.Usage_Computers 
             where u.DomainUser == s3 
             select {
                 Operation = MapSqlIntToArbitraryLabel(u.Operation)
                 // add other properties to this anonymous type as needed
             };

I've tried every suggested method of fooling Linq2Sql into running my code and this method is the only one that i've found that allows me to run code as part of a deferred-execution projection.

Answer 5

This technique does not seem particularly applicable to your problem, but here it is anyway.

You can create a SQL case statement in LinqToSql by using the C# ? : operator.

var query1 =
  from u in dc.Usage_Computers
  where u.DomainUser == s3
  select new {usage = u, 
    operation =
      u.DomainUser.Operation == 1 ? "login" :
      u.DomainUser.Operation == 2 ? "logoff" :
      "something else"
  };

Answer 6

Thanks David..Your method worked for me!