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

Question

Is this good approach to get data from stored procedure? For example procedure is making select * from base. Here is my code but I need help with 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;

}

Solution 1

Try this one 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;
}

Solution 2

Please correct the following.

You don't need to open the connection.
There shouldn't be any command.ExecuteNonQuery.
The parameter in the method is string but the datatype of SqlParameter is SqlDbType.Int.

Comments

[Oleg Dok]

Corrected tre param type following the SP script

[Admin]

It's working I used this code to retrieve data if somone needs maybe : ` foreach (DataRow dr in dataset.Tables[0].Rows) { string myVar = dr["ime_uporabnika"].ToString(); } ` thank you!!

[NickG]

You forgot to close the connection, which is extremely important.

[NickG]

Well it's not in the code sample you posted? Or are you relying on the "using" statement doing it when it garbage collects?

[Oleg Dok]

@NickG On normal workflow (including unhandled exceptions) "using" forces connection to close or move to connections pool. On abnormal flow, causing the termination of the process - the connections are closed too. "using" has no any connection to the garbage collection, it is a "disposable" pattern, nothing more