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

Answer 1

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

Answer 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.

Answer 3

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.