Bulk insert into an Oracle database through .Net C#

Bulk insert into an Oracle database through .Net C#

Below is the code to import thousands of records into an Oracle database in a .NET component.

Both allow you to do bulk inserts using something called array binding. This basically means that you can insert multiple records at once in one database call, thereby avoiding unnecessary round trips.

The table that I had to fill was called IMPORTJOBS:

CREATE TABLE IMPORTJOBS
(
  ID             RAW(16)                        NOT NULL,
  IMPORTBATCHID  RAW(16)                        NOT NULL,
  DATA           VARCHAR2(4000 BYTE)            NOT NULL,
  STATUSCODE     VARCHAR2(20 BYTE)              NOT NULL,
  RESULTID       RAW(16)
)

public interface IOracleBulkInsertProvider
{   
    void BulkInsertImportBatches(string connectionString,              
          List<ImportJob> importJobs);
}

public class OracleBulkInsertProvider : IOracleBulkInsertProvider
{
    public void BulkInsertImportBatches(string connectionString,
                                            List<ImportJob> importJobs)
    {                                        
        var ids = importJobs.Select(ib => ib.Id).ToList();
        var importBatchIds =
                  importJobs.Select(ib => ib.ImportBatchId).ToList();
        var datas = importJobs.Select(ib => ib.Data).ToList();
        var statusCodes =
                  importJobs.Select(ib => ib.ImportJobStatus.Code).ToList();               
                               
        const string sql = "insert into IMPORTJOBS (ID, IMPORTBATCHID, DATA,
            STATUSCODE) values (:id, :importBatchId, :data, :statusCode)";           

        using (var oracleConnection =
                               new OracleConnection(connectionString))
        {
            oracleConnection.Open();
            using (var command = oracleConnection.CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = CommandType.Text;
                command.Parameters.Add(":id", OracleDbType.Raw,
                  ids.ToArray(), ParameterDirection.Input);
                command.Parameters.Add(":importBatchId", OracleDbType.Raw,
                  importBatchIds.ToArray(), ParameterDirection.Input);
                command.Parameters.Add(":data", OracleDbType.VarChar,
                  datas.ToArray(), ParameterDirection.Input);
                command.Parameters.Add(":statusCode", OracleDbType.VarChar,
                  statusCodes.ToArray(), ParameterDirection.Input);
                command.ExecuteArray(importJobs.Count);                                   
            }                               
        }
    }
}

I think the code is straightforward. The goal is to create a command, add input parameters and at the same time provide a list of actual values to insert for that parameter. At the end we simply call the ExecuteArray operation.

This way, the time needed to insert 25.000 records takes about 2 seconds.

Recommendations for Abstract Classes vs. Interfaces

The general guideline: Use abstract clas for "is A" relationship,  while use interfaces for "Can do" relationship.
For example:
An Aircraft "is a" Vehicle and it "can" fly.
So it makes sense to make Vehicle an abstract class and make an interface IFlyable that class Aircraft implements and Aircraft derives from Vehicle class.

The choice of whether to design your functionality as an interface or an abstract class (a MustInherit class in Visual Basic) can sometimes be a difficult one. An abstract class is a class that cannot be instantiated, but must be inherited from. An abstract class may be fully implemented, but is more usually partially implemented or not implemented at all, thereby encapsulating common functionality for inherited classes. For details, see Abstract Classes.

An interface, by contrast, is a totally abstract set of members that can be thought of as defining a contract for conduct. The implementation of an interface is left completely to the developer.

Both interfaces and abstract classes are useful for component interaction. If a method requires an interface as an argument, then any object that implements that interface can be used in the argument. For example:

// C#
public void Spin (IWidget widget)
{}

This method could accept any object that implemented IWidget as the widget argument, even though the implementations of IWidget might be quite different. Abstract classes also allow for this kind of polymorphism, but with a few caveats:

    Classes may inherit from only one base class, so if you want to use abstract classes to provide polymorphism to a group of classes, they must all inherit from that class.
    Abstract classes may also provide members that have already been implemented. Therefore, you can ensure a certain amount of identical functionality with an abstract class, but cannot with an interface.

