c# sql parse double value to sql

Question

Hello i tried to add a double value from c# to an sql float field in an Table.

SqlConnection SQLConnection = new SqlConnection(String.Format(@"Integrated Security=SSPI;server={0};Initial Catalog={1};", SqlServer, database));

        //For the import
        SQLConnection.Open();
                                                                                        //0             1           2           3           4       5           6               7        8        9            10                  11              12               13            14         15          16        17         18        19      20         21        22          23               24          25             26            27         28                                                                                                                                                                                                 0                                     1                                2                                  3                                 4                             5                                     6                                 7                              8                               9                                    10                                      11                                     12                                 13                                    14                               15                               16                                 17                             18                             19                               20                        21                                22                                 23                                      24                     25                             26                          27                    28                                                                                            
        string stringForInsert = String.Format("INSERT INTO [dbo].[tblSPpowerPlant] ([projectName],[location],[shortName],[numberOfWtgs],[mwWtg],[mwTotal],[projectShareWeb],[mwWeb],[phase],[phaseNumber],[phaseDescription],[projectProgress],[mwDeveloped],[projectManager],[spaceURL],[country],[technology],[state],[allPermits],[cod],[statesince],[spID],[currency],[possibleWtgTypes],[hubHeight],[visibillity],[templateName],[timestamp],[note]) OUTPUT INSERTED.Id VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},{26},{27},{28})", getSqlField(powerPlant.projectName),getSqlField(powerPlant.location),getSqlField(powerPlant.shortName),getSqlField(powerPlant.numberOfWtgs),getSqlField(powerPlant.mwWtg),getSqlField(powerPlant.mwTotal),getSqlField(powerPlant.projectShareWeb),getSqlField(powerPlant.mwWeb),getSqlField(powerPlant.phase),getSqlField(powerPlant.phaseNumber),getSqlField(powerPlant.phaseDescription),getSqlField(powerPlant.projectProgress),getSqlField(powerPlant.mwDeveloped),getSqlField(powerPlant.projectManager),getSqlField(powerPlant.spaceUrl),getSqlField(powerPlant.country),getSqlField(powerPlant.technology),getSqlField(powerPlant.state),getSqlField(powerPlant.allPermits),getSqlField(powerPlant.cod),getSqlField(powerPlant.stateSince),getSqlField(powerPlant.spID),getSqlField(powerPlant.currency),getSqlField(powerPlant.possibleWtgTypes),getSqlField(powerPlant.hubHeight),getSqlField(powerPlant.visibility),getSqlField(powerPlant.templateName),getSqlField(timestamp), getSqlField(note));
        SqlCommand sqlInsertCommand = new SqlCommand(stringForInsert, SQLConnection);                                                                                                                                                                                                                                                                                                //VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24})                           
        sqlInsertCommand.CommandType = CommandType.Text;
        SqlDataReader reader;

        sqlInsertCommand.CommandText = stringForInsert;
        reader = sqlInsertCommand.ExecuteReader();

        int id = int.MaxValue;

        while (reader.Read())
        {
            id = reader.GetInt32(0);
        }

        if (id == int.MaxValue)
            throw new Exception("Insert failed.");

        reader.Close();

        SQLConnection.Close();

But when the actual save to the db happens it saves the wrong number. Stuff like 12,3 it makes 12.3..... So my question is how to prevent that from happening? so when i type 12,3 it writes 12,3 to the table. Any advice would be great thx

btw sorry for my english not may native language

EDIT: And about the SQL-Injections i now it but there is no interface for this.

EDIT2: The datatype in the database is float.

EDIT3: 5,1,8,9,0,51,4,59 here is an example of the output. Insted 5.1 it says 5,1 and counts it as two fields. When i try to run it i get an exeption.

If you *parametrize* the query, `.Net` will solve your problem for you (represent `Double`) — Dmitry Bychenko, Mar 10 '16 at 13:06
depends on your data type. If its number as in double it will convert 12,6 into 12.6 since it assumes , to be a decimal placeholder. — usamazf, Mar 10 '16 at 13:06

score 3 · Accepted Answer · edited May 23 '17 at 11:45

Parameters is what you want... doing it this was is inviting a SQL Injection attack.

Here's the basic gist of it:

cmd.CommantText = "INSERT INTO Table (Field1, Field2) VALUES(@Param1, @Param2);";
cmd.Parameters.AddWithValue("@Param1", yourObj.Property1);
cmd.Parameters.AddWithValue("@Param2", someMethodToReturnValue(someParam));

Not only does this prevent SQL Injection, but it maps the types of your object properties/values applied to the parameter to the correct SqlDbType.

Regardless of lack of an interface for it, it's still best practice to use parameters and avoid the injection attack (who's to say that there will never be a day in the future when these values aren't supplied elsewhere, or what if some unseasoned developer copies your code? There's no reason to use string concatenation over parameterized queries, and every reason not to... and of course an added plus is correct type mapping!)

Last edit: AddWithValue doesn't handle possible nulls, so you can write a quick extension method like AddWithNullableValue:

public static SqlParameter AddWithNullableValue(this SqlParameterCollection collection, string parameterName, object value)
{
     if(value == null)
        return collection.AddWithValue(parameterName, DBNull.Value);
     else
        return collection.AddWithValue(parameterName, value);
}

(taken from here: Exception when AddWithValue parameter is NULL)

As a side note, I've always tried to impress upon learners that security in software design is as much an integral best practice/experience thing as it is any specific measures taken to secure a solution... this is a perfect example. You have no way, at this level of the code, to ensure that those string values are safe, and thus to write secure code, you *must* plan for the fact that it could be unsafe values given to the method. — jleach, Mar 10 '16 at 13:23
well about the copie stuff didnt think about it but thx for your answer. — opelhatza, Mar 10 '16 at 13:24

c# sql parse double value to sql

1 Answers1