How can i return dataset perfectly from sql?

Question

i try to write a winform application:

i dislike below codes:

 DataTable dt = new DataTable();
                dt.Load(dr);
                ds = new DataSet();
                ds.Tables.Add(dt);

Above part of codes looks unsufficient.How can i best loading dataset?

   public class LoadDataset
    {
        public DataSet GetAllData(string sp)
        {
            return LoadSQL(sp);
        }
        private DataSet LoadSQL(string sp)
        {
            SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
            SqlCommand cmd = new SqlCommand(sp, con);
            DataSet ds;
            try
            {
                con.Open();

                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader dr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                ds = new DataSet();
                ds.Tables.Add(dt);
                return ds;
            }
            finally
            {
                con.Dispose();
                cmd.Dispose();
            }
        }
    }

Answer 1

Here is a simple function I converted from VB to C# (http://www.developerfusion.com/tools/convert/vb-to-csharp/). I use this extensively.

Simple wrapper function to help return a dataset from and SQL statement via an existing connection. This should have performance improvements over re-connected via a connection string each time. Wraps any SQL errors in to a custom format.

public System.Data.DataSet GetDataSet(string sqlStatement, System.Data.SqlClient.SqlConnection connection)
{

System.Data.DataSet functionReturnValue = default(System.Data.DataSet);
if (connection == null) {
    throw new ArgumentNullException("connection");
}

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter();
System.Data.DataSet dset = new System.Data.DataSet();

try {
    //   Connect to the database
    if (connection.State != ConnectionState.Open) {
        connection.Open();
    }

    if (connection.State != ConnectionState.Open) {
        throw new MyCustomException("Connection currently {0} when it should be open.", connection.State));
    }

    //   Create a command connection
    cmd = new System.Data.SqlClient.SqlCommand();
    {
        cmd.Connection = connection;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sqlStatement;
    }
    //.ExecuteReader()        'Forward only Dataset

    //   Create a data adapter to store the inforamtion
    adp = new System.Data.SqlClient.SqlDataAdapter();
    dset = new DataSet();
    {
        adp.SelectCommand = cmd;
        adp.Fill(dset, "Results");
    }

    //   Return the resulting dataset to the calling application

    functionReturnValue = dset;
}
catch (System.Data.SqlClient.SqlException objSE) {
    functionReturnValue = null;
    //   Let the calling function known they stuffed up and give them the SQL to help out.
    throw new JDDataException(System.String.Format("SQL :- {0}.", sqlStatement), objSE);
}
finally {
    if ((cmd != null)) cmd = null; 
    if ((adp != null)) adp = null; 
    if ((dset != null)) dset = null; 
}
return functionReturnValue;

}