Welcome to the CodeSmith Community!

Re: Where clause with NetTiers

.netTiers

A description has not yet been added to this group.

Where clause with NetTiers

  • rated by 0 users
  • This post has 11 Replies |
  • 1 Follower
  • Hi,

    I am getting some confusion with NetTiers as I am using NetTiers with VS.Net 05:

    Actually I have to fetch out data from database with whereclause & orderbyclause. And what I found that is there are only two possibility either using "GetPaged" or "GetTotal (here we cannt use orderby clause)".

    I have started with "GetPaged" as follows:

    int count;

    string strYears = "some _column = some condition";

    string strOrder = "some_column";

    TList<XXXX> list = DataRepository.XXXXProvider.GetPaged(null, strYears, strOrder, 0, 0, out count);

    My needs are as:

    1. My column named "some_column" has some duplicate data, When I use above code, its also show all duplicates into the binding control (say dropdown list). How can I write where clause so that it can eliminate duplicates one?

    2. For binding to dropdownlist control, we needs one or two column. How should I format where clause so that I can grab only number of column that I need?

    3. Can we write "Select col1, col2,......from table........." with NetTiers where clause.

    4. How can we use GROUP BY statement into where clause?

    5. Can we use INNER JOIN in where clause statement as with Ado.Net?

    4. Is there any other way in NetTiers that can fulfill my above requirements?

    Please answer it index wise, it'll makes me more understandable.

    Thank you

  • Hi Prashant - I've tried to answer/comment on your questions below.

    1. My column named "some_column" has some duplicate data, When I use above code, its also show all duplicates into the binding control (say dropdown list). How can I write where clause so that it can eliminate duplicates one?

    This would be as expected - if you're binding to a control then I expect that either a) you want to be able to update your data, or b) you want to be able to select a record (i.e. through a combo box etc).  If this is the case, then if you've got duplicates in your table which record should be updated/selected if you're only showing DISTINCT values? 

    2. For binding to dropdownlist control, we needs one or two column. How should I format where clause so that I can grab only number of column that I need?

    I would be tempted to either a) write a custom stored proc that just selects the cols you want, or b) do the binding through code so you can tell the control which properties you want bound to the combo box.

    3. Can we write "Select col1, col2,......from table........." with NetTiers where clause.

    The WHERE clause just limits the ROWS returned from the database, not the COLUMNS (as that happens in the SELECT part of the SQL.  Again, if you want to limit what's shown/returned then I'd look at doing either a) or b) from above.

    4. How can we use GROUP BY statement into where clause?

    5. Can we use INNER JOIN in where clause statement as with Ado.Net?

    Same as above - you can't specify joins in the WHERE clause, this is in the FROM part of the SQL so you would either need to look into deepload to get what you want, or write a customer proc to return what you need.

    Hope that helps

    Martin

  • Hi Martin,

    Thanks,

    There is not any way to use NetTiers generated classes/storeprocs/..... for my above stuffs.

    First one is not much clear:

    Yeah Actually I want to bind one column (that contain duplicate records) of  table to the combo box. When I run the application (with above code), I can see duplicate records into combobox, which is not good. So where should I use DISTINCT word to eliminate them.

    Thank you

  • Hi

    You can't use the DISTINCT keyword as part of a WHERE clause.  As I say, if you want to hide the duplicates in your combo box then you're going to have to use a custom stored proc for this.  Out of interest, what are you trying to achieve by removing the duplicates? You do realise that, if you trying to let the user select a value based on this combo then it's difficult to know which value they have actually chosen if you're hiding duplicates from them? For example:

    a table contains customers as follows:

    Company A

    Company B

    Company A

     

    Now, lets pretend you've hidden the duplicates from the user and they select "Company A" - which one are they actually choosing?

    Hope that clarifies

    Martin

  • Hi Martin,

    Thanks again for your convincing feedback.

    But my need here is just on slightly different track.

    Actually I dont have to bother about the other data related to the duplicate one (that I want to hide) because just I have to pass the selected value of the combobox to build other query/method, nothing else related to this value.

    Suppose I have one table Customer_Data contains data as follows:

    Name                              Year Of Joining

    Company A                     2000

    Company B                     2004

    Company C                     2000

    Company D                     2000

    Now I want to bind "Year Of Joining" column to combobox control and as expected this combobox should have only two data 2000 and 2004 not duplicate one as having all 4 values.

    Thank you

     

  • Hi

    You could use the ExecuteDataSet method with your SQL (but make sure you keep tight control of what SQL is passed) - e.g.

    DataRepository.Provider.ExecuteDataSet(System.Data.CommandType.Text, "Select distinct CustomerName from Customers")

    Hope that helps

    Martin

  • Hi Martin,

    I have tried it but not getting my combobox filled.

    The code that I am using is:

    DataSet list = DataRepository.Provider.ExecuteDataSet(System.Data.CommandType.Text, "Select distinct column1 from table1");

    combobox1.DataSource = list;

    cbYear.DisplayMember = "column1";

    In my combobox I have seen following value:

    System.Data.DataViewManagerListItemTypeDescriptor

    Thank you

  • Hi Prashant

    That's not how you use a dataset for binding so that's why it's not working for you.  Take a look at either http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=111 or http://samples.gotdotnet.com/quickstart/aspplus/default.aspx?url=%2fquickstart%2fwinforms%2fdoc%2fWinFormsData.aspx/ for examples.

    Cheers

    Martin

  • Hi Martin,

    I know very well how to bind combo box using Ado.Net properties and I am fully aware of it.

    Actually I am searching for NetTiers that have same functionality as system.data.sqlclient or others. Is <NameSpace>.DataAccessLayer.SqlClient has the same functionality. For what purpose it is there.

    Should I have to create "DataAdapter" object here also and then whats the purpose of ExecuteDataSet?

    Actually I am not much aware of NetTiers generated classes, thats why I am keeps on asking basic problems here.

    Thanks

  • Hi Prashant

    Firstly, I wasn't implying you didn't know how to bind - just trying to help that's all (don't forget, I don't know how experienced you are so just pointing you in a direction for possible help).

    Secondly, I've just mocked up some code to a) use the ExecuteDataset method, and b) bind the results to a combo box which appears to work as expected - code snippet below (BindSrcContacts is a BindingSource control added to the form):

    Dim tempDS As DataSet

    tempDS = DataRepository.Provider.ExecuteDataSet(System.Data.CommandType.Text, "Select distinct ConsultantName from Consultants")

    Me.BindSrcContacts.DataSource = tempDS.Tables(0)

    Me.ComboBox1.DataSource = Me.BindSrcContacts

    Me.ComboBox1.DisplayMember = "ConsultantName"

    Hope that helps

    Martin

  • Thanx Martin,

    You are really great helper. Actually I dont have much idea of the NetTiers thats why I am having such problems.

    Few more thing cause confusion to me are:

    1. For what purpose "NameSpace.DataAccessLayer.SqlClient" is for.

    2. As we can see "DataRepository.Provider.TableNameProvider...." And "DataRepository.TableNameProvider......." What is the basic idea/purpose for putting "TableNameProvider" at two places as shown above.

    Is there any descriptive document/example for each terms as "NameSpace.Entities", "NameSpace.DataAccessLayer", "NameSpace.DataAccessLayer.SqlClient", TList, VList and more so that I can get more idea for using NetTiers.

    Thank you

  • Hi Prashant

    I'm new to NetTiers as well so am in pretty much the same situation as you - the way that I'm learning is to a) look at the code, and b) look at the comments in the code (as there are lots).  I've answered your questions (where I can) below:

    1. For what purpose "NameSpace.DataAccessLayer.SqlClient" is for.

    Take a look in the forums here - this question has been asked and answered in the past few days.

    2. As we can see "DataRepository.Provider.TableNameProvider...." And "DataRepository.TableNameProvider......." What is the basic idea/purpose for putting "TableNameProvider" at two places as shown above.

    Take a look at the DataRepository.cs file - the comments in there should help.

    3. Is there any descriptive document/example for each terms as "NameSpace.Entities", "NameSpace.DataAccessLayer", "NameSpace.DataAccessLayer.SqlClient", TList, VList and more so that I can get more idea for using NetTiers.

    What I've done is to use nDoc to product a help file for each of the Namespaces/dlls to see what's in each of them - from what I understand, nDoc extracts the comments from the compiled dlls so you get them all in one place (organised by namespace hierarchy etc).

    Cheers

    Martin

Page 1 of 1 (12 items)