SMS Gateway - SQL SMS API, ASP.NET and MS SQL Server

The following table lists the software requirements of the system. Note that you can download Ozeki NG - SMS Gateway and .NET Framework 3.5 on the download page.

How it works

To send SMS messages from ASP.NET applications, you need to install Ozeki NG SMS Gateway and Microsoft SQL Server on your computer. Ozeki NG - SMS Gateway will use a GSM phone/modem attached to your PC (with a phone-to-PC data cable) or an IP SMS connection to send messages. Your ASP.NET application will insert a row into the database to send messages using the Ozeki NG program. For a better understanding of how it works, please look at the following diagram (Figure 1).

In the diagram you can see an Internet user, a web server with the ASP.NET application, an SQL Server, Ozeki NG - SMS Gateway, a mobile phone attached to the server computer and a mobile user receiving the message. Wherever the Internet user is, if they know the IP address or URL of the computer running the ASP script, and if they are authorized to log in, they can compose and send messages to any recipients. After the Internet user's action, the ASP.NET application inserts a row with the data of the SMS to be sent, which will be selected by Ozeki NG - SMS Gateway from the database. Ozeki NG will forward the message to the GSM network through a mobile phone attached to the PC with a data cable, and the mobile user will receive the SMS message.

Configure the MS SQL Server

Create a Database user in Ozeki NG - SMS Gateway

Using the code

Understanding the code

The downloadable sample script inserts the message to be sent into the ozekimessageout database table. Ozeki NG - SMS Gateway periodically checks the table, and if it finds a row where the status is send, then it will try to send it. Inside the script you have to set the server name to where the MS SQL Server is running, the username (specifying who can log in to the MS SQL Server) with the password and the type of the SMS message.

The source code of the example application is structured in the following way:

sqlsmshandling.aspx:
This file contains page builder asp elements (text boxes, labels, etc.).

sqlsmshandling.aspx.cs:
   Page_Load(...): calls refreshing methods for tables incoming and outgoing messages.
   refreshOutgoingMessageList(): refreshes table outgoing messages.
   refreshIncomingMessageList(): refreshes table incoming messages.
   buttonSendOnClick(...): creates and inserts the sms from the given data and calls refreshing methods for tables incoming and outgoing messages.
   hideAndShowTextboxError(...): shows error text box if there is an error.
   putMessageToDatabase(...): inserts the message into ozekimessageout database table.
   createDatabaseConnection(): tries to connect to the MS SQL Server.

Description of the process depicted in Figure 1 above:

Step 1: Create the HTML form
In the sqlsmshandling.aspx file, you create the form that requests the sms data and tables for incoming and outgoing messages. The Internet user fills in the necessaries. Label and textbox pairs will be displayed. Labels identify the requested data for the Internet user, and they will type it in textboxes. The user will be asked to fill in the Recipient and Message text fields. A multiline textbox is used to inform the Internet user about error(s) or result(s) of sending the message. At first, the error box will be invisible (it will be set in the sqlsmshandling.aspx.cs file).

sqlsmshandling.aspx

