Query dataset locally from code

Hi all,
I need to get 20 rows of data from the SQL Server DB to put in 20 Textboxes
always one item of one row.
It's about Text in various languages, so each language needs 20 textstrings.
I don't want to call the Stored Procedure 20 times.
So I thought in calling it one time, get the data out once of the DB and
refer to it in queries local to the report 20 times.
I tried it with an Assembly. This worked, but I needed to configure the
assembly's trust etc.
This is speaking against a copy/paste deployment to the client's Reporting
Server.
Is it possible to create a standard data set in the Report and refer in some
form to it locally?
E.g.: Dataset dsData takes one time the 20 rows.
In a textbox I call a function like =code.getValue("item17")
and in the code section I query the dsData with something like
function getValue (item as string) as string
return (select col2 from dsData where col1 = item)
end function
Thanks in advance, HenryHello Henry,
I have suggested the following article to you.
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
You do not need to configure the trust level if it not required.
I hope this will be some help.
The Assembly is more functional than the Embeded Code in Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
thank you for your answer.
But, as written above, I've already programmed an Assembly that works, but
the client DENIES the use of an Assembly.
I know also, that the <Code> part of the RDL-File is compiled implicitely
into an Assembly by ReportServer on load, so I cant access the outside world
(like the filesystem or the SQL-Server) from within the custom Code without
elevating its trustlevel.
I know also, that it is possible to handover e.g. Parameters to the code to
manipulate it programatically.
Like:
Function GetValue(reportParameters as Parameters) As Object
do sth with the Parameters
return sthelse
end function
The question was:
Since via a Data Source and a DataSet I've already the data available.
CAN I do anything like above with the Parameters, but here with the dataset?
E.g. giving a whole Dataset to a Function (best byRef) to do a select from?
Function GetValue(dsData as Dataset) As Object
return (select sth from dsData where ...)
end function
Thanks in advance, Henry|||Hello Henry,
Unfortunately, you could not refer the dataset in the code.
I would like to know the concern from your client of using the assembly.
You could only grant some proper trust for runing the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Wei Lu,
it might be possible to access the file system, as in your
XML-Assembly-Example that you sent me, but getting data out from an
SQL-Server DB is a big trouble.
The info under your hint:
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
is far from sufficient.
Only after lots of hours and the following resources, I got it to work.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
http://forums.microsoft.com/msdn/showpost.aspx?postid=139787&siteid=1
Microsoft Official Course 2840A, Implementing Security for Applications
So, in the very end I needed to give:
- fulltrust via UrlMembershipCondition
Error message: "Cannot generate Hash"
- Give the assembly a strong name
Error message:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
... failed. (rsRuntimeErrorI-nExpression)
- Adjust CAS
- Assert
System.Data.SqlClient.SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
It didn't work until now!!!
So, worse, I had to install the Assembly in the GAC.
and with this give: AllowPartiallyTrustedCallers()
With our client was agreed a copy/paste-deployment.
All this procedure is FAR from that!
Any ideas?
Thank you, Henry|||Hello Henry,
Yes, the render order is from the upper-left to bottom-right.
I would like to suggest you use a hidden textbox in the report to
initialize.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If I understood you correctly you are passing a predefined number of
parameters to this function. This can also be done dynamically using a
little hack described in this page:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
I took the code at this page and used to build a dynamic localization
function that reads the text id numbers, language codes and corresponging
texts from a dataset and stores them into a Hashtable object using text id +
language code as the key and text as the value.
Basic idea is that if an aggregate function like Sum is called for a certain
scope, it will go through all of the data in that scope and it can take a
function call that returns a number as a parameter.
Like this:
Sum(Code.LoadDictionary(Fields!TEXT_ID.Value , Fields!LANGUAGE_CODE.Value,
Fields!TEXT_TXT.Value), "DICTIONARY")
Where DICTIONARY is the dataset for dictionary. The LoadDictionary function
is called once for each row in the scope.
You can the have this 'loader' in a hidden field as Mr. Lu suggested.
Then for the localized field you just have for example
=Code.GetLocalizedText("TEXT_0001", Parameters!LanguagePar.Value)
I know that your solution works great. I just think tahat this way the
function calls are simpler. I hope this helps.
Juho Salo