Using LINQ to SQL in ASP.NET

Question

Up till now most of the web apps Ive built have an Oracle database behind them, so when fetching or updating data, Id pass search variables to a class to build the SQL, open a connection and create a DataTable to pass back to the form which I then bind to a gridview - easy stuff.

I'm now using Linq for a SQL Server database. I can easily build this up and get it working on a button_click event (code below), but if I were to pass the variables to a separate class to do this so that it not running from code-behind, what exactly is it Im returning back to the form?

Hope that makes sense - I'm basically wanting to do whats below but not in code-behind.

ASB_DataDataContext adb = new ASB_DataDataContext();
var qryAdb = from asb in adb.Inputts
             select new
             {
                 NRTno = asb.NRT_No,
                 PerpSurname = asb.Allg_Perp_Surname,
                 PerpSteet = asb.Allg_Perp_Street,
                 HouseNo = asb.Allg_Perp_House_No,
                 Complainer = asb.Complainant_Surname,
                 Complainer_Street = asb.Complainant_Street
             };

if (txtCompSurname.Text != "")
    qryAdb = qryAdb.Where(asb => asb.Complainer == txtCompSurname.Text);
if (txtCompStreet.Text != "")
    qryAdb = qryAdb.Where(asb => asb.Complainer_Street == txtCompStreet.Text);
if (txtPerpSurname.Text != "")
    qryAdb = qryAdb.Where(asb => asb.PerpSurname == txtPerpSurname.Text);
if (txtPerpStreet.Text != "")
    qryAdb = qryAdb.Where(asb => asb.PerpSteet == txtPerpStreet.Text);
if (txtNrtNo.Text != "")
    qryAdb = qryAdb.Where(asb => asb.NRTno == Convert.ToInt32(txtNrtNo.Text));


gvResults.DataSource = qryAdb.Select(o => new { o.NRTno, o.PerpSurname, o.PerpSteet, o.HouseNo, o.Complainer, o.Complainer_Street });
gvResults.DataBind();

not in code behind? so as parameters to a stored procedure? You are not bound to Linq2SQL by the way. You can still use SqlCommand and use DataTableobjects to bind to gridviews etc. — JP Hellemons
– JP Hellemons, Commented Jun 22, 2012 at 9:26

Hans Keﬆing · Accepted Answer · 2012-06-22 09:49:06Z

2

Create a POCO class for whatever it is you are getting back:

public class Person
{
    public NRTNo string {get; set;}
    ....
}

Select into this class in your LINQ (note the Person class name after select new):

var qryAdb = (from asb in adb.Inputts
                          select new Person
                          {
                              NRTno = asb.NRT_No,
                              PerpSurname = asb.Allg_Perp_Surname,
                              PerpSteet = asb.Allg_Perp_Street,
                              HouseNo = asb.Allg_Perp_House_No,
                              Complainer = asb.Complainant_Surname,
                              Complainer_Street = asb.Complainant_Street
                          });

Do what you are already doing with the query:

            if (txtCompSurname.Text != "")
                qryAdb = qryAdb.Where(asb => asb.Complainer == txtCompSurname.Text);
            if (txtCompStreet.Text != "")
                qryAdb = qryAdb.Where(asb => asb.Complainer_Street == txtCompStreet.Text);
            if (txtPerpSurname.Text != "")
                qryAdb = qryAdb.Where(asb => asb.PerpSurname == txtPerpSurname.Text);
            if (txtPerpStreet.Text != "")
                qryAdb = qryAdb.Where(asb => asb.PerpSteet == txtPerpStreet.Text);
            if (txtNrtNo.Text != "")
                qryAdb = qryAdb.Where(asb => asb.NRTno == Convert.ToInt32(txtNrtNo.Text));

Now return a list of your POCO objects:

return qryAdb.ToList<Person>();

You can now bind to this in your UI in the same way you currently bind.

edited Jun 22, 2012 at 9:49

Hans Keﬆing

39.6k10 gold badges84 silver badges119 bronze badges

answered Jun 22, 2012 at 9:38

Carl Sharman

4,8931 gold badge35 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DarkW1nter Over a year ago

Ahhh... had tried this earlier - all that I was missing was the .ToList<> - Thanks a lot!

Hans Keﬆing · Accepted Answer · 2012-06-22 09:27:20Z

2

You can't return an anonymous type from that "other class", so you need to create a "real" class for a single return value. Then you can return an IEnumerable<ReturnClass> from your method.

You can then bind against that returned value.

answered Jun 22, 2012 at 9:27

Hans Keﬆing

39.6k10 gold badges84 silver badges119 bronze badges

1 Comment

Magnus Over a year ago

You can return an anonymous type and use it for binding a grid just like with non anonymous types. I would however recommend to create a real DTO.

JP Hellemons · Accepted Answer · 2012-06-22 09:49:23Z

This is a possibility: use a webform for userinput. send the query to a class (not code behind of the form) and still write sql like you are used to. (only no-oracle syntax) and parse it to objects and return a List collection of the object. you can also return a datatable and bind that to the gridview. Hope it helps a bit.

WebForms1.aspx:

<asp:TextBox runat="server" ID="tb"></asp:TextBox>
<asp:Button runat="server" ID="btn" Text="go" OnClick="btn_OnClick"/><br/>
<asp:GridView runat="server" ID="gv"></asp:GridView>

WebForm1.aspx.cs:

protected void btn_OnClick(object sender, EventArgs e)
{
    if(!String.IsNullOrWhiteSpace(tb.Text))
    {
        Class1 cl = new Class1();
        List<Complainer> complainers = cl.SearchBySurname(tb.Text);
        gv.DataSource = complainers;
        gv.DataBind();
    }
}

Class1.cs:

public class Class1
{
    internal List<Complainer> SearchBySurname(string p)
    {
        SqlCommand com = new SqlCommand("select * from Complainers where surname = @sur ");
        com.Parameters.AddWithValue("sur", p);
        List<Complainer> complainers = new List<Complainer>();
        using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["yourconstring"].ToString()))
        {
            con.Open();
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);

            foreach(DataRow dr in dt.Rows)
            {
                Complainer c = new Complainer(dr);
                complainers.Add(c);
            }
        }
        return complainers;
    }
}

Complainer.cs:

public class Complainer
{
    private int _id;

    public Complainer(System.Data.DataRow dr)
    {
        // TODO: Complete member initialization
        _id = Convert.ToInt32(dr["id"]);
    }
}

edit put the sample project on dropbox: https://www.dropbox.com/s/l7clhlvgdk2gul3/WebApplication2.zip

Collectives™ on Stack Overflow

Using LINQ to SQL in ASP.NET

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related