Dailycode.info

Short solution for short problems

How to get a dataset with variable SQL from the entity framework

When you are working with the EF, you will finally get to the point that you need some joined data from the database that could be based on variable SQL queries. In our case we can define SQL queries in the database that will decide what to show in grids. Since this data is combining several tables from a non relational DB, its not returning entity data. So I found a good way to get a dataset and still use the entity framework connection. So no new connection logic to be instantiated:

 

public DataSet ExecuteStoreQuery(string commandText)

{

    DataSet retVal = new DataSet();

    EntityConnection entityConn = (EntityConnection)MyContext.Connection;

    SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection;

    SqlCommand cmdReport = new SqlCommand(commandText, sqlConn);

    SqlDataAdapter daReport = new SqlDataAdapter(cmdReport);

    using (cmdReport)

    {

        cmdReport.CommandType = CommandType.Text;

        daReport.Fill(retVal);

    }                  



    return retVal;

}

 

You should only use this for view purposes only!!!


Generic List Intersect VS LINQ Query

 

I needed to intersect 2 Generic List with some class objects in the lists. The 2 generic lists had the same type. The objects were self-tracking entities, that is why (I guess) the Intersect would work using the default comparer. The definition of the intersect without writing a custom comparer is: Produces the set intersection of two sequences by using the default equality comparer to compare values. So I tried a different solution using the LINQ query. Just joining the 2 lists.

List<MyEnt> favEnts = PL.GetFavoritEnts(UserID).ToList();

List<MyEnt> allEnts = PL.GetAllEnts().ToList();

 

First the intersect that wouldn’t work:, it returned 0 Ents in the returned list.

dataSource = allEnts.Intersect(favEnts).ToList();

 

Next the solution that worked fine:

var query = from a in allEnts

            join b in favEnts on a.EntID equals b.entID

            select a;

dataSource = query.ToList();

 

The query.ToList() return a list of type List<MyEnt>


Addressing tables that are not in the EDMX model.

When you are working with the entity framework, it’s no problem to use non referenced tables that belong to the same database. By using the ExecuteStoredQuery function, you can provide an SQL script. Keep in mind that the returned data needs to be mapped to your entity. Here a small example:

public Clients GetClientForPlant(string plantID)

{

    return Ents.ExecuteStoreQuery< Clients>("select * from Clients c inner join Plants p on c.Client = p.Client and p.Plant = @p0", new SqlParameter() { ParameterName = "p0", Value = plantID }).First();

}


Google maps

If you want to visualize coordinates, addresses, etc... it is easy to use Google maps.

There are 2 ways to implement Google Maps.

1 Use the Google API in your site.

You need to do 2 things to implement this in your site.

Use this code on the page you want to show the google map:

This key is good for all URLs in this directory:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>Google Maps JavaScript API Example</title>
    <script src="http://maps.google.com/maps?file=api&amp;v=2&amp;key=ABQIAAAAoVpHiXx5oCNbL2RPHM9crRSqaiXgH3

3Hm6zw0dQ5AqOYjNtLhxRvxHiU6KhHxpQSMtprF_I0ysRbIg"
      type="text/javascript"></script>
    <script type="text/javascript">
    //<![CDATA[
    function load() {
      if (GBrowserIsCompatible()) {
        var map = new GMap2(document.getElementById("map"));
        map.setCenter(new GLatLng(37.4419, -122.1419), 13);
      }
    }
    //]]>
    </script>
  </head>
  <body onload="load()" onunload="GUnload()">
    <div id="map" style="width: 500px; height: 300px"></div>
  </body>
</html>

Check out the API documentation for more information.

* Request a key at: http://www.google.com/apis/maps/signup.html. For testing you can fill in the localhost and use that key, don't forget to change this once in production.

 

2 Show the map in a pop-up or Iframe using url reference.

You can link the page to this url: http://maps.google.com/maps?spn=0.030372,0.068665&z=6&t=h&vp=53.859462,-3.038235 and use following query string parameters after the link:

