Using Sqreamnet - SQream DB Native Driver for .NET Environments

About

SqreamNet is an ADO.NET Data Provider for SQream DB. it allows programs written on the .NET platform to access a SQream DB database server.

It is implemented in C# code and is provided by SQream Technologies for use with SQream DB installations.

Getting Started

System Requirements

.NET Framework 4.0 or newer is required to utilize the provider.

💡 To verify the correct version is installed, see the %windir%\Microsoft.NET\Framework\ directory (where "%windir%" represents "Windows" directory present in the system drive where Windows is installed in your system e.g. C:\Windows\).
You should see a v4.x.x directory if .NET is installed.

If not, please follow instructions for installation from the Microsoft website https://www.microsoft.com/en-us/download/details.aspx?id=17851

Using the provider in your projects

First time installation

  1. Unzip the provided archive to the desired location
  2. Add a reference to SqreamNet.dll from the project.

Upgrading

To upgrade the provider for an existing project, just replace the DLL file, or point the project to the new driver location.

Creating an initial connection to SQream DB

Connecting to SQream DB is established by creating a SqreamConnection object with a connection string.

💡 Import the SqreamNet namespace to your source code:

using SqreamNet;

The following example shows how to connect to a server that listens on port 5000 on the local machine.

using SqreamNet; 

var connectionString = "Data Source=127.0.0.1,5000;User=sqream;Password=sqream;Initial \ Catalog=master;Integrated Security=true";
var connection = new SqreamConnection(connectionString);
connection.Open();
// Tip: Validate the connection was opened
if (connection.State != System.Data.ConnectionState.Open)
{
    throw new SqreamException(string.Format("failed to connect to sqream"));
}

Issuing a query - Data Reader

Reading data from SQream DB is achieved by using the SqreamDataReader Object which implements the IDataReader interface.

Sample

The following example shows how to read all the records in “books” table.

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
}

public List<Book> GetAllBooks(SqreamConnection connection)
{
    var sql = "select id , title , author from books;";
    var command = new SqreamCommand(sql, connection);
    var reader = (SqreamDataReader)command.ExecuteReader();
    var result = new List<Book>();
    while (reader.Read())
    {
        var book = new Book
        {
            Id = reader.GetInt32(0),
            Title = reader.GetString(1),
            Author = reader.GetString(2)
        };

        result.Add(book);
    }
    return result;
}

Extending the provider - Query Sqream with dynamic data

When the data structure of the object is unknown, saving pre-defined indexes will not help.
In this situation, it is recommended that you implement an extension method, which is an ability the .NET framework provides.

The following sample suggests a possible implementation to “wrap” the reader object so it would read the data, independent from query or data types.

💡 Classes and list object are used to illustrate the functionality. This should be adjusted in your own program according to your needs.

    /// <summary>
    /// Extension to a collection class which contains rows of data
    /// </summary>
    public class SqreamCollectionEx
    {
        public List<SqreamRowEx> Rows;
    }

    /// <summary>
    /// Row of data which contains columns data
    /// </summary>
    public class SqreamRowEx
    {
        public List<SqreamColumnEx> Columns;
    }

    /// <summary>
    /// Column Data
    /// </summary>
    [DebuggerDisplay("Index: {Index} , Name: {Name} , Type: {TypeName} , Value: {Value}")]
    public class SqreamColumnEx
    {
        public int Index { get; set; }
        public string Name { get; set; }
        public string TypeName { get; set; }
        public object Value { get; set; }
    }

    /// <summary>
    /// Reads the collection, extends sqream data reader
    /// </summary>
    public static class SqreamNetEx
    {
        public static SqreamCollectionEx ReadCollectionEx(this SqreamDataReader reader)
        {
            var result = new SqreamCollectionEx();
            result.Rows = new List<SqreamRowEx>();
            while (reader.Read())
            {
                var row = new SqreamRowEx();
                row.Columns = new List<SqreamColumnEx>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var column = new SqreamColumnEx();
                    column.Index = i;
                    column.TypeName = reader.GetDataTypeName(i);
                    column.Name = reader.GetName(i);

                    switch (reader.GetDataTypeName(i))
                    {
                        case "Boolean":
                            column.Value = reader.GetBoolean(i);
                            break;
                        case "Int32":
                            column.Value = reader.GetInt32(i);
                            break;
                        case "Int64":
                            column.Value = reader.GetInt64(i);
                            break;
                        case "Single":
                            column.Value = reader.GetFloat(i);
                            break;
                        case "Double":
                            column.Value = reader.GetDouble(i);
                            break;
                        case "DateTime":
                            column.Value = reader.GetDateTime(i);
                            break;
                        case "String":
                            column.Value = reader.GetString(i);
                            break;
                        default:
                            throw new SqreamException(string.Format("invalid type in index: {0}", i));
                    }
                    row.Columns.Add(column);
                }
                result.Rows.Add(row);
            }
            return result;
        }    
}

// This code uses the extension method to retrieve the data:
var sql = "select id , title , author from books;";
var command = new SqreamCommand(sql, connection);
var reader = (SqreamDataReader)command.ExecuteReader();
var result = reader.ReadCollectionEx();

💡 Debugging Screenshot:

Error Handling:

When working with the provider, it is recommended that error handling will be implemented on a per-project level.

Common Exceptions are:

  • Unsupported Functionalities – NotSupportedException
  • Functionality that was not implemented – NotImplementedException
  • SQream DB parsing or runtime errors – SqreamException
  • 127
  • 23-Mar-2017
  • 538 Views