CODEWITHSUNDEEP
c#asp.netrazorwebmatrix
E Benzle
E Benzle

Reputation: 346

How to select row from a query based on column value. Webmatrix C#

I am building a site in WebMatrix C# and have a page with multiple input fields that need to be populated from a database. The table I am pulling the data from has a combination id/value columns where the id corresponds to the input field id.

The table looks like below, where field_data = data field_name = id that matches to the input field

id      doc_id     field_data          field_name
------  -------    -------------       ------------   
45680   1549       Astoria Apartments   q4_1Property
45681   1549       1000                 q11_7Arbor
45682   1549       61 Street            q12_8Other[0][]
45683   1549       Cleveland            q12_8Other[0][1]
45684   1549       Ohio                 q12_8Other[1][]
45685   1549       43589                q12_8Other[1][1]
45686   1549       USA                  q12_8Other[2][]
45687   1549       12                   q12_8Other[2][1]
45688   1549       d                    q12_8Other[3][]
45689   1549       13                   q12_8Other[3][1]

I could do this:

var queryinputvalue = "SELECT field_data FROM document_data WHERE field_name = @0 AND doc_id = @1";

<input type="text" class=" form-textbox" id="input_4" name="q4_1Property" size="20" value="@db.QueryValue(queryinputvalue, "q4_1Property", 1549)"/>

But some of these forms have over 100 input fields, and it seems excessive to make a separate database query for each field.

Is there a way to make a single query for all the data, and then select from that query only the data I need?

Something like below, where the stuff in the [] should tell WebMatrix which row to return from the query.

var queryinputvalue = db.Query("SELECT * FROM document_data WHERE doc_id = @1", 1549);

<input type="text" class=" form-textbox" id="input_4" name="q4_1Property" size="20" value="@queryinputvalue.[SELECT row where field_name = 'q4_1Property'].field_data"/>

Thanks for any help.

Upvotes: 0

Views: 1884

Answers (1)

Mike Brind
Mike Brind

Reputation: 30110

You are almost there. The following gets all the relevant rows:

var queryinputvalue = db.Query("SELECT * FROM document_data WHERE doc_id = @0", 1549);

Then you can use Linq To Objects to query the resulting collection. FirstOrDefault is the extension method you should use for this:

var q4_1Property = queryinputvalue.FirstOrDefault(r => r.field_name.Equals("q4_1Property")).field_data;

Upvotes: 1

Related Questions