System.Data.SqlClient.SqlDataReader does not contain a definition for 'open'

Question

How can I open a data reader after closing it? I'm using Visual Studio 2010.

Here is my code.

bool result = Directory.EnumerateFiles(@"C:\Users\Moon\Documents\Visual Studio 2010\Projects\cdrInsertion\cdrInsertion\TempFiles").Any();

if (!result)
{
   Response.Write("Folder is empty");
}
else 
{
   DirectoryInfo info = new DirectoryInfo(@"C:\Users\Moon\Documents\Visual Studio 2010\Projects\cdrInsertion\cdrInsertion\TempFiles");
   FileInfo[] files = info.GetFiles();

   SqlConnection con = new SqlConnection("Data Source = MOON-PC\\SQLEXPRESS; Initial Catalog = Call_Detail_Record; Integrated Security = true; Persist Security Info=False;");
   con.Open();

   SqlCommand cmd = new SqlCommand();

   SqlDataReader readr = null;

   foreach (FileInfo file in files) 
   {
       string path = @"C:\Users\Moon\Documents\Visual Studio 2010\Projects\cdrInsertion\cdrInsertion\TempFiles\"+ file;
       string queryfile = "select * from file_log";
       cmd = new SqlCommand(queryfile,con);
       readr = cmd.ExecuteReader();

       while (readr.Read())
       {
           (readr.Open();)<----here i want to open it.

           string filnames = readr["file-name"].ToString();
           string filestring = file.ToString();

           if (filnames.Equals(filestring))
           {
              Response.Write("file already inserted");
              readr.Close();
           }
           else 
           {
              string text = System.IO.File.ReadAllText(path);

              string[] lines = text.Split('\n');
              //transctionscop
              // DataTable dt = new DataTable();

              // cmd = new SqlCommand();
              string[] Values;

              foreach (string line1 in lines)
              {
                 if (line1 == "")
                 {
                    Response.Write("end file");
                 }
                 else
                 {
                    Values = line1.Split(';');
                    DateTime zero = Convert.ToDateTime(Values[0]);
                    //  DateTime onezerofive = Convert.ToDateTime(Values[105]);
                    // DateTime onezerosix = Convert.ToDateTime(Values[106]);
                    //  Timer two = Convert.Tot(Values[2]);

                    // string query = "INSERT INTO cdr_info VALUES ('" + Values[0] + "'," + Values[1] + ",'" + Values[2] + "','" + Values[3] + "'," +
                    string query = "INSERT INTO cdr_info VALUES( '" + zero + "', '" + Values[1] + "', '" + Values[2] + "',  '" + Values[3] + "', '" + Values[4] + "',  '" + Values[5] + "', '" + Values[6] + "',  '" + Values[7] + "',  '" + Values[8] + "', '" + Values[9] + "'," +
        " '" + Values[10] + "',  '" + Values[11] + "',  '" + Values[12] + "',  '" + Values[13] + "',  '" + Values[14] + "','" + Values[15] + "', '" + Values[16] + "',  '" + Values[17] + "',  '" + Values[18] + "','" + Values[19] + "'," +
        "'" + Values[20] + "','" + Values[21] + "', '" + Values[22] + "',  '" + Values[23] + "',  '" + Values[24] + "',  '" + Values[25] + "','" + Values[26] + "',  '" + Values[27] + "', '" + Values[28] + "', '" + Values[29] + "', " +
        " '" + Values[30] + "', '" + Values[31] + "',  '" + Values[32] + "',  '" + Values[33] + "',  '" + Values[34] + "'," +
        "'" + Values[35] + "',  '" + Values[36] + "',  '" + Values[37] + "',  '" + Values[38] + "','" + Values[39] + "', '" + Values[40] + "', '" + Values[41] + "', '" + Values[42] + "'," +
        "'" + Values[43] + "', '" + Values[44] + "',  '" + Values[45] + "',  '" + Values[46] + "',  '" + Values[47] + "',  '" + Values[48] + "',  '" + Values[49] + "','" + Values[50] + "', '" + Values[51] + "'," +
        " '" + Values[52] + "', '" + Values[53] + "',  '" + Values[54] + "',  '" + Values[55] + "',  '" + Values[56] + "','" + Values[57] + "', '" + Values[58] + "', '" + Values[59] + "', '" + Values[60] + "', '" + Values[61] + "'," +
        "'" + Values[62] + "', '" + Values[63] + "',  '" + Values[64] + "', '" + Values[65] + "', '" + Values[66] + "','" + Values[67] + "','" + Values[68] + "','" + Values[69] + "', '" + Values[70] + "'," +
        "'" + Values[71] + "', '" + Values[72] + "','" + Values[73] + "','" + Values[74] + "', '" + Values[75] + "',  '" + Values[76] + "',  '" + Values[77] + "',  '" + Values[78] + "',  '" + Values[79] + "',  '" + Values[80] + "'," +
        "  '" + Values[81] + "',  '" + Values[82] + "',  '" + Values[83] + "',  '" + Values[84] + "',  '" + Values[85] + "','" + Values[86] + "', '" + Values[87] + "','" + Values[88] + "', '" + Values[89] + "',  '" + Values[90] + "'," +
        "  '" + Values[91] + "',  '" + Values[92] + "', '" + Values[93] + "', '" + Values[94] + "',  '" + Values[95] + "', '" + Values[96] + "',  '" + Values[97] + "', '" + Values[98] + "',  '" + Values[99] + "',  '" + Values[100] + "'," +
        "  '" + Values[101] + "',  '" + Values[102] + "',  '" + Values[103] + "'," +
        " '" + Values[104] + "',  '" + Values[105] + "',  '" + Values[106] + "',  '" + Values[107] + "', '" + Values[108] + "')";
                            // string query = "INSERT INTO demooo VALUES ('" + Values[0] + "','" + Values[1] + "','" + Values[2] + "')";
                    cmd = new SqlCommand(query, con);
                    cmd.ExecuteNonQuery();
                }
            }
       }
   }
   readr.Close();
}

You don't open a reader - you open a connection. You can't reopen a reader. — Jon Skeet
– Jon Skeet, Commented Apr 8, 2014 at 9:40
It's not really clear what you're trying to do - but having scrolled down a bit, the first thing to fix is your massive SQL injection attack vulnerability. Do not construct SQL like this. Use parameterized SQL instead. — Jon Skeet
– Jon Skeet, Commented Apr 8, 2014 at 10:02

jmcilhinney · Accepted Answer · 2014-04-08 09:44:57Z

2

The reader is already open. When you call ExecuteReader on the command, it returns an open data reader.

Why are you closing the data reader inside the loop though? You've got a Close call after the loop anyway so why close it twice? What you should do is create it with a using statement and then it will be implicitly closed at the end of the block.

answered Apr 8, 2014 at 9:44

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

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

2 Comments

user3368644 Over a year ago

if i don't close it within the if statement,the exception generates The reader is already open.tell me some alternative's.

jmcilhinney Over a year ago

It's hard to read so much code in such a confined place but what you should be doing is only closing the reader in one place, outside the while loop but inside the foreach loop. If you do as I have already suggest and create it with a using statement then that takes care of itself.

Collectives™ on Stack Overflow

System.Data.SqlClient.SqlDataReader does not contain a definition for 'open'

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related