How to use INSERT in OSQL

Some tooltip text!

• 8 minutes to read

 • 8 minutes to read

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

The examples demonstrate how we may insert data using the INSERT statement.

Note

When inserting data into any table (adding a new row), all the mandatory columns in the database should be filled. Otherwise, a runtime exception will be thrown.

Example 1

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 in 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();
  //Creating DataSets with the Tables of the Database
  RedLetterDayTableInfo newRedLetDatTab = TablesInfoGetRedLetterDayTableInfo();
  //Setting the Required Date
  DateTime baseTime = new DateTime(2007, 2, 4);
  //Creating an instance of the Insert class
  Insert newInsert = S.NewInsert();
  //Inserting the necessary fields of the Table
  newInsert.FieldValuePairs.Add(newRedLetDatTab.RedletterdayId, S.Parameter(Sequence.GetNext(newRedLetDatTab)));
  newInsert.FieldValuePairs.Add(newRedLetDatTab.Reddate, S.Parameter(baseTime));
  newInsert.FieldValuePairs.Add(newRedLetDatTab.CountryId, S.Parameter(1));
  newInsert.FieldValuePairs.Add(newRedLetDatTab.Reds, S.Parameter(0));
  newInsert.FieldValuePairs.Add(newRedLetDatTab.Colored, SParameter(0));
  newInsert.FieldValuePairs.Add(newRedLetDatTab.Color, S.Parameter(0));
  newInsert.FieldValuePairs.Add(newRedLetDatTab.Text, S.Parameter("Testers day"));
  newInsert.FieldValuePairs.Add(newRedLetDatTab.UpdatedCount, S.Parameter(0));
  //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();
}

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.

Insert newInsert = S.NewInsert();

Instantiate class

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

newInsert.FieldValuePairs.Add(newRedLetDatTab.Colored, SParameter(0));

Assign values to the fields

To insert values the FieldValuePairs property of the Insert class is used as below.

newInsert.FieldValuePairs.Add(newRedLetDatTab.UpdatedCount, S.Parameter(0));
//Begin Transaction

Begin transaction

After all the data has been inserted, the next step is to begin a transaction so that the database table will be updated without any inconsistency. For that, an instance of the SoTransaction class is created and the transaction is started with the use of the BeginTransaction() method found in the created instance of the SoConnection.

The created SoTransaction instance is then assigned to the Transaction property of the created instance of the SoCommand.

To execute the INSERT 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.

  myConn.Close();
}

Finally, to complete the above transaction, the Commit method is used. It it is found in the created instance of the SoTransaction.

The last statement is to close the established SoConnection with the use of the Close method.

Example 2

This example shows how we may add a new contact to the contact table.

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
  ContactTableInfo newConTab = TablesInfo.GetContactTableInfo();
  //Creating an instance of the Insert class
  Insert newInsert = S.NewInsert();
  //Inserting the necessary fields of the Table
  newInsert.FieldValuePairs.Add(newConTab.ContactId, S.Parameter(Sequence.GetNext(newConTab)));
  newInsert.FieldValuePairs.Add(newConTab.Name, S.Parameter("Will Hammond"));
  newInsert.FieldValuePairs.Add(newConTab.UpdatedCount,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.UpdatedAssociateId, S.Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.Updated, S. Parameter(DateTime.Now));
  newInsert.FieldValuePairs.Add(newConTab.BusinessIdx,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.CategoryIdx,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.CountryId, S. Parameter(50));
  newInsert.FieldValuePairs.Add(newConTab.Department, S. Parameter("Parks dept"));
  newInsert.FieldValuePairs.Add(newConTab.OrgNr, S. Parameter("2547869"));
  newInsert.FieldValuePairs.Add(newConTab.Registered, S. Parameter(DateTime.Now));
  newInsert.FieldValuePairs.Add(newConTab.RegisteredAssociateId, S.Parameter(103));
  //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 clossing the session
  newTrans.Commit();
  myConn.Close();
}

To insert data, we are required to create an instance of the Insert class and then use the fieldValuePairs property of it. The data has been added using the Add() method exposed in the FieldValuePairs property:

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
  ContactTableInfo newConTab = TablesInfo.GetContactTableInfo();
  //Creating an instance of the Insert class
  Insert newInsert = S.NewInsert();
  //Inserting the necessary fields of the Table
  newInsert.FieldValuePairs.Add(newConTab.ContactId, S.Parameter(Sequence.GetNext(newConTab)));
  newInsert.FieldValuePairs.Add(newConTab.Name, S.Parameter("Will Hammond"));
  newInsert.FieldValuePairs.Add(newConTab.UpdatedCount,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.UpdatedAssociateId, S.Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.Updated, S. Parameter(DateTime.Now));
  newInsert.FieldValuePairs.Add(newConTab.BusinessIdx,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.CategoryIdx,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.CountryId, S. Parameter(50));
  newInsert.FieldValuePairs.Add(newConTab.Department, S. Parameter("Parks dept"));
  newInsert.FieldValuePairs.Add(newConTab.OrgNr, S. Parameter("2547869"));
  newInsert.FieldValuePairs.Add(newConTab.Registered, S. Parameter(DateTime.Now));
  newInsert.FieldValuePairs.Add(newConTab.RegisteredAssociateId, S.Parameter(103));
  //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 clossing the session
  newTrans.Commit();
  myConn.Close();
}

Special attention should be given when inserting the contact ID. Using the Sequence class’s GetNext method, the next contact ID is retrieved and assigned to the ContactId of the inserting record.

Once all the required fields are filled, add a connection to the database.

Then the created insert statement will be assigned to a created instance of the SoCommands’s SqlCommand property.

With the ExecuteNonQuery method of the SoCommand, the insert statement will be executed and the data will be added to the tables.

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
  ContactTableInfo newConTab = TablesInfo.GetContactTableInfo();
  //Creating an instance of the Insert class
  Insert newInsert = S.NewInsert();
  //Inserting the necessary fields of the Table
  newInsert.FieldValuePairs.Add(newConTab.ContactId, S.Parameter(Sequence.GetNext(newConTab)));
  newInsert.FieldValuePairs.Add(newConTab.Name, S.Parameter("Will Hammond"));
  newInsert.FieldValuePairs.Add(newConTab.UpdatedCount,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.UpdatedAssociateId, S.Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.Updated, S. Parameter(DateTime.Now));
  newInsert.FieldValuePairs.Add(newConTab.BusinessIdx,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.CategoryIdx,S. Parameter(0));
  newInsert.FieldValuePairs.Add(newConTab.CountryId, S. Parameter(50));
  newInsert.FieldValuePairs.Add(newConTab.Department, S. Parameter("Parks dept"));
  newInsert.FieldValuePairs.Add(newConTab.OrgNr, S. Parameter("2547869"));
  newInsert.FieldValuePairs.Add(newConTab.Registered, S. Parameter(DateTime.Now));
  newInsert.FieldValuePairs.Add(newConTab.RegisteredAssociateId, S.Parameter(103));
  //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 clossing the session
  newTrans.Commit();
  myConn.Close();
}