c# - search csv file in a folder and insert in mysql database -

i'm trying parse files in directory/insert in mysql database based on post , pretty working except csv values each column being rounded , become int values in database. assume need change decimal else, couldn't figure out.

(ex: 1.11 becomes 1, 45.5 becomes 46)

csv file:

test1,test2,test3,test4  1.11,1.23,67.4,4.5 1.12,5.42,45.5,6.45 

my code:

public void sqltest()     {         string connectionstring = "server=localhost;database=test;userid=root;password=test;";         using (mysqlconnection connection = new mysqlconnection(connectionstring))         {             connection.open();             using (mysqlcommand cmd = connection.createcommand())             {                 cmd.commandtext = @"drop table test if exists";                 cmd.commandtext = @"create table test (                                          id int unsigned not null auto_increment primary key,                                         test1 decimal,                                         test2 decimal,                                         test3 decimal,                                         test4 decimal                                     )";                 cmd.executenonquery();             }             connection.close();             connection.open();              using (mysqlcommand insertcommand = connection.createcommand())             {                 insertcommand.commandtext =                     @"insert test (test1, test2, test3, test4)                       values (@test1, @test2, @test3, @test4)";                  insertcommand.parameters.add(new mysqlparameter("@test1", dbtype.decimal));                 insertcommand.parameters.add(new mysqlparameter("@test2", dbtype.decimal));                 insertcommand.parameters.add(new mysqlparameter("@test3", dbtype.decimal));                 insertcommand.parameters.add(new mysqlparameter("@test4", dbtype.decimal));                  string[] files = directory.getfiles("d:/computer/csv files", "*.csv");                  foreach (string file in files)                 {                     string[] lines = system.io.file.readalllines(file);                     bool parse = false;                      foreach (string tmpline in lines)                     {                         string line = tmpline.trim();                         if (!parse && line.startswith("test1"))                         {                             parse = true;                             continue;                         }                         if (!parse || string.isnullorempty(line))                         {                             continue;                         }                          foreach (mysqlparameter parameter in insertcommand.parameters)                         {                             parameter.value = null;                         }                          string[] values = line.split(new[] { ',' });                          (int = 0; < values.length; i++)                         {                             mysqlparameter param = insertcommand.parameters[i];                              decimal value;                             param.value = decimal.tryparse(values[i], out value) ? value : 0;                          }                         insertcommand.executenonquery();                     }                  }             }             connection.close();         }     }         } 

any guidance or tips appreciated!

if in position, first change table structure this

 create table `test`(    `test1` double(13,2),    `test2` double(13,2),    `test3` double(13,2),    `test4` double(13,2) ); 

then instead of reading files' contents , doing insert each record, can use load data infile command import csv file table in mysql.

check link mysql - load data infile. complete documentation. example usage this

load data infile 'test.csv' table test fields terminated ',' lines terminated '\r\n' ignore 1 lines 

depending on server environment, need edit path of csv file correctly , make sure mysql local infile flag set 1 , if not can set using command

mysql -u username -p --local-infile=1 

so coming code, can reduced this

... ... ...  public void sqltest() {     string connectionstring = "server=localhost;database=test;userid=root;password=test;";     using (mysqlconnection connection = new mysqlconnection(connectionstring))     {         connection.open();         string query = @"drop table test if exists;                         create table `test`(                        `test1` double(13,2),                        `test2` double(13,2),                        `test3` double(13,2),                        `test4` double(13,2) );";         using (mysqlcommand cmd = new mysqlcommand(query, connection))         {                            cmd.executenonquery();             string[] files = directory.getfiles("d:/computer/csv files", "*.csv");              foreach (string file in files)             {                query =@"load data infile '"+file+"' table test                         fields terminated ','                         lines terminated '\r\n'                         ignore 1 lines";                  using (mysqlcommand cmd1 = new mysqlcommand(query, connection))                 {                        cmd1.executenonquery();                 }                                 }         }      } } ... ... ...