Switch from Postgresql to SQL Server

listonic · Post by **listonic** » Mon 06 Dec 2010 15:52

We have been using Dotconnect for Postgresql. Now we need to switch to SQL server.
I believed that it is enough to uninstall postgresql version and install SQL Server version and it would work.

It doesn not.

What is the best way to perform such switch ?
Where did the namespace Devart.Data.Linq go in SQL Server version ?
How to convert generated Context classes from Postgresql to SQL Server.

Any other tips ?

StanislavK · Post by **StanislavK** » Tue 07 Dec 2010 17:53

As I can understand, you want to create a LinqConnect model for your SQL Server database. Am I correct? In this case, you need to install LinqConnect instead of dotConnect for SQL Server:
http://www.devart.com/linqconnect/overview.html
dotConnect for SQL Server provides enhancements to the general ADO.NET functionality only, and does not support the LinqConnect ORM.

As for the generated code, it can be easily switched to another DBMS provided that you have an external mapping file. The latter can be the .lqml file of your model; to configure it to contain the mapping information, please set the 'Mapping mode' model property to 'File' instead of 'Attributes'. For the information on how to use external mappings, please refer to the following topic of our documentation:
http://www.devart.com/linqconnect/docs/POCO.html

Feel free to contact us if anything is unclear.

listonic · Post by **listonic** » Tue 07 Dec 2010 21:26

Hello,
Let me explain again.

We are currently using dotconnect for postgresql. We want to change our database from postgresql to ms sql without or with very little change in code.

Do I understand correctly that it is not possible ?

StanislavK · Post by **StanislavK** » Wed 08 Dec 2010 17:55

It is possible. For this purpose, you can perform the following:
1) Install LinqConnect instead of dotConnect for SQL Server; the Devart.Data.SqlServer.Linq assembly is available in the LinqConnect installation package only.
2) Create a LinqConnect model for your SQL Server database with Entity Developer; please set the 'Mapping mode' model property to 'File' so that database to .NET types mapping is added to the .lqml file.
3) Attach the mappings from the new .lqml file to your DataContext in the way described in this article:
http://www.devart.com/linqconnect/docs/POCO.html

Please tell us if this helps.

listonic · Post by **listonic** » Thu 09 Dec 2010 10:55

StanislavK wrote: Please tell us if this helps.

Yes it clarifies things. The only thing I do not understand is where is the "Mapping mode" property ? I cannot find it during LinqConnect model creation, neiher in Entity Data Model Designer.

Where exactly do I set this setting ?
Im using Visual Studio 2010 Ultimate.

In lqml file there is such content :

So i suppose that mapping information is added there automatically.

I did something like this :
FileStream s = new FileStream("MsSqlLinq.lqml", FileMode.Open, FileAccess.Read, FileShare.Read);
System.Data.Linq.Mapping.MappingSource mappingSource = XmlMappingSource.FromStream(s);

And receive Exception with message "Precision"

This does not tell too much. What is the meaning of this message ?

StanislavK · Post by **StanislavK** » Thu 09 Dec 2010 17:38

You can change this or other model properties in the 'Model Settings' dialog in Entity Developer. To start this dialog, you can, e.g., double-click the root node of the model in Model Explorer.

We have reproduced the problem with generating MappingSource; we will investigate it and inform you about the results as soon as possible.

JIC: the message should inform that an unknown keyword encountered in the xml. In your case, this keyword is 'Precision'. We will also make the error message for such situations more informative.

listonic · Post by **listonic** » Sat 11 Dec 2010 09:48

Hello. Do you have any update on this ?
I need to support both databases Postgresql and SQL Server as soon as possible and it is really holding me back.

Do I have to manually remove Keywords that are not recognized by MappingSource from files that were generated by Entity Developer ?

listonic · Post by **listonic** » Mon 13 Dec 2010 09:03

Hello,
You are probably aware that

Unicode="true"
Scale="0"
MaxLength="6"

are not recognized as well...

Also when i remove unrecognized keywords from mapping and try to create context with created MappingSource :

using (ProxyfinalDataContext context = new ProxyfinalDataContext(ConfigurationManager.ConnectionStrings["MsSqlLinqConnectionString"].ConnectionString, mappingSource))

i receive exception

Provider type not found Devart.Data.SqlServer.Linq.Provider.SqlDataProvider, Devart.Data.SqlServer.Linq

I probably installed and uninstalled linqconnect to many times. Now at the Visual Studio Startup i receive error message "Devart.Data.VS.DevartDataProviderPackage" did not load correctly. In ActivityLog.xml i have :

338
2010/12/13 09:34:04.956
Error
VisualStudio
CreateInstance failed for package [Devart.Data.Vs.DevartDataProviderPackage]
{074D6159-E0E8-46D6-8ACE-E7D3152BF733}
80070002
Could not load file or assembly 'file:///C:\Program Files (x86)\Common Files\Devart\dotConnect\5.00\Net2\Devart.Data.Vs.dll' or one of its dependencies. Nie można odnaleźć określonego pliku.

And indeed there is no such file at this location.

What may be the cause ?

