Help on ASP.NET Core NET 5.0 Using ADO.NET

Is there a way that I can have my connection string either on the appsettings.json and retrieve it from the appsettings.json instead of having on every model datalayer as it reads on my class? Please provide sample code how to retrieve from the appsettings.json and use it my datalayer.

Thanks

Thank you much Amit Mohanty, finally It worked, I was very fustrated with .NET Core that I was giving up, I've been developing ASP.NET WebForm for a while now and I wanted to convert some of my project to .NET Core I tried converting straight from Microsoft suggestion that did not work, then rewrite to .NET Core 2.0 some project worked and now trying to be at least close to the game with .NET Core 5 even though Microsoft is not supporting and found this wall that stopped me but thanks to you I can continue and try to make it happen, my next challance will be the Entity Framework which I believe is better using than ADO.NET, I am afraid to ask why this didnot work string sqlQuery = "dbo.spGetTicketData 0"; it worked like a charm in ASP.NET Webform.

Thank you for all your help.

Hello Jose Siaz,

Your store procedure expecting @TicketId as parameter, You need to pass parameter in your code,

CREATE PROCEDURE [dbo].[spGetTicketData]
  @TicketId int	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF @TicketId>0 
	   BEGIN
	      SELECT [ID],[date_submitted],[business_name],[business_phone],[business_location],[contact_person],[contact_email],
			       [priority],[category],[assigned],[ticket_number],[status] FROM Tickets 
		  WHERE ID=@TicketId
	   END
    ELSE
	   BEGIN
			SELECT [ID],[date_submitted],[business_name],[business_phone],[business_location],[contact_person],[contact_email],
			       [priority],[category],[assigned],[ticket_number],[status] FROM Tickets
	   END
END

Please have a look at below code

public IEnumerable GetAllTickets()
       {
           int ticketId = 0; // set your parameter from here
           List lstticket = new List();

           using (SqlConnection con = new SqlConnection(GetConnectionString()))
           {
               string sqlQuery = "dbo.spGetTicketData";
               SqlCommand cmd = new SqlCommand(sqlQuery, con);
               cmd.CommandType = CommandType.StoredProcedure;
               command.Parameters.Add(new SqlParameter("@TicketId", ticketId));
               con.Open();
               SqlDataReader rdr = cmd.ExecuteReader();

               while (rdr.Read())
               {
                   Ticket ticket = new Ticket();

                   ticket.ID = Convert.ToInt32(rdr["ID"]);
                   ticket.Businees_Name = rdr["businees_name"].ToString();
                   ticket.Telephone = rdr["business_phone"].ToString();
                   ticket.Location = rdr["business_location"].ToString();
                   ticket.Contact_Person = rdr["contact_person"].ToString();
                   ticket.Personal_Email = rdr["contact_email"].ToString();
                   ticket.Priority = rdr["priority"].ToString();
                   ticket.Category = rdr["category"].ToString();
                   ticket.Assigned_To = rdr["assigned"].ToString();
                   ticket.Number = rdr["ticket_number"].ToString();
                   ticket.Status = rdr["status"].ToString();

                   lstticket.Add(ticket);
               }
               con.Close();
           }
           return lstticket;
       }

The whole project along with database, tables and store proc are part of the public github source https://github.com/netstair/NetstairHelpdeskV5_ADONET/blob/master/HelpDeskDb_Database_Script.sql

Thanks 

Could you please share your store procedure ? either your store procedure has parameter which we need to pass from back end but for method GetAllTickets() should not have any parameter as per name it should return all tickets.

Thank you Jignesh Kumar for the reply, but I still get the same error message with both solution provided

Suggested using exec 'dbo.spGetTicketData'
SqlException: Could not find stored procedure 'exec 'dbo.spGetTicketData' '.

Suggested using "spGetTicketData"'
SqlException: Procedure or function 'spGetTicketData' expects parameter '@TicketId', which was not supplied.

Or when using "spGetTicketData 0"'
SqlException: Could not find stored procedure 'spGetTicketData 0'.

I am not sure the MS-SQL environment has anything to do with, I am using MS-SQL 2008 R2, meanwhile If I drop the .NET core to version 2.0 it works, I understand Core 2.0 is old and no longer supported, that is why I am using .NET Core 5.0

Thanks

Hello,

Are you getting error in GetAllTickets() method. please do change in this line only,

string sqlQuery = "[dbo].[spGetTicketData] 0";

// need to replace with

string sqlQuery = "dbo.spGetTicketData"; // if you are using commandText = commandType.StoreProcedure then you need to pass only store procedure name.

 public IEnumerable GetAllTickets()
        {
            List lstticket = new List();
            
            using (SqlConnection con = new SqlConnection(GetConnectionString()))
            {
                string sqlQuery = "dbo.spGetTicketData";
                SqlCommand cmd = new SqlCommand(sqlQuery, con);
                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Ticket ticket = new Ticket();

                    ticket.ID = Convert.ToInt32(rdr["ID"]);
                    ticket.Businees_Name = rdr["businees_name"].ToString();
                    ticket.Telephone = rdr["business_phone"].ToString();
                    ticket.Location = rdr["business_location"].ToString();
                    ticket.Contact_Person = rdr["contact_person"].ToString();
                    ticket.Personal_Email = rdr["contact_email"].ToString();
                    ticket.Priority = rdr["priority"].ToString();
                    ticket.Category = rdr["category"].ToString();
                    ticket.Assigned_To = rdr["assigned"].ToString();
                    ticket.Number = rdr["ticket_number"].ToString();
                    ticket.Status = rdr["status"].ToString();

                    lstticket.Add(ticket);
                }
                con.Close();
            }
            return lstticket;
        }

Still you are getting error, then please run this store procedure in SQL Query window using

exec 'dbo.spGetTicketData'