How to get data from sql stored procedure in a dataset using SqlDataAdapter?

Question

Is this a good approach to getting data from a stored procedure? For example, the procedure creates select * from base . Here is my code, but I need help with the dataset and adapter:

 public static DataSet Osvezi(string naziv_tablice) { SqlCommand cmd = null; DataSet dataset = null; SqlConnection konekcija = new SqlConnection(ConfigurationManager.AppSettings["skripta"]); if (konekcija != null) { try { if (konekcija.State == ConnectionState.Closed) konekcija.Open(); cmd = new SqlCommand(); cmd.Connection = konekcija; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Osvezi"; cmd.Parameters.Add(new SqlParameter("@tablica", SqlDbType.Int)).Value = naziv_tablice; cmd.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(cmd); // Fill the DataSet using default values for DataTable names, etc da.Fill(dataset); return dataset; } catch (Exception ee) { //Obravnava napak } finally { konekcija.Close(); konekcija.Dispose(); cmd.Dispose(); } return dataset; } return dataset; } 

Answer 1

Try instead:

 public static DataSet Osvezi(string naziv_tablice) { try { using (SqlConnection konekcija = new SqlConnection(ConfigurationManager.AppSettings["skripta"])) { konekcija.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = konekcija; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Osvezi"; cmd.Parameters.AddWithValue("@tablica", naziv_tablice??DBNull.Value); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { // Fill the DataSet using default values for DataTable names, etc DataSet dataset = new DataSet(); da.Fill(dataset); return dataset; } } } } catch (Exception ee) { //Obravnava napak } return null; } 

Answer 2

Please correct the following.

You do not need to open the connection.
There should be no team. ExecuteNonQuery.
The parameter in the method is a string, but the SqlParameter data type is SqlDbType.Int.