I want to return count of albumid

Question

total count of album id is 11 based on current year(2022)

I want to return that 11 number and showing 11 in chart

see my function

CREATE OR REPLACE FUNCTION public.getmonthfromdatetest(  
    orderdate date)  
    RETURNS SETOF record   
    LANGUAGE 'sql'  
    COST 100  
    VOLATILE PARALLEL UNSAFE  
    ROWS 1000  
  
AS $BODY$  
SELECT  albumid as albumid,extract(month FROM orderdate) AS yr_month  
    FROM public.orderalbum  
    WHERE  
        extract(year FROM orderdate)::int = 2022   
        --GROUP BY albumid,orderdate      
        --select extract(month from orderdate)::int;  
$BODY$;  
  
ALTER FUNCTION public.getmonthfromdatetest(date)  
    OWNER TO cpupdate;  

and see my function output image

https://imgur.com/UQowfs4

and see my table image

https://imgur.com/wCcloW1

controller code

        [HttpGet]  
        public ActionResult getmonthSales()  
        {  
            conn.Open();  
            var trans = conn.BeginTransaction();  
  
            var command = new Npgsql.NpgsqlCommand("getdaysfromdate", conn);  
            command.CommandType = System.Data.CommandType.StoredProcedure;  
  
            var parameter = command.CreateParameter();  
            parameter.ParameterName = "orderdate";  
            parameter.DbType = System.Data.DbType.String;  
            parameter.Value = "2022-01-01";  
            command.Parameters.Add(parameter);  
  
            var da = new Npgsql.NpgsqlDataAdapter(command);  
            var ds = new System.Data.DataSet();  
            da.Fill(ds);  
  
            trans.Commit();  
            conn.Close();  
  
            //conn.Open();  
            //var trans = conn.BeginTransaction();  
            //var cmd1 = new NpgsqlCommand("public.getmonthfromdatetest", conn);  
            //{  
            //cmd1.CommandType = System.Data.CommandType.StoredProcedure; ;  
            //cmd1.Parameters.AddWithValue("@orderdate", NpgsqlTypes.NpgsqlDbType.Text, "2022-01-01");  
            //var ret = (bool)cmd1.ExecuteScalar();  
            //}  
  
            // Start a transaction as it is required to work with cursors in PostgreSQL  
            //  NpgsqlTransaction tran = conn.BeginTransaction();  
  
            // Define a command to call stored procedure show_cities_multiple  
            //NpgsqlCommand command = new NpgsqlCommand("getmonthfromdateTest", conn);  
            // command.CommandType = CommandType.StoredProcedure;  
  
            // Execute the stored procedure and obtain the first result set  
            // NpgsqlDataReader dr = command.ExecuteReader();  
  
            //string query = @"SELECT getmonthfromdateTest(orderdate) as y FROM public.orderAlbum";  
            //NpgsqlCommand cmd = new NpgsqlCommand("SELECT getmonthfromdateTest(orderdate) as y FROM public.orderAlbum", conn);  
  
            //NpgsqlCommand cmd = new NpgsqlCommand(query, conn);  
            //conn.Open();  
            //object cursorVal = cmd.ExecuteScalar();  
            //DataSet ds = FetchAll(conn, cursorVal);  
            //cmd.Dispose();  
            //conn.Close();  
  
            List<OrderData> lst = new List<OrderData>();  
            DataTable dt = new DataTable();  
  
            NpgsqlDataAdapter adp = new NpgsqlDataAdapter(command);  
            adp.Fill(dt);  
  
            foreach (DataRow dr in dt.Rows)  
            {  
                lst.Add(new OrderData  
                {  
                    aid = Convert.ToInt32(dr[0]),  
                    ordermonth = Convert.ToInt32(dr[1])  
                });  
            }  
            return Json(lst, JsonRequestBehavior.AllowGet);  
  
        }  
  
        private DataSet FetchAll(NpgsqlConnection _connection, object cursorVal)  
        {  
            try  
            {  
                DataSet actualData = new DataSet();  
  
                string strSql = "fetch all from \"" + cursorVal + "\";"; ;  
                //string strSql = "fetch all from \"" + ((object[])cursorVal)[0] + "\" + \"" + ((object[])cursorVal)[1] + "\";";  
                NpgsqlCommand cmd = new NpgsqlCommand(strSql, _connection);  
                NpgsqlDataAdapter ada = new NpgsqlDataAdapter(cmd);  
                ada.Fill(actualData);  
  
                return actualData;  
  
            }  
            catch (Exception Exp)  
            {  
                throw new Exception(Exp.Message);  
            }  
        }  

Models

        public int aid { get; set; }  
        public int ordermonth { get; set; }  

I am trying different code but not solving problem

I am trying and trying but get an error

this below line give an error

da.Fill(ds);  

{"Exception while reading from stream"}

I am trying and trying but not return 11

need help

Answer 1

Hi @Rahul Patil ,
You can refer to the following sql statement.