Storing symbols in SQL

Question

I have a XML file where each tag of the XML is a column in my table, that code looks like this:

try
{
    SqlCommand makeCols = new SqlCommand
    {
        CommandText = "ALTER TABLE Devices ADD " + reader.Name + " nvarchar(100)",
        CommandType = CommandType.Text,
        Connection = conn
    };
    makeCols.ExecuteNonQuery();
}
catch (Exception)
{
    break;
}

And then I have code that updates the SQL table with data at each column like so:

SqlCommand fillCols = new SqlCommand
{
    CommandText = "UPDATE Devices  SET "+ reader.Name + "=" + reader.Value + ", WHERE ID=1",
    CommandType = CommandType.Text,
    Connection = conn
};
fillCols.ExecuteNonQuery();

Where reader.Name is the XML tag, and reader.Value is the data.

I have data that will look like this: v2.14, - 0.00 kg/s, -99.00 Deg C, and so on. When I try to update the table I get the following error: Exception Occured while creating table:Incorrect syntax near 'v2'. System.Data.SqlClient.SqlException.

My question is, how can I store data in SQL table that will have symbols like . , - / \

Edit - To Clarify

Reader is just a loop that reads the XML file. reader.Name is the XML tag, so for example <port1> becomes port and is made a column. reader.Value is what is between <port1> and </port1> which needs to be stored in my database. reader.Value can have symbols such as: . , - / \ %. I just want to know how to store those symbols in the database.

Indeed - basically, as soon as you use parameterized queries, all of this goes away, in terms of *values*. You still need to worry about escaping for table / column names though. — Jon Skeet, Jan 31 '18 at 17:10
See here for further discussion: https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements. — Polyfun, Jan 31 '18 at 17:11
The data I will need to insert into the table will be dynamic, can you parameterize dynamic information? — GreenSaber, Jan 31 '18 at 17:11
Why are you recreating a database in XML? Why not design a database schema that is able to properly model the data stored in XML? — Daniel Mann, Jan 31 '18 at 17:11
This can't be fully parameterized because the columns are not allowed to be parameters. This does however scream of a design gone very wrong. — Sean Lange, Jan 31 '18 at 17:11
Some important information missing here; where does `reader` come from? What are all possible values of `reader.name`? What are your XML tags and what is your XML schema? Altering your database schema at runtime is very unusual; why do you think you need to do this? — Dour High Arch, Jan 31 '18 at 17:21
Constantly adding new columns to a table like this is a very strong indication that your design is not well put together. Tables are structured elements designed to hold data. When you have to make the design of the table flexible you have defeated the purpose of structured data. — Sean Lange, Jan 31 '18 at 17:30
@SeanLange the problem is that I've been asked to hold this information in a database while also making sure that if a tag is added in the XML that it is also added to the table, is there another way to do that? — GreenSaber, Jan 31 '18 at 17:33
add Cotation "UPDATE Devices SET "+ reader.Name + "=' " + reader.Value + " ', WHERE ID=1" — محمد النعيمي, Jan 31 '18 at 17:38
There are many ways to “hold this information in a database while also making sure that if a tag is added in the XML that it is also added to the table”. Which way is best for you depends on things like the possible values of `reader.Name`, which you refuse to tell us so we cannot give you a good answer. For example, why would “`` becomes port and is made a column”? What happened to the `1`? Could there be a ``? Ports 3 to 9782894420? Why must it be “made a column”? To give directions you need to tell us where you're starting from and you haven't done that. — Dour High Arch, Jan 31 '18 at 18:17
Don't have a lot of details here but this might be one of those where a hybridized EAV type of system makes sense. — Sean Lange, Jan 31 '18 at 19:24

score 0 · Accepted Answer · answered Jan 31 '18 at 17:17

There are escape symbols that allow you to have non-trivial column names; so:

create table Foo (Id int, [Evil \ Column | Name] int)

works - but makes for very awkward and brittle code. For example, it prompts the question: "what if my name contains a [ or ]?". Escaping runtime text is a dangerous game where getting it wrong once can be devastatingly bad.

However! I strongly suspect that this data might be better mapped using a key/value pair, so the name is a row (not column) in a Metrics table (with an Id and a Name, and whatever else you need - maybe a Unit), and your readings have a DeviceMetric table with a DeviceId, MetricId and a Value. It is often but not quite always the case that you shouldn't be mutating the column schema like this at runtime. There are some cases when it makes perfect sense (especially when data volume and layout is a real factor), but by default it should definitely be avoided.

Storing symbols in SQL

1 Answers1