CODEWITHSUNDEEP
c#
Thang
Thang

Reputation: 25

Read line by line from text file and save to database

Here is my code:

List<string> myValues = new List<string>();

string line;

StreamReader file = new StreamReader(@"D:\a.txt");
if ((line = file.ReadLine()) != null)
{
    string[] fields = line.Split(',');
    using (SqlConnection con = new SqlConnection(@"Data Source=NT;Initial Catalog=SinhVien;Integrated Security=True"))
    {

        con.Open();
        while((line = file.ReadLine()) != null)
        {
        SqlCommand cmd = new SqlCommand("INSERT INTO Sinhvien(ID, HoTen, DiaChi) VALUES (@id, @hoten, @diachi)", con);
        cmd.Parameters.AddWithValue("@id", fields[0].ToString());
        cmd.Parameters.AddWithValue("@hoten", fields[1].ToString());
        cmd.Parameters.AddWithValue("@diachi", fields[2].ToString());
        cmd.ExecuteNonQuery();
        }
    }

}

And my text file is:

10,Hong,10 hung vuong

11,Thuy,11 Hung Vuong

22,Thanh,22 Hung Vuong

But i can't save all line to database?

Upvotes: 1

Views: 13768

Answers (2)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107526

You're not too far off. Let's simplify this:

// If the file is small, read it all at once
string[] lines = File.ReadAllLines(@"D:\a.txt");

// TODO: if lines is empty, bail out

using (SqlConnection con = new SqlConnection(@"Data Source=NT;Initial Catalog=SinhVien;Integrated Security=True"))
{
    con.Open();
    // for each line (no ifs or whiles here)
    foreach (var line in lines)
    {
        string[] fields = line.Split(',');
        // TODO: verify fields contain what you want
        // SqlCommand implements IDisposable too
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Sinhvien(ID, HoTen, DiaChi) VALUES (@id, @hoten, @diachi)", con))
        {
            cmd.Parameters.AddWithValue("@id", fields[0]); // these are already strings, no ToString()s needed
            cmd.Parameters.AddWithValue("@hoten", fields[1]);
            cmd.Parameters.AddWithValue("@diachi", fields[2]);
            cmd.ExecuteNonQuery();
        }
    }
}

If your source file is rather large, and need to stream the lines out of it, then the other answerer shows that approach.

Upvotes: 2

Jonesopolis
Jonesopolis

Reputation: 25370

you're looping after you declare your fields, and never reset them. You want to loop through your file,get the fields, and save them in each iteration:

string line;

using (SqlConnection con = new SqlConnection(@"Data Source=NT;Initial Catalog=SinhVien;Integrated Security=True"))
{
    con.Open();
    using(StreamReader file = new StreamReader(@"D:\a.txt"))
    {
         while((line = file.ReadLine()) != null)
         {
             string[] fields = line.Split(',');

             SqlCommand cmd = new SqlCommand("INSERT INTO Sinhvien(ID, HoTen, DiaChi) VALUES (@id, @hoten, @diachi)", con);
             cmd.Parameters.AddWithValue("@id", fields[0].ToString());
             cmd.Parameters.AddWithValue("@hoten", fields[1].ToString());
             cmd.Parameters.AddWithValue("@diachi", fields[2].ToString());
             cmd.ExecuteNonQuery();
         }
    }
}

StreamReader implements IDisposable, so it advisable you wrap it in a using statement as well.

Upvotes: 5

Related Questions