Contents | Search

How to send SMS messages from VB.net using an SQL database

This guide will provide you information about how to add SMS functionality to your VB.NET application. The solution explained uses a database server, and it makes SMS sending and receiving possible through database tables. To implement this solution you can use any database server, such as MS SQL, MS SQL Express, MySQL, Access, Oracle, etc. Source code is also provided. You can use and edit this source code freely.

Introduction

If your VB.net application works with a database, you can add SMS functionality to it by setting up an SQL to SMS gateway configuration. In this configuration you can send SMS messages by simply inserting records into a database table, and if you want to receive SMS messages all you have to do is check another database table to see if new records are inserted into it.

To get a better idea on how this solution works, please take a look at Figure 1. On this figure you will see, that this solution is based on an SMS gateway that shares a database with your VB.NET application. The SMS gateway is responsible for taking your messages from the database to the mobile network. It can connect to the mobile network through a GSM phone attached to a computer or through the Internet. Incoming messages will also be handled by this SMS gateway. If an SMS message comes in, it will insert it into the database.

Figure 1 - How to send / receive SMS from VB.NET through SQL

Prerequisites

To setup this solution, you need to install the Ozeki NG SMS gateway software into one of your computers. Ozeki NG SMS gateway will be used to connect your system to the mobile network. You also need a database server. For example you can

• send SMS from Microsoft SQL Server
• send SMS from Microsoft SQL Express
• send SMS from Access
• send SMS from MySQL
• send SMS from Oracle
• send SMS from Postgres
• send SMS from Sybase

Of course you also need Visual Studio or a Visual Basic development IDE, such as Microsoft Visual Basic 2008 Express Edition. It is also recommended to download and use the demo project from the top of this webpage. So here is your complete checklist:

• Ozeki NG SMS Gateway
• Database server (MS SQL, SQL Expres, Access, MySQL, Oracle, Postgres, Sybase, etc)
• Microsot Visual Basic 2008 Express Edition
  
• vb.net-sms-example-sql.zip (70 Kb)
  

Installation and configuration

After you got all the prerequisites you are ready to add SMS functionality to your VB.Net application. You can use the following steps to build a working solution. These steps use the Microsoft SQL Express database server. Before you start, please download and install Ozeki NG SMS Gateway and test it to make sure you are able to send and receive SMS messages from it's graphical user interface. The SMS quick start guide can help you in this.

Step 1 - Setup your database

If you don't already have Microsoft SQL Express, installed, I recommend you to download and install Microsoft Visual Basic 2008 Express Edition. In SQL Express you should create the database tables for storing the messages. You can use the following SQL commands to do this:

create database ozeki
GO

use database ozeki
GO

CREATE TABLE ozekimessagein (
 id int IDENTITY (1,1),
 sender varchar(30),
 receiver varchar(30),
 msg varchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(30),
 msgtype varchar(30),
 reference varchar(30),
);

CREATE TABLE ozekimessageout (
 id int IDENTITY (1,1),
 sender varchar(30),
 receiver varchar(30),
 msg varchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(100),
 msgtype varchar(30),
 reference varchar(30),
 status varchar(30),
 errormsg varchar(250)
);

GO

sp_addLogin 'ozekiuser', 'ozekipass'
GO

sp_addsrvrolemember 'ozekiuser', 'sysadmin'
GO

Step 2 - Connect Ozeki NG SMS Gateway to your database

The next step in implementing this solution is to install a "Database user" in Ozeki NG SMS Gateway. When you configure the database user, you should select "SQL Server" as the connection string type, and you should use the following connection string:

ID=ozekiuser;password=ozekipass;Database=ozeki;Persist Security Info=True

Step 3 - Create the code for SMS sending in your VB.Net appliction

Open the demo project and start to edit the source code.

The first thing to do is to set the information for the database connection.

im dbUsername As String = "ozekiuser"
Dim dbPassword As String = "ozekipass"
Dim database As String = "ozeki"

Dim myConnection As New SqlConnection("Server=.\SQLEXPRESS;User ID=" _
& dbUsername _
& ";password=" _
& dbPassword _
& ";Database=" _
& database _
& ";Persist Security Info=True")

Step 4 - Insert your SMS into the database

After the database connection has been prepared, you can insert an SMS message into the ozekimessageout database table using the following SQL INSERT command.

Dim mySqlQuery As String = "INSERT INTO ozekimessageout (receiver,msg,status) " _
& "VALUES ('" & tbReceiver.Text & "', '" & tbMessage.Text & "', 'send');"

Dim mySqlCommand As New SqlCommand(mySqlQuery, myConnection)

To execute this SQL command, you need to connect to the database.

myConnection.Open()

Insert the message into the database.

mySqlCommand.ExecuteNonQuery()

Finally you need to close the database connection.

myConnection.Close()

Source code

Please feel free to use and modify the following source code!

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Private Sub bSend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSend.Click
        Try
            Dim myConnection As New SqlConnection("Server=.\SQLEXPRESS;User ID=ozekiuser;" _
            & "password=ozekipass;Database=ozeki;Persist Security Info=True")


            Dim mySqlQuery As String = "INSERT INTO ozekimessageout (receiver,msg,status) " _
            & "VALUES ('" & tbReceiver.Text & "', '" & tbMessage.Text & "', 'send');"

            Dim mySqlCommand As New SqlCommand(mySqlQuery, myConnection)

            'mySqlCommand.ExecuteNonQuery()

            myConnection.Open()

            mySqlCommand.ExecuteNonQuery()

            myConnection.Close()


        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

There are several advantages to this solution. The greatest advantage is that it can be implemented very easily and quickly. If you already work with a database adding a few SQL Select and SQL Insert statements to your VB.Net application should be very easy for you. Another advantage is that you don't have to worry about queuing outgoing messages and about encoding/decoding issues. All these issues will be handled by the SMS gateway. You also don't have to worry about processing SMS delivery reports. The SMS gateway will update your submitted message's status and will insert a timestamp into the database record to mark that your message has arrived to the mobile network, or to mark that your message has arrived to the recipient handset. Finally your database server can be used to keep all the messages sent and received for later processing. Your VB.Net application does not have to be online all the time to be able to process incoming messages.