StanislavK · Post by **StanislavK** » Mon 13 Dec 2010 17:22

We are working on the problem with creating mappings from .lqml files; we will inform you as soon as our investigation is completed. At the moment, you can remove the problem keywords from the model manually.

As for the second problem, it appears to be caused by an error in installation. Please try performing the following:
- uninstall LinqConnect;
- remove all Devart.* and policy.*.Devart.* files from the GAC;
- clear the Program Files\Devart\dotConnect and Program Files\Common Files\Devart folders.
- re-install LinqConnect.
Please tell us if this helps.

listonic · Post by **listonic** » Tue 14 Dec 2010 09:04

Maybe the problem is that i kept switching between Linq Connect and Dotconnect for postgresql as I needed to continue development using Dotconnect. I uninstalled everything from Devart that i had on my computer, checked GAC and restarted the computer. I installed linq connect and the problem arised again.

I am using Visual Studio 2010 Ultimate, Windows 7 64 bit

Everything concerning devart from ActivityLog.xml (visual studio log)

300
2010/12/14 08:57:16.979
Information
VisualStudio
Begin package load [Devart.EntityDeveloper.Vs.EntityDeveloperPackage, Devart.EntityDeveloper.Vs, Version=3.0.50.0, Culture=neutral, PublicKeyToken=09af7300eec23701]
{F768FF79-B6CD-4D64-9D7D-0A44615D6DB3}

301
2010/12/14 08:57:19.122
Information
VisualStudio
End package load [Devart.EntityDeveloper.Vs.EntityDeveloperPackage, Devart.EntityDeveloper.Vs, Version=3.0.50.0, Culture=neutral, PublicKeyToken=09af7300eec23701]
{F768FF79-B6CD-4D64-9D7D-0A44615D6DB3}

306
2010/12/14 08:57:36.193
Information
VisualStudio
Begin package load [Devart.Data.Vs.DevartDataProviderPackage]
{074D6159-E0E8-46D6-8ACE-E7D3152BF733}

307
2010/12/14 08:57:36.292
Error
VisualStudio
CreateInstance failed for package [Devart.Data.Vs.DevartDataProviderPackage]
{074D6159-E0E8-46D6-8ACE-E7D3152BF733}
80070002
Could not load file or assembly 'file:///C:\Program Files (x86)\Common Files\Devart\dotConnect\5.00\Net2\Devart.Data.Vs.dll' or one of its dependencies. Nie można odnaleźć określonego pliku.

308
2010/12/14 08:57:36.313
Error
VisualStudio
End package load [Devart.Data.Vs.DevartDataProviderPackage]
{074D6159-E0E8-46D6-8ACE-E7D3152BF733}
80004005 - E_FAIL
Could not load file or assembly 'file:///C:\Program Files (x86)\Common Files\Devart\dotConnect\5.00\Net2\Devart.Data.Vs.dll' or one of its dependencies. Nie można odnaleźć określonego pliku.

41
2010/12/14 08:56:39.138
Information
VisualStudio
Begin package load [Devart.Data.Linq.Vs.LinqConnectProviderPackage]
{0513593D-0C4D-468B-BF9B-A0D4B926454A}

42
2010/12/14 08:56:39.389
Information
VisualStudio
End package load [Devart.Data.Linq.Vs.LinqConnectProviderPackage]
{0513593D-0C4D-468B-BF9B-A0D4B926454A}

listonic · Post by **listonic** » Tue 14 Dec 2010 21:42

Do you have any more suggestions about installation problem ? I am not willing to reinstall whole system.

listonic · Post by **listonic** » Wed 15 Dec 2010 10:06

Everything works when I have Attribute Mapping Mode set. It all crashes when I have file mode set.

With file mode i keep getting
Provider type not found Devart.Data.SqlServer.Linq.Provider.SqlDataProvider, Devart.Data.SqlServer.Linq

exception.

StanislavK · Post by **StanislavK** » Wed 15 Dec 2010 16:34

Please try specifying the version of LinqConnect in the .lqml file. I.e., please set the Provider parameter of the Database node to

Code: Select all

"Devart.Data.SqlServer.Linq.Provider.SqlDataProvider, Devart.Data.SqlServer.Linq, Version=2.0.8.0, Culture=neutral, PublicKeyToken=09af7300eec23701"

instead of

Code: Select all

"Devart.Data.SqlServer.Linq.Provider.SqlDataProvider, Devart.Data.SqlServer.Linq"

We will investigate the problem with using mappings created from lqml files where provider is not fully qualified, and inform you about the results.

StanislavK · Post by **StanislavK** » Thu 16 Dec 2010 12:27

As for the integration problem, it is caused by the Visual Studio attempts to load a package for dotConnect for PostgreSQL which is no longer available.

I will send you a .reg file in a letter, please check that it is not blocked by your mail filter. Please execute this .reg file and tell us if the problem with loading the package is resolved.

StanislavK · Post by **StanislavK** » Tue 28 Dec 2010 15:00

We have fixed the 'Provider type not found' error when loading an external mapping. The fix will be available in the nearest build which we plan to release in a week or two.