Database

Imports System.Data.OleDb
Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Randomize(-1000)
        Dim start As Date = Date.Now
        ExecuteDAO()
        System.Console.WriteLine("DAO output took " & DateDiff(DateInterval.Second, start, Date.Now) & " seconds.")
        start = Date.Now
        ExecuteOleDb()
        System.Console.WriteLine("OleDb output took " & DateDiff(DateInterval.Second, start, Date.Now) & " seconds.")
        start = Date.Now
        ExecuteSql()
        System.Console.WriteLine("Sql output took " & DateDiff(DateInterval.Second, start, Date.Now) & " seconds.")
        System.Console.WriteLine("Press any key to continue...")
        System.Console.Read()
    End Sub

    Public Function ExecuteOleDb()
        Dim conn As OleDbConnection
        Dim cmd As IDbCommand
        Dim i, j As Integer
        conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\test.mdb'")
        conn.Open()
        cmd = CreateCommand(conn)
        For i = 1 To 8760
            cmd.Parameters(0).Value = New Date(2002, 1, 1).AddHours(i - 1)
            For j = 1 To 40
                cmd.Parameters(j).Value = Rnd()
            Next
            cmd.ExecuteNonQuery()
        Next
        conn.Close()
    End Function
    Public Function ExecuteSql()
        Dim conn As SqlConnection
        Dim cmd As IDbCommand
        Dim i, j As Integer
        conn = New SqlConnection("Data Source=COMPUTER\SQLServer; Database=testSQL; Integrated Security=SSPI")
        conn.Open()
        cmd = CreateCommand(conn)
        For i = 1 To 8760
            cmd.Parameters(0).Value = New Date(2002, 1, 1).AddHours(i - 1)
            For j = 1 To 40
                cmd.Parameters(j).Value = Rnd()
            Next
            cmd.ExecuteNonQuery()
        Next
        conn.Close()
    End Function
    Public Function ExecuteDAO()
        Dim dbEng As DAO.DBEngineClass = New DAO.DBEngineClass()
        Dim wk As DAO.Workspace = dbEng.Workspaces(0)
        Dim db As DAO.Database = wk.OpenDatabase("C:\test.mdb")
        Dim rs As DAO.Recordset = db.OpenRecordset("Test")
        Dim i, j As Integer
        For i = 1 To 8760
            rs.AddNew()
            rs.Fields(0).Value = New Date(2002, 1, 1).AddHours(i - 1)
            For j = 1 To 40
                rs.Fields(j).Value = Rnd()
            Next
            rs.Update()
        Next
        rs.Close()
        db.Close()
    End Function

    Public Function CreateCommand(ByVal conn As IDbConnection) As IDbCommand
        Dim cmd As IDbCommand = conn.CreateCommand()
        Dim param As IDbDataParameter
        param = cmd.CreateParameter()
        param.ParameterName = "@obs"
        param.DbType = DbType.DateTime
        cmd.Parameters.Add(param)
        Dim cmdText As String = "INSERT INTO Test (Observation"
        Dim cmdTextParams As String = "@obs"
        Dim scen As Integer
        For scen = 1 To 40
            param = cmd.CreateParameter()
            param.ParameterName = "@val" & scen
            param.DbType = DbType.Double
            cmd.Parameters.Add(param)
            cmdText &= ", Val" & scen
            cmdTextParams &= ", @val" & scen
        Next
        cmdText &= ") VALUES (" & cmdTextParams & ")"
        cmd.CommandText = cmdText
        cmd.Prepare()
        Return cmd
    End Function

End Module

__declspec(dllexport) UINT APIENTRY MyFunction(MSIHANDLE hMSI)

BSTR    g_bstrEmpty = ::SysAllocString(T2OLE(""));
CString DatabasePath("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=test;Data Source=SERVER_NAME");

ADOConnection *pIDBCon     = 0;
ADORecordset  *pIRecordset = 0;

// Create connection object
HRESULT hr = CoCreateInstance(CLSID_CADOConnection, 
                              NULL, 
                              CLSCTX_INPROC_SERVER, 
                              IID_IADOConnection, 
                              static_cast<LPVOID *>(&pIDBCon));
if(SUCCEEDED(hr))
{
  BSTR bstrConnection = DatabasePath.AllocSysString();

  pIDBCon->put_ConnectionString(bstrConnection);

  // Open database
  hr = pIDBCon->Open(g_bstrEmpty, g_bstrEmpty, g_bstrEmpty, -1);
  if(SUCCEEDED(hr))
  {
    hr = pIDBCon->put_CursorLocation(adUseClient);

    // Create recordset
    hr = CoCreateInstance(CLSID_CADORecordset, 
                          NULL, 
                          CLSCTX_INPROC_SERVER, 
                          IID_IADORecordset, 
                          static_cast<LPVOID *>(&pIRecordset));
    if(SUCCEEDED(hr))
    {
      hr = pIRecordset->putref_ActiveConnection(pIDBCon);

      // Get all records
      VARIANT g_varEmpty;

      g_varEmpty.vt    = VT_ERROR;
      g_varEmpty.scode = DISP_E_PARAMNOTFOUND;	

      COleVariant varSQL("SELECT * FROM Table");

      hr = pIRecordset->Open(varSQL, g_varEmpty, adOpenKeyset, adLockOptimistic, adCmdText);

      // Close recordset
      pIRecordSet->Close();
    }
  }

  // Close connection
  pIDBCon->Close();
}

Function GetSQLDate ( dtDate)
  ' check dtDate is a Variant->Date type
  If Not IsDate(dtDate) then
     Err.Raise vbObjectError + 1024, "Global::Function::GetSQLDate","Argument not a date"
  End If

  Dim dpYear, dpMonth, dpDay, dpHour, dpMinute, dpSecond
  dpYear=Year(CDate(dtDate))
  dpMonth=Month(CDate(dtDate))
  dpDay=Day(CDate(dtDate))
  dpHour=Hour(CDate(dtDate))
  dpMinute=Minute(CDate(dtDate))
  dpSecond=Second(CDate(dtDate))

  if Not Len(dpYear) = 4 then dpYear="20" & dpYear
  if Not Len(dpMonth) = 2 then dpMonth = "0" & dpMonth
  if Not Len(dpDay) = 2 then dpDay = "0" & dpDay
  if Not Len(dpHour) = 2 then dpHour = "0" & dpHour
  if Not Len(dpMinute) = 2 then dpMinute = "0" & dpMinute
  if Not Len(dpSecond) = 2 then dpSecond = "0" & dpSecond

  GetSQLDate=dpYear & "-" & dpMonth & "-" & dpDay & " " & dpHour & ":" & dpMinute & ":" & dpSecond

End Function