I have a scenario where I have a ReadOnlyList BO that returns 3 columns from a table. One of the columns contains SQL for a Select. After a user enters data into a field, the SQL in that column needs to be ran (it adds the user input to the Where clause) and results retrieved to a grid.
Any ideas on how to accompish this? I am using CSLA 3.5. Thanks.
Jonathan
I really do not know a good way to do this in CSLA. If
query for results grid is not set and is varying a lot, I do not think CSLA is
good for this. I would recommend using data table or dataset for results
grid instead. Beware of SQL injection because this solution is very prone
to it.
Sergey Barskiy
Senior Consultant
office: 678.405.0687 |
mobile: 404.388.1899
Microsoft Worldwide Partner of the Year | Custom
Development Solutions, Technical Innovation
From: jfreeman
[mailto:cslanet@lhotka.net]
Sent: Thursday, June 26, 2008 2:24 PM
To: Sergey Barskiy
Subject: [CSLA .NET] SQL Query in column
I have a scenario where I have a ReadOnlyList BO that returns 3 columns from
a table. One of the columns contains SQL for a Select. After a
user enters data into a field, the SQL in that column needs to be ran (it adds
the user input to the Where clause) and results retrieved to a grid.
Any ideas on how to accompish this? I am using CSLA 3.5. Thanks.
Jonathan
I have a scenario where I have a ReadOnlyList BO that returns 3 columns from a table. One of the columns contains SQL for a Select. After a user enters data into a field, the SQL in that column needs to be ran (it adds the user input to the Where clause) and results retrieved to a grid.
Any ideas on how to accompish this? I am using CSLA 3.5. Thanks.
Jonathan
Thanks for the replies. The SQL commands look similar to this:
SELECT CustomerID, CompanyName, CustomerNo [Company Code] FROM Customer WHERE Customer.CustomerID = {Customer ID} ORDER BY Customer.CustomerID
All we do is take the user input and replace {CustomerID}. So we're not just adding a Where clause. Any other thoughts? Thanks.
Jonathan
So your resulting list of columns is fixed? You always get back the same data?
You should be able to do this relatively easily by using a ReadOnlyBase object to retrieve the new values.
The process would be kind of like this:
Unfortunately, they are not fixed. The SQL could also be something like this:
SELECT ProductID, ProductNo, ProductDescription, ProductType FROM Product WHERE ProductNo LIKE '{Product No}%' order by ProductNo
The SQL command is different based on other selections a user makes before the application retrieves the SQL portion. Thanks.
Jonathan
What I’m saying is that your SELECT seems consistent (in
your examples) – only the WHERE varies.
If the SELECT is consistent then this isn’t too hard
(though the injection attack is a real problem).
If the SELECT varies (so the user gets to pick the columns to
return) then an untyped DataTable is probably your easiest solution. Just get
the data into the DataTable – the DataTable is a field of your object –
and return your object to the client, then give the DataTable to the UI via a
property.
Rocky
From: jfreeman
[mailto:cslanet@lhotka.net]
Sent: Thursday, June 26, 2008 3:03 PM
To: rocky@lhotka.net
Subject: Re: [CSLA .NET] RE: SQL Query in column
Unfortunately, they are not fixed. The SQL could also be something
like this:
SELECT ProductID, ProductNo, ProductDescription, ProductType FROM
Product WHERE ProductNo LIKE '{Product No}%' order by ProductNo
The SQL command is different based on other selections a user makes before
the application retrieves the SQL portion. Thanks.
Jonathan
Could the CommandBase be used to pass the SQL into and return a DataTable?
Jonathan
Yes, either that or a ReadOnlyBase object – either would
work.
Rocky
From: jfreeman
[mailto:cslanet@lhotka.net]
Sent: Thursday, June 26, 2008 4:44 PM
To: rocky@lhotka.net
Subject: Re: [CSLA .NET] RE: SQL Query in column
Could the CommandBase be used to pass the SQL into and return a
DataTable?
Jonathan
Can you explain how this might work in a ReadOnlyBase? Sample code maybe? Thanks.
Jonathan
I could have the use of the tableadapter wrong - I don't use them more than once every couple years for some demo or other, but the basic structure of using ReadOnlyBase is like this:
[Serializable]
public class GetDataTable : ReadOnlyBsae<GetDataTable>
{
private DataTable _table;
public DataTable Table
{
get { return _table; }
}
public static GetDataTable GetObject()
{
return DataPortal.Fetch<GetDataTable>();
}
private GetDataTable()
{ /* require use of factory */ }
private void DataPortal_Fetch()
{
_table = new DataTable();
var da = new MyTableAdapter();
da.Fill(_table);
}
}
Yes, it can be.
Sergey Barskiy
Senior Consultant
office: 678.405.0687 |
mobile: 404.388.1899
Microsoft Worldwide Partner of the Year | Custom
Development Solutions, Technical Innovation
From: jfreeman
[mailto:cslanet@lhotka.net]
Sent: Thursday, June 26, 2008 5:44 PM
To: Sergey Barskiy
Subject: Re: [CSLA .NET] RE: SQL Query in column
Could the CommandBase be used to pass the SQL into and return a
DataTable?
Jonathan
Copyright (c) Marimer LLC