How to use DELETE in OSQL
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();
}
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 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.