Hi @Rahul Patil ,
You can refer to the following sql statement.
I want to return count of albumid
Rahul Patil
46
Reputation points
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
and see my table image
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
Accepted answer
-
Lan Huang-MSFT 30,186 Reputation points Microsoft External Staff
2022-12-16T08:34:52.8+00:00