locked
How to import XML data file into SQL Server RRS feed

  • Question

  • User-718146471 posted

    Hey everyone, I have a database that has several columns.  These columns are data from the national institute of system technology (NIST).  What I would like to do is take the CPE dictionary and import the CPE components into my database.  My CPE columns are as follows:

    Manufacturer, Product, Version, CPE, and CPEType.  The CPE dictionary catalog entries look like this:

    <?xml version='1.0' encoding='UTF-8'?>
    <cpe-list xmlns:config="http://scap.nist.gov/schema/configuration/0.1" xmlns="http://cpe.mitre.org/dictionary/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:scap-core="http://scap.nist.gov/schema/scap-core/0.3" xmlns:cpe-23="http://scap.nist.gov/schema/cpe-extension/2.3" xmlns:ns6="http://scap.nist.gov/schema/scap-core/0.1" xmlns:meta="http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2" xsi:schemaLocation="http://scap.nist.gov/schema/cpe-extension/2.3 http://scap.nist.gov/schema/cpe/2.3/cpe-dictionary-extension_2.3.xsd http://cpe.mitre.org/dictionary/2.0 http://scap.nist.gov/schema/cpe/2.3/cpe-dictionary_2.3.xsd http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2 http://nvd.nist.gov/schema/cpe-dictionary-metadata_0.2.xsd http://scap.nist.gov/schema/scap-core/0.3 http://nvd.nist.gov/schema/scap-core_0.3.xsd http://scap.nist.gov/schema/configuration/0.1 http://nvd.nist.gov/schema/configuration_0.1.xsd http://scap.nist.gov/schema/scap-core/0.1 http://nvd.nist.gov/schema/scap-core_0.1.xsd">
      <generator>
        <product_name>National Vulnerability Database (NVD)</product_name>
        <product_version>2.33</product_version>
        <schema_version>2.3</schema_version>
        <timestamp>2015-08-15T03:50:00.152Z</timestamp>
      </generator>
      <cpe-item name="cpe:/a:1024cms:1024_cms:0.7">
        <title xml:lang="en-US">1024cms.org 1024 CMS 0.7</title>
        <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:0.7:*:*:*:*:*:*:*"/>
      </cpe-item>
    </cpe-list>

    The CPE format is as follows:

    cpe:/a:1024cms:1024_cms:0.7
    
    cpe:/ identifies it as a CPE
    
    a: identifies this as application, there are three states, h: hardware, o: operating system
    
    1024cms is the manufacturer
    
    1024_cms is the product
    
    0.7 is the version
    

    Each octet is separated by colon.  What I would like to do is import these bits into my database fields.

    Wednesday, August 19, 2015 1:50 PM

