Which is more optimised code when using Dataset with or without Using() for Dispose

Question

I use lot of Dataset for my website so far i have been using Dataset as

    string strSql = "SELECT * FROM Articles";
    DataSet ds = new DataSet();
    ds = DataProvider.Connect_Select(strSql);
    string Title = ds.Tables[0].Rows[0]["Article_Title"].ToString();
    string Desc = ds.Tables[0].Rows[0]["Article_Desc"].ToString();

CODE with Using block

string strSql = "SELECT * FROM Articles";
    // Create a DataSet in using statement.
using (DataSet ds = new DataSet())
{
    ds = DataProvider.Connect_Select(strSql);
    string Title = ds.Tables[0].Rows[0]["Article_Title"].ToString();
    string Desc = ds.Tables[0].Rows[0]["Article_Desc"].ToString();
}

What is the best approach and optimized approach for using DataSet

Above code i am using SQL Statment otherwise i use Stored Procedures

Both are same. In first statement you need to include reference of System.Data while another will work without it. — Dev, Dec 18 '12 at 05:34
"Using" is to ensure dispose after out of scope. But DataSet is managed in memory database and no necessary to dispose yourself. Dot Net will take care of disposing your object. Note: if this code is in function, you cann't return your dataset. — Min Min, Dec 18 '12 at 05:42

Habib · Answer 1 · 2012-12-18T05:41:22.900

2

using only ensures to call the Dispose method on the DataSet, even if the exception occurs. Not sure about how much it is optimized but it is a safer approach and better practice for objects implementing IDisposable interface. using statement is like try/finally block.

Its like:

DataSet ds;
try
{
ds = new DataSet();
ds = DataProvider.Connect_Select(strSql);
string Title = ds.Tables[0].Rows[0]["Article_Title"].ToString();
string Desc = ds.Tables[0].Rows[0]["Article_Desc"].ToString();
}
finally 
{
if(ds != null)
     ds.Dispose();
}

edited Dec 18 '12 at 05:41

answered Dec 18 '12 at 05:34

Habib

219,104
29
407
436

What will happen if i dont use `ds.Dispose();` is it save if i have to use it Doesnt .Net take care of Disposing Dataset. – Learning Dec 18 '12 at 05:37
@KnowledgeSeeker, If the object has implemented IDisposable, then its always better to call Dispose and not let the garbage collector cleans it, here is a very good discussion about it. http://stackoverflow.com/questions/2926869/do-you-need-to-dispose-of-objects-and-set-them-to-null – Habib Dec 18 '12 at 05:40

score 1 · Answer 2 · answered Dec 22 '12 at 08:47

I would suggest that you use dapper. DataSet is quite inefficient. Download Dapper, from Nuget or dapper.org

Creating a DTO (Data Transfer Object)

public class ArticleDto
{
    public int ArticleID {get; set;}
    public string Title {get; set;}
    public string Description {get; set;}
}

Then you create in your data tier an base class that manages the connection to the database

public abstract class SalesDb : IDisposable
{
    protected static IDbConnection OpenConnection()
    {
        IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NAME"].ConnectionString);
        connection.Open();
        return connection;
    }
}

Then, create your service class that return data from the database

public class ArticleService : SalesDb
{
    public IEnumerable<ArticleDto> SelectAll()
    {
        using (IDbConnection connection = OpenConnection())
        {
            var articles = connection.Query<ArticleDto>("SELECT * FROM Articles");

            return articles;
        }
    }
}

Which is more optimised code when using Dataset with or without Using() for Dispose

2 Answers2