I want to return count of albumid

Rahul Patil 46 Reputation points
2022-12-15T12:48:28.73+00:00

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

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,597 questions
{count} votes

Accepted answer
  1. Lan Huang-MSFT 30,186 Reputation points Microsoft External Staff
    2022-12-16T08:34:52.8+00:00

    Hi @Rahul Patil ,
    You can refer to the following sql statement.
    271257-image.png

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.