Trying to Configure the UDL file for C#

[WayneMorgan]

Trying to Configure the .UDL file for the Correct SQL Server Driver for C# (.NET Generator)

 

When Running on a local machine with Full DB Access

Using OLEDB Driver  - the Code fails with ePLEX999 and cannot cast the Date/Times when displaying the fields on the Panel

Using the SQL NATIVE Driver - the Date Do convert (no code changes)

 

When Using a QA/Prod Remote Server with SQL Server Authority Defined

- cannot use the OLD DB Driver as the dates fail

- trying to Configure the UDL file to force a connection to prompt for a user to connect to the SQL Server for Credentials

 

our Windows Group Policy prevents Us from saving the Connection password in the UDL File  - so need a way to have the Connection prompt for User/pwd or even store the user ID and require the Password

 

I have tried numerous options and it fails each time - trying to take my Windows (Domain/user) id and trying to connect to the Database - even though I need to use a SQl user account to connect

 

Any ideas please....

[George Jeffcock]

What version of Plex are you at?

 

If Plex7.0 or above you could look at connection to SQL server with System.Data.SqlClient.SqlConnection without using UDL file and therefore passords etc saved to your configuration file encrypted

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx

 

As I am not yet at plex7  I was schooled this by RE: sql_preserv cursor on .net server while trying to answer another connection issue

[Shefeeque]

Hello Wayne...

 

I had played with SQL server/Plex 7.0 successfully sometime back.. Did you select the Data provider as OLEDB in config file?.

 

Shefeeque

[WayneMorgan]

As I Stated above - the 2 drivers have direct effects

"

Using OLEDB Driver  - the Code fails with PLEX999 and cannot cast the Date/Times when displaying the fields on the Panel

Using the SQL NATIVE Driver - the Date Do convert (no code changes)

"

so causes a Date Error when trying to display the date/times

 

 

[George Jeffcock]

What version of Plex are you at?

 

If Plex7.0 or above you could look at connection to SQL server with System.Data.SqlClient.SqlConnection without using UDL file and therefore passords etc saved to your configuration file encrypted

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx

 

As I am not yet at plex7  I was schooled this by RE: sql_preserv cursor on .net server while trying to answer another connection issue

[WayneMorgan]

I am at 7.1 Using the C# .NET Generator -

 

for the current application I am trying to run a local (Client app) no .NET Server  - so the connection to the DB is made from the client -. the app.exe.config file pointsto the url - the problem is that the OLD DB Driver for SQL Server (2008) behaves differently than the SQL Native driver - we are also using SQL Server Authentication - not Trusted in this case - 

 

In the WinC Generator - you can leave the user/pwd blank and the OLD DB Driver and the Native driver prompts for the connection - the .NET C# geberator does not - it connects using the Windows id.

[Anon Anon]

Hi Wayne,

 

You should be able to connect like this:

HTH

Lorenz

[WayneMorgan]

Lorenz

 

I am not using the Plex C## management console/App server component - just making a direct connection from the Client so reliant on using the .udl file

[Anon Anon]

Wayne -

 

In any case the yourapp.exe.config file has to be set correctly. This can be done using the Application Management Console or by manually editing the config file. I prefer the editor :-) .

 

In my opinion there is no difference in establishing DB connections between "standalone" applications and using a Plex dispatcher. The runtime is the same in both scenarios.

 

The UDL file is not necessary.

 

Excerpt from Plex Help:

 

Accessing Data with .NET Functions

CA Plex C# functions access databases using either Microsoft’s OLE DB or SQL Client interfaces. Both providers support all standard CA Plex data access features such as multiple database connections and stored procedures. OLE DB is the underlying technology used by ADO.NET and enables the Plex .NET runtime to access MS Access, SQL Server, Oracle and other databases. SQL Client is the .NET data provider for SQL Server, and allows optimized access to SQL Server and SQL Azure Services. In addition to the Plex online help, see the Visual Studio online help and the MSDN for more information about the data access technologies.

At design time, the language setting of ODBC is recommended for table, views, and stored procedures (functions). This setting primarily determines how the database objects are generated and built. These settings have no effect on the data access technology used by the associated C# functions, which are determined at run time. When you generate and build your database schemas from Plex, you use an ODBC datasource to create the schemas in your target database. Then at runtime you configure either a UDL file (OLE.DB) or connection string (SQL Client) to access the same database.

 

Related Topics:

Using the CA Plex .NET Management Console

[George Jeffcock]

Exactly Lorenz and what my link says from Kiyoshi RE: sql_preserv cursor on .net server

 

 

 

 

Some interesting reading from ODBC administrator panel

 

Specify the method of authentication, and set up Microsoft SQL Server advanced-client entries and the login and password the SQL Native Client ODBC driver will use to connect to SQL Server while configuring the data source.

Options

With Integrated Windows authentication using the network login ID option button