Answers

  • User-718146471 posted

    Everyone, here is the complete source code for importing NIST XML files to update your corresponding data dictionary. A special thanks goes out to Zhi Lv - MSFT for all the assistance.  The database schema is as follows:

    CREATE TABLE [dbo].[CPEs] (
        [Id]             INT           IDENTITY (1, 1) NOT NULL,
        [Manufacturer]   VARCHAR (200) NULL,
        [Product]        VARCHAR (300) NULL,
        [Version]        VARCHAR (50)  DEFAULT ((0)) NULL,
        [CPE]            VARCHAR (500) NULL,
        [CPEType]        VARCHAR (1)   NULL,
        [SubmittedDate]  DATE          DEFAULT (getdate()) NULL,
        [SubmittedByPIN] VARCHAR (15)  NULL,
        [AddedToCPEDict] BIT           DEFAULT ((0)) NULL,
        [ReferenceURL]   VARCHAR (500) NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );
    
    
    GO
    CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150807-112309]
        ON [dbo].[CPEs]([Manufacturer] ASC, [CPE] ASC);
    
    

    Now the C# code:

            protected void btnUpload_Click(object sender, EventArgs e)
            {
                long conta = 0;
                try
                {
                    string cpetype = string.Empty, manuf = string.Empty, prod = string.Empty, vers = string.Empty, cpe = string.Empty;
                    string url = string.Empty;
                    XmlDocument myDoc = new XmlDocument();
                    myDoc.Load(FileUpload1.FileContent);
    
                    // Add the namespaces:
                    XmlNamespaceManager nsmgr = new XmlNamespaceManager(myDoc.NameTable);
                    nsmgr.AddNamespace("ns6", "http://scap.nist.gov/schema/scap-core/0.1");
                    nsmgr.AddNamespace("cpe-23", "http://scap.nist.gov/schema/cpe-extension/2.3");
                    nsmgr.AddNamespace("ns", "http://cpe.mitre.org/dictionary/2.0");
                    nsmgr.AddNamespace("meta", "http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2");
                    nsmgr.AddNamespace("scap-core", "http://scap.nist.gov/schema/scap-core/0.3");
                    nsmgr.AddNamespace("xsi", "http://www.w3.org/2001/XMLSchema-instance");
                    nsmgr.AddNamespace("config", "http://scap.nist.gov/schema/configuration/0.1");
    
                    XmlNodeList nodeList;
                    nodeList = myDoc.DocumentElement.SelectNodes("//ns:cpe-list/ns:cpe-item", nsmgr);
                    
    
                    StringBuilder sb = new StringBuilder();
                    foreach (XmlNode node in nodeList)
                    {
                        //Use SelectNodes method to find reference node.
                        XmlNodeList typeNode = node.SelectNodes(".//ns:references", nsmgr);
                        //check whether current node contains the reference node
                        if (typeNode.Count > 0)
                        {
                            string str = typeNode[0].InnerXml;
    
                            int startIndex = str.IndexOf("href");
                            int endIndex = str.IndexOf("xmlns");
    
                            url = str.Substring(startIndex + 6, endIndex - startIndex - 8); // Output first URL
                        }
                        else
                        {
                            //if it doesn't contains the reference node. skip this node.
                            continue;
                        }
    
                        // Access to the <title> tag content:
                        XmlNode titleNode = node.SelectSingleNode("./title", nsmgr);
    
                        // splitting out values
                        string s = node.Attributes["name"].Value;
    
                        s = s.Replace("/", "");
                        var array = s.Split(':');
                        cpetype = array[1].ToString();
                        manuf = array[2].ToString();
                        prod = array[3].ToString();
                        vers = array[4].ToString();
                        cpe = "cpe:2.3:" + cpetype.ToString() + ":" + manuf.ToString() + ":" + prod.ToString() + ":" + vers.ToString() + "";
    
                        try
                        {
                            // Now check to see if the CPE is already in the database
                            string CheckDB = "SELECT * from CPEs where CPE = @cpeval";
                            string DBConnect = WebConfigurationManager.AppSettings["DBConn"];
                            SqlConnection conn = new SqlConnection(DBConnect);
                            SqlConnection conn2 = new SqlConnection(DBConnect);
                            SqlCommand cmd = new SqlCommand(CheckDB, conn);
                            cmd.Parameters.AddWithValue("cpeval", cpe.ToString());
                            conn.Open();
                            SqlDataReader dr = cmd.ExecuteReader();
    
                            if (dr.HasRows == true)
                            {
                                // TODO: Update existing CPE record with information from NIST Dictionary
                                string updateNIST = "UPDATE CPEs SET Manufacturer = @Manufacturer, Product = @Product, Version = @Version, CPEType = @CPEType, ReferenceURL = @ReferenceURL where CPE = @CPE";
                                SqlCommand updatecmd = new SqlCommand(updateNIST, conn2);
                                updatecmd.Parameters.AddWithValue("Manufacturer", manuf.ToString());
                                updatecmd.Parameters.AddWithValue("Product", prod.ToString());
                                updatecmd.Parameters.AddWithValue("Version", vers.ToString());
                                updatecmd.Parameters.AddWithValue("CPEType", cpetype.ToString());
                                updatecmd.Parameters.AddWithValue("ReferenceURL", url.ToString());
                                updatecmd.Parameters.AddWithValue("CPE", cpe.ToString());
                                conn2.Open();
                                updatecmd.ExecuteNonQuery();
                                conn2.Close();
                            }
                            else
                            {
                                // TODO: Insert data into CPE DB Respository of reporting tool
                                string insertNIST = "INSERT INTO CPEs (Manufacturer,Product,Version,CPEType,ReferenceURL,CPE) values (@Manufacturer,@Product,@Version,@CPEType,@ReferenceURL,@CPE)";
                                SqlCommand insertcmd = new SqlCommand(insertNIST, conn2);
                                insertcmd.Parameters.AddWithValue("Manufacturer", manuf.ToString());
                                insertcmd.Parameters.AddWithValue("Product", prod.ToString());
                                insertcmd.Parameters.AddWithValue("Version", vers.ToString());
                                insertcmd.Parameters.AddWithValue("CPEType", cpetype.ToString());
                                insertcmd.Parameters.AddWithValue("ReferenceURL", url.ToString());
                                insertcmd.Parameters.AddWithValue("CPE", cpe.ToString());
                                conn2.Open();
                                insertcmd.ExecuteNonQuery();
                                conn2.Close();
    
                            }
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            Response.Write("Uh oh, something went wrong. " + ex.ToString() + "");
                        }
                        conta++;
                    }
                    Response.Write("CPE Dictionary Update Successful! Imported/Updated " + conta + " records.");
                }
                catch (Exception ex)
                {
                    Response.Write("CPE Dictionary Update failure. Imported/Updated " + conta + " records. Diagnostics:<br>" + ex.ToString() + "");
                }
            }
    

    My hope is that this helps someone else trying to do the same thing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 8, 2015 8:38 AM

