A little background: Our Database Administrator decided we should move to using different schemas in SQL server then just the DBO schema. Please don't confuse a SQL schema with a Data Schema. Consider the SQl schema as the owner. Unfortunately most Microsoft tools for SQL & Visual Studio suck for this. And this I think is one of those times.
We have tables that are are named like "keys.MenuDirectories". The "keys" part is a schema in SQL. This is used in place of the dbo schema that is used by default. If you use Server Explorer in Visual Studio (2008) it lists tables like this as "MenuDirectories (keys)". Even groups them by schema (only here most places don't).
So now in Visual Studio I create a new blank dataset and drag the keys.MenuDirectores table from the Server Explorer onto it. And as a good developer, and how our DBA demands it, I configure the table adapters to use stored procedures and choose to have studio create them. In the screen that asks for the names of the procedures, I first change them from the really lame names the wizards provide (come on at least try to put the table name in somewhere). When I view the SQL it will create, it shows that it will create stored procs on the dbo schema. This is bad for us as we want them to be, at least by default, the same schema as the table (keys not dbo).
To get around this I try naming the procedures as keys.SomeProcName. When it generates the SQL it drops the "keys." and uses dbo as the schema. So I try to use the naming that is used in Visual Studio and try "SomeProcName (keys)". When it generates the SQL it uses dbo and includes the (keys) as part of the sproc name.
Is there any way to assign the schema in studio? Is there a template for the SQL generation that could be changed? Perhaps is there a way to replace the user unfriendly TableAdapter Configuration Wizard with one we create ourselves?
I would really like it if someone from the dev team for this tool could spend 2 minutes with me to see how lame it is and how it could be fixed.
I know I could copy that SQL that is generated and paste it into management studio and replace the dbo with keys. But then I would have to then change the tableadapter to use existing stored procs and then find them in the list. This is made harder since this list does not group or sort by schema. For a dataset of 10 or so tables or a bunch of datasets you can waste ALOT of time doing this work around.
When DBO is a schema it seems really bad for Visual Studio to assume everything uses the DBO schema.
Sorry this email is a little rude. I've just wasted a few hours working around this problem. Any help would be appreciated.
Been kind of busy lately, had no chance to get back but felt important to revisit the issue.
I want to tell you a bit in a way of what you so arrogantly called a "
background." DataAdapter/TableAdapter is a very complicated object. I am sure thousands of man-hours at MS went into building it. It's undergone evolution from a primitive form thru .NET versions to its present form which you came to hate. TableAdapters are made for people who are pretty much at TechNet level, who either do not know how to program or unwilling to do so. Ater I tried them first I made a decision to expunge them all and do hard coding of my own design for many reasons, and one of them is that they are wastful. They bring your entire tables into the memory and I did not want it. By the time I decided to get rid of them I had so many in my code that I had hard time to find them all. They are also prone to other hidden bugs, for instance if something goes wrong with your code run the connection remains open.
What you are seeing is not MS fault. It is by design. They just could not make it so universal that it would work for every schema in the world. Some of those schemas may be used by only one customer. This is why the schema defaults to dbo. One perhaps can blame them for not clearly documenting it but they cannot document everything. If they do, nobody would read all this documentation anyway because many things must be simply figured out or if you are dumb you come here and ask questions. Besides they don't have time. I would prefer them to keep rushing ahead with their new developments.
I don't use DataAdapters and look down on people who do. If a new person comes around asking a question I try to talk them out of it. You could have created your own schema either with a function call from C# code or you could call a system Sql System SP from .NET to create a schema. Instead you chose to come here with all your arrogance to exhault that you wish the dev team to spend 2 min with you to crank their DA.
If I were your manager and read your post I would have given you a pink slip tomorrow because you are not qualified to be allowed to pass nowhere near any database at all. I wish he will do it.
AlexB