Specifies that the SQL Native Client ODBC driver request a secure (or trusted) connection to a SQL Server running on Microsoft Windows XP, Windows 2000, or Windows 2003. When this button is selected, SQL Server uses integrated login security to establish connections using this data source, regardless of the current login security mode at the server. Any login ID or password supplied is ignored. The SQL Server system administrator must have associated your Windows login with a SQL Server login ID.

With SQL Server authentication using a login ID and password entered by the user option button

Specifies that the SQL Native Client ODBC driver not request a secure (or trusted) connection to SQL Server. When this button is selected, SQL Server uses standard login security to establish connections using this data source. You must specify a SQL Server login ID and password for all connection requests.

Connect to SQL Server to obtain default settings for the additional configuration options check box,

When this check box is selected, the SQL Native Client ODBC driver obtains initial settings from SQL Server for the options on the following screens of the wizard. The SQL Native Client ODBC driver connects to the instance of SQL Server named in the Server box on the first screen.

When this check box is cleared, the driver uses standard defaults as the initial settings for the options on the following screens in the wizard.

Login ID box

Specifies the login ID the SQL Native Client ODBC driver uses when connecting to SQL Server if With SQL Server authentication using a login ID and password entered by the user is selected. This only applies to the connection made to determine the server default settings; it does not apply to subsequent connections made using the data source after it has been created.

Password box

Specifies the password the SQL Native Client ODBC driver uses when connecting to SQL Server if With SQL Server authentication using a login ID and password entered by the user is selected. This only applies to the connection made to determine the server default settings; it does not apply to subsequent connections made using the new data source.

Both the Login ID and Password boxes are disabled if With Integrated Windows authentication using the network login ID is selected, or if Connect to SQL Server to obtain default settings for the additional configuration options is cleared.

[WayneMorgan]

Understood on the Config file - I can connect  - however I get different results when using the date/time fields depending on the combination of driver I use - so I get date errors using one driver and connection issues using the other

 

on my DEV pc -

Trusted Authority to the SQL DB  = Yes

Plex Config File: Specifes a udl file

the udl file Uses the SQL Native driver and specifies Integrated Security

 

in this setup I can read and write/update records with DateISO/Time ISO values

 

If I switch the udl file to use the OLE DB provider - the data will read & update but I get Errors

'plex999 - Specified cast not valid and 'No Data Exists for the row column

 

the ONLY thing changed here was the provider in the UDL file

 

If I switch this to the SQLCLIENT in the PLEX config file - the plex connects using the usr/pwd in the config file  - but will not connect using my user/pwd that I use on my PC as it is a Domain/User accoun

 

 

if I take this same code to our shared DEV environment  - that we do NOT have integrated security to we need to specify a Specific user to connect to the SQL Database - I cannot use the udl file as we cannot save the Password

 

if I switch to SQLCLIENT in the Plex config file - I can get the client to connect and display the data rows  - including Date/Times - however  - I get data conversion error when Updating the rows - Even though I have checked the values of the dates immediately prior to update and  they are valid (and they update correctly in scenario 1 above)

 

 

just trying to determine the best combination (or restrictions that exist)  when using which driver as pertaining to the date/time operations

[Anon Anon]

Hi Wayne -

 

a) Please indicate the version of SQL server you are using.

b) What SQL type is used to implement the dates and times ?  Is it "datetime" or the newer  types "date" and "time" ?

c) What is the version of the Native Driver you are using ?

 

TIA

Lorenz

[WayneMorgan]

Lorenz

 

SQL Server 8

Data Types are Date & Time as individual Fields NOT a Datetime

SQL Native Driver 10.0

 

 

 

 

[Anon Anon]

Wayne,

 

so your date and time fields are created like this :

 

using Plex to create the table via ODBC :

Result in SQL Server

 

 

I am asking this, because older ODBC drivers will create "datetime" types for each iso date or iso time.

 

There are two other areas to look into:

 

What is the db  configuration used in the .exe.config file and wht is the culture used ?

 

is it like this ?

 

[WayneMorgan]

All match your details above with the following Exceptions..

 

we did not build this table using Plex as it was part of another System - however the data types in SQL match the plex generated ones (date & Time not datetime)

 

 

We only have 2 drivers available to use

'SQL Server Native Client 10.0'

Microsoft OLE DB provider for SQL

 

 

[Anon Anon]

Wayne -

 

are you getting this error ?   Cannot convert DateTime to TimeSpan ?  There seems to be a bug in the runtime....

 

[WayneMorgan]

Yes - When Using the SQLCLIENT -  that is the Error I get when I am trying to perform an Update or Insert of a record - reading the data does NOT appear to generate that error

 

 

 

 

[Anon Anon]

Wayne -

 

I opened a support ticket with CA regarding this problem. (21897411-01)

 

Hope we will have a fix for this soon.

 

Best

Lorenz

[George Jeffcock]

bravo Lorenz, level 2 support in your spare time.