All replies

  • User1223857158 posted

    Hi bbcompent1,

    <cpe-item name="cpe:/a:1024cms:1024_cms:0.7">

    From your description, do you mean you want to read this node from the CPE XML file , then, insert the values into the database? If that is the case, I suggest you could use SelectNodes method to find the relevant node, and get the attribute. Then you could use string.split() method to break the cpe format string into substrings and get the column values. Then insert them into database.

    You could refer to the following code:

                XmlDocument xml = new XmlDocument();
                xml.Load(Server.MapPath("Questions.xml"));
                StringBuilder sb = new StringBuilder();
    
                List<DiagnosticTool> queslist = new List<DiagnosticTool>();
    
                XmlNodeList list = xml.SelectNodes("/Questions/Question");
                foreach (XmlNode xn in list)
                {
                    DiagnosticTool dt = new DiagnosticTool();
                    dt.QuestionId = Convert.ToInt32(xn.Attributes["id"].Value); //Get Question Id                
                    dt.QuestionType = xn.Attributes["type"].Value;  //Get Question Type
                    dt.Text = xn["Text"].InnerText; //Get Text Node
    
                    sb.AppendLine(dt.QuestionId.ToString());
                    sb.AppendLine("\t");
                    sb.AppendLine(dt.QuestionType);
                    sb.AppendLine("\t");
                    sb.AppendLine(dt.Text);
                    sb.AppendLine("<br />");
    
                    List<Answer> answerlist = new List<Answer>();
                    XmlNodeList list2 = xn.SelectNodes(".//Answers/Answer");
                    foreach (XmlNode xn2 in list2)
                    {
                        Answer an = new Answer();
                        an.AnswerId = Convert.ToInt32(xn2.Attributes["id"].Value);//Get answer id.
                        an.Text = xn2.InnerText;//Get answer node.
    
                        answerlist.Add(an); //add answer to List
    
                        sb.AppendLine(an.AnswerId.ToString());
                        sb.AppendLine("\t");
                        sb.AppendLine(an.Text);
                        sb.AppendLine("<br />");
                    }
    
                    dt.Answers = answerlist;
                    queslist.Add(dt); // Add question to list
                    sb.AppendLine("<br />");
                }

    Xml file:

    <?xml version="1.0" encoding="utf-8" ?>
    <Questions>
      <Question id="1" type='SingleChoice'>
        <Text>Where is your project primarily located? (select only one)</Text>
        <Answers>
          <Answer id="1"> In an urbanized area</Answer>
          <Answer id="2"> In a metropolitan area outside of core urbanized area </Answer>
          <Answer id="3"> In a small city/town</Answer>
          <Answer id="4"> In a rural area</Answer>
        </Answers>
      </Question>

    Thursday, August 20, 2015 2:08 AM
  • User-718146471 posted

    That is what I am trying to do exactly.  I have been trying to follow along with your example but I seem to be having difficulty.  It loads the file into string builder just fine, I built out the List<DiagnosticTool> but when I try to parse the XML, nothing is found.  here is my code:

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Text;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Xml;
    
    namespace CPEReportingTool
    {
        public partial class ImportDictionary : System.Web.UI.Page
        {
            public class DiagnosticTool
            {
                string Manufacturer { get; set; }
                string Product { get; set; }
                string version { get; set; }
                string CPE { get; set; }
                string CPEType { get; set; }
                public IList<string> Values { get; set; }
            }
            protected void Page_Load(object sender, EventArgs e)
            {  
    
            }
    
            protected void btnUpload_Click(object sender, EventArgs e)
            {
                XmlDocument myDoc = new XmlDocument();
                myDoc.Load(FileUpload1.FileContent);
                StringBuilder sb = new StringBuilder();
    
                List<DiagnosticTool> CPEList = new List<DiagnosticTool>();
    
                XmlNodeList list = myDoc.SelectNodes("/item/cpe23");
                foreach (XmlNode xn in list)
                {
                    DiagnosticTool dt = new DiagnosticTool();
                    XmlNodeList list2 = xn.SelectNodes(".//item/cpe23");
                    foreach (XmlNode xn2 in list2)
                    {
    
                    }
                }
            }
        }
    }

    My XML file I shortened so it loads faster.  I want to see this parse the data before I move forward with it.

    <?xml version='1.0' encoding='UTF-8'?>
    <cpe-list xmlns:config="http://scap.nist.gov/schema/configuration/0.1" xmlns="http://cpe.mitre.org/dictionary/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:scap-core="http://scap.nist.gov/schema/scap-core/0.3" xmlns:cpe-23="http://scap.nist.gov/schema/cpe-extension/2.3" xmlns:ns6="http://scap.nist.gov/schema/scap-core/0.1" xmlns:meta="http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2" xsi:schemaLocation="http://scap.nist.gov/schema/cpe-extension/2.3 http://scap.nist.gov/schema/cpe/2.3/cpe-dictionary-extension_2.3.xsd http://cpe.mitre.org/dictionary/2.0 http://scap.nist.gov/schema/cpe/2.3/cpe-dictionary_2.3.xsd http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2 http://nvd.nist.gov/schema/cpe-dictionary-metadata_0.2.xsd http://scap.nist.gov/schema/scap-core/0.3 http://nvd.nist.gov/schema/scap-core_0.3.xsd http://scap.nist.gov/schema/configuration/0.1 http://nvd.nist.gov/schema/configuration_0.1.xsd http://scap.nist.gov/schema/scap-core/0.1 http://nvd.nist.gov/schema/scap-core_0.1.xsd">
      <generator>
        <product_name>National Vulnerability Database (NVD)</product_name>
        <product_version>2.33</product_version>
        <schema_version>2.3</schema_version>
        <timestamp>2015-08-15T03:50:00.152Z</timestamp>
      </generator>
      <cpe-item name="cpe:/a:1024cms:1024_cms:0.7">
        <title xml:lang="en-US">1024cms.org 1024 CMS 0.7</title>
        <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:0.7:*:*:*:*:*:*:*"/>
      </cpe-item>
      <cpe-item name="cpe:/a:1024cms:1024_cms:1.2.5">
        <title xml:lang="en-US">1024cms.org 1024 CMS 1.2.5</title>
        <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:1.2.5:*:*:*:*:*:*:*"/>
      </cpe-item>
      <cpe-item name="cpe:/a:1024cms:1024_cms:1.3.1">
        <title xml:lang="en-US">1024cms.org 1024 CMS 1.3.1</title>
        <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:1.3.1:*:*:*:*:*:*:*"/>
      </cpe-item>
      <cpe-item name="cpe:/a:1024cms:1024_cms:1.4.1">
        <title xml:lang="en-US">1024cms.org 1024 CMS 1.4.1</title>
        <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:1.4.1:*:*:*:*:*:*:*"/>
      </cpe-item>
      <cpe-item name="cpe:/a:1024cms:1024_cms:1.4.2">
        <title xml:lang="en-US">1024cms.org 1024 CMS 1.4.2</title>
        <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:1.4.2:*:*:*:*:*:*:*"/>
      </cpe-item>
      <cpe-item name="cpe:/a:1024cms:1024_cms:1.4.2:beta">
        <title xml:lang="en-US">1024cms.org 1024 CMS 1.4.2 beta</title>
        <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:1.4.2:beta:*:*:*:*:*:*"/>
      </cpe-item>
      <cpe-item name="cpe:/a:1024cms:1024_cms:2.1.1">
        <title xml:lang="en-US">1024cms.org 1024 CMS 2.1.1</title>
        <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:2.1.1:*:*:*:*:*:*:*"/>
      </cpe-item>
    </cpe-list>

    Thursday, August 20, 2015 7:49 AM
  • User-718146471 posted

    Just to clarify, I am trying to get this XMLNode: <cpe-23:cpe23-item name="cpe:2.3:a:1024cms:1024_cms:0.7:*:*:*:*:*:*:*"/>

    Thursday, August 20, 2015 10:06 AM
  • User-718146471 posted

    Ok, I am really close now.  I just need someone to show me how to split out the node to the four pieces.

            protected void btnUpload_Click(object sender, EventArgs e)
            {
                XmlDocument myDoc = new XmlDocument();
                myDoc.Load(FileUpload1.FileContent);
    
                // Add the namespaces:
                XmlNamespaceManager nsmgr = new XmlNamespaceManager(myDoc.NameTable);
                nsmgr.AddNamespace("ns6", "http://scap.nist.gov/schema/scap-core/0.1");
                nsmgr.AddNamespace("cpe-23", "http://scap.nist.gov/schema/cpe-extension/2.3");
                nsmgr.AddNamespace("ns", "http://cpe.mitre.org/dictionary/2.0");
                nsmgr.AddNamespace("meta", "http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2");
                nsmgr.AddNamespace("scap-core", "http://scap.nist.gov/schema/scap-core/0.3");
                nsmgr.AddNamespace("xsi", "http://www.w3.org/2001/XMLSchema-instance");
                nsmgr.AddNamespace("config", "http://scap.nist.gov/schema/configuration/0.1");
    
                XmlNodeList nodeList;
                nodeList = myDoc.DocumentElement.SelectNodes("//ns:cpe-list/ns:cpe-item", nsmgr);
                long conta = 0;
    
                foreach (XmlNode node in nodeList)
                {
                    // Access to the name ATTRIBUTE of the <cpe-item> tag:
                    Response.Write(String.Format("[{0:N0}] CPE: {1}  Title: {2}", conta, node.Attributes["name"].Value, node.FirstChild.FirstChild.Value));
                    // Split out the InnerText for Type, Manufacturer, Model, and Version
                    // InnerText shows as format of CPE cpe:2.3:a:1024cms:1024_cms:0.7:*:*:*:*:*:*:*
                    // If version is empty, use - as place holder
                    // Access to the <title> tag content:
                    //Debug.WriteLine(String.Format("[{0:N0}] Title: {1}  Title: {2}", conta, node.SelectSingleNode("./title", nsmgr)));
                    XmlNode titleNode = node.SelectSingleNode("./title", nsmgr);
                    conta++;
                }
            }
    

    Thursday, August 20, 2015 1:31 PM
  • User-718146471 posted

    I will post my solution when this is finished; I figure it will help someone else at some point.

    Friday, August 21, 2015 9:02 AM
  • User-718146471 posted

    Everyone, here is the complete source code for importing NIST XML files to update your corresponding data dictionary. A special thanks goes out to Zhi Lv - MSFT for all the assistance.  The database schema is as follows:

    CREATE TABLE [dbo].[CPEs] (
        [Id]             INT           IDENTITY (1, 1) NOT NULL,
        [Manufacturer]   VARCHAR (200) NULL,
        [Product]        VARCHAR (300) NULL,
        [Version]        VARCHAR (50)  DEFAULT ((0)) NULL,
        [CPE]            VARCHAR (500) NULL,
        [CPEType]        VARCHAR (1)   NULL,
        [SubmittedDate]  DATE          DEFAULT (getdate()) NULL,
        [SubmittedByPIN] VARCHAR (15)  NULL,
        [AddedToCPEDict] BIT           DEFAULT ((0)) NULL,
        [ReferenceURL]   VARCHAR (500) NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );
    
    
    GO
    CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150807-112309]
        ON [dbo].[CPEs]([Manufacturer] ASC, [CPE] ASC);
    
    

    Now the C# code:

            protected void btnUpload_Click(object sender, EventArgs e)
            {
                long conta = 0;
                try
                {
                    string cpetype = string.Empty, manuf = string.Empty, prod = string.Empty, vers = string.Empty, cpe = string.Empty;
                    string url = string.Empty;
                    XmlDocument myDoc = new XmlDocument();
                    myDoc.Load(FileUpload1.FileContent);
    
                    // Add the namespaces:
                    XmlNamespaceManager nsmgr = new XmlNamespaceManager(myDoc.NameTable);
                    nsmgr.AddNamespace("ns6", "http://scap.nist.gov/schema/scap-core/0.1");
                    nsmgr.AddNamespace("cpe-23", "http://scap.nist.gov/schema/cpe-extension/2.3");
                    nsmgr.AddNamespace("ns", "http://cpe.mitre.org/dictionary/2.0");
                    nsmgr.AddNamespace("meta", "http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2");
                    nsmgr.AddNamespace("scap-core", "http://scap.nist.gov/schema/scap-core/0.3");
                    nsmgr.AddNamespace("xsi", "http://www.w3.org/2001/XMLSchema-instance");
                    nsmgr.AddNamespace("config", "http://scap.nist.gov/schema/configuration/0.1");
    
                    XmlNodeList nodeList;
                    nodeList = myDoc.DocumentElement.SelectNodes("//ns:cpe-list/ns:cpe-item", nsmgr);
                    
    
                    StringBuilder sb = new StringBuilder();
                    foreach (XmlNode node in nodeList)
                    {
                        //Use SelectNodes method to find reference node.
                        XmlNodeList typeNode = node.SelectNodes(".//ns:references", nsmgr);
                        //check whether current node contains the reference node
                        if (typeNode.Count > 0)
                        {
                            string str = typeNode[0].InnerXml;
    
                            int startIndex = str.IndexOf("href");
                            int endIndex = str.IndexOf("xmlns");
    
                            url = str.Substring(startIndex + 6, endIndex - startIndex - 8); // Output first URL
                        }
                        else
                        {
                            //if it doesn't contains the reference node. skip this node.
                            continue;
                        }
    
                        // Access to the <title> tag content:
                        XmlNode titleNode = node.SelectSingleNode("./title", nsmgr);
    
                        // splitting out values
                        string s = node.Attributes["name"].Value;
    
                        s = s.Replace("/", "");
                        var array = s.Split(':');
                        cpetype = array[1].ToString();
                        manuf = array[2].ToString();
                        prod = array[3].ToString();
                        vers = array[4].ToString();
                        cpe = "cpe:2.3:" + cpetype.ToString() + ":" + manuf.ToString() + ":" + prod.ToString() + ":" + vers.ToString() + "";
    
                        try
                        {
                            // Now check to see if the CPE is already in the database
                            string CheckDB = "SELECT * from CPEs where CPE = @cpeval";
                            string DBConnect = WebConfigurationManager.AppSettings["DBConn"];
                            SqlConnection conn = new SqlConnection(DBConnect);
                            SqlConnection conn2 = new SqlConnection(DBConnect);
                            SqlCommand cmd = new SqlCommand(CheckDB, conn);
                            cmd.Parameters.AddWithValue("cpeval", cpe.ToString());
                            conn.Open();
                            SqlDataReader dr = cmd.ExecuteReader();
    
                            if (dr.HasRows == true)
                            {
                                // TODO: Update existing CPE record with information from NIST Dictionary
                                string updateNIST = "UPDATE CPEs SET Manufacturer = @Manufacturer, Product = @Product, Version = @Version, CPEType = @CPEType, ReferenceURL = @ReferenceURL where CPE = @CPE";
                                SqlCommand updatecmd = new SqlCommand(updateNIST, conn2);
                                updatecmd.Parameters.AddWithValue("Manufacturer", manuf.ToString());
                                updatecmd.Parameters.AddWithValue("Product", prod.ToString());
                                updatecmd.Parameters.AddWithValue("Version", vers.ToString());
                                updatecmd.Parameters.AddWithValue("CPEType", cpetype.ToString());
                                updatecmd.Parameters.AddWithValue("ReferenceURL", url.ToString());
                                updatecmd.Parameters.AddWithValue("CPE", cpe.ToString());
                                conn2.Open();
                                updatecmd.ExecuteNonQuery();
                                conn2.Close();
                            }
                            else
                            {
                                // TODO: Insert data into CPE DB Respository of reporting tool
                                string insertNIST = "INSERT INTO CPEs (Manufacturer,Product,Version,CPEType,ReferenceURL,CPE) values (@Manufacturer,@Product,@Version,@CPEType,@ReferenceURL,@CPE)";
                                SqlCommand insertcmd = new SqlCommand(insertNIST, conn2);
                                insertcmd.Parameters.AddWithValue("Manufacturer", manuf.ToString());
                                insertcmd.Parameters.AddWithValue("Product", prod.ToString());
                                insertcmd.Parameters.AddWithValue("Version", vers.ToString());
                                insertcmd.Parameters.AddWithValue("CPEType", cpetype.ToString());
                                insertcmd.Parameters.AddWithValue("ReferenceURL", url.ToString());
                                insertcmd.Parameters.AddWithValue("CPE", cpe.ToString());
                                conn2.Open();
                                insertcmd.ExecuteNonQuery();
                                conn2.Close();
    
                            }
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            Response.Write("Uh oh, something went wrong. " + ex.ToString() + "");
                        }
                        conta++;
                    }
                    Response.Write("CPE Dictionary Update Successful! Imported/Updated " + conta + " records.");
                }
                catch (Exception ex)
                {
                    Response.Write("CPE Dictionary Update failure. Imported/Updated " + conta + " records. Diagnostics:<br>" + ex.ToString() + "");
                }
            }
    

    My hope is that this helps someone else trying to do the same thing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 8, 2015 8:38 AM