Sending SMS through Microsoft SQL Server
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
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.
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 ServerStep 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 GatewayStep 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# codeStep 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):
Figure 2 - The GUI generated from 'MainForm.cs'
ComposeMessageForm.cs (Figure 3):
Figure 3 - The GUI generated from 'ComposeMessageForm.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
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
You can use other message types than 'SMS:TEXT' (e.g. 'SMS:WAPPUSH', 'SMS:VCARD') as you can see on Figure 7.
Frequently asked questions
Can this C# example run on a different computer than
Ozeki SMS Gateway's or MSSQL server's machine?
Can I change the sender's phone number?