How to use UPDATE in OSQL

Some tooltip text!

• 4 minutes to read

 • 4 minutes to read

To update data using OSQL, we would need to use the Update class located in the SuperOffice.Data namespace.

This example shows how we use the UPDATE command in OSQL to modify existing data.

using SuperOffice.CRM.Data;
using SuperOffice.Data;
using SuperOffice.Data.SQL;
using SuperOffice;
using(SoSession mySession = SoSession.Authenticate("SAM", "sam"))
{
  if (mySession == null) return;
  //Using the Insert Statement to Add new Data
  //Establishing a Database Connection
  SoConnection myConn = ConnectionFactory.GetConnection();
  //Creating and SoCommand instance and assigning the earlier created Select statement
  SoCommand myComm = myConn.CreateCommand();
  myConn.Open();
  //Begin Transaction
  SoTransaction newTrans = myConn.BeginTransaction();
  myComm.Transaction = newTrans;
  //Creating DataSets with the Tables of the Database
  ContactTableInfo newConTable = TablesInfo.GetContactTableInfo();
  //Creating an Instance of the Update Class
  Update newUpdate = S.NewUpdate();
  //Retrieve the Contact with ContactID "10" to make the update
  newUpdate.SetPrimaryKey(newConTable.ContactId);
  newUpdate.SetPrimaryKeyValue(S.Parameter(10));
  //Making the necessary update                               
  NewUpdate.FieldValuePairs.Add(newConTable.ActiveInterests, S.Parameter(3));
  newUpdate.FieldValuePairs.Add(newConTable.AssociateId, S.Parameter(5));
  newUpdate.FieldValuePairs.Add(newConTable.BusinessIdx, S.Parameter(7));
  newUpdate.FieldValuePairs.Add(newConTable.CategoryIdx, S.Parameter(4));
  newUpdate.FieldValuePairs.Add(newConTable.CountryId, S.Parameter(4));
  newUpdate.FieldValuePairs.Add(newConTable.Department, S.Parameter("Testing"));
  newUpdate.FieldValuePairs.Add(newConTable.Name, S.Parameter("Tester"));
  newUpdate.FieldValuePairs.Add(newConTable.Number1, S.Parameter("01214578"));
  newUpdate.FieldValuePairs.Add(newConTable.OrgNr, S.Parameter("7845"));
  //Make the Database Update
  myComm.SqlCommand = newUpdate;
  myComm.ExecuteNonQuery();
  //Commit the transaction and close the session
  newTrans.Commit();
  myConn.Close();
}

Setting up the connection is explained here.

Then an instance of SoCommand and SoTransaction is created with CreateCommand() and BeginTransaction().

Next, is to assign the transaction to the created command instance.

Then we create aliases of the tables, which we need to update the data using the TablesInfo factory class.

Instantiate class

Now, we need to create an instance of the Update class. This is similar to that of when creating a Select class.

newUpdate.FieldValuePairs.Add(newConTable.AssociateId, S.Parameter(5));

Set primary key

Next, we need to set the primary key for the UPDATE command:

newUpdate.FieldValuePairs.Add(newConTable.CountryId, S.Parameter(4));

The SetPrimaryKey method will set the primary key for the UPDATE command. However, this will throw an exception if the value is not of FieldInfo type and is a primary key.

Now, with the below statement, we would set the value for the primary key (the row we need to update). The value should be an Int type, if not it would throw an exception.

The ContactId where it is equal to 10 is updated in the example as it has been passed to the Update class with the use of the Parameter method of the S class.

newUpdate.FieldValuePairs.Add(newConTable.Department, S.Parameter("Testing"));

Assign new values to the fields

The next step is to assign the new values for the fields in the contact table. For this, we use the FieldValuePairs property exposed in the Update class.

The following statements show how we may update different data types that are found in the contact table.

  newUpdate.FieldValuePairs.Add(newConTable.OrgNr, S.Parameter("7845"));
  //Make the Database Update
  myComm.SqlCommand = newUpdate;
  myComm.ExecuteNonQuery();
  //Commit the transaction and close the session
  newTrans.Commit();
  myConn.Close();
}

Note

All values retrieved from the FieldValuePairs property are of database data types such as Varchar, Int, and Boolean.

Execute

To execute the UPDATE command, we would assign the created Update class instance to the SQlCommand property of the created instance of the SoCommand. Then, we would use ExecuteNonQuary() to execute the SQL command against the connection object.

Note

If you try to update the primary key of the table, it would produce an exception when the compiler reaches the ExecuteNonQuary method.