dotConnect Universal Documentation
In This Topic
    Using dotConnect Universal with SQL Client
    In This Topic

    This article provides a brief overview of using dotConnect Universal with the .NET Framework Data Provider for SQL Server, describes some useful features and helps you get started quickly.

    Overview

    The .NET Framework Data Provider for SQL Server is a standard 100% managed ADO.NET data provider for the .NET Framework. It is preinstalled with every version of the .NET Framework and the .NET Compact Framework, so you can be sure that the SQL Client is always available. For more information about the data provider refer to MSDN documentation.

    Compatibility

    dotConnect Universal is compatible with SQL Client version 1.05 and above. dotConnect Universal automatically uses SQL Client from appropriate .NET Framework.

    Deployment

    To deploy applications written with dotConnect Universal, copy the run-time assembly Devart.Data.Universal.dll to target machine. This assembly can be registered at the Global Assembly Cache (GAC) for the appropriate .NET Framework or placed in the folder of your application.

    For web projects, you may also need to deploy the Devart.Data.Universal.Web.dll and App_Licenses.dll assemblies. The Devart.Data.Universal.Web.dll assemblies are required for using the ASP.NET Provider Model, and the App_Licenses.dll assembly is a part of licensing mechanism.

    Web projects are usually run as partially trusted code. dotConnect Universal requires ReflectionPermission in addition to the medium trust level

    Connection String

    Suppose you want to connect to a SQL Server running on the host myserver, with the user name sa and the password mypassword. Suppose also that you would like to choose Northwind as the default database. In this case your connection string would look like this: 

    Provider=SQL Server;server=myserver;user id=sa;password=mypassword;initial catalog=Northwind

    The following table enumerates most important connection string parameters.

    Name

    Default

    Description

    Application Name

    N/A

    Specifies the name of the application, or '.NET SQLClient Data Provider' if no application name is provided.

    ApplicationIntent

    'ReadWrite'

    Declares the application workload type when connecting to a server. Possible values are ReadWrite (default) and ReadOnly, used for read-only routing in an Always On availability group.

    Asynch

    'false'

    Enables asynchronous operation support when set to true. Recognized values are true, false, yes, and no.

    AttachDBFilename

    -or-

    extended properties

    -or-

    Initial File Name

    N/A

    Specifies the name of the primary database file, including the full path name of an attachable database. The path may be absolute or relative by using the DataDirectory substitution string. If DataDirectory is used, the database file must exist within a subdirectory of the directory pointed to by the substitution string.

    The database name must be specified with the keyword 'database' (or one of its aliases) as in the following:

    "AttachDbFileName=|DataDirectory|\data\YourDB.mdf;integrated security=true;database=YourDatabase"

    Authentication

    N/A

    Specifies the method used to authenticate with SQL Server. Possible values are: SQL Password, Active Directory Password, and Active Directory Integrated.

    Column Encryption Setting

    'Disabled'

    Enables Always Encrypted functionality for the connection when set to Enabled.

    Connect Timeout

    -or-

    Connection Timeout

    15

    Specifies the length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.

    ConnectRetryCount

    1

    Specifies the number of reconnection attempts after identifying an idle connection failure. Valid values are 0 to 255.

    ConnectRetryInterval

    10

    Specifies the time (in seconds) between each reconnection attempt after identifying an idle connection failure. Valid values are 1 to 60.

    Context Connection

    'false'

    Indicates whether an in-process connection to SQL Server should be made, when set to true.

    Current Language

    N/A

    Specifies the SQL Server Language record name.

    Data Source

    -or-

    Server

    -or-

    Address

    -or-

    Addr

    -or-

    Network Address

    N/A

    Specifies the name or network address of the instance of SQL Server to which to connect. The port number can be specified after the server name:

    server=tcp:servername, portnumber

    When specifying a local instance, always use (local). To force a protocol, add one of the following prefixes:

    np:(local), tcp:(local), lpc:(local)

    Enclave Attestation Url

    N/A

    Specifies the URL of the attestation service used to verify a secure enclave when Always Encrypted with secure enclaves is enabled.

    Encrypt

    'false'

    Enables SSL encryption for all data sent between the client and server when set to true, provided the server has a certificate installed. Recognized values are true, false, yes, and no.

    Enlist

    'false'

    Indicates that the SQL Server connection pooler automatically enlists the connection in the creation thread's current transaction context, when set to true.

    Failover Partner

    N/A

    Specifies the name of the failover partner server where database mirroring is configured.

    The Failover Partner keyword is not supported by .NET Framework version 1.0 or 1.1.

    Initial Catalog

    -or-

    Database

    N/A

    Specifies the name of the database.

    Integrated Security

    -or-

    Trusted_Connection

    'false'

    Determines whether User ID and Password are specified in the connection (false) or the current Windows account credentials are used for authentication (true).

    Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.

    MultipleActiveResultSets

    'true'

    Enables an application to maintain multiple active result sets (MARS) when set to true. When false, an application must process or cancel all result sets from one batch before it can execute any other batch on that connection.

    Recognized values are true and false.

    The keyword is not supported by .NET Framework version 1.0 or 1.1.

    MultiSubnetFailover

    'false'

    Enables faster detection of, and connection to, the currently active server when set to true, for applications connecting to an AlwaysOn availability group or database mirroring configuration that spans multiple subnets.

    Network Library

    -or-

    Net

    'dbmssocn'

    Specifies the network library used to establish a connection to an instance of SQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmslpcn (Shared Memory) and dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP).

    The corresponding network DLL must be installed on the system to which you connect. If you do not specify a network and you use a local server (for example, "." or "(local)"), shared memory is used.

    Packet Size

    8192

    Specifies the size (in bytes) of the network packets used to communicate with an instance of SQL Server.

    Password

    -or-

    Pwd

    N/A

    Specifies the password for the SQL Server account logging on. Not recommended. To maintain a high level of security, we strongly recommend that you use the Integrated Security or Trusted_Connection keyword instead.

    Persist Security Info

    'false'

    Determines whether security-sensitive information, such as the password, is retained in the connection string after the connection has been opened. Resetting the connection string resets all connection string values including the password. Possible values are: true, false, yes, and no.

    PoolBlockingPeriod

    'Auto'

    Controls the connection pool blocking period behavior for the application. Possible values are Auto (default), AlwaysBlock, and NeverBlock.

    Replication

    'false'

    Indicates that replication is supported using the connection, when set to true.

    Transaction Binding

    'Implicit Unbind'

    Specifies the transaction binding behavior when a local transaction commits or rolls back. Possible values are Implicit Unbind (default) and Explicit Unbind.

    TransparentNetworkIPResolution

    'true'

    Enables the client to attempt connections to multiple IP addresses in parallel when set to true, reducing connection time if the first resolved IP address is unreachable.

    TrustServerCertificate

    'false'

    Uses SSL to encrypt the channel while bypassing certificate chain validation, when set to true. If the connection string has TrustServerCertificate set to true but Encrypt is not set to true, the channel is not encrypted. Recognized values are true, false, yes, and no. For more information, see "Encryption Hierarchy" and "Using Encryption Without Validation" in SQL Server 2005 Books Online.

    Type System Version

    N/A

    Indicates, as a string value, the type system the application expects. Possible values are:

    Type System Version=SQL Server 2000;

    Type System Version=SQL Server 2005;

    Type System Version=Latest;

    When set to SQL Server 2000, the SQL Server 2000 type system is used. The following conversions are performed when connecting to a SQL Server 2005 instance:

    XML to NTEXT

    UDT to VARBINARY

    VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) to TEXT, NEXT and IMAGE respectively.

    When set to SQL Server 2005, the SQL Server 2005 type system is used. No conversions are made for the current version of ADO.NET.

    When set to Latest, the latest version than this client-server pair can handle is used. This will automatically move forward as the client and server components are upgraded.

    User ID

    N/A

    Specifies the SQL Server login account. Not recommended. To maintain a high level of security, we strongly recommend that you use the Integrated Security or Trusted_Connection keywords instead.

    User Instance

    'false'

    Indicates whether to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller.

    Workstation ID

    The local computer name

    Specifies the name of the workstation connecting to SQL Server.

    The following table lists the valid names for connection pooling values within the ConnectionString.

    Name

    Default

    Description

    Connection Lifetime

    0

    Specifies the maximum lifetime (in seconds) of a pooled connection. When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online.

    A value of zero (0) causes pooled connections to have the maximum connection timeout.

    Connection Reset

    'true'

    Determines whether the database connection is reset when being drawn from the pool. For SQL Server version 7.0, setting to false avoids making an additional server round trip when obtaining a connection, but you must realize that the connection state, such as database context, is not being reset.

    The connection pooler is not influenced by the ChangeDatabase method as long you do not set Connection Reset to false. As the connection comes out of the pool the connection is reset with the server moving back to the login time database. There are no new connections created or reauthentications. If you set Connection Reset to false, connections in the pool to different databases might result.

    Enlist

    'true'

    Enlists the connection automatically in the creation thread's current transaction context when set to true. Recognized values are true, false, yes, and no.

    Load Balance Timeout

    0

    Specifies the minimum time (in seconds) for the connection to live in the connection pool before being destroyed.

    Max Pool Size

    100

    Specifies the maximum number of connections allowed in the pool.

    Min Pool Size

    0

    Specifies the minimum number of connections allowed in the pool.

    Pooling

    'true'

    Draws the SQLConnection object from the appropriate pool, or creates and adds it to the appropriate pool if required, when set to true. Recognized values are true, false, yes, and no.

    Quick Start Steps

    To get up and running quickly, you have to perform the following steps:

    1. Embed dotConnect Universal licensing information into your application. For more details on licensing refer to the Licensing article. It may be enough to drop a UniConnection component onto a form designer to embed the licensing information.
    2. Create a UniConnection object.
    3. Set the ConnectionString property of the UniConnection object to a string containing the part Provider=SQL Server.
    4. Create a UniCommand object and link it to the UniConnection object.
    5. Open the connection.

    That's all you need to start executing queries on the server. Below is a code fragment that illustrates steps 2-5. It also shows how to perform INSERT and SELECT operations. 

    UniConnection connection = new UniConnection("Provider=SQL Server;server=myserver;user id=sa;password=mypassword;initial catalog=Northwind");
