We need to develop an API where we need to share the data to another application from database through API or any other way.Database may have neara bout 5 million rows 1. what would be best way to do this.Read data share to file. 2. Or Instead of having API, can we give database details to them and let them get the data. We can create a View and Let them share the data. So we avoid IO operation.
You won't be able to avoid the I/O - somehow the data needs to be sent, after all. There are many ways to share data between applications, but to recommend one we would need to know a lot more about the specifics. What kind of process (and user) is reading the data? What kind of process (and user) is sending the data? How often does the data change? How often is the data read? In terms of network architecture, where are the two processes located relative to one another? Etc. etc.
This depends on how strong you want the coupling to be between the second application and the database. A direct SQL login is efficient, but it requires the second application to know details about the database schema. Additionally, it may require firewall privileges to get to the database. And it's definitely not recommended for desktop apps - that's basically the root of the infamous Microsoft SQL Server "Slammer" attack.
If you want less coupling and more flexibility, you can provide an API either as a third application or as an addition to the first application. It's more overhead, but it's more likely to be change-sensitive (if properly maintained) and generally it's going to be more secure.
The secret of how to be miserable is to constantly expect things are going to happen the way that they are "supposed" to happen.
You can have faith, which carries the understanding that you may be disappointed. Then there's being a willfully-blind idiot, which virtually guarantees it.