How to use DELETE in OSQL

• 3 minutes to read

To delete data using OSQL, we would need to use the Delete class located in the SuperOffice.SQL.Data namespace.

This example shows how we use the DELETE command in OSQL.

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 Delete Class
  Delete newDelete = S.NewDelete();
  newDelete.SetPrimaryKey(newConTable.ContactId);
  newDelete.SetPrimaryKeyValue(S.Parameter(122));
  myComm.SqlCommand = newDelete;
  myComm.ExecuteNonQuery();
  newTrans.Commit();
  myConn.Close();
}

How to create a new connection.

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 delete the data. In this example, we have created an alias of the contacts table using the TablesInfo factory class.

Instantiate class

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

myConn.Close();

Set primary key

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

The SetPrimaryKey method will set the primary key for the DELETE 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 delete). The value should be an Int type, if not it would throw an exception.

The ContactId where it is equal to 122 is deleted in the example as it has been passed to the Delete class with the use of the Parameter method of the S class.

Execute

To execute the DELETE command, we would assign the created Delete 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.