[Close]Memory leak if not implement IDisposable?

[Close]Memory leak if not implement IDisposable?

Postby waertf » Sat 07 Jun 2014 02:04

I create the sql class , it has connect/disconnect/modify/receive function to access DB.
The class didn't implement IDisposable but use dispose function
Code: Select all
public void Dispose()
      {
            PgSqlConnection.ClearAllPools(true) ;
            pgSqlConnection.Dispose();
            pgSqlConnection = null;
      }

I figure out that the memory increase if other class use the object create by sql class.
below is the source code
Code: Select all
class SqlClient
    {
       
        PgSqlConnection pgSqlConnection;
        public bool IsConnected{get;set;}
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public SqlClient(string ip, string port, string user_id, string password, string database, string Pooling, string MinPoolSize, string MaxPoolSize, string ConnectionLifetime)
        {
            PgSqlConnectionStringBuilder pgCSB = new PgSqlConnectionStringBuilder();
            pgCSB.Host = ip;
            pgCSB.Port = int.Parse(port);
            pgCSB.UserId = user_id;
            pgCSB.Password = password;
            pgCSB.Database = database;

            pgCSB.Pooling = bool.Parse(Pooling);
            pgCSB.MinPoolSize = int.Parse(MinPoolSize);
            pgCSB.MaxPoolSize = int.Parse(MaxPoolSize);
            pgCSB.ConnectionLifetime = int.Parse(ConnectionLifetime); ;
           
            pgCSB.Unicode = true;
            pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString);
        }
        public bool connect()
        {
            try
            {
                if (pgSqlConnection != null)
                {
                    pgSqlConnection.Open();
                    IsConnected = true;
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (PgSqlException ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Connect exception occurs: {0}", ex.Error);
                log.Error("Connect exception occurs: "+ ex.Error);
                Console.ResetColor();
                return false;
            }
        }
        public bool disconnect()
        {
            try
            {
                if (pgSqlConnection != null)
                {
                    pgSqlConnection.Close();
                    IsConnected = false;
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (PgSqlException ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Disconnect exception occurs: {0}", ex.Error);
                log.Error("Disconnect exception occurs: "+ ex.Error);
                Console.ResetColor();
                return false;
            }
        }
        //For UPDATE, INSERT, and DELETE statements
        public bool modify(string cmd)
        {
            PgSqlCommand command = null;
            try
            {
                if (pgSqlConnection != null && IsConnected)
                {
                    //insert
                    command = pgSqlConnection.CreateCommand();
                    command.CommandText = cmd;
                    //cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
                    pgSqlConnection.BeginTransaction();
                    //async
                    IAsyncResult cres = command.BeginExecuteNonQuery(null, null);
                    //Console.Write("In progress...");
                    while (!cres.IsCompleted)
                    {
                        //Console.Write(".");
                        //Perform here any operation you need
                    }
                    /*
                    if (cres.IsCompleted)
                        Console.WriteLine("Completed.");
                    else
                        Console.WriteLine("Have to wait for operation to complete...");
                    */
                    int RowsAffected = command.EndExecuteNonQuery(cres);
                    //Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
                    /*
                     //sync
                     int aff = cmd.ExecuteNonQuery();
                     Console.WriteLine(aff + " rows were affected.");
                     *
                     */
                    pgSqlConnection.Commit();
                    ThreadPool.QueueUserWorkItem(callback =>
                    {
                        Console.ForegroundColor = ConsoleColor.Cyan;
                        Console.WriteLine(
                            "S++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
                        Console.WriteLine("sql Write:\r\n" + cmd);
                        Console.WriteLine(
                            "E++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
                        Console.ResetColor();
                        log.Info("sql Write:\r\n" + cmd);
                    });
                    if (command != null)
                        command.Dispose();
                    command = null;
                    return true;
                }
                else
                {
                    return false;
                }
                   
            }
            catch (PgSqlException ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Modify exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
                log.Error("Modify exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd);
                Console.ResetColor();
                pgSqlConnection.Rollback();
                if (command != null)
                    command.Dispose();
                command = null;
                return false;
            }

        }
        //For SELECT statements
        public DataTable get_DataTable(string cmd)
        {
            Stopwatch stopWatch = new Stopwatch();
            PgSqlCommand command = null;
            stopWatch.Start();
            try
            {
                if (pgSqlConnection != null && IsConnected)
                {
                    DataTable datatable = new DataTable();
                    command = pgSqlConnection.CreateCommand();
                    command.CommandText = cmd;
                    //Console.WriteLine("Starting asynchronous retrieval of data...");
                    IAsyncResult cres = command.BeginExecuteReader();
                    //Console.Write("In progress...");
                    while (!cres.IsCompleted)
                    {
                        //Console.Write(".");
                        //Perform here any operation you need
                    }

                    //if (cres.IsCompleted)
                    //Console.WriteLine("Completed.");
                    //else
                    //Console.WriteLine("Have to wait for operation to complete...");
                    PgSqlDataReader myReader = command.EndExecuteReader(cres);
                    try
                    {
                        // printing the column names
                        for (int i = 0; i < myReader.FieldCount; i++)
                        {
                            //Console.Write(myReader.GetName(i).ToString() + "\t");
                            datatable.Columns.Add(myReader.GetName(i).ToString(), typeof (string));
                        }
                        //Console.Write(Environment.NewLine);
                        while (myReader.Read())
                        {
                            DataRow dr = datatable.NewRow();

                            for (int i = 0; i < myReader.FieldCount; i++)
                            {
                                //Console.Write(myReader.GetString(i) + "\t");
                                dr[i] = myReader.GetString(i);
                            }
                            datatable.Rows.Add(dr);
                            //Console.Write(Environment.NewLine);
                            //Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
                        }
                    }
                    finally
                    {
                        myReader.Close();
                        stopWatch.Stop();
                        // Get the elapsed time as a TimeSpan value.
                        TimeSpan ts = stopWatch.Elapsed;

                        // Format and display the TimeSpan value.
                        string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                            ts.Hours, ts.Minutes, ts.Seconds,
                            ts.Milliseconds/10);
                        SiAuto.Main.AddCheckpoint(Level.Debug, "sql query take time:" + elapsedTime, cmd);
                    }
                    /*
                    foreach (DataRow row in datatable.Rows) // Loop over the rows.
                    {
                        Console.WriteLine("--- Row ---"); // Print separator.
                        foreach (var item in row.ItemArray) // Loop over the items.
                        {
                            Console.Write("Item: "); // Print label.
                            Console.WriteLine(item); // Invokes ToString abstract method.
                        }
                    }
                    */
                    if (command!=null)
                        command.Dispose();
                    command = null;
                    return datatable;
                }
                else
                {
                   
                    return null;
                }
                   
            }
            catch (PgSqlException ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("GetDataTable exception occurs: {0}"+Environment.NewLine+"{1}", ex.Error,cmd);
                log.Error("GetDataTable exception occurs: " + Environment.NewLine + ex.Error+Environment.NewLine+ cmd);
                Console.ResetColor();
                if (command != null)
                    command.Dispose();
                command = null;
                return null;
            }
        }
      public void Dispose()
      {
            PgSqlConnection.ClearAllPools(true) ;
            pgSqlConnection.Dispose();
            pgSqlConnection = null;
      }
        //~SqlClient()
        //{
            //PgSqlConnection.ClearPool(pgSqlConnection);
            //pgSqlConnection.Dispose();
            //pgSqlConnection = null;
        //}   
    }
Last edited by waertf on Thu 19 Jun 2014 02:31, edited 2 times in total.
waertf
 
Posts: 9
Joined: Sat 07 Jun 2014 01:52

Re: [find root cause]Memory leak if not implement IDisposable?

Postby waertf » Tue 10 Jun 2014 03:14

set "ConnectionLifetime other than zero"
I use the class to create object in multi-thread and use timer for fast process.
If I set the ConnectionLifetime to zero , even call clearallpools function it something like the connection remain connect I guess.
waertf
 
Posts: 9
Joined: Sat 07 Jun 2014 01:52

Re: [root cause found]Memory leak if not implement IDisposable?

Postby Pinturiccio » Tue 10 Jun 2014 15:32

We need more information for reproducing the issue. Please create and send us a small test project which uses SqlClient and reproduces the memory leak. Please also describe the steps for reproducing the issue. If the project requires any DDL/DML scripts, please send them too.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: [root cause found]Memory leak if not implement IDisposable?

Postby waertf » Sun 15 Jun 2014 11:07

because of the project is too large and complicated , will research if send test project.
waertf
 
Posts: 9
Joined: Sat 07 Jun 2014 01:52

Re: [root cause found]Memory leak if not implement IDisposable?

Postby Pinturiccio » Mon 16 Jun 2014 15:22

If we understood you correctly, the example in this post http://forums.devart.com/viewtopic.php?t=29795 is an example of using the SqlClient class. However, we are unable to reproduce the memory leak issue with it. Tell us which changes to code must be made for reproducing the memory leak.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: [root cause found]Memory leak if not implement IDisposable?

Postby waertf » Thu 19 Jun 2014 02:31

After review my code , found that this is my mistake for the software architect(using thread.sleep in loop cause bad performance than increase create threads number over the threads end).Thank you for your effort.
waertf
 
Posts: 9
Joined: Sat 07 Jun 2014 01:52

Re: [Close]Memory leak if not implement IDisposable?

Postby Pinturiccio » Fri 20 Jun 2014 13:26

We are glad to hear that the issue is solved. If you have any questions, feel free to contact us.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL