Connecting to SQream Using .NET

The SqreamNet ADO.NET Data Provider lets you connect to SQream through your .NET environment.

The .NET page includes the following sections:

Integrating SQreamNet

The Integrating SQreamNet section describes the following:

Prerequisites

The SqreamNet provider requires a .NET version 6 or newer.

Getting the DLL file

The .NET driver is available for download from the client drivers download page.

Integrating SQreamNet

After downloading the .NET driver, save the archive file to a known location. Next, in your IDE, add a Sqreamnet.dll reference to your project.

If you wish to upgrade SQreamNet within an existing project, you may replace the existing .dll file with an updated one or change the project’s reference location to a new one.

Known Driver Limitations

  • Unicode characters are not supported when using INSERT INTO AS SELECT.

  • To avoid possible casting issues, use getDouble when using FLOAT.

Connecting to SQream For the First Time

An initial connection to SQream must be established by creating a SqreamConnection object using a connection string.

Connection String

To connect to SQream, instantiate a SqreamConnection object using this connection string.

The following is the syntax for SQream:

"Data Source=<hostname or ip>,<port>;User=<username>;Password=<password>;Initial Catalog=<database name>;Integrated Security=true";

Connection Parameters

Item

State

Default

Description

<data source>

Mandatory

None

Hostname/IP/FQDN and port of the SQream DB worker. For example, 127.0.0.1:5000, sqream.mynetwork.co:3108

<initial catalog>

Mandatory

None

Database name to connect to. For example, master

<username>

Mandatory

None

Username of a role to use for connection. For example, username=rhendricks

<password>

Mandatory

None

Specifies the password of the selected role. For example, password=Tr0ub4dor&3

<service>

Optional

sqream

Specifices service queue to use. For example, service=etl

<ssl>

Optional

false

Specifies SSL for this connection. For example, ssl=true

<cluster>

Optional

true

Connect via load balancer (use only if exists, and check port).

Connection String Examples

The following is an example of a SQream cluster with load balancer and no service queues (with SSL):

Data Source=sqream.mynetwork.co,3108;User=rhendricks;Password=Tr0ub4dor&3;Initial Catalog=master;Integrated Security=true;ssl=true;cluster=true;

The following is a minimal example for a local standalone SQream database:

Data Source=127.0.0.1,5000;User=rhendricks;Password=Tr0ub4dor&3;Initial Catalog=master;

The following is an example of a SQream cluster with load balancer and a specific service queue named etl, to the database named raviga

Data Source=sqream.mynetwork.co,3108;User=rhendricks;Password=Tr0ub4dor&3;Initial Catalog=raviga;Integrated Security=true;service=etl;cluster=true;

Sample C# Program

You can download the .NET Application Sample File below by right-clicking and saving it to your computer.

.NET Application Sample
 1        public void Test()
 2        {
 3            var connection = OpenConnection("192.168.4.62", 5000, "sqream", "sqream", "master");
 4
 5            ExecuteSQLCommand(connection, "create or replace table tbl_example as select 1 as x , 'a' as y;");
 6
 7            var tableData = ReadExampleData(connection, "select * from tbl_example;");
 8        }
 9
10        /// <summary>
11        /// Builds a connection string to sqream server and opens a connection
12        /// </summary>
13        /// <param name="ipAddress">host to connect</param>
14        /// <param name="port">port sqreamd is running on</param>
15        /// <param name="username">role username</param>
16        /// <param name="password">role password</param>
17        /// <param name="databaseName">database name</param>
18        /// <param name="isCluster">optional - set to true when the ip,port endpoint is a server picker process</param>
19        /// <returns>
20        /// SQream connection object
21        /// Throws SqreamException if fails to open a connction
22        /// </returns>
23        public SqreamConnection OpenConnection(string ipAddress, int port, string username, string password, string databaseName, bool isCluster = false)
24        {
25            // create the connection string according to the format
26            var connectionString = string.Format(
27                "Data Source={0},{1};User={2};Password={3};Initial Catalog={4};Cluster={5}",
28                ipAddress,
29                port,
30                username,
31                password,
32                databaseName,
33                isCluster
34                );
35
36            // create a sqeram connection object
37            var connection = new SqreamConnection(connectionString);
38
39            // open a connection
40            connection.Open();
41
42            // returns the connection object
43            return connection;
44        }
45
46        /// <summary>
47        /// Executes a SQL command to sqream server
48        /// </summary>
49        /// <param name="connection">connection to sqream server</param>
50        /// <param name="sql">sql command</param>
51        /// <exception cref="InvalidOperationException"> thrown when the connection is not open</exception>
52        public void ExecuteSQLCommand(SqreamConnection connection, string sql)
53        {
54            // validates the connection is open and throws exception if not
55            if (connection.State != System.Data.ConnectionState.Open)
56                throw new InvalidOperationException(string.Format("connection to sqream is not open. connection.State: {0}", connection.State));
57
58            // creates a new command object utilizing the sql and the connection
59            var command = new SqreamCommand(sql, connection);
60
61            // executes the command
62            command.ExecuteNonQuery();
63        }
64
65        /// <summary>
66        /// Executes a SQL command to sqream server, and reads the result set usiing DataReader
67        /// </summary>
68        /// <param name="connection">connection to sqream server</param>
69        /// <param name="sql">sql command</param>
70        /// <exception cref="InvalidOperationException"> thrown when the connection is not open</exception>
71        public List<Tuple<int, string>> ReadExampleData(SqreamConnection connection, string sql)
72        {
73            // validates the connection is open and throws exception if not
74            if (connection.State != System.Data.ConnectionState.Open)
75                throw new InvalidOperationException(string.Format("connection to sqream is not open. connection.State: {0}", connection.State));
76
77            // creates a new command object utilizing the sql and the connection
78            var command = new SqreamCommand(sql, connection);
79
80            // creates a reader object to iterate over the result set
81            var reader = (SqreamDataReader)command.ExecuteReader();
82
83            // list of results
84            var result = new List<Tuple<int, string>>();
85
86            //iterate the reader and read the table int,string values into a result tuple object
87            while (reader.Read())
88                result.Add(new Tuple<int, string>(reader.GetInt32(0), reader.GetString(1)));
89
90            // return the result set
91            return result;
92        }