ForeignKey and/or ForeignKeyColumn

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Charles Zhang - 15 Jul 2006 08:25 GMT

I am developing a DDEX provider for my ADO.NET provider. I based my DDEX
provider on DDEX sample 4 (VSPackage-Based DDEX Provider
Implementation). But I found out there are two problems with the sample.

1. Foreign Key info is not picked up in the query designer.
You can follow the following steps to duplicate the problem.
(You need to build VSPackage based Sample first)

A. Right Click on "Data Connections" from server exlporer window
B. From the menu select "Add Conenction..."
C. You should see "Add Connection" form, Click "Change"
D. In "Change Data Source" form, select "Microsoft SQL Server" in
"Data Source" list
E. In the "Data provider" list, select ".NET Framework data Provider
for SQL Server (VSPackage based Sample)
F. Click OK
G. Fill in needed info for "Add Connection", in the Database Name
text box, please type "AdventureWorks"
H. Click OK to return "Server explorer"
I. Right Click "Tables", select "New Query" from the menu
J. Select "Product" and "ProductReview", then click OK.
L. As you can see the the query designer window, the foreign key
info is not picked up.

I have done similar things by using ".NET Frameword Data Provider
for SQL Server", the foreign key info is picked up correctly.

I have debugged into the VSPackage-Based sample and looks like the
Foreign Key and Foreign Key Columns are returned correctly from the
queries ( I copied the queries into Query Analyzer to see the results).

As well, I tried similar things by using Northwind database ( I
backed up it from SQL Server 2000 then restore it to SQL Server 2005).
By accident, I named the database as "northwind" ( first letter is lower
case), for some tables, the foreign keys info is picked up, for some
tables, the foreign key info is not picked up. After I recreated the
database and named it as "Northwind" ( the first letter is upper case).
The foreign key info is is not shown for any tables.

2. No correct parameters are generated for functions based "Select"
statements.

You can follow the following steps to duplicate the problem
A. Create a Windows Application project
B. Added a new data source using ".NET Framework data Provider for
SQL Server (VSPackage based Sample)
C. Choose one of TabularFunction with at least one input parameter
from the functions node.
D. Hit finish, you will get an error message. I got something like:
<Northwind2.dbo.TOTableFromString>
Must declare the scalar variable "@IDString".
The message you get will be different from mine.

Thanks you very much in advance.

Charles Zhang

Reply to this Message

"Gary Chang[MSFT]" - 17 Jul 2006 09:11 GMT

Hi Charles,

Thank you posting!

The DDEX provider issue is a little complicated to handle in the newsgroup,
we may need to involve our VSIP specialist to help you on this issue. Would
you please provide your contact information to us? You can send it tom my
mailbox directly (please remove the "online" of my email address alias).

Thanks for your understanding!

Best regards,

Gary Chang
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Robert Simpson - 19 Jul 2006 20:19 GMT

I'm using the July 2006 CTP of the VSIP SDK ...

For #1:

I compiled the Package-based provider (sample 4) included in the SDK. The
New Query context menu brings up a sample messagebox showing how it was
overridden, so I fixed up the linkage in the SqlDataViewSupport.XML file so
the real query designer shows up, and recompiled.

I then hit "New Query". I didn't have your tables or database, so I created
a simple database with two tables, Main and Sub. Main had an ID primary
key, and Sub had a MainID foreign key.

I added both tables to the query designer, and the foreign keys showed up
just fine.

For #2: The sample provider doesn't implement
AdoDotNetConnectionSupport.DeriveParameters(), which may be the reason
parameters aren't automatically generated.

Robert

>I am developing a DDEX provider for my ADO.NET provider. I based my DDEX
>provider on DDEX sample 4 (VSPackage-Based DDEX Provider Implementation).
[quoted text clipped - 52 lines]
>
> Charles Zhang

Reply to this Message

Charles Zhang - 19 Jul 2006 21:13 GMT

Thank you very much. Both for your post to my question and your SqlLite
provider.

Sorry about that I forgot to mention I fixed SqlDataViewSupport.XML to
make the real query designer come up.

The database I mentioned is a sample database from Microsoft and is not
installed by default.

It might be a good idea to backup Northwind sample database from SQL
Server 2000 then restore it to SQL Server 2005 server as I mentioned in
my post.

If the database is named as "northwind" (first letter is lower case),
then foreign key info will be picked up for some tables ( not all the
time), if the database is named as "Northwind" (first letter is upper
case), then no foreign key info will be picked up.

I guess the problem is related to something like case sensitive( even
though the database is setup as case insensitive).

For #2, I will try to implement DeriveParameters and see what will happen.

Thanks again and have a good day.

Charles Zhang

> I'm using the July 2006 CTP of the VSIP SDK ...
>
[quoted text clipped - 74 lines]
>>
>> Charles Zhang

Reply to this Message

Ed Dore - 20 Jul 2006 01:27 GMT

Hi Charles,

Can you walk me through the edit on that SqlDataViewSupport.xml file? I
tried commenting out the CommandBinding entry for NewQuery, but that just
removed the command from the context menu.

I was able to build and run the sample successfully, and have access to
that AdventureWorks database. Once I have this repro'd, I'd be more than
happy to look into this further.

Sincerely,
Ed Dore [MSFT]

This post is 'AS IS' with no warranties, and confers no rights.

Reply to this Message

Robert Simpson - 20 Jul 2006 04:02 GMT

Ed, change the XML where it specifies the NewQuery command binding to:

