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

public static DataSet GetDs(string sql)
{
  try
  {
    string ConnectionString = ConfigurationManager.AppSettings["SqlConnectionString"];

    using (SqlConnection cn = new SqlConnection(ConnectionString))
    {
      using (SqlCommand cmd = new SqlCommand(sql, cn))
      {
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
          DataSet ds = new DataSet();
          da.Fill(ds);
          return ds;
        }
      }
    }
  }
  catch (Exception ex)
  {
    Console.WriteLine("GetDs Error");
    Console.WriteLine("sql = " + sql);
    Console.WriteLine("Exception caught: " + ex.ToString());
    //SendErrorMessage("Error : " + ex.ToString());

    return null;
  }
}

Caution: Beware of SQL Injection attacks. Make sure the sql statement being passed in is safe.

Answer 2

   public string GetSqlConnection()
    {
        return  System.Configuration.ConfigurationManager.AppSettings["SqlConnectionString"];
    }


   public DataSet getDataSet(string sql)
    {
        DataSet ds = new DataSet();
        SqlConnection conn = new SqlConnection(GetSqlConnection());
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        da.Fill(ds);
        conn.Close();
        conn.Dispose();
        da.Dispose();
        return ds;
    }

Answer 3

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;

}