...
<form id="SMSForm" runat="server">
    <div>
        <asp:Table ID="Table1" runat="server" HorizontalAlign="Center" Width="750">
            <asp:TableHeaderRow>
                <asp:TableHeaderCell Font-Bold="true" ColumnSpan="2" HorizontalAlign="Center">
                    Compose message
                </asp:TableHeaderCell>
            </asp:TableHeaderRow>
            <asp:TableRow>
                <asp:TableCell VerticalAlign="Top">
                    <asp:Label ID="labelRecipient" runat="server" Text="Recipient(s):"></asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:TextBox ID="textboxRecipient" runat="server" TextMode="MultiLine" Width="500" Rows="3">
                    </asp:TextBox>
                </asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell VerticalAlign="Top">
                    <asp:Label ID="labelMessage" runat="server" Text="Message text:"></asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:TextBox ID="textboxMessage" runat="server" TextMode="MultiLine" Width="500" Rows="8">
                    </asp:TextBox>
                </asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell HorizontalAlign="Center" ColumnSpan="2">
                    <asp:Button ID="buttonSend" runat="server" Text="Send" OnClick="buttonSendOnClick" />
                </asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell HorizontalAlign="Center" ColumnSpan="2">
                    <asp:TextBox ID="textboxError" ForeColor="Red" TextMode="MultiLine" Rows="4"
                                    Visible="false" Text="" Width="400" runat="server"></asp:TextBox>
                </asp:TableCell>
            </asp:TableRow>
        </asp:Table>

        <br />
        <br />

        <asp:Table ID="tableOutMessages" BorderWidth="1" GridLines="Both" HorizontalAlign="Center" runat="server" Width="750">
            <asp:TableHeaderRow>
                <asp:TableHeaderCell ColumnSpan="9" HorizontalAlign="Center" Font-Bold="true">
                    Outgoing messages
                </asp:TableHeaderCell>
            </asp:TableHeaderRow>
            <asp:TableRow>
                <asp:TableCell ColumnSpan="9" HorizontalAlign="Center">
                    <asp:Button ID="buttonRefreshOut" runat="server" Text="Refresh" />
                </asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell Font-Bold="true">ID</asp:TableCell>
                <asp:TableCell Font-Bold="true">Sender</asp:TableCell>
                <asp:TableCell Font-Bold="true">Receiver</asp:TableCell>
                <asp:TableCell Font-Bold="true">Sent time</asp:TableCell>
                <asp:TableCell Font-Bold="true">Recieved time</asp:TableCell>
                <asp:TableCell Font-Bold="true">Operator</asp:TableCell>
                <asp:TableCell Font-Bold="true">Status</asp:TableCell>
                <asp:TableCell Font-Bold="true">Message type</asp:TableCell>
                <asp:TableCell Font-Bold="true">Message</asp:TableCell>
            </asp:TableRow>
        </asp:Table>

        <br />
        <br />

        <asp:Table ID="tableInMessages" BorderWidth="1" GridLines="Both" HorizontalAlign="Center" runat="server" Width="750">
            <asp:TableHeaderRow>
                <asp:TableHeaderCell ColumnSpan="8" HorizontalAlign="Center" Font-Bold="true">
                    Incoming messages
                </asp:TableHeaderCell>
            </asp:TableHeaderRow>
            <asp:TableRow>
                <asp:TableCell ColumnSpan="8" HorizontalAlign="Center">
                    <asp:Button ID="buttonRefreshIn" runat="server" Text="Refresh" />
                </asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell Font-Bold="true">ID</asp:TableCell>
                <asp:TableCell Font-Bold="true">Sender</asp:TableCell>
                <asp:TableCell Font-Bold="true">Receiver</asp:TableCell>
                <asp:TableCell Font-Bold="true">Sent time</asp:TableCell>
                <asp:TableCell Font-Bold="true">Recieved time</asp:TableCell>
                <asp:TableCell Font-Bold="true">Operator</asp:TableCell>
                <asp:TableCell Font-Bold="true">Message type</asp:TableCell>
                <asp:TableCell Font-Bold="true">Message</asp:TableCell>
            </asp:TableRow>
        </asp:Table>
    </div>
</form>
...

In the "protected void Page_Load(object sender, EventArgs e)" procedure (in sqlsmshandling.aspx.cs), you refresh the content of the 2 tables. The text of the error box is red, and the first is invisible and empty.

sqlsmshandling.aspx.cs

...
protected void Page_Load(object sender, EventArgs e)
{
    string errStr = refreshOutgoingMessageList();
        if (errStr != "")
        {
            hideAndShowTextboxError(errStr);
            return;
        }
        else
            hideAndShowTextboxError("");

        errStr = refreshIncomingMessageList();
        if (errStr != "")
        {
            hideAndShowTextboxError(errStr);
            return;
        }
        else
            hideAndShowTextboxError("");
}
...

