SqlDataAdapter Class

SqlDataAdapter Class

SqlDataAdapter Class:- The SqlDataAdapter, serves as a bridge between a DataSet and SQL Server for retrieving and saving data. The SqlDataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet, using the appropriate Transact-SQL statements against the data source. The update is performed on a by-row basis. For every inserted, modified, and deleted row, the Update method determines the type of change that has been performed on it (Insert, Update, or Delete).

SqlDataAdapter is used in conjunction with SqlConnection and SqlCommand to increase performance when connecting to a SQL Server database.

SqlDataAdapter Represents a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database. This class cannot be inherited.


Note :-
If you are using SQL Server stored procedures to edit or delete data using a DataAdapter, make sure that you do not use SET NOCOUNT ON in the stored procedure definition. This causes the rows affected count returned to be zero, which the DataAdapter interprets as a concurrency conflict. In this event, a DBConcurrencyException will be thrown.

The SqlDataAdapter also includes the SelectCommand, InsertCommand, DeleteCommand, UpdateCommand, and TableMappings properties to facilitate the loading and updating of data.

When an instance of SqlDataAdapter is created, the read/write properties are set to initial values. For a list of these values, see the SqlDataAdapter constructor.

Constructor:-

1. SqlDataAdapter():- Initializes a new instance of the SqlDataAdapter class.
2. SqlDataAdapter(SqlCommand):- Initializes a new instance of the SqlDataAdapter class with the specified SqlCommand as the SelectCommand property.
3. SqlDataAdapter(String, SqlConnection):- Initializes a new instance of the SqlDataAdapter class with a SelectCommand and a SqlConnection object.
4. SqlDataAdapter(String, String):- Initializes a new instance of the SqlDataAdapter class with a SelectCommand and a connection string.

When we right click on SqlDataAdapter class and Go To Definition or Press (F12) then following list will be shown as below. In which we can see Constructors, Properties, Events, and Methods of SqlDataAdapter class.

  

namespace System.Data.SqlClient
{
    public sealed class SqlDataAdapter : DbDataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
    { 
        //-----------------Constructors here-----------------
        public SqlDataAdapter();
        public SqlDataAdapter(SqlCommand selectCommand);
        public SqlDataAdapter(string selectCommandText, SqlConnection selectConnection);
        public SqlDataAdapter(string selectCommandText, string selectConnectionString);

      //-------------------properties here------------------
        public SqlCommand DeleteCommand { get; set; }
        public SqlCommand InsertCommand { get; set; }
        public SqlCommand SelectCommand { get; set; }
        public override int UpdateBatchSize { get; set; }
        public SqlCommand UpdateCommand { get; set; }

     //-------------------events here------------------
        public event SqlRowUpdatedEventHandler RowUpdated;
        public event SqlRowUpdatingEventHandler RowUpdating;

     //-------------------methods here------------------
        protected override int AddToBatch(IDbCommand command);
        protected override void ClearBatch();
        protected override RowUpdatedEventArgs CreateRowUpdatedEvent(DataRow dataRow, IDbCommand command, <br />StatementType statementType, DataTableMapping tableMapping);
        protected override RowUpdatingEventArgs CreateRowUpdatingEvent(DataRow dataRow, IDbCommand command, <br />StatementType statementType, DataTableMapping tableMapping);
        protected override int ExecuteBatch();
        protected override IDataParameter GetBatchedParameter(int commandIdentifier, int parameterIndex);
        protected override bool GetBatchedRecordsAffected(int commandIdentifier, out int recordsAffected, <br />out Exception error);
        protected override void InitializeBatching();
        protected override void OnRowUpdated(RowUpdatedEventArgs value);
        protected override void OnRowUpdating(RowUpdatingEventArgs value);
        protected override void TerminateBatching();
    }
}

Now, see here how can we use SqlDataAdapter class in .Net:-

  

  //code for insert or save
        private void btnSave_Click(object sender, EventArgs e)
        {
            if (validate() == false)
            {
                return;
            }
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter;   //----- SqlDataAdapter class object is created.
                using (SqlCommand cmd = new SqlCommand("Select * from tblDemo", con))
                {
                    con.Open();

                    adapter = new SqlDataAdapter(cmd);  //---here passed cmd to SqlDataAdapter class object
                    adapter.Fill(ds, "tblDemo");        //----fill DataSet class object by adapter with Fill method.

                    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
                    DataRow row = ds.Tables["tblDemo"].NewRow();
                    row["Name"] = txtName.Text.Trim();
                    row["Rollno"] = Convert.ToInt32(txtRollno.Text.Trim());
                    row["Branch"] = txtBranch.Text.Trim();
                    ds.Tables["tblDemo"].Rows.Add(row);

                    int Result = adapter.Update(ds, "tblDemo");
                    if (Result > 0)
                    {
                        MessageBox.Show("Record is inserted.");
                    }
                    else
                    {
                        MessageBox.Show("Record is not inserted.");
                    }
                }
            }

            catch (Exception exec)
            {
                MessageBox.Show("Internal problem. Try Again...");
            }
             finally
             {
                 con.Close();
                 Clear();
             }  
            
        }