Here are some recommendations to help you to decide whether to use an interface or an abstract class to provide polymorphism for your components.

    If you anticipate creating multiple versions of your component, create an abstract class. Abstract classes provide a simple and easy way to version your components. By updating the base class, all inheriting classes are automatically updated with the change. Interfaces, on the other hand, cannot be changed once created. If a new version of an interface is required, you must create a whole new interface.
    If the functionality you are creating will be useful across a wide range of disparate objects, use an interface. Abstract classes should be used primarily for objects that are closely related, whereas interfaces are best suited for providing common functionality to unrelated classes.
    If you are designing small, concise bits of functionality, use interfaces. If you are designing large functional units, use an abstract class.
    If you want to provide common, implemented functionality among all implementations of your component, use an abstract class. Abstract classes allow you to partially implement your class, whereas interfaces contain no implementation for any members.
  
If you need shared behavior to derived classes then use abstract class or if you need plug gable element structure use interfaces because interface is only alternative of multiple inheritance in term of reusable component design not for shared functionality, it’s just define a contract to derived classes .   
 Differences:

    Abstract class may or may not have abstract method other than interfaces only declarative.
    Abstract class can contain variable other than interface can’t.
    Interfaces members are implicitly public; so you can’t use access modifiers to restrict access other than abstract class follows the general class rules.
    We can’t use static and constant members in interface other than abstract class we can use.
    A Class or Struct can be inherited from multiple interfaces but only from single abstract class.
Interface provides loose coupling other than concrete class provide strong coupling.
Interfaces support events, indexers, methods, and properties.

Coupling: In Brief when one class has direct knowledge of other class refer as coupling. If dependent class has direct reference to concrete class then it‘s called strong coupling else when dependent class contains pointer to interface it’s called loose coupling.
Partial Interfaces: Interfaces can be used with partial keyword like partial classes as we know compiler combine the partial elements to create unified interface definition.
partial interface IProcess
      {   void DataProcess();   }
partial interface IProcess
      {   string ProcessType { get; } }

