SQreamNET

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

Before You Begin

• The SQreamNET provider requires a .NET version 6 or newer

• Download the SQreamNET driver from the client drivers page

Integrating SQreamNET

1. After downloading the .NET driver, save the archived file to a known location.

2. In your IDE, add a SQreamNET.dll reference to your project.

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

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 Syntax

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

        public void Test()
        {
            var connection = OpenConnection("192.168.4.62", 5000, "sqream", "sqream", "master");

            ExecuteSQLCommand(connection, "create or replace table tbl_example as select 1 as x , 'a' as y;");

            var tableData = ReadExampleData(connection, "select * from tbl_example;");
        }

        /// <summary>
        /// Builds a connection string to sqream server and opens a connection
        /// </summary>
        /// <param name="ipAddress">host to connect</param>
        /// <param name="port">port sqreamd is running on</param>
        /// <param name="username">role username</param>
        /// <param name="password">role password</param>
        /// <param name="databaseName">database name</param>
        /// <param name="isCluster">optional - set to true when the ip,port endpoint is a server picker process</param>
        /// <returns>
        /// SQream connection object
        /// Throws SqreamException if fails to open a connction
        /// </returns>
        public SqreamConnection OpenConnection(string ipAddress, int port, string username, string password, string databaseName, bool isCluster = false)
        {
            // create the connection string according to the format
            var connectionString = string.Format(
                "Data Source={0},{1};User={2};Password={3};Initial Catalog={4};Cluster={5}",
                ipAddress,
                port,
                username,
                password,
                databaseName,
                isCluster
                );

            // create a sqeram connection object
            var connection = new SqreamConnection(connectionString);

            // open a connection
            connection.Open();

            // returns the connection object
            return connection;
        }

        /// <summary>
        /// Executes a SQL command to sqream server
        /// </summary>
        /// <param name="connection">connection to sqream server</param>
        /// <param name="sql">sql command</param>
        /// <exception cref="InvalidOperationException"> thrown when the connection is not open</exception>
        public void ExecuteSQLCommand(SqreamConnection connection, string sql)
        {
            // validates the connection is open and throws exception if not
            if (connection.State != System.Data.ConnectionState.Open)
                throw new InvalidOperationException(string.Format("connection to sqream is not open. connection.State: {0}", connection.State));

            // creates a new command object utilizing the sql and the connection
            var command = new SqreamCommand(sql, connection);

            // executes the command
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Executes a SQL command to sqream server, and reads the result set usiing DataReader
        /// </summary>
        /// <param name="connection">connection to sqream server</param>
        /// <param name="sql">sql command</param>
        /// <exception cref="InvalidOperationException"> thrown when the connection is not open</exception>
        public List<Tuple<int, string>> ReadExampleData(SqreamConnection connection, string sql)
        {
            // validates the connection is open and throws exception if not
            if (connection.State != System.Data.ConnectionState.Open)
                throw new InvalidOperationException(string.Format("connection to sqream is not open. connection.State: {0}", connection.State));

            // creates a new command object utilizing the sql and the connection
            var command = new SqreamCommand(sql, connection);

            // creates a reader object to iterate over the result set
            var reader = (SqreamDataReader)command.ExecuteReader();

            // list of results
            var result = new List<Tuple<int, string>>();

            //iterate the reader and read the table int,string values into a result tuple object
            while (reader.Read())
                result.Add(new Tuple<int, string>(reader.GetInt32(0), reader.GetString(1)));

            // return the result set
            return result;
        }

Limitations

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

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

• The ARRAY data types is not supported. If your database schema includes ARRAY columns, you may encounter compatibility issues when using SQreamNET to connect to the database.