PDA

View Full Version : SQL Script for Controls and LINQ



jlertle
Dec 18, 2008, 2:03 PM
All,


This is for SQL 2000 but it might work for later.


I wrote this to ease some of the repetitive tasks. This script will generate the basic defaults that I use and then I tweak to fit.


I build everything based on datatype, field length and some field naming conventions.


So far I have:
-Anchors and appropriate controls.
-LINQ "Bindings" for pull and save of data.
-GridPanel, columns and conditional LINQ where clauses.


Here is an example of how to use the conditional LINQ where clause:

protected void IndSearchCondQuery(String vIndCUSTOMERCD, String vIndFIRSTNAME, String vIndLASTNAME, String vIndEMAIL, DateTime vIndJOINDATE)
{
if (!String.IsNullOrEmpty(vIndCUSTOMERCD) || !String.IsNullOrEmpty(vIndFIRSTNAME) || !String.IsNullOrEmpty(vIndLASTNAME) || !String.IsNullOrEmpty(vIndEMAIL) || vIndJOINDATE.Year != 1)
{


CV2DataContext db = new CV2DataContext();


var query = from c in db.CUSTOMER2s
where (String.IsNullOrEmpty(vIndCUSTOMERCD) || c.CUSTOMERCD.ToString().Equals(vIndCUSTOMERCD))
&& (String.IsNullOrEmpty(vIndFIRSTNAME) || c.FIRSTNAME.Contains(vIndFIRSTNAME))
&& (String.IsNullOrEmpty(vIndLASTNAME) || c.LASTNAME.Contains(vIndLASTNAME))
&& (String.IsNullOrEmpty(vIndEMAIL) || c.EMAIL.Contains(vIndEMAIL))
&& (vIndJOINDATE.Year == 1 || c.JOINDATE >= vIndJOINDATE)
select new { c.CUSTOMERCD, c.FIRSTNAME, c.LASTNAME, c.ORGNAME, c.CITY, c.STATECD, c.PROVINCE, c.COUNTRY, c.EMAIL };


this.IndSearchStore.DataSource = query;
this.IndSearchStore.DataBind();
}


}


I call this from an AjaxEvent button click fed by text fields:


protected void IndStoreBind(object sender, AjaxEventArgs e)
{
IndSearchCondQuery(this.tfCustCD.Text, this.tfIndFirst.Text, this.tfIndLast.Text, this.tfIndEmail.Text, , this.dfIndJoinDate.SelectedDate);
}



Feel free to modify and share.



Hope it helps someone!



Oops.. SQL attachment not accepted. Just remove the .txt.


Oh yeah... do a find and replace on:

WHERE O.name = 'customer'
to change the table name from 'customer' to whatever.

and


'Ind' as [Abrv] is the table abbreviation I prefix my search form controls with.