Create an appointment through OSQL

Some tooltip text!

• 2 minutes to read

 • 2 minutes to read

When using OSQL, we would first have to import the necessary namespaces:

• SuperOffice.CRM.Data
• SuperOffice.Data
• SuperOffice.Data.SQL

The syntax of this code is similar to that of SQL with the difference being in the keywords.

The following example shows how we could create an appointment using OSQL.

Code

using SuperOffice.CRM.Data;
using SuperOffice.Data;
using SuperOffice.Data.SQL;
using SuperOffice;
using(SoSession mySession = SoSession.Authenticate("sam", "sam"))
{
  //Creating DataSets with the Tables of the Database
  AppointmentTableInfo newAppTab = TablesInfo.GetAppointmentTableInfo();

  //Creating an instance of the Insert class
  Insert newInsert = S.NewInsert();

  //Inserting the necessary fields of the Table
  newInsert.FieldValuePairs.Add(newAppTab.AppointmentId, S.Parameter(Sequence.GetNext(newAppTab)));
  newInsert.FieldValuePairs.Add(newAppTab.AssociateId, S.Parameter(103));
  newInsert.FieldValuePairs.Add(newAppTab.ContactId, S.Parameter(20));
  newInsert.FieldValuePairs.Add(newAppTab.PersonId, S.Parameter(10));
  newInsert.FieldValuePairs.Add(newAppTab.GroupIdx, S.Parameter(1));
  newInsert.FieldValuePairs.Add(newAppTab.DoBy, S.Parameter(new DateTime(2007, 4, 20)));
  newInsert.FieldValuePairs.Add(newAppTab.Status, S.Parameter(SuperOffice.Data.AppointmentStatus.NotStarted));
  newInsert.FieldValuePairs.Add(newAppTab.Done, S.Parameter(new DateTime(2007, 4, 20)));
  newInsert.FieldValuePairs.Add(newAppTab.TaskIdx, S.Parameter(10));
  newInsert.FieldValuePairs.Add(newAppTab.Updated, S.Parameter(new DateTime(2007, 4, 20)));
  newInsert.FieldValuePairs.Add(newAppTab.UpdatedAssociateId, S.Parameter(103));
  newInsert.FieldValuePairs.Add(newAppTab.UpdatedCount, S.Parameter(1));

  //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;

  //Executing the Insert Statement
  myComm.SqlCommand = newInsert;
  myComm.ExecuteNonQuery();

  //Committing the transaction and closing the session
  newTrans.Commit();
  myConn.Close();
}

Walk-through

Since we are creating an appointment, we would need to create an instance of the AppointmentTableInfo with the use of the TablesInfo class's GetAppointmentTableInfo():

AppointmentTableInfo newAppTab = TablesInfo.GetAppointmentTableInfo();

Next, we create an instance of the Insert class that can be used to update the appointment table. After the instance has been created, we would be able to use the instance to update the fields of the appointment table.

Insert newInsert = S.NewInsert();
newInsert.FieldValuePairs.Add(newAppTab.AppointmentId, S.Parameter(Sequence.GetNext(newAppTab)));
newInsert.FieldValuePairs.Add(newAppTab.AssociateId, S.Parameter(103));

Once the required fields have been added, a database connection will be made with the use of the ConnectionFactory and the values will be inserted into the database with the ExecuteNonQuery method.

Note

In OSQL, there is no method that provides default values. It is necessary to insert values to all mandatory columns. If not a runtime exception will occur.