try {
  UniCommand cmd = connection.CreateCommand();
  cmd.CommandText = "INSERT INTO dept (DeptNo, Dname, Loc) VALUES (:DeptNo, :DName, :Loc)";
  UniParameter parameter = cmd.Parameters.Add("DeptNo", UniDbType.Int);
  parameter.Value = 150;
  parameter = cmd.Parameters.Add("DName", UniDbType.VarChar);
  parameter.Value = "DEPTNAME";
  cmd.Parameters.Add(parameter);
  parameter = cmd.Parameters.Add("Loc", UniDbType.VarChar);
  parameter.Value = "LOCATION";
  cmd.Parameters.Add(parameter);
  connection.Open();
  cmd.ExecuteNonQuery();
}
finally {
  connection.Close();
}

...

DataSet dataSet = new DataSet();
UniDataAdapter da = new UniDataAdapter("SELECT * FROM dept", "Provider=SQL Server;server=myserver;user id=sa;password=mypassword;initial catalog=Northwind");
da.Fill(dataSet, "Dept");

    
Dim connection As Devart.Data.Universal.UniConnection = New _
  Devart.Data.Universal.UniConnection("Provider=SQL Server;server=myserver;user id=sa;password=mypassword;initial catalog=Northwind")
Try
  Dim cmd As UniCommand = connection.CreateCommand()
  cmd.CommandText = "INSERT INTO dept (DeptNo, DName, Loc) VALUES (:DeptNo, :DName, :Loc)"
  Dim parameter As Devart.Data.Universal.UniParameter = cmd.Parameters.Add("DEPTNO", UniDbType.Int)
  parameter.Value = 150
  parameter = cmd.Parameters.Add("DName", UniDbType.VarChar)
  parameter.Value = "DEPTNAME"
  parameter = cmd.Parameters.Add("Loc", UniDbType.VarChar)
  parameter.Value = "LOCATION"
  connection.Open()
  cmd.ExecuteNonQuery()
