This article is a brief introduction about IVR database integration in relation with Ozeki VoIP SIP SDK. After reading through this page you will be fully familiar with all the essential terms concerning IVR databases and what you will need for creating your own solution using Ozeki VoIP SIP SDK.

Introduction

The IVR (interactive voice response) system is a software solution that supports the communication of the companies. In many cases the company customers call for help for problems that can be solved with the same, automatic answer. In these cases there is no need to talk to a human employee, an therefore the company's human resources can be used more effectively.

The IVR tree usually uses a database as a background. This means that the navigation information is stored in database tables. When the user makes an interaction in the tree navigation process the next audio file to be played is got from a database table. The sample voice recognition information can also be stored in a database.

Figure 1 - VoIP IVR database integration

Ozeki VoIP SIP SDK provides all the support for IVR system implementation, you only have to call the methods you need.

The following program code uses the background support of Ozeki VoIP SIP SDK, therefore you will need to download and install the SDK on your computer before starting to use the program code. You will also need to have Visual Studio 2010 or compatible IDE and .NET Framework installed on your system, as the program code below is written in C# language.

If you want to write a database support for an IVR solution, you need to decide on the database type to use. If you want to use a local database provided by Visual Studio 2010, you need to know that the possibilities of that solution are limited.

The server type for a local database should be SqlServerCe. You should define a DatabaseConnection class that covers the databade support and only write database specific methods in that class.

Code 1 shows how you need to define the DatabaseConnection class. It should be derived from the DBConnection class that is abstract, therefore cannot be used directly.

class DatabaseConnection : DbConnection
    {
        public enum ServerType : byte
        {
            MySql,
            Oracle,
            SqlServer,
            SqlServerCe,
            /*SQLite,*/
        };

Code 1 - The definition of the connection handler class

The constructor of the DatabaseConnection class can contain a switch instruction that chooses the actually used database server type. In this case the code snippet in Code 2 will be needed for a local database.

case ServerType.SqlServerCe:
    // MSSql Server Compact Edition (for standalone .sdf files)
    // Requires a separate install of "Microsoft SQL Server Compact Edition" deployment.
    // Note, that SQL CE still does NOT support VIEWs!
    this._connection = new SqlCeConnection(this.ConnectionString);
    break;

Code 2 - Using SQLServerCE database server for a local database

You need to override some methods for database handling, for example the ones that open and close tha database shown in Code 3.

public override void Open()
{
    if (this._connection != null && this._connection.State == ConnectionState.Closed)
    {
        this._connection.ConnectionString = this._connectionstring;
        this._connection.Open();
    }
}
public override void Close()
{
    if (this._connection != null && this._connection.State != ConnectionState.Closed)
    {
        this._connection.Close();
    }
}

Code 3 - The methods for opening or closing the database

The most important methods are the ones that handle the database commands. These are also provided by the DBConnection class and needs to be overridden. Some of these are shown in Code 4.

protected override DbCommand CreateDbCommand()
{
    return this._connection.CreateCommand();
}

public DbCommand CreateDbCommand(string query)
{
    DbCommand cmd = this.CreateDbCommand();
    if (query != null)
    {
        cmd.CommandText = query;
    }
    return cmd;
}

Code 4 - Handling Database commands

When the database connection is defined you can create an instance of the connection by defining a connection string like it is shown in Codde 5.

 connection = new DatabaseConnection(DatabaseConnection.ServerType.SqlServerCe, @"Data Source=|DataDirectory|\CustomerDatabase.sdf");
 connection.Open();

Code 5 - Defining the database connection

When you want to execute a query on the database, you can use the code snippet, shown in Code 6.

string sql = string.Format("SELECT c.customer_id, c.fullname, n.phone FROM customer c INNER JOIN number n ON c.customer_id = n.customer_id WHERE n.phone = '{0}'", PhoneNumber);
reader = this.CreateDbCommand(sql).ExecuteReader();

Code 6 - Executing an SQL query

When you want to execute an SQL INSERT command on the database, you can do it with the code snippet, shown in Code 7.

this.CreateDbCommand(string.Format("INSERT INTO note (customer_id, note, created) VALUES ({0}, '{1}', '{2}')", customerDatasheet.Id, customerDatasheet.Notes.Last().Value, customerDatasheet.Notes.Last().Key.ToString("yyyy-MM-dd HH:mm:ss"))).ExecuteNonQuery();

Code 7 - Executing an SQL INSERT command

You can find a complete database handling example in the Call center database handling article.

This article introduced you the basic knowledge about IVR database integration and showed how Ozeki VoIP SIP SDK can help you to fulfill your wishes about this topic. If you have read through this page carefully, you already have all the knowledge you need to start on your own solution.

As you are now familiar with all the terms concerning this topic, now it is time to take a step further and explore what other extraordinary solution Ozeki VoIP SIP SDK can provide to you.

If you have any questions or need assistance, please contact us at info@voip-sip-sdk.com

You can select a suitable Ozeki VoIP SIP SDK license for your project on licensing page

Related Pages

• Setup Ozeki VoIP SIP SDK effectively: Quick start guide
• Download Ozeki VoIP SIP SDK form the Ozeki VoIP SIP SDK download page
• You can find licensing information of Ozeki VoIP SIP SDK on Pricing and licensing information page