How to dispose of datatables by using the 'Using' statement

Question

I am trying to use using statement for a data table so that it disposes off when out of scope, but inside using scope it says cannot assign datatable to a using variable. Below is the code. Without using the 'Using statement' and by using DataTable dt = null, all works fine.

using (DataTable dt = new DataTble())
{
 
 using (SqlConnection connection = new SqlConnection("DBConection"))
 {
 cmd = new SqlCommand("SPNAME", connection);
 cmd.CommandType = CommandType.StoredProcedure;
 dt = (DataTable)cmd.ExecuteNonquery(); // here it says cannot assign to dt because it is a using variable
 }      
 
 return dt;
}

Yes, `DataTable` does implement `IDisposable` -> `DataTable : MarshalByValueComponent` & `MarshalByValueComponent : IDisposable`. — Enigmativity, Sep 07 '20 at 05:55
You can't return the `DataTable` as it will be disposed before it is returned to the calling code. — Enigmativity, Sep 07 '20 at 05:56
@Enigmativity I stand corrected. IIRC there was a time where the interfaces implemented via inheritance was documented as well, which is why I made that mistake in he first place. — Zohar Peled, Sep 07 '20 at 06:03

score 2 · Answer 1 · answered Sep 07 '20 at 05:49

2

You should remember that within the using block, the object is read-only and cannot be modified or reassigned. https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement

answered Sep 07 '20 at 05:49

Roman Ryzhiy

1,540
8
5

While this does help understand the error that the OP is seeing, it doesn't address the actual question asked. – Enigmativity Sep 07 '20 at 05:58

score 0 · Answer 2 · answered Sep 07 '20 at 06:27

The code to posted to populate a DataTable seems wrong to me as ExecuteNonQuery() returns an int so it can't be cast to a DataTable anyway.

The way I do it is to use a SqlDataAdapter to fill a table.

Here's code that compiles - but don't use it as this is not going to work:

public DataTable UsingDataTable()
{
    using (SqlConnection connection = new SqlConnection("DBConection"))
    {
        using (SqlCommand cmd = new SqlCommand("SPNAME", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    da.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

You can't return a DataTable that you are disposing at the same time it returns. The calling code needs to be able to work with it before it is disposed.

So the way to do that is to inject the code that uses the DataTable into the method so that it can be executed before it returns. Try this:

public void UsingDataTable(Action<DataTable> action)
{
    using (SqlConnection connection = new SqlConnection("DBConection"))
    {
        using (SqlCommand cmd = new SqlCommand("SPNAME", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    a.Fill(dt);
                    action(dt);
                }
            }
        }
    }
}

Now you can call that like this:

UsingDataTable(dt => Console.WriteLine(dt.Rows[0][0].ToString()));

score 0 · Answer 3 · answered Sep 07 '20 at 06:27

0

There is no need for implementing the using statement on datatables,datasets.This is because datatables,datasets suppress finalization in their constructors.There is no real benefit in implementing the using statement on datatables,datasets.

answered Sep 07 '20 at 06:27

PALLAVI JOSHI

66
4

1

Please explain this. What does "suppress finalization in their constructors" have to do with "no need for implementing the using statement"? – Enigmativity Sep 07 '20 at 06:29

score -1 · Answer 4 · answered Sep 07 '20 at 05:51

-1

as comments say, you can only use using statement on a class that implements IDisposible and Datatable doesn't do that. However, you can dispose the DataTable by using its own dispose() method. Just to make it super clear, that also isn't necessary as DataTable does not have any unmanaged resources.

Have a look at this answer for details.

answered Sep 07 '20 at 05:51

Syed Ali Taqi

4,898
3
34
44

Yes, `DataTable` does -> `DataTable : MarshalByValueComponent` & `MarshalByValueComponent : IDisposable`. – Enigmativity Sep 07 '20 at 05:54

How to dispose of datatables by using the 'Using' statement

4 Answers4