Products
Ozeki 10
Apps
Chat
SMS Gateway
SMS with Client Software
HTTP API
SMS from/to Database
PHP SMS API
Solutions
C# SMS API
API reference guide
C# to MSSQL
Change fields
Run example
VB.NET SMS API
SQL Logging
System Database
Robot Controller
Softphone
Connections
Installation guide
Terms and Definitions
SMS
Bulk Messenger
Cluster
Developer tools
Robots
Hardware
Technology
Company


Ozeki SMS Gateway
API reference guide API reference guide | Change fields Change fields

Sending SMS through Microsoft SQL Server

Download: Sending_SMS_through_MS_SQL_Server.zip

See how to send SMS by inserting rows into a Microsoft SQL database through a C#.NET application. This technology is intended for developers with basic knowledge in C#.NET and SQL. The downloadable source code helps you get started.

In the following chapters you can find the required prerequisites and a detailed explanation on how to use the code. A helpful workflow diagram shows you the basic connection between the C# application's user and the recipient's phone.

The code is useful if who would like to

  • include SMS functionality to your C# application.
  • integrate automated SMS notification.
  • secure your products by adding SMS login.

Prerequisites

The software requirements of the system is listed on the following table. Please download and install Ozeki SMS Gateway with .NET framework 4.5 and a Microsoft Visual Studio to run your code.

Operating System: Windows Vista, Windows 7, Windows Server 2008 R2, Windows Server 2012 R2, Windows Server 2016, Windows 8 or Windows 10
Basic software requirements: .NET Framework 4.5
Ozeki SMS Gateway
Microsoft SQL Server 2005 Express Edition or newer Microsoft SQL Server versions
Development platform: Microsoft Visual Studio

How does it work

First you need to install Ozeki SMS Gateway and create a Database User in the SMS Gateway. Connect the user to your MSSQL database. The SMS messages to send will be SELECT-ed from the outgoing messages SQL table, which is called 'ozekimessageout' as default.

Then you should install a C#.NET environment. Your C# application can connect to your MSSQL database and insert SMS messages to the SQL table of outgoing messages.

Make sure your Ozeki SMS Gateway is connected to the GSM network through a GSM modem or any IP SMS service provider over the internet. For example SMPP, CIMD2 or UCP/EMI are very popular service provider connections.

See the workflow of the C# through MSSQL connection on Figure 1.


Figure 1 - Message flow from a cellphone to your C# application and vice versa

So basicly the Microsoft SQL Server can be reached from both your C# application and Ozeki SMS Gateway. All incoming and outgoing messages are stored on the MSSQL Server for further usage.

By adding the appropriate codes to your C# application, you can connect it to your SQL database to make it able to INSERT new rows into the outgoing message table and read incoming messages.

Ozeki SMS Gateway's Database User is capable to read the outgoing message table to send SMS messages and INSERT new rows into the incoming message table for other users to read. For example the C# application user can read it.

How to set up your MSSQL Server

Step 1: Install Microsoft SQL Server 2005 Express Edition
Step 2: Start Microsoft SQL Server 2005 Express Edition and log in.
Step 3: Turn on server authentication, so it would always require database user and password pair. (Short help tutorial)
Step 4: Create database and grant select, insert, update, delete permissions to a user. (Short help tutorial)

Add a Database User to Ozeki SMS Gateway

Step 1: Start Ozeki 10 browser GUI (Picture help)
Step 2: Start Ozeki SMS Gateway from Ozeki 10's desktop.
Step 3: Install a Database User and set the connection string. (Short help tutorial)

Use the downloaded C# code

Step 1: Download the zip file and unpack it.
Step 2: Set SQL database connection data in 'DatabaseHandling.cs'. (Short help tutorial)
Step 3: Start Ozeki 10 service if it is not running. (Short help tutorial)
Step 4: Build and run the project in Microsoft Visual Studio
Step 5: A window will pop up. Click the Compose message button.
Fill the recipient and message text fields and click 'Send'. (Short help tutorial)

How does the example code work

The downloadable code on the top of the page can insert messages in the 'ozekimessageout' table. Ozeki SMS Gateway's Database User periodically checks 'ozekimessageout' to find new message rows and send them to the recipient's phone. Each message row has a status attribute. After sending the SMS message, the status will change from 'Send' to 'Sent'.

Do not forget to check if your Microsoft SQL server is up and running and modify the server connection details in 'DatabaseHandling.cs', which you can find in the example project.

