Hi,

I have to edit programmatically some values in all the items of umbraco. For this purpose, I developed a little third-party web application that fetch data from the DB, modify the data and then update the DB.

All the DB updating process works pretty well but the problem is that I do not see the result in the umbraco interface.

Any idea of what I'm missing?

Am I attacking the right tables?

Find my code below.

Thanks in advance for the advises.

Damien

public void test()
        {
            StringBuilder sqls = new StringBuilder();
            SqlConnection SQLConnection = new SqlConnection();
            SQLConnection.ConnectionString = ConnectionString;
            SQLConnection.Open();

            // get all XML content in umbraco
            string sql = @"select 
                       distinct
                       doc.nodeId,
                       ct.alias,
                            CONVERT(varchar(max), cxml.xml) as xml
                        from cmsDocument doc
                       inner join cmsTemplate ct on doc.templateId=ct.nodeId
                       inner join cmsContentXml cxml on doc.nodeId=cxml.nodeId
                       inner join cmsPropertyData cpd on cpd.contentNodeId=doc.nodeId
                       inner join cmsPropertyType cpt on cpt.id=cpd.propertytypeid and cpt.Alias='navigationTitle'                        order by ct.alias";

            SqlDataAdapter da = new SqlDataAdapter(sql, SQLConnection); 
            DataSet ds = new DataSet();

            da.Fill(ds, "Records");

            List<UmbracoAlias> lstXmlNodeNames = new List<UmbracoAlias>();
            foreach (DataRow row in ds.Tables["Records"].Rows)
            {
                try
                {
                    XmlDocument xdoc = new XmlDocument();
                    xdoc.LoadXml(row["xml"].ToString());

                    XmlNode node = xdoc.SelectSingleNode("//" + row["alias"].ToString() + "");

                    if (String.IsNullOrEmpty(node.SelectSingleNode("navigationTitle").InnerText.Trim()))
                    {
                        node.SelectSingleNode("navigationTitle").InnerText = node.Attributes["nodeName"].Value;

// special condition for my needs
                        if (row["alias"].ToString() == "BlogPost"

                            && (String.Compare(ua.name.Substring(0, 3), "en-") == 0 ||
                                String.Compare(ua.name.Substring(0, 3), "fr-") == 0 ||
                                String.Compare(ua.name.Substring(0, 3), "nl-") == 0 ||
                                String.Compare(ua.name.Substring(0, 3), "de-") == 0 ||
                                String.Compare(ua.name.Substring(0, 3), "es-") == 0))
                        {

                            node.SelectSingleNode("navigationTitle").InnerText = (String.IsNullOrEmpty(node.SelectSingleNode("pageTitle").InnerText)) ? node.SelectSingleNode("title").InnerText : node.SelectSingleNode("pageTitle").InnerText;
                        }

                    }

                    // ...
                    StringBuilder sb = new StringBuilder();
                    XmlWriterSettings xws = new XmlWriterSettings();
                    xws.OmitXmlDeclaration = true;
                    XmlWriter xw = XmlWriter.Create(sb, xws);
                    xdoc.Save(xw);

                    string upd = String.Format("update cmsContentXml set xml='{0}' where nodeId={1};", sb.ToString().Replace("\r", "").Replace("\n", "").Replace("'", "''"), row["nodeId"].ToString()); sqls.Append(upd + "\r\n");
                    sqls.Append(String.Format("update cmsDocument set updateDate=GETDATE() where nodeId={1}", row["nodeId"].ToString())); sqls.Append(upd + "\r\n");
                }

                catch (Exception e)
                {
                    Console.Write(e.Message);
                }
            }
// EXECSQL == bool in class property or in method variables

            if (EXECSQL)

            {

                SqlCommand cmd = new SqlCommand(sqls.ToString(), SQLConnection);
                cmd.ExecuteNonQuery();
            }

            SQLConnection.Close();
            SQLConnection.Dispose();

}