q= Query - anything passed in the q parameter is treated as if it had been typed into the query box on the maps.google.com page. In particular:
a precise address is looked up and a marker and info box is displayed at the specified point
a town name, or the first half of a postcode causes the region to be displayed with to marker or info box
an exact location can be specified as latitude,longitude, in decimal form (52.123N,2.456W or 52.123,-2.456) or as degrees, minutes and seconds (52 7 22.8N,2 27 21.6W or 52 7 22.8,-2 27 21.6) or as degrees and minutes (52 7.38N,2 27.36W or 52 7.38,-2 27.36).
any text added in parentheses () is displayed in the info window and sidebar in bold, there appears to be no way to cause a line break, but you can use %A0 as a "no break space" to prevent line breaks happening where you don't want them.
the words "to" and "from" cause the driving directions function to be activated.
the word "near" or "loc:" causes the local search function to be activated
space-ampersand-space (use +%26+ or %20%26%20 in URLs, not +&+ or +&amp;+ otherwise the ampersand gets treated as a separator) can be used between the names of two streets to specify an intersection.
A location can also be specified after an at sign, in decimal form (@52.123,-2.456). This gives a lower zoom level than using the same values without the at sign. Using this format interferes with other options of the query parameter. The @ sign instead of "near" or "loc:" tends to cause only a single business result to be displayed, compare (Church near Hallows Road Blackpool, Lancashire) which returns over 1000 results to (Church @ Hallows Road Blackpool, Lancashire) which returns one result.
The full URL of a Google Earth .KML/.KMZ file or a RSS feed that includes GeoRSS data, can be used, and the contained information will be displayed on the map.
ll= Latitude,longitude of map centre - Note the order. Only decimal format is accepted.
If this is used without a query, then the map is centred at the point but no marker or info window is displayed
sll= Latitude,longitude of the point from which the business search should be performed. You could use this to perform an off-centre busness search. Google use it so "link to this page" can record a map that has had the centre moved after performing a business search.
spn= Approximate lat/long span. The zoom level will be adjusted to fit if there's no &z parameter.
sspn= ?? lat/long of... what?
hl= Host language - only a few languages are supported, e.g. &hl=fr for French.
t= Map Type. The available options are "m" map, "k" satellite, "h" hybrid
saddr= Source address. Use this when asking for driving directions
any text added in brackets is displayed in the sidebar in bold
daddr= Destination address(es). Use this when asking for driving directions
any text added in brackets is displayed in the sidebar in bold
"+to:" clauses can be appended to the destination to request multiple destination routing, like this "&dadr=Blackpool+to:Manchester+to:Leeds"
text in brackets can also be added to the "+to:" clauses.
mrad= Additional destination address.
If you've got three points in your trip you can use &saddr, &daddr and &mrad instead of "+to:" clauses.
start= Skips the first (start-1) matches
num= Display, at most, this number of matches. The valid range is 0 to 10 (but 0 is a bit pointless).
near= Can be used as the location part of a query instead of putting the whole thing into &q.
f= Controls the style of query form to be displayed. &f=d displays the "directions" form (two input boxes: from, to), &f=l displays the "local" form (two input boxes: what, where). Otherwise the default search form is displayed (single input).
output=html Uses the old style Google Local page format from before it merged with Google Maps, with the small map and large sidebar.
output=js Outputs JavaScript object literals and function calls used by Google Maps, including encoded polyline data for driving directions, and stage information in HTML format.
output=kml Outputs a KML file containing full Placemark information representing the current map.
output=nl Outputs a small KML file containing a NetworkLink wrapper linking to a URL from which Google Earth and Google Maps can obtain the Placemark information.
latlng= This is a weird one. It takes three numbers separated by commas. The first two numbers (presumably representing latitude and longitude multiplied by 1000000) are ignored. The third number seems to be a Google internal "Company ID" number for a particular business. E.g. &latlng=0,0,14944637421527611642 represents Blackpool Community Church. Specifying this parameter performs a Google Search for pages that reference that business, and displays a tiny map. Other parameters, in particular &q, must have valid contents (but need not relate to the target business) for this to work.
cid= Similar to latlng, but generating a different map size. It takes three numbers separated by commas. The first two numbers (presumably representing latitude and longitude multiplied by 1000000) are ignored. The third number seems to be a Google internal "Company ID" number for a particular business. E.g. &cid=0,0,14944637421527611642 represents Blackpool Community Church. Specifying this parameter displays a large map of the identified company location. Other parameters, in particular &q, must have valid contents (but need not relate to the target business) for this to work.
vp= The presence of this parameter causes maps.google.com to switch into Copyright Service mode. Instead of returning the html that draws a map, it returns information about the copyright ownership in Javascript format. The &vp parameter specifies the viewpoint (i.e. the centre of the map). Copyright Service only works when the &spn and &z parameters are also supplied, indicating the span and the zoom. Optional parameters are &t, which specifies the map type, and &key which specifies the API key of the site performing the request. E.g. http://maps.google.com/maps?spn=0.030372,0.068665&z=6&t=h&vp=53.859462,-3.038235
om= The presence of this parameter with a value other than 1 causes the overview map to be closed. If the parameter is omitted, or present with the value 1, then the overview map is open.
ie= Can be used to specify the character set. e.g. &ie=UTF8.
pw= Activates print mode and initiates printing. There seems to be a problem at the moment with &pw=1, but using settings like &pw=2 is OK.
z= Sets the zoom level.
iwloc= Specifies where the infowindow will be displayed. In a business search &iwloc=A to &iwloc=J will open the info window over the corresponding business marker, and &iwloc=near will place it over the big green arrow if that's currently displayed. &iwloc=addr can be used on map search to explicitly request the info window to be open on the address, but that's the default anyway. Directions search supports &iwloc=start, &iwloc=end and &iwloc=pause1 etc.
layer=t Activates the traffic overlay
msa=b Activates the "My Maps" sidebar when used in conjunction with "maps.google.com/ms". It does nothing without the "/ms" and "/ms" does nothing without the "&msa=b".
msa=0 Involved in My Maps processing. Possibly specifies the My Maps server number.
msid= Specifies a My Maps identifier. When used in conjunction with "maps.google.com/ms" and &msa=0, the corresponding My Map is displayed.
mrt=kmlkmz Skips the normal search and goes directly to User-Created Content. Only works if the query contains a location and something to search for. E.g. q=hotel&near=london&mrt=kmlkmz or q=hotel+in+london&mrt=kmlkmz.
view= Can be used to select text view (view=text) or the normal map view (view=map)
dirflg=h Switches on "Avoid Highways" route finding mode.
Retrieved from "http://mapki.com/wiki/Google_Map_Parameters"