Consume REST Api thorugh Script task component

[shilpikumari12]

Hi all,


I have one question regarding to Consume REST API without using NewtonJson library or third party tool. I created paresJson function and stored proc to insert data into sql table. But first step which i am trying to call REST api i am not able to resolve that section.

1. Consume REST api stored result into jsonstring

2. Call storedproc example (sqlCommand.CommandText = "Exec" + storedproc + jsonString)

3. When i will execute stored proc from sql management studio then rest api -> parseJson function-> insert json data into sql table.

I am having issues in the first process.

I will be thanksful for your effort and help.


Code : what i did till this time


string wUrl = Dts.Variables["User::Extratrafficapi"].Value.ToString();
string storedproc = Dts.Variables["User::storedproc"].Value.ToString();
string json = Dts.Variables["User::json"].Value.ToString();


var webRequest = System.Net.WebRequest.Create(wUrl);

if (webRequest != null)
{
webRequest.Method = "GET";
webRequest.Timeout = 12000;
webRequest.ContentType = "application/json";
HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse();
var webResponseStream = webResponse.GetResponseStream();
if (webResponseStream != null && webResponseStream != Stream.Null)
{
using (System.IO.Stream s = webRequest.GetResponse().GetResponseStream())
{

string jsonString;

using (System.IO.StreamReader sr1 = new System.IO.StreamReader(s))
{
jsonString = sr1.ReadToEnd().Replace("\\", "");
//jsonString = sr1.ReadToEnd();
sr1.Close();
}




string consString = @"Data Source=servername;Initial Catalog=databasename;Integrated Security=True;";
using (SqlConnection con = new SqlConnection(consString))
{
con.Open();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = con;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "Exec" + storedproc + jsonString;
sqlCommand.ExecuteNonQuery();
MessageBox.Show(sqlCommand.CommandText);
}
}
}
}

Dts.TaskResult = (int)ScriptResults.Success;
}


Regards,

Shilpi kumari

Continue reading...