Click here to Skip to main content
15,903,854 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
i have 500000 record in the my sql table .when i am reading all the value data reader only taking 1,00,000 records .how can i slove the problem?

code:-
C#
MySqlConnection con = new MySqlConnection("SERVER=192.168.3.1;DATABASE=mysar;UID=root;PASSWORD=xxxx;");
SqlConnection sqlcon = new SqlConnection("Data Source=RMS;Initial Catalog=TestMySQLLinuxProxyServer;Integrated Security=True");
sqlcon.Open();
try
{
    con.Open();
    MessageBox.Show("Connection is established");
    MySqlCommand cmd = new MySqlCommand("select * from mysar.traffic", con);
    MySqlDataReader datareader = cmd.ExecuteReader();
    string[] s = new string[10];
    int i = 0;
    string tempstring;
    while (datareader.Read())
    {
        //list[0].Add(dataReader["id"] + "");
        ////id,ip,description,isResolved,hostname
        //hostnames[0].Add(datareader["id"] + "");
        //hostnames[1].Add(datareader["ip"]+ "");
        //hostnames[2].Add(datareader["description"] + "");
        //hostnames[3].Add(datareader["isResloved"] + "");
        //hostnames[4].Add(datareader["hostname"] + "");
        //MessageBox.Show(hostnames[0].ToString());
        
        int j = 0;
        for (i = 0; i < datareader.FieldCount; i++)
        {
            //MessageBox.Show(datareader[i].ToString());
            tempstring = datareader[i].ToString();
            s[j] = tempstring;
            j++;
            if ((i % 9) == 0 && i != 0)
            {
                //MessageBox.Show(s[0]+s[1]+s[2]+s[3]+s[4]);
                SqlCommand sqlcmd = new SqlCommand("insert into traffic values('" + @s[0] + "','" + @s[1] + "','" + @s[2] + "','" + @s[3] + "','" + @s[4] + "','" + @s[5] + "','" + @s[6] + "','" + @s[7] + "','" + @s[8] + "','" + @s[9] + "')", sqlcon);
                sqlcmd.ExecuteNonQuery();
                j = 0;
            }
            //MessageBox.Show(s[i].ToString());
        }
    }
    MessageBox.Show(datareader.FieldCount.ToString());
    sqlcon.Close();
    datareader.Close();
    con.Close();
    MessageBox.Show(s[0]);
    
    //List< string >[] list = new List< string >[3];
}
catch (Exception ex12)
{
    MessageBox.Show(ex12.ToString());
}


Error :- SqlException (0x80131904): Line 28: Incorrect syntax near '('.]incorrect syntax near s inclosed quotation mark after the characte')'
it is giving me only one lakh record it give an error like 0 bracket missing bulk copy like that
Posted
Updated 16-Aug-13 2:25am
v6
Comments
Maciej Los 16-Aug-13 7:03am    
Sorry, but we can't see your screen and we can't read in your mind... ;(
Please, be moe specific and provide more details.
ZurdoDev 16-Aug-13 7:36am    
1. You would have to show your code so we don't have to guess.
2. What are you doing that requires you to process that much through a reader? Perhaps there is a better way.
ZurdoDev 16-Aug-13 8:23am    
OK, so now you have added an error? What does the error have to do with getting only 1000000 records?
You need to fix the syntax error.
CodeBlack 16-Aug-13 8:37am    
can you show me line no 28 ?
[no name] 16-Aug-13 8:48am    
Try adding a space between values and (

1 solution

You're problem is that you're building a SQL queery string using string concatenation. If your data that your building the string with has a "'" character in it, it'll screw up your query string.

Google for "C# SQL parameterized query" for how to fix this and why what you're doing is such a high security risk.

By the way, you don't need the "@" characters in front of your s[] array variables.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900