How to send SMS using SQL Server ? OR How to send SMS using SQl store Procedure ?

Here I have written a sql store Procedure to send SMS using Thrid pary api.

Step 1 :-  Configure   sp_configure to enable Ole Automation Procedures
Step 2 :- Create  sp_OACreate 
Step 3 :- Open  sp_OAMethod
Step 4 :-  Send  sp_OAMethod
Step 5 :-  Get Error using  sp_OAGetErrorInfo
Step 6 :-  Destroy Object using   sp_OADestroy
Step 7 :-  Configure   sp_configure to disable Ole Automation Procedures
           

USE [DemoDb]
GO
/****** Object:  StoredProcedure [PWS].[Sms_Sender]    Script Date: 25-04-2015 12:14:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [PWS].[Sms_Sender] 
 ( @MobileNo bigint, 
    @smstext as varchar(300))
as 
BEGIN 
   Declare @obj int,@Result int 
   Declare @sUrl as varchar(500) 
   DECLARE @errorSource VARCHAR(8000)
   DECLARE @errorDescription VARCHAR(8000) 
   DECLARE @sResponse VARCHAR(50)

   
     if((SELECT value FROM sys.configurations where name='show advanced options')=0)
      begin 
      Exec sp_configure 'show advanced options', 1;
      RECONFIGURE with override;
      end

     if((SELECT value FROM sys.configurations where name='Ole Automation Procedures')=0)
     begin 
     Exec sp_configure 'Ole Automation Procedures', 1;
     RECONFIGURE with override;
     end
--exec [PWS].[sp_Enable_OleAutomation] 

    EXEC @Result = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @obj OUT
   
set @sUrl='http://enterprise.smsgupshup.com/GatewayAPI/rest?method=sendMessage&send_to=#MobNo#&msg=Test&userid=userId&password=*******
&msg=#Msg#&v=1.1&msg_type=TEXT&auth_scheme=PLAIN'

   set @sUrl=REPLACE(@sUrl,'#MobNo#',@MobileNo) 
   set @sUrl=REPLACE(@sUrl,'#Msg#',@smstext) 
   EXEC @Result = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false 
   EXEC @Result = sp_OAMethod @obj, 'send' 
  select @obj

   if @Result <> 0 
       Begin 
       EXEC sp_OAGetErrorInfo @obj, @errorSource OUTPUT, @errorDescription OUTPUT
       SELECT [Error Source] = @errorSource, [Description] = @errorDescription
       Raiserror('sp_OAMethod Send FAILED!', 16, 1) 
      end 
  else 
      Begin
      EXEC @Result = sp_OAGetProperty @obj,'responseText', @sResponse OUT 
      end
 Exec sp_OADestroy @obj

if((SELECT value FROM sys.configurations where name='Ole Automation Procedures')=1)
    begin 
    Exec sp_configure 'Ole Automation Procedures',0;
    RECONFIGURE;
    end


    if((SELECT value FROM sys.configurations where name='show advanced options')=1)
begin 
Exec sp_configure 'show advanced options', 0;
RECONFIGURE;
end

end

How to convert a DataTable into JSON format in c# asp.net ?

Here I am writing a c# program that will convert a DataTable into JSON formate. First I created a DataTable and then inserted 5 Rows into it, then passed to a method ConvertDataTableToJSON(DataTable dt) and it will return a JSON string.

Source code:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Xml;

using System.IO;

using System.Data;

using System.Globalization;

using Newtonsoft.Json;

namespace ConsoleDemoApp

{

    class ConvertDataTableToJSON

    {

     protected void Page_Load(object sender, EventArgs e)

     {

         try{

                // Create datatable first

               DataTable dt= GetTable();

                // Convert it into JSON formate.

             String jsonString=  ConvertDataTableToJson(dt);

               }

            catch(Exception ex)

            {

                //catch the exception and write to txt file

                writeInFile(ex.ToString());

            }

      }

// Create DataTable and insert some rows.

       protected DataTable GetTable()

        {

            // Here we create a DataTable with four columns.

            DataTable table = new DataTable();

            table.Columns.Add("Id", typeof(int));

            table.Columns.Add("Name", typeof(string));

            table.Columns.Add("Gender", typeof(string));

            table.Columns.Add("Email", typeof(DateTime));

            // Here we add five DataRows.

            table.Rows.Add(1, "Ram", "Male", "abc@gmail.com");

            table.Rows.Add(2, "Shyam", "Male", "abc@gmail.com");

            table.Rows.Add(3, "Kavita", "Female","abc@gmail.com");

            table.Rows.Add(4, "Suman", "Female", "abc@gmail.com");

            table.Rows.Add(4, "Naveen", "Male","abc@gmail.com");

            return table;

        }

   // Method to convert.

        protected string ConvertDataTableToJson(DataTable dt)

        {

            System.Web.Script.Serialization.JavaScriptSerializer JSSerializer = new

            System.Web.Script.Serialization.JavaScriptSerializer();

            List<Dictionary<string, object>> DtRows =

              new List<Dictionary<string, object>>();

            Dictionary<string, object> newrow = null;

            //Code to loop each row in the datatable and add it to the dictionary object

            foreach (DataRow drow in dt.Rows)

            {

                newrow = new Dictionary<string, object>();

                foreach (DataColumn col in dt.Columns)

                {

                    newrow.Add(col.ColumnName.Trim(), drow[col]);

                }

                DtRows.Add(newrow);

            }

            //Serialising the dictionary object to produce json output

            JSSerializer.MaxJsonLength = Int32.MaxValue;

            return JSSerializer.Serialize(DtRows);

        }

   // Write exception to text file

        static void writeInFile(string excep)

        {

            // it will get base directory path but you can change it.

            string path = AppDomain.CurrentDomain.BaseDirectory;

            String dir = Path.GetDirectoryName(path);

            dir += "\\Error\\";

            if (!Directory.Exists(dir))

            {

                Directory.CreateDirectory(dir);

            }

            string newFileName = string.Format("{0:dd-MMM-yyyy_hh-mm-ss-tt}" + ".txt", DateTime.Now);

            string filepath = dir + newFileName;

            if (!File.Exists(filepath))

            {

                File.Create(filepath).Close();

            }

            using (StreamWriter w = File.AppendText(filepath))

            {

                w.WriteLine("\r\nLog Entry : ");

                w.WriteLine("{0}", DateTime.Now.ToString(CultureInfo.InvariantCulture));

                string err = "Excepation :" + excep;

                w.WriteLine(err);

                w.WriteLine("__________________________");

                w.Flush();

                w.Close();

            }

        }

  }

}

Output in JSON format

          [

             {

"Id": "1",

"Name": "Ram",

"Gender": "Male",

"Email": "abc@gmail.com",

    },

{

"Id": "2",

"Name": "Shyam",

"Gender": "Male",

"Email": "abc@gmail.com",

    },

{

"Id": "3",

"Name": "Kavita",

"Gender": "Female",

"Email": "abc@gmail.com",

    },

{

"Id": "4",

"Name": "Suman",

"Gender": "Female",

"Email": "abc@gmail.com",

    },

{

"Id": "5",

"Name": "Naveen",

"Gender": "Male",

"Email": "abc@gmail.com",

    }

]