none
please solve it RRS feed

  • Question

  • private void DisconnectFromDataBase()
    {
    //Makes sure that the connection isn't already disposed.
    if (SQLiteConnection!=null)
    {
    //check to see if the connection is open.
    if(SQLiteConnection.state==connectionstate.open)
    {
    //close the connection.
    SQLiteConnection.close();
    }
    //dispose the connection object.
    SQLiteConnection.dispose();
    //set it to null,so that is easy to find out
    //if it is in null or not.
    SQLiteConnection=null;
    }
    }
    Saturday, April 13, 2019 3:13 AM

Answers

  • It would appear that you created a connection that is scoped to form or class level and want to close and dispose of the connection. If this is the case than I would recommend considering to alter how you are working with database connections.

    The proper way to work with connections is to have a string variable scoped to form or class level if the data operations are done in one form or class then for each data operation that requires a connection, create the connection with a using statement which when out of scope closes and disposes of the connection.

    Here is an example done in a form, same applies if you were to perform data operations in a class. For each separate data operation in a method create a connection and command (or DataAdapter or TableAdapter).

    Old school idea was to have one connection, reuse it as it appears you are but that has changed many years ago. Sure it's a few more lines of code but this ensures (and may not be in your case but it could happen) you always know the state of a connection. Also when keeping a connection alive not only do you need to check if the connection is null and open/close but another state, Broken which is not the case with how I've shown below.

    public partial class Form1 : Form
    {
        private string _connectionString = "Data Source=c:\mydb.db;Version=3;";
        public Form1()
        {
            InitializeComponent();
        }
    
        private DataTable ReadData()
        {
            DataTable dt = new DataTable();
    
            var selectStatement = "SELECT id,FirstName,LastName FROM dbo.Persons ORDER BY LastName";
    
            using (var cn = new SqliteConnection ) { ConnectionString = _connectionString })
            {
                using (var cmd = new SqliteCommand()) { Connection = cn, CommandText = selectStatement })
                {
                    cn.Open();
                    dt.Load(cmd.ExecuteReader());
                }
            }
    
            return dt;
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, April 13, 2019 9:38 AM
    Moderator
  • Unfortunately, Karen's reply has a few typos in it and won't compile (I used SqlConnection instead of SqlLiteConnection, but the compilation errors have nothing to do with those differences). Use either of these two sets of usings; either one will compile fine:

    using (var cn = new SqliteConnection  { ConnectionString = _connectionString })
    {
        using (var cmd = new SqliteCommand { Connection = cn, CommandText = selectStatement })
        {
            cn.Open();
            dt.Load(cmd.ExecuteReader());
        }
    }
    

    -or-

    using (var cn = new SqliteConnection (_connectionString))
    {
        using (var cmd = new SqliteCommand(selectStatement, cn))
        {
            cn.Open();
            dt.Load(cmd.ExecuteReader());
        }
    }


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, April 14, 2019 12:20 AM
    Moderator

All replies

  • please solve it

    That's not clear. What is there to solve?

    The method appears to be correct and it should work, even though the comments are not very good. For instance "set it to null so that is easy to find out if it is in null or not" is not a very good description for why it is set to null. And "//Makes sure that the connection isn't already disposed if (SQLiteConnection!=null)" is also not a good comment: comparing it to null will NOT make sure that it isn't disposed. It could be disposed or not disposed regardless of whether the reference is null or not.

    Saturday, April 13, 2019 9:07 AM
    Moderator
  • It would appear that you created a connection that is scoped to form or class level and want to close and dispose of the connection. If this is the case than I would recommend considering to alter how you are working with database connections.

    The proper way to work with connections is to have a string variable scoped to form or class level if the data operations are done in one form or class then for each data operation that requires a connection, create the connection with a using statement which when out of scope closes and disposes of the connection.

    Here is an example done in a form, same applies if you were to perform data operations in a class. For each separate data operation in a method create a connection and command (or DataAdapter or TableAdapter).

    Old school idea was to have one connection, reuse it as it appears you are but that has changed many years ago. Sure it's a few more lines of code but this ensures (and may not be in your case but it could happen) you always know the state of a connection. Also when keeping a connection alive not only do you need to check if the connection is null and open/close but another state, Broken which is not the case with how I've shown below.

    public partial class Form1 : Form
    {
        private string _connectionString = "Data Source=c:\mydb.db;Version=3;";
        public Form1()
        {
            InitializeComponent();
        }
    
        private DataTable ReadData()
        {
            DataTable dt = new DataTable();
    
            var selectStatement = "SELECT id,FirstName,LastName FROM dbo.Persons ORDER BY LastName";
    
            using (var cn = new SqliteConnection ) { ConnectionString = _connectionString })
            {
                using (var cmd = new SqliteCommand()) { Connection = cn, CommandText = selectStatement })
                {
                    cn.Open();
                    dt.Load(cmd.ExecuteReader());
                }
            }
    
            return dt;
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, April 13, 2019 9:38 AM
    Moderator
  • Unfortunately, Karen's reply has a few typos in it and won't compile (I used SqlConnection instead of SqlLiteConnection, but the compilation errors have nothing to do with those differences). Use either of these two sets of usings; either one will compile fine:

    using (var cn = new SqliteConnection  { ConnectionString = _connectionString })
    {
        using (var cmd = new SqliteCommand { Connection = cn, CommandText = selectStatement })
        {
            cn.Open();
            dt.Load(cmd.ExecuteReader());
        }
    }
    

    -or-

    using (var cn = new SqliteConnection (_connectionString))
    {
        using (var cmd = new SqliteCommand(selectStatement, cn))
        {
            cn.Open();
            dt.Load(cmd.ExecuteReader());
        }
    }


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, April 14, 2019 12:20 AM
    Moderator