New Posts New Posts RSS Feed: how to select more than one columns?
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

how to select more than one columns?
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
monkeyking View Drop Down
Groupie
Groupie
Avatar

Joined: 04-Mar-2009
Location: brisbane
Posts: 68 		Post Options Post Options   Quote monkeyking Quote  Post ReplyReply Direct Link To This Post Topic: how to select more than one columns?
    Posted: 14-Apr-2009 at 1:16am
I want to select two or three columns (properties) in the data entities, how to do that?

Secondly how can i bind devforce entities with Datagridview?
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715 		Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 14-Apr-2009 at 3:29pm
We can select more than one column in a DataGridView or a DevExpress XtraGrid or an Infragistics or UltraGrid using the mouse and/or cursor keys.  This is easy enough to do.  What is more complicated is developing an application that can handle the multiple selections.
 
We have purposely kept the Learning Units simple enough so that we do not deal withe additional complexities of multiple selections.
Back to Top
monkeyking View Drop Down
Groupie
Groupie
Avatar

Joined: 04-Mar-2009
Location: brisbane
Posts: 68 		Post Options Post Options   Quote monkeyking Quote  Post ReplyReply Direct Link To This Post Posted: 14-Apr-2009 at 4:37pm
May be I didn't say clearly. what i mean is the columns in DB rather than Datagridview's columns. in DB, it has many datatables which contain columns as well. Devforce uses entities to deal with DB, but how can i select two or three columns in DB by using Devforce EF? 
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715 		Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 14-Apr-2009 at 4:48pm
Well maybe, I don't understand your problem.  I would think that you are making the selection in the grid (DataGridView or Devex XtraGrid or Infragistics UltraGrid).  For example, you can make your selection using the mouse,  By looking at the properties of the grid, you can tell which columns and rows are selected.
Back to Top
monkeyking View Drop Down
Groupie
Groupie
Avatar

Joined: 04-Mar-2009
Location: brisbane
Posts: 68 		Post Options Post Options   Quote monkeyking Quote  Post ReplyReply Direct Link To This Post Posted: 14-Apr-2009 at 7:04pm
from the pic below you can see that i was trying to query the stock table in DB. now, the problem is i can't select two columns, i only be able to determine which rows i'm going to selece by ".where(o=> o.STOCK_CODE.Equals(id);". supose i want to only select STOCK_CODE and STOCK_DESC these two columns, how can i achieve that before using 'foreach' loop? I don't want select whole stock table first and get values from that, i want to select two columns in the beginning (in the 'query' viriable).
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 15-Apr-2009 at 9:50am
You can add a projection to your query to return only the columns you're interested in.  Something like this:
 
var query = entityManager.Stocks
 .Where(o => o.STOCK_CODE.Equals(id)
 .Select(o => new { o.STOCK_CODE, o.DESCRIPTION });
 
Back to Top
monkeyking View Drop Down
Groupie
Groupie
Avatar

Joined: 04-Mar-2009
Location: brisbane
Posts: 68 		Post Options Post Options   Quote monkeyking Quote  Post ReplyReply Direct Link To This Post Posted: 16-Apr-2009 at 4:22pm
Thanks Kimj

your solution seems right, but another issue arise. once i select two columns (properties), then foreach loop would have difficulty to access each data row, because the wraper class generated by devforce doesn't match the data in 'query'.



in this example 'Stock' which is the wraper class doesn't contain the same properties with the data in 'query' as only two properties have been selected (STOCK_CODE and BUY_UNIT), so how to resolve this issue?

btw, once i remove the 'Select' statement, the program can run, because 'Stock' can match the data in 'query'
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 17-Apr-2009 at 11:10am

When you use a projection an anonymous type containing only the requested properties is returned, not the entity type.  You can still databind to that anonymous type (except in Silverlight, where DevForce supplies a method to turn an anonymous type into a dynamic type for databinding).  From your prior post I thought you wanted to select only a subset of columns, which is why I showed the projection query.  If you instead want your entity types to be a subset of the database columns, look into using a view to back the entity type.

Here's some information on anonymous types, if you're interested -
Back to Top
monkeyking View Drop Down
Groupie
Groupie
Avatar

Joined: 04-Mar-2009
Location: brisbane
Posts: 68 		Post Options Post Options   Quote monkeyking Quote  Post ReplyReply Direct Link To This Post Posted: 19-Apr-2009 at 9:13pm
thx kimj, that works
Back to Top
pucsoftware View Drop Down
Groupie
Groupie
Avatar

Joined: 15-Apr-2008
Location: United States
Posts: 46 		Post Options Post Options   Quote pucsoftware Quote  Post ReplyReply Direct Link To This Post Posted: 19-May-2009 at 3:03pm
I'm using a PassthruEsqlQuery to return all the columns in a table. I'm working on a form for users to query the table using multiple criteria so I plan on building a SQL statement with the appropriate WHERE clause. However, I'm just getting started and am getting an error. It seems that the SELECT * is giving me an error? Here is my code:
 
string SQL = "";
SQL = "SELECT * FROM dbo.ST_STAP_VOUCHER WHERE" +
" ST_VOUCHER_NO LIKE '" + this.txtVoucherNo.Text + "%'";
      
var query = new PassthruEsqlQuery(typeof(StapVoucher), SQL);
var results = mPersMgr.ExecuteQuery(query);
mStapVoucher.ReplaceRange((BindableList<StapVoucher>) results);
 
The error is: The query value is not valid near term '*'
 
Am I doing something wrong? What is causing this?
puctx
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 19-May-2009 at 3:15pm
The PassthruEsqlQuery allows you to pass an Entity SQL query through to the Entity Framework, not a SQL query to the database.  Entity SQL doesn't actually allow the "*" syntax.  Here's a link with information on the differences - http://msdn.microsoft.com/en-us/library/bb738573.aspx.
Back to Top
pucsoftware View Drop Down
Groupie
Groupie
Avatar

Joined: 15-Apr-2008
Location: United States
Posts: 46 		Post Options Post Options   Quote pucsoftware Quote  Post ReplyReply Direct Link To This Post Posted: 20-May-2009 at 6:59am
Okay,  thanks for the link. I guess there's more to tackling LINQ and DEVFORCE EF than I originally thought as I am still new to both. So does this mean that I would refer to the Entity objects field/property names and not the actual table and field names in the database?
 
Otherwise Use:
 
Select VoucherNo, FirstName, etc.. FROM StapVoucher  (entity mapped names)
 
Instead of this:
SELECT ST_VOUCHER_NO, ST_FIRST_NAME FROM ST_STAP_VOUCHER  (actual table and field names)
 
????
 
Also, if this is the case, is there any easy way of retrieving this list of property names other than retyping them in a long string. SQL Management Studio will generate a select statement for example. Do you have any examples that may show a function that loops through all the properties and return this string?
 
Are there any DevForce object/method that will accept a standard SQL statement and populate my entity objects? I don't have DevForce classic installed only DevForce EF. The current application I'm working on allows some advanced user to modify the SQL as they want. As they aren't programmers, I don't think they would like the idea of learning LINQ so I'd like to maintain this feature for searching if possible, but without having to mix DevForce Classic and EF.
 
Thanks for your help
puctx
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 20-May-2009 at 10:18am
Originally posted by pucsoftware

So does this mean that I would refer to the Entity objects field/property names and not the actual table and field names in the database?
 
Yes, you need to use the names as defined in the "conceptual" model.  To do a * query here, you can do:
 
   SELECT VALUE v FROM StapVoucher AS v
 
Originally posted by pucsoftware

Also, if this is the case, is there any easy way of retrieving this list of property names other than retyping them in a long string.
 
You can look at the EntityMetadata for the entity, which you can retrieve like this:
 
   EntityMetadata metadata = EntityMetadataStore.Instance.GetEntityMetadata(entityType);
 
From that, you can look at DataProperties, KeyProperties, etc.
 
Originally posted by pucsoftware

Are there any DevForce object/method that will accept a standard SQL statement and populate my entity objects?
 
No, I'm afraid not.
Back to Top
pucsoftware View Drop Down
Groupie
Groupie
Avatar

Joined: 15-Apr-2008
Location: United States
Posts: 46 		Post Options Post Options   Quote pucsoftware Quote  Post ReplyReply Direct Link To This Post Posted: 20-May-2009 at 10:38am
Thanks for the info. I had already started working on this but I'm still getting an error.
 
[CODE FOR SEARCH]

SQL = "SELECT VALUE c FROM StapVouchers AS c WHERE (c.VoucherNo.ToLower().StartsWith(" + (char)34 + this.txtVoucherNo.Text + (char)34 + "))";

txtSQL.Text = SQL;

var query = new PassthruEsqlQuery(typeof(StapVoucher), SQL);

var results = mPersMgr.ExecuteQuery(query);

mStapVoucher.ReplaceRange((BindableList<StapVoucher>) results);

[RESOLVES TO]
SELECT VALUE c FROM StapVouchers AS c WHERE (c.VoucherNo.ToLower().StartsWith("123"))
 
[ERROR]
'StapVouchers' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier, line 1, column 21.
 
What is this saying? Do you see what is wrong?
puctx
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 20-May-2009 at 11:13am
"StapVouchers" must be defined as an entity set within the entity model.  Translated, this means you'd expect to see a property on your DomainModelEntityManager called "StapVouchers".  If you instead see a "StapVoucher" property, then that's the name to use.  If everything looks OK and you do have a StapVouchers property, then check that your EDMX and its generated code is OK, and that you're pulling in the correct EDMX resources at runtime.
 
Even with this change I don't think the query will works as it stands without a few more modifications.  These string functions aren't directly supported in Entity SQL syntax, so you need something (more SQL-like) here:
 
  string sql = "SELECT VALUE c FROM StapVouchers AS c WHERE ToLower(c.VoucherNo) LIKE \"123%\"";
 
The Entity SQL Language Reference here - http://msdn.microsoft.com/en-us/library/bb399560.aspx - is a good resource.
Back to Top
pucsoftware View Drop Down
Groupie
Groupie
Avatar

Joined: 15-Apr-2008
Location: United States
Posts: 46 		Post Options Post Options   Quote pucsoftware Quote  Post ReplyReply Direct Link To This Post Posted: 20-May-2009 at 12:02pm

Thanks for your help. I got this working now.

puctx
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down