jackie.williams (3/11/2009)
jcraddock (3/11/2009)
Um, how are you going to declare the variables and such inside one statement from an ASP.Net page/class/etc? This looks like you have to use a proc, and procs are out with the queries I generate.I'm not sure what you're saying here. We use stored procedures that generate dynamic queries to retrieve datasets similar to this all the time. Our datalayer takes a datarow containing the values for whatever parameters are necessary for the procedures and iterates through that to pass the necessary parameters into our stored proc, then the stored proc makes sure any sql injection chars etc are stripped, generates the dynamic select query with paging etc. based on whatever values have been passed into it and returns a dataset with the requested page of data and a full count of the records. Why would procs be out in what you're doing?
Well, all my applications are actually one application. All table information, validation rules, permissions, roles, etc, are stored in metadata. I generate the queries for list windows dynamically from the ASP.NET page with full filtering and sorting - one list page works for every single table in all systems, similarly one editpage works for every table in every system...to do what you propose, I would have to move my OBJECT class into Sql, as it contains all the metadata. I have no interest in running .NET from my SQL Server for several reasons - number one being total rewrite of the architecture. I was just hoping there was a way to do it directly from the ASP code instead of relying on a procedure. At the top of this window you can see the record count and then the page is displayed. I was hoping for a way (without rearchitecting my class onto SQL Server and taking the performance hit for turning that on) to get both numbers at once.
Here is a Screenshot:
