How to send SMS from C# using an SQL database

C Sharp SMS Gateway

This example explains a C# sms gateway setup. In this solution SMS messages sent by the C# sms project will be inserted into a database. Ozeki SMS Gateway needs to be configured to pick up the text messages from this database and to send them to the mobile network. Once the messages are sent the status field of the sms records will be changed to sent by the sms gateway. The benefit of this solution, is that all your messages will be recorded in the database, and you don't have to worry about messages delivery in your C# code. You can always look up the status of a message by simply executing an SQL SELECT statement.

Send sms from c# with these simple steps:

To send sms from c#:
  1. Connect the C# class to the database
  2. Connect the SMS Gateway to the database
  3. Write an SQL INSERT command
  4. Set the status of the sms to 'Send'
  5. Send the sms from C# by executing the INSERT
  6. See how the sms "Send" status is changed to "Sent"
  7. View the SMS on your mobile

System architecture

The system architecture used for SMS messaging consists of a GSM Modem, that is attached to the PC with a phone-to-PC datacable, an SQL-SMS Gateway software installed onto your PC, an SQL database server, such as SQL Express or MS SQL and your SMS application (Figure 1). As you can see on the figure, your SMS application will create an SQL record in the database. The SMS gateway will poll this record using an SQL SELECT statement and will send it using a GSM modem.

in house sms messaging
Figure 1 - In house SMS messaging / system architecture

Example code

To send sms from C# using a database, you may use the following C# code example. This example connects to a Microsoft SQL Server database server, and inserts an SQL data record into the ozekimessageout table in the apropriate format.

Download: csharp-sms-example.zip (173kb)

Preparing your database server

To get this architecture running, first you should prepare your database. This means that you should create two database tables. One will be used for sending SMS messages (ozekimessageout) and the other will be used for receiving SMS messages (ozekimessagein). The database table you create should contain a field for sender number, recipient number and message text. For sending messages you also need a status field, that will indicate whether the message has been sent.

The recommended database table layout can be seen on Figure 2. Please note that you can add extra columns to this layout freely. After creating the database table layout you should also create a username and a password that can be used to log into database.

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
Figure 2 - Database table layout

Configuring the SMS Gateway

Once the database has been setup you should install and configure the SMS Gateway. The SMS Gateway we use in this article is the Ozeki NG - SMS Gateway it can be downloaded from www.ozekisms.com. There are other similar SMS gateways available. We chose this SMS Gateway, because it is very reliable, easy to configure and it is based on .NET which means it integrates well into our architecture. In the SMS gateway first you should configure the GSM modem attached to your PC. This can be done by adding a GSM Modem service provider connection. Detailed steps:

Step 1.) Open http://127.0.0.1:9501 in Internet Explorer, login with admin/abc123

Step 2.) In the "Serviceprovider connections" menu click on "Add service provider connection", then select "GSM Modem Connection" and click "Install"

Step 3.) On the GSM modem configuration form, select the com port (usually COM1) and click on autodetect to configure your modem

Step 4.) In the "Users and applications" menu click on "Add user", the select "Database user" and click on "install"

Step 5.) For username provide "sql1"

Step 6.) For connection string type select "OleDb" and for connection string enter:

Provider=SQLNCLI;Server=.\SQLEXPRESS;User ID=ozekiuser;password=ozekipass;Database=ozeki;Persist Security Info=True

Step 7.) In the configuration form I suggest you to turn on SQL logging. You can do this by enabling the checkboxes:
"Log SQL SELECT statements" and "Log SQL UPDATE statements"

c sharp gateway configuration
Figure 3 - SMS Gateway configured

After these steps you have configured the SMS Gateway to forward your messages from the database to the mobile devices.

Creating your SMS application

Once the database has been prepared and the SMS Gateway has been setup you can use Visual Studio.NET to create your SMS application. In Visual Studio create a new project of "Windows Application" type (Figure 4). This application will provide a GUI for sending the SMS message. It will connect to the database server and will insert a new record when an message is sent to a mobile phone.

select windows application as application type
Figure 4 - Application type: Windows Application

In this application you should have a form, that can be used by the user of your app to compose the message (Figure 5). This form should contain a field for the recipient number and another field for the message text. The form will have a button, that will initiate the sending process.

sms message form
Figure 5 - SMS Message form

To be able to connect to your database server you should include the "using System.Data.OleDb;" directive in the using section for your code. The code for sending the SMS message is written into the event handler of the button. In this code first we connect to the database using an OleDbConnection object. For the connection we use a standard connection string. If the connection is successful (the state equals ConnectionState.Open), we execute our SQL INSERT statement to insert the SMS message. To achieve this we compose the SQL statement and we use an OleDbCommand object to execute it (Figure 6).

          private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                //Connect to the database
                OleDbConnection conn = new OleDbConnection();

                conn.ConnectionString = "Provider=SQLNCLI;Server=.\\;"+
                "User ID=ozekiuser;password=ozekipass;Database=ozeki;Persist
                Security Info=True";
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    //Send the message
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    string SQLInsert =
                        "INSERT INTO "+
                        "ozekimessageout (receiver,msg,status) "+
                        "VALUES "+
                        "('"+tbSender.Text+"','"+tbMsg.Text+"','send')";
                    cmd.CommandText = SQLInsert;
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Message sent");
                }

                //Disconnect from the database
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
Figure 6 - Sending an SMS message source code

Verifying your SMS application

After executing the application, you can send your first SMS message. To track the message you can use an SQL console to see what is inserted into your SQL table and you should examine the event log of the SMS gateway to see the SQL events. The eventlog of the Ozeki NG SMS gateway is located at the following location:

C:\Program Files\Ozeki\OzekiNG - SMS Gateway\Logs

References

1.) You can download SQLExpress free of charge from the following URL:
https://www.microsoft.com/en-us/sql-server/sql-server-downloads

2.) Ozeki NG - SMS Gateway can be downloaded from the following URL:
Ozeki NG SMS Gateway download

3.) Additional information for setting up SQL Express is available at:
https://ozekisms.com/p_2305-send-sms-messages-using-microsoft-sql-express.html

More information