C# classes of the example code

MainForm.cs (Figure 2):
This class contains the first window that opens up. As you can see on Figure 2, it can show the content of two tables. This content is read by a SELECT statement from 'ozekimessageout' and 'ozekimessagein'. It also contains two 'Refresh' buttons and a 'Compose message' button as well. Press it to use the next class, 'ComposeMessageForm.cs'.


Figure 2 - The GUI generated from 'MainForm.cs'

ComposeMessageForm.cs (Figure 3):
This is the next page where you can fill the necessary data fields to create a new message.


Figure 3 - The GUI generated from 'ComposeMessageForm.cs'

DatabaseHandling.cs:
This is where you can set the database connection (Figure 4) and refresh the tables of 'MainForm.cs'.


Figure 4 - Database connection settings in 'DatabaseHandling.cs'

How does the message sending code work

In 'ComposeMessageForm.cs' you can fill the two fields with the recipient's address and message text. By pressing 'Send', you can start the following code:

ComposeMessageForm.cs
...
private void buttonSend_Click(object sender, EventArgs e)
{
    CheckAndSendMessage();
}

private void CheckAndSendMessage()
{
    if (textBoxRecipient.Text == "")
    {
        MessageBox.Show("Recipient field mustn't be empty!",
                    "Incorrect field value");
        return;
    }

    string errorMsg = "";
    DatabaseHandling.insertMessage(textBoxRecipient.Text,
                textBoxMessageText.Text, out errorMsg);

    MessageBox.Show(errorMsg, "Result of inserting message");
}
...
Figure 5 - Checks if the recipient's field is empty

The CheckAndSendMessage() method is called by 'buttonSend_Click(...)'. The method starts by checking the recipient's field and runs insertMessage(...) from 'DatabaseHandling.cs' if the recipient's field is NOT empty (Figure 5). insertMessage(...) can insert a new row to the 'ozekimessageout' table (Figure 6) (The default value of the msgtype attribute is SMS:TEXT). Ozeki SMS Gateway will read the rows from the SQL server as you can see on the workflow diagram above.

DatabaseHandling.cs
...
public static void insertMessage(string receiver, string messageText,
            out string errorMsg)
{
    Connect(out errorMsg);
    if (errorMsg != "")
       return;

    try
    {
       SqlCommand sqlComm = sqlConn.CreateCommand();
       sqlComm.CommandText = "insert into ozekimessageout " +
                    "(msgtype,receiver,msg,status) " +
                    "values ('SMS:TEXT','" + receiver + "','" +
                    messageText + "','send');";
       if (sqlComm.ExecuteNonQuery() == 0)
       {
            errorMsg = "Insert was UNsuccessful!";
       }
       else
       {
            errorMsg = "Insert was successful!";
       }
    }
    catch (Exception e)
    {
        errorMsg = e.Message;
    }

    CloseConnection();
}
...
Figure 6 - INSERT's message into your SQL database

You can use other message types than 'SMS:TEXT' (e.g. 'SMS:WAPPUSH', 'SMS:VCARD') as you can see on Figure 7.

...
sqlComm.CommandText = "insert into ozekimessageout " +
                    "(msgtype,receiver,msg,status) " +
                    "values ('SMS:WAPPUSH','" + receiver + "','" +
                    messageText + "','send');";
...
Figure 7 - Message type changed from 'SMS:TEXT' to 'SMS:WAPPUSH'

Frequently asked questions

Question: Can this C# example run on a different computer than Ozeki SMS Gateway's or MSSQL server's machine?
Answer: Yes, it can. Please modify the IP address in 'DatabaseHandling.cs' to your MSSQL server's IP address.

Question: Can I change the sender's phone number?
Answer: Yes. Please INSERT the sender's number into the message row as well (Figure 8).
It only works if you have an IP SMS connection.

...
string SenderNumber = "+449876543";
sqlComm.CommandText = "insert into ozekimessageout " +
                    "(msgtype,sender,receiver,msg,status) " +
                    "values ('SMS:TEXT','" + SenderNumber + "','" + receiver + "','" +
                    messageText + "','send');";
...
Figure 8 - Modified INSERT INTO statement with an additional 'sender' attritube

Copyright © 2000- - Ozeki Informatics Ltd. | info@ozeki.hu | Tel: +36 1 371 0150
Home > Products > Ozeki 10 > Apps > SMS Gateway > C# SMS API > C# to MSSQL
Page: 5766 | Login