CODEWITHSUNDEEP
c#xmlrest
ramesh
ramesh

Reputation: 19

Inserting XML data from REST Services into DB using C#

Can anyone help me to insert the XML data (which I get from a remote REST service) into my SQL Server database using C#?

Thanks in advance.

Code

HttpWebRequest myHttpWebRequest = WebRequest.Create("http://api.asicentral.com/v1/news.xml") as HttpWebRequest;

HttpWebResponse myHttpWebResponse = (HttpWebResponse)myHttpWebRequest.GetResponse();

Encoding enc = System.Text.Encoding.GetEncoding(1252);
StreamReader loResponseStream = 
    new StreamReader(myHttpWebResponse.GetResponseStream(), enc);

string Response = loResponseStream.ReadToEnd();
myHttpWebResponse.Close();

Upvotes: 1

Views: 2493

Answers (3)

Abhisek Das
Abhisek Das

Reputation: 113

    [WebMethod]
    public void XMLPersing()
    {
        var XMLDATA = "";

        WriteLogCLS objWriteLog = new WriteLogCLS();
        Stream receiveStream = HttpContext.Current.Request.InputStream;
        receiveStream.Position = 0;
        StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);      //For xml persing..
        XMLDATA = readStream.ReadToEnd();
        readStream.Close();

      objWriteLog.WriteLog(Convert.ToString(XMLDATA));
        XmlTextReader xmlreader = new XmlTextReader(Server.MapPath("Log/Exception/Sample.xml"));
        DataSet ds = new DataSet();
        ds.ReadXml(xmlreader);
        xmlreader.Close();
        if (ds.Tables.Count != 0)
        {
            var strCon = string.Empty;
            strCon = ConfigurationManager.AppSettings["constring"];
            SqlCommand cmdInsertXMLData = new SqlCommand();
            SqlConnection SqlConn;
            SqlConn = new SqlConnection(strCon);
            try
            {
                cmdInsertXMLData = new SqlCommand("usp_InsertXML", SqlConn);
                cmdInsertXMLData.CommandType = CommandType.StoredProcedure;
                // cmdInsertLoginDetails.Parameters.Add("@XMLdata", SqlDbType.Xml).Value = ds.GetXml();
                cmdInsertXMLData.Parameters.AddWithValue("@XMLdata", SqlDbType.Xml);

                if (SqlConn.State == ConnectionState.Closed)
                {
                    SqlConn.Open();
                }
                cmdInsertXMLData.ExecuteNonQuery();
                // response = cmdInsertLoginDetails.Parameters["@Message"].Value.ToString();
            }
            catch (Exception ex)
            {
                objWriteLog.WriteLog("Error on XML Persing : " + ex.Message);
                // response = "Error";
            }
            finally
            {
                if (cmdInsertXMLData != null)
                {
                    cmdInsertXMLData.Dispose();
                }
                if (SqlConn.State == ConnectionState.Open)
                {
                    SqlConn.Close();
                    SqlConn.Dispose();
                }
                objWriteLog = null;
            }
            // return response  ;

        }
    }
}

Upvotes: 1

Chris Moutray
Chris Moutray

Reputation: 18379

Start to learn ADO.Net http://www.csharp-station.com/Tutorial/AdoDotNet/lesson01

Or try this Entity Framework Code First approach http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx

Ok so the above is a bit blunt, so why do you want to store the web service response into the database?

To do anything meaningful you'll probably want to parse the xml. You have a number of options here. A couple that spring to mind; deserialise the xml into a strongly typed object, or perhaps use linq to create an anonymous type. Finally you can then store the object into the database use ADO.Net or Entity Framework.

Upvotes: 1

marc_s
marc_s

Reputation: 755063

In order to insert this XML into SQL Server, you need to have a table in SQL Server with a column of type XML.

Then you can use something very simple like:

string connectionString = "......";  // define your connection string here
string query = "INSERT INTO dbo.YourTableNameHere(XmlColumn) VALUES(@XmlContent)";

// set up SqlConnection and SqlCommand
using(SqlConnection conn = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand(query, conn))
{
    // define parameter for query and set its value to your XML response
    cmd.Parameters.Add("@XmlContent", SqlDbType.VarChar, -1);
    cmd.Parameters["@XmlContent"].Value = Response;    // assign your XML response here

    // open connection, execute INSERT, close connection
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();     
}

Of course, if you use something like Entity Framework or some other ORM, then things would look a lot different.... this is just "straight-up" pure ADO.NET

Upvotes: 1

Related Questions