Reputation: 2861
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();
Upvotes: 0
Views: 1544
Reputation: 6057
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
Upvotes: 0
Reputation: 4855
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.
Upvotes: 2
Reputation: 39338
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.
Upvotes: 2