Finally
  connection.Close()
End Try

...

Dim dataSet As DataSet = New DataSet()
Dim da As UniDataAdapter = New UniDataAdapter("Provider=SQL Server;server=myserver;user id=sa;password=mypassword;initial catalog=Northwind")
da.Fill(dataSet, "Dept")

    Special Features

    dotConnect Universal supports handling SQL Server BLOBs. For detailed information on how to work with BLOB objects, refer to the Working with BLOB Data article.

    dotConnect Universal supports the SQL Server DECIMAL type. Use the UniDecimal structure reference to find out how to manage big numbers on client side.

    Note that SQL Server does not support INTERVAL database types. However, in most cases this restriction can be compensated with DateAdd and DateDiff functions.

    UniSQL Notes

    SQL Client supports all features of UniSQL. Use SQL Server and SQLClient predefined macros to build conditional UniSQL statements. For more information refer to the UniSQL topic.

    UniDbType Map

    The following table shows how the UniDbType enumeration maps to the SQL Server database type.

    UniDbType SQL Server Type
    BigInt BIGINT
    Binary BINARY
    Bit BIT
    Blob IMAGE
    Boolean BIT
    Byte SMALLINT
    Char CHAR
    Clob TEXT
    Currency DOUBLE
    Date DATETIME
    DateTime DATETIME
    DateTime2 DATETIME2
    Decimal DECIMAL
    Double REAL
    Guid UNIQUEIDENTIFIER
    Int INT
    NChar NCHAR
    NClob NTEXT
    NVarChar NVARCHAR
    Single FLOAT
    SmallInt SMALLINT
    TinyInt TINYINT
    Time DATETIME
    TimeStamp DATETIME
    VarChar VARCHAR

    Additional Information

    For more information on the .NET Framework Data Provider for SQL Server refer to MSDN.

    See Also

    Using dotConnect Universal with dotConnect for Oracle  | Using dotConnect Universal with dotConnect for MySQL  | Using dotConnect Universal with dotConnect for PostgreSQL  | Working with BLOB Data

     

     

    © 2004 - 2026 Devart. All Rights Reserved.

    [email protected]