class Process : IProcess
 {
      public void DataProcess()
        {     //Logic    }
      public string ProcessType { get { return "Data"; } }

  }
Explicit Interface Implementation: Multiple inheritances by using interfaces are quite easy until members are identical .Suppose two interfaces have same member like below:

interface IExportProcess {  void Save();   }
interface IImportProcess {  void Save();   }

Problem occur when XMLDocument object up cast to IExportProcess ,calls to the Save Method expecting different result then if the same method was called when the object up cast to IImportProcess .
To sort out this problem you can use explicit interface implementation as below;
class XMLDocument :IExportProcess,IImportProcess
{
   public void IExportProcess.Save()
      {   //Logic    }

   public void IImportProcess.Save()
       {  //Logic    }

}

MySQL: Split comma separated list into multiple rows

    In MySQL this can be achieved as below  
 
    SELECT id, length FROM vehicles WHERE id IN ( 117, 148, 126)

    +---------------+
    | id  | length  |
    +---------------+
    | 117 | 25        |
    | 126 | 8        |
    | 148 | 10        |
    +---------------+

    SELECT id,vehicle_ids FROM load_plan_configs WHERE load_plan_configs.id =42

    +---------------------+
    | id  | vehicle_ids   |
    +---------------------+
    | 42  | 117, 148, 126 |
    +---------------------+

    Now to get the length of comma separated vehicle_ids use below query

    Output

    SELECT length FROM vehicles, load_plan_configs   WHERE load_plan_configs.id = 42 AND FIND_IN_SET(vehicles.id, load_plan_configs.vehicle_ids)
   
    +---------+
    | length  |
    +---------+
    | 25        |
    | 8          |
    | 10       |
    +---------+

Macro to unprotect excel sheet

sub MyMacro()
                        Dim i As Integer, j As Integer, k As Integer
                Dim l As Integer, m As Integer, n As Integer
                Dim i1 As Integer, i2 As Integer, i3 As Integer
               Dim i4 As Integer, i5 As Integer, i6 As Integer
               On Error Resume Next
                For i = 65 To 66 : For j = 65 To 66 : For k = 65 To 66
             For l = 65 To 66 : For m = 65 To 66 : For i1 = 65 To 66
             For i2 = 65 To 66 : For i3 = 65 To 66 : For i4 = 65 To 66
             For i5 = 65 To 66 : For i6 = 65 To 66 : For n = 32 To 126
             ActiveSheet.Unprotect(Chr(i) & Chr(j) & Chr(k) & _
                Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
                 Chr(i4) & Chr(i5) & Chr(i6) & Chr(n))
             If ActiveSheet.ProtectContents = False Then
                Exit Sub
             End If
                                Next : Next : Next : Next : Next : Next
                                 Next : Next : Next : Next : Next : Next
                end sub

----------------------

Private Sub UnprotectSheet()
        'Breaks worksheet password protection.
        Dim strPassword As String = Configuration.ConfigurationManager.AppSettings("ProtectPassword").ToString()
        Dim intCtr As Integer = 0
        Dim ObjExcel As New Excel.Application
        Dim objWbook As Excel.Workbook
        For intCtr = 0 To lstFiles.Items.Count - 1
            ObjExcel.Workbooks.Add(txtInputFolder.Text & "\" & lstFiles.Items(intCtr))
        Next
        Dim wsHeet As Excel.Worksheet
        ObjExcel.DisplayAlerts = False
        intCtr = 0
        For Each objWbook In ObjExcel.Workbooks
            Dim xlmodule As Object 'VBComponent
            xlmodule = objWbook.VBProject.VBComponents.Add(1) 'vbext_ct_StdModule
            Dim strCode As String
            strCode = _
               "sub MyMacro()" & vbCr & _
               "         Dim i As Integer, j As Integer, k As Integer " & vbNewLine & _
               " Dim l As Integer, m As Integer, n As Integer " & vbNewLine & _
                "Dim i1 As Integer, i2 As Integer, i3 As Integer " & vbNewLine & _
              " Dim i4 As Integer, i5 As Integer, i6 As Integer " & vbNewLine & _
              " On Error Resume Next " & vbNewLine & _
                "For i = 65 To 66 : For j = 65 To 66 : For k = 65 To 66 " & vbNewLine & _
            " For l = 65 To 66 : For m = 65 To 66 : For i1 = 65 To 66 " & vbNewLine & _
            " For i2 = 65 To 66 : For i3 = 65 To 66 : For i4 = 65 To 66 " & vbNewLine & _
            " For i5 = 65 To 66 : For i6 = 65 To 66 : For n = 32 To 126 " & vbNewLine & _
            " ActiveSheet.Unprotect(Chr(i) & Chr(j) & Chr(k) & _" & vbNewLine & _
            "    Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ " & vbNewLine & _
                " Chr(i4) & Chr(i5) & Chr(i6) & Chr(n))" & vbNewLine & _
            " If ActiveSheet.ProtectContents = False Then " & vbNewLine & _
            "    Exit Sub " & vbNewLine & _
            " End If " & vbNewLine & _
                          "      Next : Next : Next : Next : Next : Next " & vbNewLine & _
                                " Next : Next : Next : Next : Next : Next " & vbNewLine & _
                "end sub"

            strCode = _
               "sub MyMacro()" & vbCr & _
               " If ActiveSheet.ProtectContents = true Then" & vbNewLine & _
             "   ActiveSheet.Unprotect """ & strPassword & """ " & vbNewLine & _
            " End If " & vbNewLine & _
             "end sub"

            xlmodule.CodeModule.AddFromString(strCode)

            For Each wsHeet In objWbook.Worksheets
                wsHeet.Activate()
                If objWbook.ActiveSheet.ProtectContents = True Then
                    ObjExcel.Run("MyMacro")
                End If
            Next
            objWbook.Close(True, txtInputFolder.Text & "\" & lstFiles.Items(intCtr))
            intCtr = intCtr + 1

           
        Next
        ObjExcel.Quit()

    End Sub