NvarChar is not member of NpgsqlTypes.NpgsqlDbTypes ..But i import Namespace
Imports Npgsql
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Web.Services
Imports NpgsqlTypes
Public Class GraphData
Public Property label As String
Public Property value As Integer
End Class
<WebMethod()>
Public Shared Function GetGraphData(ByVal customerId As String) As List(Of GraphData)
Dim graphDataList As List(Of GraphData) = New List(Of GraphData)()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim con As NpgsqlConnection = New NpgsqlConnection(constr)
Dim cmd As NpgsqlCommand = New NpgsqlCommand("SELECT TOP 10 CAST(OrderDate AS DATE) OrderDate, SUM(Freight) Freight FROM Orders WHERE CustomerID = @Id OR @Id IS NULL GROUP BY Orderdate", constr)
cmd.Parameters.Add(New NpgsqlParameter("Id", NpgsqlDbType.NVarChar))
cmd.Parameters("Id").Value = If(Not String.IsNullOrEmpty(customerId), customerId, CObj(DBNull.Value))
cmd.Connection = con
con.Open()
Dim sdr As NpgsqlDataReader = cmd.ExecuteReader()
While sdr.Read()
graphDataList.Add(New GraphData() With {
.label = Convert.ToString(sdr("OrderDate")),
.value = (Convert.ToInt32(sdr("Freight")))
})
End While
con.Close()
Return graphDataList
End Function
<asp:DropDownList ID="ddlCustomerID" runat="server">
<asp:ListItem Text="Maria" Value="ALFKI"></asp:ListItem>
<asp:ListItem Text="Ana Trujillo" Value="ANATR"></asp:ListItem>
<asp:ListItem Text="Antonio Moreno" Value="ANTON"></asp:ListItem>
<asp:ListItem Text="Thomas Hardy" Value="AROUT"></asp:ListItem>
<asp:ListItem Text="Christina Berglund" Value="BERGS"></asp:ListItem>
</asp:DropDownList>
<div id="line-chart">
</div>
<link type="text/css" rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css" />
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
<script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script>
<script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
BindMorrisChart('');
$('[id*=ddlCustomerID]').on('change', function () {
var id = $(this).val();
BindMorrisChart(id);
});
});
function BindMorrisChart(id) {
$('#line-chart').empty();
$.ajax({
type: 'POST',
url: "Default.aspx/GetGraphData",
data: '{ "customerId": "' + id + '" }',
dataType: 'json',
contentType: "application/json; charset=utf-8",
success: function (result) {
new Morris.Donut({
element: 'line-chart',
data: result.d,
xkey: 'label',
ykeys: ['value'],
labels: ['value']
});
},
error: function (error) {
alert(error.responseText);
}
});
}
</script>