| Author |
Topic |
|
klca
Starting Member
17 Posts |
 Posted - 2010-11-01 : 13:55:09
|
| I want to filter displaying of data under certain login conditions: 1) businesstype 2) login typeI'd like to create a view in which I could implement this kind of filtering. Is that possible?If it weren't a view, could this as well be obtained by means of a trigger or stored procedure or function?Any answer, idea or any other kind of contribution will be greatly appreciatedThanks in advanceCarlos Porras (El Salvador) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
klca
Starting Member
17 Posts |
Posted - 2010-11-01 : 19:52:23
|
Would you mind indicating me a path or walktrough or book or tutorial that I could use aas a guideline for accomplishing this?Best RegardsCarlos Porras (El Salvador)quote:
Originally posted by tkizer
Yes you can do to this in a view. Or a stored procedure. A trigger wouldn't work as that's not what a trigger does. Or you can do it without those things and just a straight SELECT statement with a WHERE clause.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
klca
Starting Member
17 Posts |
Posted - 2010-11-01 : 21:02:13
|
Thank you.Sure I know that I can use a "where sentence" for writing a query. Maybe that's not the point but maybe that my question hasn't been properly elaborated (maybe because of my limited English) but I'd rather like to have answers oriented in a more "not-easy way". I wish this shouldn't be considered "disrispectful" whatsoever. I'm just implying that in solving this it should be considered that it is something I'm goint to put into "production" so I wouldn't dare to go ahead with a solution that can cause a lot of overhead to the database server.That's why I really need a "great guru's advice"How to filter information taken from a Session["businesstype"] and another Session["logintype"], talking about ASP.Net Dynamic Data and C# development, and pass those values as parameters for invoking a "View" which will accept those incoming values as to filter properly the data presented to a specific type of user (defined by the type of business: drugstore, bakery, pet shop, supermarket, hardwarestore, stc. and the type of loging granted to that user: create, delete, retrieve, update, print, admin, etc.)Best regards and thank you againCarlos Porras (El Salvador)P.S.: I mention my country as to let you know that I am not a native english speaker.quote:
Originally posted by X002548
Any ideas?SureSELECT * FROM Table WHERE SomethingBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspx
|
|
|
|
klca
Starting Member
17 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
klca
Starting Member
17 Posts |
Posted - 2010-11-03 : 18:54:06
|
Hola,I think that maybe I have not been clear enough in the way I am asking about this issue.It is not about the C# code or ASP.Net code.The question should be simple and straightforward: Is there a way to create a parameterized view in SQL Server? And if it is not, what equivalent directive should I follow as to accomplish the following scenario:In ASP.Net I just add a DataSource Control and it is done. But it is expected to have a "good" data source as to filter properly the data presented to the end-user.So in my case I want to call a parameterized view from my DataSource Control and at the same time pass the values of my session parameters as to filter the data accoddingly.Best regadsCarlos Porras (El Salvador)quote:
Originally posted by tkizer
Your question seems to be about what code to write in your application and not about SQL Server. I'd suggesting posting your question on an ASP.NET/C# forum for assistance with that aspect.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
klca
Starting Member
17 Posts |
Posted - 2010-11-03 : 19:08:25
|
Thank you.The thing goes like this, talking about SQL Server only.If I were to use views instead of tables for creating my DataContext in Visual Studio 2010 the I need to have those views accepting two external parameters (coming from two session variables) whose values depends on who is logged (each login has a businesstype number a a login authorization code number)So I need the View to be able to be called passing those two values. How it is called from within my application is up to me, I'm pretty clear and aware of that.Is that possible at all?Carlos Porras (El Salvador)quote:
Originally posted by tkizer
It depends on what you mean by a parameterized view. Some people say that table functions are equivalent to that concept. But a view can have a WHERE clause, and you can add a WHERE clause when you SELECT from the view. I have no idea about ASP.NET. I can only help with SQL Server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
klca
Starting Member
17 Posts |
Posted - 2010-11-03 : 19:33:32
|
I am trying to show the need of using that kind of views if there were.Just for the matter of dismissing any prejudice you might have regarding my question is that I have indulged myself in writing a little about my development environment: Just to make my question a little more clearer to you and at the same time it will be probably useful for any other person dealing with something like this.That and also that two previous answers to my question were like taking my question so easy, not even trying to understand what I'm really asking.So the point of using tables or views comes to the point that I group them together trough a visual modeling tool (provided by Visual Studio). The grouping of them is what is called a DataContext (a group of tables selected from my databse schema)So, if I were to use views instead of tables in my model is because (and this is the reasion of my question) I'd wish that my "parameterized views" will accept (from within my code) to be called by passing to them two values which will be used as filters when querying the database.So let's say that businestype is 3 and login type is 4.From within my application I would have: DataSource database="northwind", view="customers", parameter1 = "3", parameter2 = "4"So I'd expect to have a view creted something like this: create view "customers" as select * from customers where cod_businesstype = @businesstype and cod_logintype = @logintypeThe problem is that the where clause is not allowed in creating SQL Server views.Carlos Porras (El Salvador)quote:
Originally posted by tkizer
Can you just use a stored procedure? I don't see any point of using a view here, but I may be missing the point. You say we are only talking about SQL Server in your last post, but then you mention DataContext, VS 2010, and session variables. Those are not in my scope of knowledge.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
klca
Starting Member
17 Posts |
Posted - 2010-11-03 : 20:16:06
|
Ok and Thank you.Just to show how this is done from inside the application:Let's say I have a control which will be used to display data (a Grid). I link my Grid to the DataSource by means of the DataSourceId <asp:GridView ID="gvProducts" runat="server" DataSourceID="dsProducts" ...> ...</asp:GridView>Let see how my DataSource is configured:<asp:EntityDataSource ID="dsProducts" runat="server" ConnectionString="name=NorthwindEntities" DefaultContainerName="NorthwindEntities" EnableFlattening="False" EntitySetName="Products" Where="it.UnitPrice <= @UnitPrice"> <WhereParameters> <asp:ControlParameter ControlID="txtMaxPrice" Name="UnitPrice" PropertyName="Text" Type="Decimal" /> </WhereParameters></asp:EntityDataSource> So I have something called DataContext which is defined in a DefaultContainerName= "NorthwindEntities" of which I am using an EntitySetName ="Products"Nothing about SQL Server until now but a simple call trough an instruction, in this case: EntityDataSource.So you see an additional WHERE instruction divided in two parts: a global "where" sentence and a "WhereParameters", as many as neededAfter all of this comes my question:If I were to have 100 entities (tables) in my data model I wouldn't like to customize a 100 queries in a 100 html like pages. That's where ASP.Net 4.0 come into play. It allows you to create a visual model of your tables and it creates all CRUD (create, retrieve, update, delete) sentences for you trough the use of templates and at the same time it uses what is called MetaData as to automatically generate the end-user forms for you.BUT THERE IS NO WAY OF CUSTOMIZING THE FRAMEWORK TEMPLATES EASILY SO YOU RATHER WORK TROUGH THE METADATA FILES. THEN YOU NEED TO FIND A WAY OF CREATING A MODEL IN WHICH FILTERS HAD BEEN ALREADY PREDIFINED That's what I call a parametrized view (I just made the name up, sorry) they would be a kind of view which will allow you to pass parameters to them (like when you call a function) which will be used for the view to filtering the data accordingly.Best regardsCarlos Porras (El Salvador)quote:
Originally posted by X002548
ok, I'll bite....What is a parametrized view, AND HOW DO YOU ENVISION YOU would call it?Maybe you need a sprocdamn caps lockBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
|
|
|
|
|
Parameterized Views?