SqlHelper DataAdapter

Question

I am using a SqlHelper class which has common methods for CRUD operations.

  public static void Fill(DataSet dataSet, String procedureName)
    {
        SqlConnection oConnection = new SqlConnection(DBInterface.ConnectionString);
        SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
        oCommand.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter oAdapter = new SqlDataAdapter();

        oAdapter.SelectCommand = oCommand;
        oConnection.Open();
        using (SqlTransaction oTransaction = oConnection.BeginTransaction())
        {
            try
            {
                oAdapter.SelectCommand.Transaction = oTransaction;
                oAdapter.Fill(dataSet);
                oTransaction.Commit();
            }
            catch
            {
                oTransaction.Rollback();
                throw;
            }
            finally
            {
                if (oConnection.State == ConnectionState.Open)
                    oConnection.Close();
                oConnection.Dispose();
                oAdapter.Dispose();
            }
        }
    }

Now in my code, I am calling this method as,

 private void BindCustomers()
        {
            DataSet dsCust = new DataSet();           
            SqlHelper.Fill(dsCust, "getCustomers");
            --then I bind this dataset to datagridview
        }

This all works fine. Now I want to update the data in the database. But I am confused how do I call DataAdatpaer.Update(dataset) here to update the changes made in datagridview into database. Is this possible here? Or I need to do it conventionally to find the updated row and call the ExecuteNonQuery function in the SqlHelper? Is there anything which can be done to use dataadapter.update(ds) Thanks

Answer 1

By calling this method in your code you can perform all crud operation select, update, delete and insert. you just need to pass connection string, procedure name and parameters list. Using this method you will retrieve data in the DataTable.if anyone want Dataset(Multiple Result) then just need to replace DataSet on the place of DataTable

SqlConnection conn = new SqlConnection("Your ConnectionString");
   
public DataTable ExecuteDataTable(string ProcedureName, SqlParameter[] _Param)
{
    try
    {               
        DataTable dataTable = new DataTable();
        SqlCommand cmd = new SqlCommand(ProcedureName, conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Clear();
        if (_Param is not null)
        {
            for (int i = 0; i < _Param.Length; i++)
            {
                if (_Param[i].ParameterName is not null)
                {
                    if (_Param[i].Value is not null)
                    {
                        cmd.Parameters.AddWithValue(_Param[i].ParameterName, _Param[i].Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue(_Param[i].ParameterName, DBNull.Value);
                    }
                }
            }
        }                
        conn.Open();
        SqlDataAdapter DA = new SqlDataAdapter(cmd);
        DA.Fill(dataTable);
        conn.Close();
        return dataTable;
    }
    catch (Exception ex)
    {
        conn.Close();
        throw;

    }
}

Answer 2

You don't need to hide data adapter, or if for any reason you did so, you need to expose a method in your class to push updates to server.

Example

Public class SqlHelper
{
    string commandText;
    string connectionString;
    public SqlHelper(string command, string connection)
    {
        commandText = command;
        connectionString = connection;
    }
    public DataTable Select()
    {
        var table = new DataTable();
        using (var adapter = new SqlDataAdapter(this.commandText, this.connectionString))
            adapter.Fill(table)
        return table;
    }
    public void Update(DataTable table)
    {
        using (var adapter = new SqlDataAdapter(this.commandText, this.connectionString))
        {
            var builder = new SqlCommandBuilder(adapter);
            adapter.Update(table);
        }
    }
}