Step 2: Processing data coming from the HTML form
After filling in the fields and clicking the Send button , the ASP server receives the information about the form. In the sqlsmshandling.aspx.cs file, the "protected void buttonSendOnClick(object sender, EventArgs e)" procedure is called. At the begining of the procedure, check the data of the textbox fields. The Recipient box is mandatory. If it is empty, the processing will be aborted, and the Internet user will be informed about the error. If the checking is successful, you will insert a new row into the ozekimessageout database table (in putMessageToDatabase(...)). We need the following: the address of the computer running the Microsoft SQL Server [the default address is (local)\SQLExpress ((local) means that MS SQL Server is installed on the same computer on which the ASP.NET script is running)], the username (who is authorized to log in to the MS SQL Server and to insert messages, the user's password, the message type (the default is SMS:TEXT), the recipient and the message data.

sqlsmshandling.aspx.cs

...
    protected void buttonSendOnClick(object sender, EventArgs e)
    {
        //you check that the recipient text box isn't empty
        if (textboxRecipient.Text == "")
        {
            hideAndShowTextboxError("Recipient(s) field mustn't be empty!");
            return;
        }

        hideAndShowTextboxError(
            putMessageToDatabase(textboxRecipient.Text, textboxMessage.Text));

        refreshIncomingMessageList();
        refreshOutgoingMessageList();
    }

    ...

    protected string putMessageToDatabase(string receiver, string messageText)
    {
        string errText = createDatabaseConnection();
        if (sqlConn == null)
        {
            return errText;
        }
        else
            errText = "";

        try
        {
            System.Data.SqlClient.SqlCommand mySQLComm = sqlConn.CreateCommand();
            mySQLComm.CommandText = "insert into ozekimessageout (msgtype,receiver,msg,status) " +
                "values ('SMS:TEXT','" + receiver + "','" + messageText + "','send');";
            if (mySQLComm.ExecuteNonQuery() == 0)
            {
                errText = "Insert was UNsuccessful!";
            }
            else
            {
                errText = "Insert was successful!";
            }

            sqlConn.Close(); //we close the SQL connection
        }
        catch (Exception e)
        {
            errText = e.Message;
        }

        return errText;
    }

    protected string createDatabaseConnection()
    {
        string errorText = "";

        if (sqlConn != null)
        {
            try
            {
                sqlConn.Open();
            }
            catch (Exception e)
            {
                errorText = e.Message;
            }
            return errorText;
        }

        //we create a database connection
        string SQLServerName = "(local)\\SQLExpress";
        string SQLServerDatabaseName = "ozekisms";
        string SQLServerUserName = "sqluser";
        string SQLServerPassword = "abc123";
        string connectionString =
            "Server=" + SQLServerName + ";Database=" + SQLServerDatabaseName + ";" +
            "User Id=" + SQLServerUserName + ";Password=" + SQLServerPassword + ";";

        try
        {
            sqlConn =
                new System.Data.SqlClient.SqlConnection(connectionString);
            sqlConn.Open();
        }
        catch (Exception exc)
        {
            errorText = exc.Message;
        }

        return errorText;
    }
...

By refreshing the table of incoming and outgoing messages, you can check the incoming messages and the status of outgoing messages. Look at the refreshOutgoingMessageList() procedure. First, you delete its rows, but not the first 3 rows. You have to delete them, because this procedure may be called more than once. Then, you create the connection object. If it is successful, you will refill the outgoing messages table.

sqlsmshandling.aspx.cs

...
    protected string refreshOutgoingMessageList()
    {
        //we keep the first 3 rows
        for (int i = tableOutMessages.Rows.Count - 1; i >= 3; --i)
            tableOutMessages.Rows.RemoveAt(i);

        string errText = createDatabaseConnection();
        if (sqlConn == null)
        {
            return errText;
        }
        else
            errText = "";

        try
        {
            System.Data.SqlClient.SqlCommand mySQLComm = sqlConn.CreateCommand();
            mySQLComm.CommandText = "select id,sender,receiver,msg,senttime,receivedtime," +
                "operator,msgtype,status from ozekimessageout;";

            System.Data.SqlClient.SqlDataReader dr = mySQLComm.ExecuteReader();
            if (dr != null)
            {
                while (dr.Read())
                {
                    TableRow tr = new TableRow();
                    TableCell tc = new TableCell();
                    tc.Text = (dr.IsDBNull(0)) ? (" ") : (dr.GetInt32(0).ToString()); //id
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = (dr.IsDBNull(1)) ? (" ") : (dr.GetString(1)); //sender
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = (dr.IsDBNull(2)) ? (" ") : (dr.GetString(2)); //receiver
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = (dr.IsDBNull(4)) ? (" ") : (dr.GetString(4)); //senttime
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = (dr.IsDBNull(5)) ? (" ") : (dr.GetString(5)); //received time
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = (dr.IsDBNull(6)) ? (" ") : (dr.GetString(6)); //operator
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = (dr.IsDBNull(8)) ? (" ") : (dr.GetString(8)); //status
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = (dr.IsDBNull(7)) ? (" ") : (dr.GetString(7)); //msgtype
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = (dr.IsDBNull(3)) ? (" ") : (dr.GetString(3)); //msg
                    tr.Cells.Add(tc);
                    tableOutMessages.Rows.Add(tr);
                }
                dr.Close();
            }

            sqlConn.Close(); //we close the SQL connection
        }
        catch (Exception e)
        {
            errText = e.Message;
        }

        return errText;
    }
...

Sending SMS using ASP.NET (C#) through MS SQL Server

Download source code

Introduction

When to use this code

Prerequisites

How it works

Configure the MS SQL Server

Create a Database user in Ozeki NG - SMS Gateway

Using the code

Understanding the code

Summary

Frequently asked questions