If you are working on telephone solutions, please check
out the Ozeki VoIP SIP SDK.
It can be used to create VoIP client and server software. |
|
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
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:
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.
Insert the message into the database.
mySqlCommand.ExecuteNonQuery() |
Finally you need to close the database
connection.
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
|
Conclusion
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.
Dig deeper!
People who read this also read...
Next page:
VB.NET HTTP sms example guide
|
