CODEWITHSUNDEEP
c#mysqlodbcblob
user1217950
user1217950

Reputation: 13

Insertion of data in mysql with C#

I am using Visual Studio 2010 c# to insert huge amount of binary data in mysql database, but when inserting I am getting the following error:

Exception is+ The connection has been disabled.StackTrace= at System.Data.Odbc.OdbcConnection.ConnectionIsAlive(Exception innerException) at System.Data.Odbc.OdbcConnection.HandleErrorNoThrow(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteNonQuery() at UploadFile.btnFileUpload_Click1(Object sender, EventArgs e) in c:\inetpub\wwwroot\computingClient\UploadFile.aspx.cs:line 133

Mysql datatype is LONGBLOB Here is my code:

string connStr = ConfigurationManager.AppSettings["connectionString"];
OdbcConnection conn = new OdbcConnection(connStr);
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
string sql = "insert into uploadeddata values(?,?,?,?,?)";
//cmd.CommandText = "insert into uploadeddata values(0 ,'" + fileName + "'," + id +",'"+strTokens +"')";
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@id", 0);
cmd.Parameters.AddWithValue("@filename", fileName);
cmd.Parameters.AddWithValue("@ServerKey", id);
cmd.Parameters.AddWithValue("@Token", strTokens);
cmd.Parameters.AddWithValue("@userId", Session["LoggedInUserId"]);
//Logger.writeLog(@"D:\Cloud Computing\CloudClient\Log\Log.txt","id:"+id+",filename:"+fileName+",userid:"+Session["LoggedInUserId"]+",strtokens:"+strTokens);
cmd.ExecuteNonQuery();
conn.Close();
progressCounter = 100;
//progress.SecondaryValue = progressCounter;
//progress.SecondaryPercent = progressCounter;
string script = "<script>alert('File uploaded successfully');</script>";
ClientScript.RegisterClientScriptBlock(this.GetType(), script, script);

Upvotes: 0

Views: 1690

Answers (1)

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

Since you are trying to insert huge amount of data, you should probably increase max_allowed_packet in your MySQL configuration file (and restart MySQL, afterwards). This value is usually by default small, to prevent invalid packets sent to mysql, but you can increase it if you expect huge inserts like you do, but note that protocol limit is 1GB (so, no larger packet than that). Here you can find more info.

Also, as @Lloyd pointed out, you should use .NET Connector instead of odbc driver. In that case, your code should look like this:

using(var connection = new MySqlConnection(ConfigurationManager.AppSettings["connectionString"]))
{
    connection.Open();
    using(var command = connection.CreateCommand())
    {
        command.CommandText = "INSERT INTO uploadeddata VALUES (@id, @filename, @serverKey, @token, @userId)";
        command.Parameters.Add(new MySqlParameter("id", 0));
        command.Parameters.Add(new MySqlParameter("filename", fileName));
        command.Parameters.Add(new MySqlParameter("serverKey", id));
        command.Parameters.Add(new MySqlParameter("token", strTokens));
        command.Parameters.Add(new MySqlParameter("userId", Session["LoggedInUserId"]));

        command.ExecuteNonQuery();
    }
}

Upvotes: 2

Related Questions