Simulating DAvg() equivalent to Microsoft Access
I was writing a ClassLibrary to COM Interop and this one came up. This was original whiten to use with JET (VBA and VB6)
C#
public object DAvg(string connectionString, string expression, string domain, string criteria)
{
var strSQL = "Select " + expression + " As ThisValue" +
" From " + domain +
" Where " + criteria;
using (var con = new OleDbConnection(connectionString))
{
using (var da = new OleDbDataAdapter(strSQL, con))
{
using (var dt = new DataTable())
{
da.Fill(dt);
int i;
double sum = 0;
for (i = 0; (i <= dt.Rows.Count -1); i++)
{
sum = Convert.ToDouble(dt.Rows[i].ItemArray[0].ToString()) + sum;
}
return sum / i;
}
}
}
}
VB.NET
Public Function DAvg(ByVal connectionString As String, ByVal expression As String, ByVal domain As String, ByVal criteria As String) As Object
Dim strSQL = "Select " + expression + " As ThisValue" + _
" From " + domain + _
" Where " + criteria
Using con = New OleDbConnection(connectionString)
Using da = New OleDbDataAdapter(strSQL, con)
Using dt = New DataTable()
da.Fill(dt)
Dim i As Integer
Dim sum As Double = 0
i = 0
While (i <= dt.Rows.Count - 1)
sum = Convert.ToDouble(dt.Rows(i).ItemArray(0).ToString()) + sum
i += 1
End While
Return sum / i
End Using
End Using
End Using
End Function
Cross-Posted from http://msmvps.com/blogs/officept/default.aspx