<CommandBinding name="NewQuery"
guid="501822E1-B5AF-11d0-B4DC-00A0C91506EF" cmdid="13608"
handler="884DD964-5327-461f-9F06-6484DD540F8F"/>

(you're just changing the handler from the sample's command handler to VS's
built-in handler for the New Query command)

Robert

> Hi Charles,
>
[quoted text clipped - 10 lines]
>
> This post is 'AS IS' with no warranties, and confers no rights.

Reply to this Message

Ed Dore - 24 Jul 2006 20:07 GMT

Thanks for the hint Robert.

Charles, I've tried reproducing the behavior you mention, but I don't see
foreign key info when using either the DDEX sample, or the ".Net Framework
Data Provider for SQL Server".

Can you detail what the expected behavior is here. I'm not overly familiar
with the datatools in VS, but in running through your repro steps, I see no
differences in the results using either provider.

Please bear in mind my SQL Server 2005 installation is on a separate
Windows 2003 Server box, and the AdventureWorks database was installed with
the original setup.

Thanks,
Ed Dore [MSFT]

Reply to this Message

Charles Zhang - 25 Jul 2006 07:09 GMT

Thank you very much for your response.

After further investigation, I found it is related to the case the
database name you typed when you create the connection.

If you type "adventureWorks" (first letter is lower case) for the
example prodvider, the foreign key info will not be shown in the query
designer ( two table is linked by ProductID, possibly because indexes on
both tables are picked up, but no "key" mark on Products table and no
"many" mark on ProductReviews table).

But if you type in "adventureWorks" (first letter is lower case) for the
".NET Framework Data Provider for SQL Server", then the foreign key info
will show up ( two tables are linked by productID, "key" mark is shown
on Products table and "many" mark is shown on ProductPreviews table).

I hope this will help you to identify the problem.

Thanks

Charles Zhang

> Thanks for the hint Robert.
>
[quoted text clipped - 12 lines]
> Thanks,
> Ed Dore [MSFT]

Reply to this Message

Ed Dore - 01 Aug 2006 21:30 GMT

Hi Charles,

Sorry about the delayed response. I completely missed your earlier post in
the newsgroup. Per your 7/24 post, I can now reproduce the behavior. I've
got a bit of debugging to do here, but once I have an answer, I'll repost
back on this thread (and also send you an email).

Sincerely,
Ed Dore [MSFT]

This post is 'AS IS' with no warranties, and confers no rights.

Reply to this Message

Robert Simpson - 20 Jul 2006 04:04 GMT

Btw, Ed -- I'm in Redmond end of this month for the Provider Writers Tech
Preview. You going to peek in on that at all? :)

Robert

> Hi Charles,
>
[quoted text clipped - 10 lines]
>
> This post is 'AS IS' with no warranties, and confers no rights.

Reply to this Message

Ed Dore - 24 Jul 2006 20:03 GMT

Thanks for the hint Robert.

Charles, when you mention that the "Foreign Key info is not picked up in
the query designer". What specifically do you mean? I'm not overly famliar
with the db connectivity features in VS, so you're going to have to be very
specific in your descriptions.

Please note, I'm running SQL Server 2005 installed on a separate Windows
Server 2003 machine. The sample database was installed with my SQL Server
2005 installation.

I tried following the steps you outlined using both the .NET Framework Data
Provider for SQL Server, and the provider sample in the SDK. Following your
steps verbatim, resulted in identical results in the query designer. Both
the designer and the query looked identical to me.

Can you provide further details on what specifically you are seeing that is
different between using these two providers.

Where exactly are you noting the missing Foreign Key info?

Thanks,
Ed Dore [MSFT]

Reply to this Message

Charles Zhang - 19 Jul 2006 21:15 GMT

Thank you very much. Both for your post to my questions and your SqlLite
provider.

Sorry about that I forgot to mention I fixed SqlDataViewSupport.XML to
make the real query designer come up.

The database I mentioned is a sample database from Microsoft and is not
installed by default.

It might be a good idea to backup Northwind sample database from SQL
Server 2000 then restore it to SQL Server 2005 server as I mentioned in
my post.

If the database is named as "northwind" (first letter is lower case),
then foreign key info will be picked up for some tables ( not all the
time), if the database is named as "Northwind" (first letter is upper
case), then no foreign key info will be picked up.

I guess the problem is related to something like case sensitive( even
though the database is setup as case insensitive).

For #2, I will try to implement DeriveParameters and see what will happen.

Thanks again and have a good day.

Charles Zhang

> I'm using the July 2006 CTP of the VSIP SDK ...
>
[quoted text clipped - 74 lines]
>>
>> Charles Zhang

Reply to this Message

Charles Zhang - 20 Jul 2006 06:33 GMT

For item 2:

I override the AdoDotNetConnectionSupport.DeriveParameters() in my own
ConnectionSupport class like the following:

public override DataParameter[] DeriveParameters(string command,
int commandType, int commandTimeout)
{
return base.DeriveParameters(command, commandType, commandTimeout);
}

And I set a break point inside the function, but it does not get called
at all.

(I have not implemented the real logic yet, I just wanted to make sure
the function being called)

Looks like your guess is correct, but I need to find the way to make the
DeriveParameters being called.

Thanks

Charles Zhang

> I'm using the July 2006 CTP of the VSIP SDK ...
>
[quoted text clipped - 74 lines]
>>
>> Charles Zhang

Reply to this Message

ForeignKey and/or ForeignKeyColumn

Free Magazines