Web API Visual Studio - Get query (with parameters)
I created my first ever Web API using Visual Studio Core, using the database-first approach, with a SQL Server on Azure and hosted on Azure. The GET returns only about 20 rows of data of about 3 column, which is all the content in the table. This was easy and works fine.
Now I want to create a similar Web API, but on a table that has 100,000 rows and 20 columns. But I only want to GET about 150 rows at any time, filtered by 3 columns. So, I'm thinking the way to do this is to get ALL of the rows and then filter on the client side (Google Sheets), but I'm thinking it is not practical (will take too long to get). Or, is it?
I'm thinking the way to go is to send the parameters to the query (like I do with ADO/SQL directly on the SQL Server from an Excel application). But I don't know how to as yet with a Web API.
Questions:
1. is getting 100,000 rows way too much?
2. if so, can I have a Stored Procedure working with the Web API? (as my only knowledge is of the database-first approach to building the API, from a database table and not a Stored Procedure. Newbie).
Any advice welcome, and any links to an example to follow please?
Thank you
Now I want to create a similar Web API, but on a table that has 100,000 rows and 20 columns. But I only want to GET about 150 rows at any time, filtered by 3 columns. So, I'm thinking the way to do this is to get ALL of the rows and then filter on the client side (Google Sheets), but I'm thinking it is not practical (will take too long to get). Or, is it?
I'm thinking the way to go is to send the parameters to the query (like I do with ADO/SQL directly on the SQL Server from an Excel application). But I don't know how to as yet with a Web API.
Questions:
1. is getting 100,000 rows way too much?
2. if so, can I have a Stored Procedure working with the Web API? (as my only knowledge is of the database-first approach to building the API, from a database table and not a Stored Procedure. Newbie).
Any advice welcome, and any links to an example to follow please?
Thank you
ASKER
Thanks Chinmay. Pagination will be useful too if the data returned by the parameters is over a reasonable size.
I also found a course on LinkedIn Learning. I'm browsing it at the moment.
I think what I need is a way to adapt the database-first approach to include parameters. Otherwise I'll need to learn from the ground up, like code-first. The YouTube videos don't go that one step further to show how to use parameters :(
I also found a course on LinkedIn Learning. I'm browsing it at the moment.
I think what I need is a way to adapt the database-first approach to include parameters. Otherwise I'll need to learn from the ground up, like code-first. The YouTube videos don't go that one step further to show how to use parameters :(
I use Database first approach for one of our largest client. I am not sure where are you facing the challenge in querying the Database? Are you using EF Core? If so there should not be any issue at all.
PS: If you YouTube videos could really taught everything.... IT jobs won't be high paying ;)
PS: If you YouTube videos could really taught everything.... IT jobs won't be high paying ;)
ASKER
LOL. I agree with your comment about YT videos. In my field of expertise it is horrifying to see people simply creating videos to get attention, but usually only ending up confusing the audience.
I am using EF Core and I have VS2019 Community Edition. My brain is geared to ADO/SQL querying the database directly, so if there was a SQL statement involved I can easily modify that.
I'm thinking I should learn Web API/C# from the ground up (do I have the time?), or look to finding out how to modify the code generated by EF Core, so that I can send parameters.
For example, a URL like http://myweb.com/mydata?category=USA&car=BMW should give me a result (that will be about 150 records). Or, a JSON GET statement that does the same thing. I don't know if it's a small modification to the EF Core generated code, or a big one. (too many YT videos that can confuse).
I am using EF Core and I have VS2019 Community Edition. My brain is geared to ADO/SQL querying the database directly, so if there was a SQL statement involved I can easily modify that.
I'm thinking I should learn Web API/C# from the ground up (do I have the time?), or look to finding out how to modify the code generated by EF Core, so that I can send parameters.
For example, a URL like http://myweb.com/mydata?category=USA&car=BMW should give me a result (that will be about 150 records). Or, a JSON GET statement that does the same thing. I don't know if it's a small modification to the EF Core generated code, or a big one. (too many YT videos that can confuse).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've looked into how much I will need to learn. Before I dive in, I got a question.
Is there a way to use the Database-First approach, and also specify some Fields on which I want to setup to be able to filter? (this is because I have done some Database-First Web APIs that needed minimal code modification. Looking for the quickest way to the result I need. Sorry being a newbie!)
Is there a way to use the Database-First approach, and also specify some Fields on which I want to setup to be able to filter? (this is because I have done some Database-First Web APIs that needed minimal code modification. Looking for the quickest way to the result I need. Sorry being a newbie!)
ASKER
Chinmay, I'm closing the question as I need to be more precise if I'm looking for a quick fix. I'll start another question with the code that VS2019 creates and ask for what I need to modify to filter by a Field.
Thanks for your help on this.
Thanks for your help on this.
Sorry.. I am not able to respond quickly. I will try to answer your query there. And generally, I will not provide a quick fix because it does not really work in the end. I will explain later.
ASKER
I agree about the 'quick fix'. I'd prefer to understand the principles. My background is VBA so I have a fantasy that I can just follow coding patterns and it will work. Sometimes they do. But it is better to learn the fundamentals. And then I can apply it to any situation.
I'll post on another thread in the next few days when I have the database-first-generated code, which I don't yet. While learning C# properly.
I'll post on another thread in the next few days when I have the database-first-generated code, which I don't yet. While learning C# properly.
It seems you have not asked the other question. So here are the steps
1. Generate the DBContext
2. Either on the Page Or in a Controller, Add a method, Say GetXyz
3. Assuming you want to pass just one parameter, the signature of your method should be
If you need to get more parameters in the play and also you do not want to rely on the URL to relay the parameters, you can use [FromHeader], [FromBody], [FromForm] and [FromQuyery]
Once you get the required parameters on server side, you can use Linq as shown below (Line 2 - rest of the lines are standard C# and EF Core Code).
Please give it a try and let me know how it goes.
1. Generate the DBContext
2. Either on the Page Or in a Controller, Add a method, Say GetXyz
3. Assuming you want to pass just one parameter, the signature of your method should be
[HttpGet("{id}")]
public async Task<JsonResult> GetXyz(string id)
{
...
}If you need to get more parameters in the play and also you do not want to rely on the URL to relay the parameters, you can use [FromHeader], [FromBody], [FromForm] and [FromQuyery]
[HttpPost("GetData")]
public async Task<JsonResult> GetData([FromForm] int start, [FromForm] int length, [FromForm] string dataFilter1)Once you get the required parameters on server side, you can use Linq as shown below (Line 2 - rest of the lines are standard C# and EF Core Code).
var result = this._context.Table.AsNoTracking();
result = result.Where(c => c.Filter1.Equals(dataFilter1));
result = await result.ListAsync();Please give it a try and let me know how it goes.
Oh... VBA to C# can be challenging.. (I was VB6 guy 20 years back... but switched to C#... and I think I love C# a lot more than VB6... now).
Please continue learning C# and ASP.Net Core from Microsoft Docs and then tackle this problem again. It will be far easier.
Please continue learning C# and ASP.Net Core from Microsoft Docs and then tackle this problem again. It will be far easier.
ASKER
Wow. Thanks.
Yes, VBA to JavaScript(TypeScrip/GoogleActionScript/OfficeScript) is an unnatural jump also :(
Yes, VBA to JavaScript(TypeScrip/GoogleActionScript/OfficeScript) is an unnatural jump also :(
Don't worry.. you will get used to it and will forget everything about VB.. someday :P
2. You can use anything - SP, EF Core (Any other ORM) to do this.
I user EF Core along with pagination and if you'd like, there is an end to end example here that demonstrates pagination: Tutorial: Add sorting, filtering, and paging - ASP.NET MVC with EF Core | Microsoft Docs