|
Even though they are slow, cursors are sometimes the best option. Especially when compared to running a hundred queries.
You could dynamically create an SQL statement by doing something like this:
create procedure GetFooInfo
as
DECLARE @nFooID int,@cType varchar(50),@cSQL varchar(2000)
set @cSQL = ''
DECLARE fooBase CURSOR FOR
SELECT distinct Type
FROM fooTable
OPEN fooBase
FETCH NEXT FROM fooBase INTO @cType
WHILE @@FETCH_STATUS = 0
BEGIN
if len(@cType) > 0
set @cSQL = @cSQL + ' LEFT JOIN ' + @cType + ' ON fooTable.FooID = ' + @cType + '.FooInheritorID '
FETCH NEXT FROM fooBase INTO @cType
END
CLOSE fooBase
DEALLOCATE fooBase
exec('SELECT * FROM fooTable ' + @cSQL)
go
ps. This is what I did to set things up to be similar to your description:
CREATE TABLE fooTable (
FooID int IDENTITY (1, 1) NOT NULL,
Type varchar (50),
FooBaseInfo varchar(50)
) ON [PRIMARY]
GO
insert into fooTable (Type,FooBaseInfo) values ('','this is a basic foo')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor1','FooInheritor1 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor3','FooInheritor3 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 2')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 2')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 3')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 3')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 4')
CREATE TABLE FooInheritor1 (
FooInheritorID int,
Foo1Specific varchar(50)
) ON [PRIMARY]
INSERT INTO FooInheritor1 select FooID,'Specific to 1' as Specific FROM fooTable where Type = 'FooInheritor1'
CREATE TABLE FooInheritor2 (
FooInheritorID int,
Foo2Specific varchar(50)
) ON [PRIMARY]
INSERT INTO FooInheritor2 select FooID,'Specific to 2' as Specific FROM fooTable where Type = 'FooInheritor2'
CREATE TABLE FooInheritor3 (
FooInheritorID int,
Foo3Specific varchar(50)
) ON [PRIMARY]
INSERT INTO FooInheritor3 select FooID,'Specific to 3' as Specific FROM fooTable where Type = 'FooInheritor3'
CREATE TABLE FooInheritor4 (
FooInheritorID int,
Foo4Specific varchar(50)
) ON [PRIMARY]
INSERT INTO FooInheritor4 select FooID,'Specific to 4' as Specific FROM fooTable where Type = 'FooInheritor4'
GO
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
What about something like this:
FooTypes[] fTypes = fooTable.Query("SELECT DISTINCT FooInheritorType FROM fooTable");
for(int i = 0; i < fTypes.Length; i++)
{
string cQuery;
if fTypes[i] == "")
cQuery = "SELECT * FROM fooTable ft WHERE FooInheritorType = ''";
else
cQuery = "SELECT * FROM fooTable ft INNER JOIN " + fTypes[i] + " fi ON ft.id = fi.id";
FooRow[] rows = fooTable.Query(cQuery);
for(int i2 = 0; i2 < rows.Length; i2++)
{
Foo theFoo = new Foo(rows[i2]);
...
}
}
Then you will only need to make 15 or 16 trips to the DB (or one trip for how many Inheritor Types there are).
-- modified at 9:55 Friday 22nd September, 2006
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Ok. So it's essentially like a view that returns all the possible data, or all the data that can be returend by that particular query, right? We had gone the view route, where the view returns all possible data for all the Foo inheritors...but it gets really messy, ugly, and is a pain to maintain.
|
|
|
|
|
Judah Himango wrote: So it's essentially like a view that returns all the possible data
The first suggestion I made was like that (the one using a stored procedure). Except it would be better than a view in that it wouldn't have to be maintained, since it is dynamic.
But, the nested loop suggestion is even better. It would first return a resultset of all possible Inheritors. Looping through each of those, you get a resultset for each Inheritor type that has records for all Inheritors of that type along with their Foo data and FooInheritor specific data. This resultset can be used to create the Foo objects.
I believe you said that there are about 15 different inheritor types. So, if you add an inheritor type, you will not need to alter this code. Just have a field in the Foo table that tells what child table the FooInheritor specific information should be gotten from. (In the pseudo code I submitted earlier, I assumed that the FooInheritorType field would have the name of the child table. In the case of a Foo that is not an inheritor, the field would be empty and only the Foo table would be queried - hence the IF (fTypes[i] == "") .)
Or...I'm in over my head here, and I have no idea what the hell you're trying to do.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
You are understanding my problem correctly. Thanks for the suggestions.
|
|
|
|
|
Sometimes I use this kind of schema if I need something highly changeable (usually only when data columns are user-configurable):
Have your main Foo table, then have 1 table for foo-inheritor values. In the foo-inheritor table, you have a foreign key to the Foo table, a FieldName field and a Value field. Your stored procedure would return 1 result set for Foo, and another for the foo-inheritor. Your business object would load the individual field values from the foo-inheritor result set into the appropriate fields.
This model works best when only 1 Foo is queried at a time, but can also be used to return more than 1 (with slightly more effort in both the stored proc and the .net code). It is also more difficult if the values cannot be stored as character types, but you can add a Type field and store everything in varbinary if necessary, then convert when loading the objects.
I would definitely go with Eric's solutions if the tables aren't very dynamic, but this solution works when many foo-inheritors are expected to be added over time.
Scott
|
|
|
|
|
Scott Serl wrote: our stored procedure would return 1 result set for Foo, and another for the foo-inheritor.
Oh man, there are so many foo inheritors though; combine them all together and you've got something like 35-40 fields. Needless to say, this is F-UGLY on the consuming code side, where I basically say Foo instance = ParseFooFromRow("idField", "textField", ....[all 40 fields])
Not only is that code ugly, but it's a nightmare to maintain; everytime you add a new foo type, or add a change an existing type, you have to go in and modify that huge thing. Nasty stuff.
|
|
|
|
|
Yes, you have to store the schema somewhere, and you can build a factory that can build any foo-inheritor. I usually build a UI to create new foo types, then store the schema in the database. When I go get the data, I grab and return the schema so the object knows how to load the data. Each foo-type specific data item is a single record in the result set, and is loaded by name from the schema. It's not too dificult to maintain, as the code is generic for any foo-inheritor.
It is ugly, but is the easiest way I know to design end-user-configurable objects. If the foo-types aren't going to change much, it is much easier to use seperate tables, then you can have stored procs for getting each foo-type.
Scott Serl
|
|
|
|
|
Interesting, thanks. Sounds like a lot of work, but hey.
What do you think of the solution of 1 initial query (to get all common Foo data), then an extra query each time you want to retrieve a FooInheritor?
|
|
|
|
|
If the data is for display to a human and the database calls are not across the internet via dialup, then the performance should be OK (and I am talking about the case of maybe dozens of individual foo-inheritor queries). If it is for some kind of automated data collection and reporting, then the performance could be an issue if you end up making a hundred queries each second.
As always, the best solution depends on all the little details, so it is not easy to recommend one solution over another. Each has its own implications for maintenance and performance. Think about it, then choose one way to do it, and just do it. Try to burry the details in an assembly and access it through an interface. If the implementation starts to cause problems, redesign it later, and the code accessing the interface will not need to be changed; just recompile the implementation assembly, convert the database, and you are done.
Scott
|
|
|
|
|
It is for display for a human.
I'm thinking I will go with the multiple queries thing and see how it goes.
Thanks for your in-depth answers, much appreciated.
|
|
|
|
|
Hi All;
I have a dataGridView binding to a dataView. I change the row filter of dataview on textChange of a textbox and it works very fine but the problem arises when i add the datatable (from which the dataview has been created) in a dataset, RowFilter stops working... any clue?
sorry for my bad English.
|
|
|
|
|
Why you have a DataTable without a DataSet? It does not matter. I dont know, why the RowFilter stops working, but try to use the Select-Method from DataTable instead a DataView.RowFilter.
Regards
Stephan
\\\| \\ - -
( @ @ )
+---------------oOOo-(_)-oOOo-----------------+
| Stephan Pilz stephan.pilz@stephan-pilz.de |
| <a href=www.stephan-pilz.de>www.stephan-pilz.de</a> |
| ICQ#: 127823481 |
+-----------------------Oooo------------------+
oooO ( )
( ) ) /
\ ( (_/
\_)
|
|
|
|
|
hi,
Is there a way to invoke in .net the visual Query Builder but in your running application,
not in design mode. If it is possible please tell me how, cause i can't seem to find the
corresponding control
Thanks Kurt
codito ergo sum
|
|
|
|
|
how to declare,fetch,deallocate and manipulate using cursor in SQL SERVER
|
|
|
|
|
choorakkuttyil wrote: how to declare
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]
opening a cursor:===
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
choorakkuttyil wrote: fetch
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar}
]
FROM
]
{ { [GLOBAL] cursor_name } | @cursor_variable_name}
[INTO @variable_name[,...n] ]
choorakkuttyil wrote: deallocate
DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}
Best Regards,
Apurva Kaushal
|
|
|
|
|
SQL Server Books Online:
Using Cursors[^]
Declare Cursor (T-SQL)[^]
Example:
CREATE PROCEDURE MyProcedure @cMyParameter varchar(100)
AS
DECLARE @cMyVar varchar(100)
DECLARE mycursor CURSOR FOR
SELECT MyField
FROM MyTable
WHERE MyOtherField = @cMyParameter
OPEN mycursor
FETCH NEXT FROM mycursor INTO @cMyVar
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM mycursor INTO @cMyVar
--do some processing
END
CLOSE mycursor
DEALLOCATE mycursor
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I want to use report services in SQL server 2005 express edition. Does it require IIS?
Thanks
|
|
|
|
|
I'm not sure about the Express Edition, but Reporting Services, in general, requires IIS, at least 5.0, and ASP.NET 2.0.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Does anybody know what we need to read out an Access database?
Is MS Access really needed or can you install a provider only (or Access only without the rest of Office)? We need to show the data in our application only. (the users don't have to open the file itself) Currently the client provided the data in a mdb file, which occasionaly can be updated. Unfortunately this application has to be installed on several hundreds of workstations where Office is not installed.
The application is a html application embedded in our product. I use javascript to access the db.
Thanks a lot for your help...
|
|
|
|
|
V. wrote: has
The MS access oledb provide (ADO0) is intalled with the system in in "program files\common files\system\ADO"
The Jet engine dllas are normally around in windows\system32
As a result, you should not have to intall anything if you are using ADO/ADO.Net to read from the file.
If these are old clients (win2k, ME, Win98 etc.) you may need to install ADO and possibly the jet engine stuff.
Redistributables can be found here[^]
|
|
|
|
|
Hey man,
thanks a lot, this is really helpfull !
|
|
|
|
|
i am having a database and i entered some data and deleted it also.Now the Primary Key starts from 4 or say any thing..i want it to start it from 1.And the primary key is auto incremented and not for replication.So how to do it.Please help me out.
Mohinder Singh
|
|
|
|
|
DBCC CHECKIDENT[^]
DBCC CHECKIDENT('MyTable', RESEED, 0)
You need to be logged on as a user with at least one of the following roles: sysadmin , db_owner or db_dlladmin
|
|
|
|
|
Rename ur column[PK].
Add new column with the name of pk column.
Now delete the column u renamed.
Your problem will be solved.
Do good and have good.
|
|
|
|
|