Solved

mvc, linq, join and group and grid problem..

Posted on 2010-09-22
4
816 Views
Last Modified: 2012-05-10
Hello,

In my company we are working on a new CMS and one of the feature that we add to the system is the option to create a custom forms with different inputs like text, select box etc.

We manage to finish this part and now we are building the result part which needs to show the result that sent through the custom form.

We have a "FormsOrders" table which stored the answer that sent through the custom form in this way:
**OrderID**  - just an id saved as GUID

**NumOrder** – the address's id (every time someone fill the form I increase it by one) save as int

**ControlID** – GUID of the control. Each control (like textbox, select box etc.) Have is own ControlID and it's stored in "FormsControls" table.

**Value** – the value of the specific control id the address.

For example:
I Have contact form with 3 textbox field (first name, last name, message).
The "FormsControls" table will look like this:

formscontrols1.png

After someone submits an address, the "FormsOrders" table will look like this:

formsorders1.png


And another address will look like this:

formsorders2.png

And so on…

Now, I am trying to join the "FormsControls" value and "FormsOrders" value and get something like this:

goodresult.png


I can join them with linq using the same ControlID but than my table look like this:
trytojoin.png

But it's not helping me because I am using this example
http://weblogs.asp.net/rajbk/archive/2010/05/08/asp-net-mvc-paging-sorting-filtering-using-the-mvccontrib-grid-and-pager.aspx
(Which using the MvcContib Grid and Pager).

Now, all I want is to get the "FormsControls" Values as a column and the FormsOrders as a Value and group all the same numorder  in one row.

Like this:

goodresult.png

Me and another dev working on this for 2 days and we can't figure out how to fix this.

We tried join by NumOrder (which only give us the key and value column – not good for us),
We tried to make new datatable with the columns and rows (we manage to make the datratable but the MvcContib grid is not working with datatable).

We tried dynamiclinq (we didn't succeed with that).

And so on…

I just want to get all the address, to show each address in one row and to be able to sort them and search in those rows.

I will glad to get any idea how to do that…

Please help me.
0
Comment
Question by:ateam
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:gsiric
ID: 33733795
Hi,

Create 3 pseudo tables: one contains firstname, second lastname, third message.
Then join them on NumOrder to  get desired result.
This sql assume that you allways have all 3 values (for all controls) in the FormsOrders table.

This is an example:

SELECT  FirstName,LastName,Message FROM
( SELECT NumOrder, Controld, Value As FirstName from FormsOrders Where ControlID='controlid of fisrtname' ) As FormsOrdersFirstName,
( SELECT NumOrder, Controld, Value As LastName from FormsOrders Where ControlID='control id of lastname') As FormsOrdersLastName,
( SELECT NumOrder, Controld, Value As Message from FormsOrders Where ControlID='control id of message') As FormsOrdersMessage
WHERE FormsOrdersFirstName.NumOrder = FormsOrdersLastName.Order AND FormsOrdersFirstName.NumOrder = FormsOrdersMessage
0
 

Author Comment

by:ateam
ID: 33734370
Thanks for the answer, but the is that i dont know what is the column becuase the end user is the that create the form and it can with more than just first last name and message. I can only get the column name dynamicly from the formcontrol table
0
 
LVL 7

Assisted Solution

by:gsiric
gsiric earned 250 total points
ID: 33734914
Does grid accept dinamicaly created SQL ?

You can dinamicaly create sql like in this pseudo code :)

stringarray columns;
stringarray tables;
stringarray joins;

for int (i=0;i<controls.getsize();i++)
{

  Control control = controls[i];
  columns.add (control.Name.ToText());    
 
  tables.add("SELECT NumOrder, Value As " + control.Name + " FROM FormsOrders Where ControlID=" +  
 control.ID + ") As FormsOrders + control.Name;

  // join
  if (i>0)
  {
        joins.add(" FormsOrders" + controls[0].Name + " = FormsOrders"+control.Name] );
  }
 
}

//create sql

sql = "SELECT " + MakeCommaDelimitedString(columns)  
     +  " FROM " +  MakeCommaDelimitedString(tablles)
     + "WHERE " + MakeJoinString(joins) ;

 
0
 

Accepted Solution

by:
ateam earned 0 total points
ID: 33778298
Hey gsiric,

Thank you for your help.

The dinamicaly create sql doesnt work well with the mvccontrib.

I found another way to do this with dynamic property, but it was very slow.
in the end i change the table and stop to work  multipurpose table.


Lior
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Different Delete Messages 7 37
Not showing JavaScript in the list 5 40
Facial recognition to look through the whole database for a person 3 44
C# parent child form 5 19
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now