Modify an attribute according to its data type c#

Question

For a giving DB, I used CodeSmith to generate a text file that has the following values

(TableName)([TableGUID]) (AttributeName).(AttributeType)

for example

CO_CallSignLists[e3fc5e2d-fe84-492d-ad94-3acced870714] SunSpots.smallint

Now I parsed these values and assigned each to a certain variable

        for (int j = 0; j < newLst.Count; j += 2)
        {
            test_objectName_Guid = newLst[j];   //CO_CallSignLists[e3fc5e2d-fe84-492d-ad94-3acced870714]
            test_attr = newLst[j + 1];          //SunSpots.smallint

            //Seperate Guid from objectName
            string[] obNameGuid = test_objectName_Guid.Split('[',']');
            var items = from line in obNameGuid
                        select new
                        {
                            aobjectName = obNameGuid[0],
                            aGuid = obNameGuid[1]
                       };
            foreach (var item in items)
            {
                final_objectName = item.aobjectName;
                final_oGuid = new Guid(item.aGuid);
            }
            Console.WriteLine("\nFinal ObjectName\t{0}\nFinal Guid\t\t{1}",
                    final_objectName, final_oGuid);

        string final_attributeName = string.Empty;
        string final_attributeType = string.Empty;
        string[] words = test_attr.Split('.');
        var items2 = from line in words
                    select new
                    {
                        attributeName = words[0],
                        attributeType = words[1]
                    };
        foreach (var item in items2)
        {
            final_attributeName = item.attributeName;
            final_attributeType = item.attributeType;

        }
        Console.WriteLine("Attribute Name\t\t{0}\nAttributeType\t\t{1}\n", 
            final_attributeName, final_attributeType);

I then generate an xml file that loads data from the DB depending on the objectName and its GUID and save this xml in a string variable

string generatedXMLFile = Test.run_Load_StoredProcedure(final_objectName, final_oGuid, Dir);

Now I wanna modify the attribute in this xml file that is equal to the attribute from the parsed txt file. (I wanna modify it according to its type)

public void modifyPassedAttribute(string myFile, string attributeName)
    {
        Object objString = (Object)attributeName;
        string strType = string.Empty;
        int intType = 0;
        XDocument myDoc = XDocument.Load(myFile);

        var attrib = myDoc.Descendants().Attributes().Where(a => a.Name.LocalName.Equals(objString));
        foreach (XAttribute elem in attrib)
        {
            Console.WriteLine("ATTRIBUTE NAME IS {0} and of Type {1}", elem, elem.Value.GetType());

            if (elem.Value.GetType().Equals(strType.GetType()))
            {
                elem.Value += "_change";
                Console.WriteLine("NEW VALUE IS {0}", elem.Value);
            }
            else if (elem.Value.GetType().Equals(intType.GetType()))
            {
                elem.Value += 2;
                Console.WriteLine("NEW VALUE IS {0}", elem.Value);
            }
        }
        myDoc.Save(myFile);
    }

The problem is that I always says that the value type is 'string' and modifies it as a string (adds "_change").. though when I wanna save the data back into the DB it says you can't assign a nvarchar to a smallint value.

What's wrong with my code? Why do I always get a string type? Is it because all attributes are treated as strings in an xml file? How then can I modify the attribute according to its original type so that I won't get errors when I wanna save it back in the DB?

I'm open for suggestion to optimize the code I know it's not the best code to archive my goal

EDIT

Here is the code to generate the XMLs

    public void generate_XML_AllTables(string Dir)
    {
        SqlDataReader Load_SP_List = null;  //SQL reader that gets list of stored procedures in the database
        SqlDataReader DataclassId = null;   //SQL reader to get the DataclassIds from tables

        SqlConnection conn = null;
        conn = new SqlConnection("Data Source= EUADEVS06\\SS2008;Initial Catalog=TacOps_4_0_0_4_test;integrated security=SSPI; persist security info=False;Trusted_Connection=Yes");

        SqlConnection conn_2 = null;
        conn_2 = new SqlConnection("Data Source= EUADEVS06\\SS2008;Initial Catalog=TacOps_4_0_0_4_test;integrated security=SSPI; persist security info=False;Trusted_Connection=Yes");

        SqlCommand getDataclassId_FromTables;

        int num_SP = 0, num_Tables = 0;
        string strDataClass;    //Name of table
        string sql_str;         //SQL command to get 

        conn.Open();

        //Select Stored Procedeurs that call upon Tables in the DB. Tables which have multiple DataClassIds (rows)
        //Selecting all Load Stored Procedures of CLNT & Get the table names
        // to pass the Load operation which generates the XML docs.
        SqlCommand cmd = new SqlCommand("Select * from sys.all_objects where type_desc='SQL_STORED_PROCEDURE' and name like 'CLNT%Load';", conn);
        Load_SP_List = cmd.ExecuteReader();

        while (Load_SP_List.Read())
        {
            //Gets the list of Stored Procedures, then modifies it
            //to get the table names
            strDataClass = Load_SP_List[0].ToString();
            strDataClass = strDataClass.Replace("CLNT_", "");
            strDataClass = strDataClass.Replace("_Load", "");

            sql_str = "select TOP 1 DataclassId from " + strDataClass;

            conn_2.Open();
            getDataclassId_FromTables = new SqlCommand(sql_str, conn_2);
            DataclassId = getDataclassId_FromTables.ExecuteReader();

            while (DataclassId.Read())
            {
                string test = DataclassId[0].ToString();
                Guid oRootGuid = new Guid(test);
                run_Load_StoredProcedure(strDataClass, oRootGuid, Dir);
                num_Tables++;
            }

            DataclassId.Close();
            conn_2.Close();
            num_SP++;
        }

        Load_SP_List.Close();
        conn.Close();
        System.Console.WriteLine("{0} of Stored Procedures have been executed and {1} of XML Files have been generated successfully..", num_SP,num_Tables);
    }

    public string run_Load_StoredProcedure(string strDataClass, Guid guidRootId, string Dir)
    {
        SqlDataReader rdr = null;

        SqlConnection conn = null;
        conn = new SqlConnection("Data Source= EUADEVS06\\SS2008;Initial Catalog=TacOps_4_0_0_4_test;integrated security=SSPI; persist security info=False;Trusted_Connection=Yes");
        conn.Open();

        // Procedure call with parameters
        SqlCommand cmd = new SqlCommand("CLNT_" + strDataClass + "_Load", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandTimeout = 0;

        //Adding parameters, in- and output
        SqlParameter idParam = new SqlParameter("@DataclassId", SqlDbType.UniqueIdentifier);
        idParam.Direction = ParameterDirection.Input;
        idParam.Value = guidRootId;

        SqlParameter xmlParam = new SqlParameter("@XML", SqlDbType.VarChar, -1 /*MAX*/ );
        xmlParam.Direction = ParameterDirection.Output;

        cmd.Parameters.Add(idParam);
        cmd.Parameters.Add(xmlParam);

        rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);

        DirectoryInfo dest_2 = new DirectoryInfo(Dir + "\\Copies");
        DirectoryInfo dest = new DirectoryInfo(Dir + "\\Backup");
        DirectoryInfo source = new DirectoryInfo(Dir);

        if (source.Exists == false)
        {
            source.Create();

            if (dest.Exists == false)
            {
                dest.Create();
            }

            if (dest_2.Exists == false)
            {
                dest_2.Create();
            }
        }
        string xmlFile = @Dir + "\\" + strDataClass + " [" + guidRootId + "].xml";
        //The value of the output parameter ‘xmlParam’ will be saved in XML format using the StreamWriter. 
        System.IO.StreamWriter wIn = new System.IO.StreamWriter(xmlFile, false);
        wIn.WriteLine(xmlParam.Value.ToString());
        wIn.Close();
        rdr.Close();

        rdr.Close();

        conn.Close();

        return xmlFile;
    }

Answer 1

Short answer:

Is it because all attributes are treated as strings in an xml file?

Yes.

You'll need to store the original type in the Xml - as far as I could tell, you're not including that, so you're discarding the information.

Answer 2

ok so I solved the issue! All I had to do was to pass the attributeType string to the modifyPassedAttribute function and use it to determine the changes I wanna make

Here is the modified final code

    public void modifyPassedAttribute(string myFile, string attributeName, string attributeType)
    {
        Object objString = (Object)attributeName;
        string strType = "nvarchar";
        string smallintType = "smallint";
        string intType = "int";
        string dateType = "datetime";            
        XDocument myDoc = XDocument.Load(myFile);

        //var myAttr = from el in myDoc.Root.Elements()
        //                    from attr in el.Attributes()
        //                    where attr.Name.ToString().Equals(attributeName)
        //                    select attr;

        var attrib = myDoc.Descendants().Attributes().Where(a => a.Name.LocalName.Equals(objString));
        foreach (XAttribute elem in attrib)
        {
            Console.WriteLine("ATTRIBUTE NAME IS {0} and of Type {1}", elem, elem.Value.GetType());

            if (strType.Equals(attributeType))
            {
                if (elem.Value.EndsWith("_change"))
                {
                    elem.Value = elem.Value.Replace("_change", "");
                    Console.WriteLine("NEW VALUE IS {0}", elem.Value);
                }
                else
                {
                    elem.Value += "_change";
                    Console.WriteLine("NEW VALUE IS {0}", elem.Value);
                }

            }
            else if (smallintType.Equals(attributeType))
            {
                if (elem.Value.EndsWith("2"))
                {
                    elem.Value = elem.Value.Replace("2", "");
                    Console.WriteLine("NEW VALUE IS {0}", elem.Value);
                }
                else
                {
                    elem.Value += 2;
                    Console.WriteLine("NEW VALUE IS {0}", elem.Value);
                }
            }
            else if (intType.Equals(attributeType))
            {
                if (elem.Value.EndsWith("2"))
                {
                    elem.Value = elem.Value.Replace("2", "");
                    Console.WriteLine("NEW VALUE IS {0}", elem.Value);
                }
                else
                {
                    elem.Value += 2;
                    Console.WriteLine("NEW VALUE IS {0}", elem.Value);
                }
            }
            else if (dateType.Equals(attributeType))
            {
                if (elem.Value.EndsWith("2"))
                {
                    elem.Value = elem.Value.Replace("2", "");
                    Console.WriteLine("NEW VALUE IS {0}", elem.Value);
                }
                else
                {
                    elem.Value += 2;
                    Console.WriteLine("NEW VALUE IS {0}", elem.Value);
                }
            }

        }
        myDoc.Save(myFile);
    }

the if statements inside are there so that no large numbers would be generated because the 2 is added to the string 100 (i.e. 1002) and if each time I'm gonna add 2 then it's gonna simply crash