Usage from .Net

The general manner how to use it in .Net, is same as using SQL Server, MySQL or Oracle. For faster development you can use imports (vb.net) or using (c#) directives to save time in typing fully qualified namespace class names and to access static members.

The main name space is SQLDatabase.Net.SQLDatabaseClient and most methods starts with SqlDatabase e.g. SqlDatabaseConnection, SqlDatabaseCommand , SqlDatabaseConnectionStringBuilder etc.

Connection String

You can use direct connection string or use the ConnectionStringBuilder Class to build connection string, it requires minimum of Schema Name and File path for in memory databaes using @memory instead of file name. If no schema name is provided sdbn is used as default. The datasource or data source or uri have same meaning.

ASP.NET

In Asp.net if database is in App_Data folder DataDirectory keyword can be used. Read and Write permissions are required either on directory such as App_Data , database file etc, if database does not exists Empty database will be created by default this behavior can be controlled using DatabaseFileMode settings.

       
--SchemaName=primary is optional can be SchemaName=dbo etc
          
-- Keyword Datasource and Uri are interchangeable, @memory is for in-memory databases.

   
"SchemaName=sdbn;datasource=file://@memory;"


"SchemaName=primary;datasource=file:FILE PATH GOES HERE;"


ASP.Net (App_Data)


"SchemaName=primary;datasource=|DataDirectory|FILE NAME;"


Encryption


"SchemaName=primary;datasource=|DataDirectory|FILE NAME;"

--Then use SYSCMD Key or SYSCMD ReKey check How to page for more details.
                

Adding Reference

Once you add the reference use the USING or IMPORTS directive. e.g. using SQLDatabase.Net.SQLDatabaseClient;

Usage with C#

                    
using (SqlDatabaseConnection connectionObject = new SqlDatabaseConnection())
{
 connectionObject.ConnectionString = "SchemaName=db;datasource=file://c:\\Databases\\db1.db;";
    try
    {
        connectionObject.Open();
    } catch(SqlDatabaseException ex)
    {

    }

    if (connectionObject.State != ConnectionState.Open)
        return;

    using (SqlDatabaseCommand commandObject = new SqlDatabaseCommand())
    {
        //Execute non query such as create, update, delete, insert
        commandObject.Connection = connectionObject;
        commandObject.CommandText = "CREATE TABLE;";
        commandObject.ExecuteNonQuery();

        //Load Datatable
        commandObject.CommandText = "SELECT * FROM TABLE NAME;";
        DataTable dt = new DataTable();
        SqlDatabaseDataAdapter dtAdapter = new SqlDatabaseDataAdapter();
        dtAdapter.SelectCommand = commandObject;
        dtAdapter.Fill(dt);

        //SQL Data Reader
        commandObject.CommandText = "SELECT * FROM TABLE NAME;";
        SqlDatabaseDataReader dr = commandObject.ExecuteReader();

        //Scalar (returns single value)
        commandObject.CommandText = "SELECT Column1 FROM Table Name LIMIT 1; ";
        commandObject.ExecuteScalar();
    }
    connectionObject.Close();
}
                    
                

VB.Net Example

VisualBasic.Net is fully supported.

                    
                        
Using connectionObject As New SqlDatabaseConnection()
	connectionObject.ConnectionString = "SchemaName=db;datasource=file://c:\Databases\db1.db;"
	Try
		connectionObject.Open()

	Catch ex As SqlDatabaseException
	End Try

	If connectionObject.State <> ConnectionState.Open Then
		Return
	End If

	Using commandObject As New SqlDatabaseCommand()
		'Execute non query such as create, update, delete, insert
		commandObject.Connection = connectionObject
		commandObject.CommandText = "CREATE TABLE;"
		commandObject.ExecuteNonQuery()

		'Load Datatable
		commandObject.CommandText = "SELECT * FROM TABLE NAME;"
		Dim dt As New DataTable()
		Dim dtAdapter As New SqlDatabaseDataAdapter()
		dtAdapter.SelectCommand = commandObject
		dtAdapter.Fill(dt)

		'SQL Data Reader
		commandObject.CommandText = "SELECT * FROM TABLE NAME;"
		Dim dr As SqlDatabaseDataReader = commandObject.ExecuteReader()

		'Scalar (returns single value)
		commandObject.CommandText = "SELECT Column1 FROM Table Name LIMIT 1; "
		commandObject.ExecuteScalar()
	End Using
	connectionObject.Close()
End Using

                    
                

Transaction

Database also support BEGIN and END transaction but nested transactions are not supported. SavePoint can be used for transaction safety and nesting. See Example code on GitHub.

C# Transaction Example

                    
using (SqlDatabaseConnection connectionObject = new SqlDatabaseConnection())
{
 connectionObject.ConnectionString = "SchemaName=db;datasource=file://c:\\Databases\\db1.db;";
    try
    {
        connectionObject.Open();
    } catch(SqlDatabaseException ex)
    {
        //error handling here e.g. file or folder does not exists.
        //Folder does not have read and write permission etc
    }

    if (connectionObject.State != ConnectionState.Open)
        return;

    using (SqlDatabaseCommand commandObject = new SqlDatabaseCommand())
    {
        SqlDatabaseTransaction trans;                                         
        trans = connectionObject.BeginTransaction();
        commandObject.Transaction = trans;
        for (int i = 0; i < 5000; i++)
        {
            commandObject.CommandText = "INSERT INTO SOME TABLE;";
            commandObject.ExecuteNonQuery();
        }
        trans.Commit();
        //trans.Rollback();
    }
    connectionObject.Close();
}
                    
                

                    
                        
Using connectionObject As New SqlDatabaseConnection()
 connectionObject.ConnectionString = "SchemaName=db;datasource=file://c:\Databases\db1.db;"
	Try
		connectionObject.Open()			
	Catch ex As SqlDatabaseException
                'error handling here e.g. file or folder does not exists.
		'Folder does not have read and write permission etc
	End Try

	If connectionObject.State <> ConnectionState.Open Then
		Return
	End If

	Using commandObject As New SqlDatabaseCommand()
		Dim trans As SqlDatabaseTransaction
		trans = connectionObject.BeginTransaction()
		commandObject.Transaction = trans
		For i As Integer = 0 To 4999
			commandObject.CommandText = "INSERT INTO SOME TABLE;"
			commandObject.ExecuteNonQuery()
		Next
		'trans.Rollback();
		trans.Commit()
	End Using
	connectionObject.Close() 'Optional End Using will close & dispose
End Using