Text file to database

[Karthika_g]

Hi, can anyone help me in storing a text file data to database using C# coding. I'm not sure of the below code can anyone correct me.

Thanks in advance.

Note:
TextFile1.txt
124 Keer 22 14000.000000
125 gjh 45 12103.000000
126 sg 48 63230.000000
129 fdgd 21 65620.000000

con = new SqlConnection();
            con .ConnectionString="Server=    SQLEXPRESS;integrated security = true;initial catalog = Practice;";
            con.Open();
            cmd = con.CreateCommand();
            foreach (string line in File.ReadAllLines("TextFile1.txt"))
            {
                string[] parts = line.Split(' ');
                int i = 0;
                foreach (string part in parts)
                {


                    cmd.CommandText = "Insert into BankDetails values(@acc,@nm,@ag,@bal)";
                    cmd.Parameters.Add("@acc", SqlDbType.NVarChar).Value = part[i];
                   cmd.Parameters.Add("@nm", SqlDbType.NVarChar).Value = part[i];
                    cmd.Parameters.Add("@ag", SqlDbType.Int).Value = part[i];
                    cmd.Parameters.Add("@bal", SqlDbType.Decimal).Value = part[i].ToString();
                } i++;

            }
            int x =cmd.ExecuteNonQuery();
            if(x==1)
            MessageBox.Show("Inserted to DB");
            else
            MessageBox.Show("Not Inserted");

            con.Close();

[Murtan]

You don't want the 'foreach' on line 9, you don't want to add a record to the database for every field on the line.

and presuming that the construct cmd.Parameters.Add("@acc", SqlDbType.NVarChar).Value = part[i]; makes sense (I've not seen it before so I'll trust you that it will work). You will need to have each of the command parameters access the appropriate field from the line. In this case I think it should be cmd.Parameters.Add("@acc", SqlDbType.NVarChar).Value = parts[0];

[DanielGreen]

hi!

First: you must execute the sql-Command for each row!
Second: you parameter-collection will have duplicate parameter-names -> you are adding for each part of the line (in your case 4 parts) the same names.

foreach (string part in parts)
{
cmd.CommandText = "Insert into BankDetails values(@acc,@nm,@ag,@bal)";
cmd.Parameters.Add("@acc", SqlDbType.NVarChar).Value = part;
cmd.Parameters.Add("@nm", SqlDbType.NVarChar).Value = part;
cmd.Parameters.Add("@ag", SqlDbType.Int).Value = part;
cmd.Parameters.Add("@bal", SqlDbType.Decimal).Value = part.ToString();
} i++;

try this code:

con = new SqlConnection();
try
{
  con .ConnectionString="Server= SQLEXPRESS;integrated security = true;initial catalog = Practice;";
  con.Open();
  int inserted=0;
  foreach (string line in File.ReadAllLines("TextFile1.txt"))
  {
      cmd = con.CreateCommand();
      string[] parts = line.Split(' ');  // this splits the current line in parts
      if (parts.Length>=4)
      {
           cmd.CommandText = "Insert into BankDetails  values(@acc,@nm,@ag,@bal)";
           cmd.Parameters.Add("@acc", SqlDbType.NVarChar).Value = parts[0];
           cmd.Parameters.Add("@nm", SqlDbType.NVarChar).Value = parts[1];
           cmd.Parameters.Add("@ag", SqlDbType.Int).Value = parts[2];
           cmd.Parameters.Add("@bal", SqlDbType.Decimal).Value = parts[3].ToString();
       } 
       int x =cmd.ExecuteNonQuery();
       if(x>0)
         inserted++;
  }
  if (inserted>0)
     MessageBox.Show("Inserted " + inserted + " rows to DB ");
  else
     MessageBox.Show("Nothing Inserted");
}
catch (Exception ex)
{
     MessageBox.Show("Error"); // you can add there you exception-handling if something goes wrong with the db-commands
}
finally
{
    con.Close();
}