Inserting Rows into a Table Using the AseDataAdapter

Insert rows using the AseDataAdapter object.

1. Declare and initialize an AseConnection object:

   For C#:

   AseConnection conn = new AseConnection(c_connStr);

   For Visual Basic .NET:

   Dim conn As New AseConnection( _
      c_connStr )

2. Open the connection:

   For C#:

   conn.Open();

   For Visual Basic .NET:

   conn.Open()

3. Create a new AseDataAdapter object:

   For C#:

   AseDataAdapter adapter = new AseDataAdapter(); 
   adapter.MissingMappingAction =        MissingMappingAction.Passthrough; 
   adapter.MissingSchemaAction =        MissingSchemaAction.Add;

   For Visual Basic .NET:

   Dim adapter As New AseDataAdapter()
   adapter.MissingMappingAction = _
      MissingMappingAction.Passthrough
   adapter.MissingSchemaAction = _
      MissingSchemaAction.Add

4. Create the necessary AseCommand objects and define any necessary parameters:

   The following code creates a Select and an Insert command and defines the parameters for the Insert command:

   For C#:

   adapter.SelectCommand = new AseCommand(
      "SELECT * FROM publishers", conn );
   adapter.InsertCommand = new AseCommand(
      "INSERT INTO publishers( pub_id, pub_name, city, state) " +
      "VALUES( @pub_id, @pub_name, @city, @state  )", conn);
   adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
   AseParameter parm = new AseParameter("@pub_id", AseDbType.Char, 4);
   parm.SourceColumn = "pub_id";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parm );
   parm = new AseParameter("@pub_name", AseDbType.VarChar, 40);
   parm.SourceColumn = "pub_name";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parm );
   parm = new AseParameter("@city", AseDbType.VarChar, 20);
   parm.SourceColumn = "city";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parm );
   parm = new AseParameter("@state", AseDbType.Char, 2);
   parm.SourceColumn = "state";
   parm.SourceVersion = DataRowVersion.Current;
   adapter.InsertCommand.Parameters.Add( parm );

   For Visual Basic .NET:

   adapter.SelectCommand = New AseCommand( _
      "SELECT * FROM publishers", conn )
   adapter.InsertCommand = New AseCommand( _
      "INSERT INTO publishers( pub_id, pub_name,  city, state) " + _
      " VALUES( @pub_id, @pub_name, @city, @state  )", conn)
   adapter.InsertCommand.UpdatedRowSource = _
      UpdateRowSource.None
   Dim parm As New AseParameter("@pub_id", AseDbType.Char, 4)
   parm.SourceColumn = "pub_id"
   parm.SourceVersion = DataRowVersion.Current
   adapter.InsertCommand.Parameters.Add( parm )
   parm = New AseParameter("@pub_name", AseDbType.VarChar, 40)
   parm.SourceColumn = "pub_name"
   parm.SourceVersion = DataRowVersion.Current
   adapter.InsertCommand.Parameters.Add( parm )
   parm = New AseParameter("@city", AseDbType.VarChar, 20)
   parm.SourceColumn = "city"
   parm.SourceVersion = DataRowVersion.Current
   adapter.InsertCommand.Parameters.Add( parm )
   parm = New AseParameter("@state", AseDbType.Char, 2)
   parm.SourceColumn = "state"
   parm.SourceVersion = DataRowVersion.Current
   adapter.InsertCommand.Parameters.Add( parm )

5. Fill the DataTable with the results of the Select statement:

   For C#:

   DataTable dataTable = new DataTable( "publishers" );
   int rowCount = adapter.Fill( dataTable );

   For Visual Basic .NET:

   Dim dataTable As New DataTable( "publishers" )
   Dim rowCount As Integer = adapter.Fill( dataTable )

6. Insert the new rows into the DataTable and apply the changes to the database:

   For C#:

   DataRow row1 = dataTable.NewRow();
   row1[0] = "9901";
   row1[1] = "New Publisher";
   row1[2] = "Concord";
   row1[3] = "MA";
   dataTable.Rows.Add( row1 );
   DataRow row2 = dataTable.NewRow();
   row2[0] = "9902";
   row2[1] = "My Publisher";
   row2[2] = "Dublin";
   row2[3] = "CA";
   dataTable.Rows.Add( row2 );
   int recordsAffected = adapter.Update( dataTable );

   For Visual Basic .NET:

   Dim row1 As DataRow = dataTable.NewRow()
   row1(0) = "9901"
   row1(1) = "New Publisher"
   row1(2) = "Concord"
   row1(3) = "MA"
   dataTable.Rows.Add( row1 )
   Dim row2 As DataRow = dataTable.NewRow()
   row2(0) = "9902"
   row2(1) = "My Publisher"
   row2(2) = "Dublin"
   row2(3) = "CA"
   dataTable.Rows.Add( row2 )
   Dim recordsAffected As Integer =_
      adapter.Update( dataTable )

7. Display the results of the updates:

   For C#:

   dataTable.Clear();
   rowCount = adapter.Fill( dataTable );
   dataGrid.DataSource = dataTable;

   For Visual Basic .NET:

   dataTable.Clear()
   rowCount = adapter.Fill( dataTable )
   dataGrid.DataSource = dataTable

8. Close the connection:

   For C#:

   conn.Close();

   For Visual Basic .NET:

   conn.Close()