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) — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Mar 10, 2016 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
– usamazf, Commented Mar 10, 2016 at 13:06

Community · Accepted Answer · 2017-05-23 11:45:16Z

3

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)

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Mar 10, 2016 at 13:10

jleach

7,8904 gold badges38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jleach Over a year ago

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.

opelhatza Over a year ago

well about the copie stuff didnt think about it but thx for your answer.

Collectives™ on Stack Overflow

c# sql parse double value to sql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related