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...