I want to build a smart search agent which would use Watson conversation to process the request and give response but will use my own database say SQL server to search the desired output.
In Short Instead of writing intents and dialogues manually or importing from a csv file, I want to write my won code in .net in such a way that all the request and responses are influenced by my own data stored in my database. I only intent to use watson's processing and interpreting capability. But the processing must happen on my data.
E.g If the user searches for a word say "Dog", the Watson conversation service must search in my database and give relevant answers to the user based on the search.
Take a look at the solution architecture in the Watson Conversation documentation. Your database would be one of the depicted backend systems. Your application would be, as you mentioned, written in .NET and would use WCS to process the user input. It would return a response with all the associated metadata. Instead of having complete answers configured in a dialog, you would use something I have described as "replaced markers" in my collection of examples. Those markers are kind of hints to your application of which database query or which action to perform.
Note that WCS requires some intents and entities to work on. If you want to rely just on the detected intents and entities, you could work with one or two generic dialog nodes. As another technique you could use data from your database to generate intents and entities as an initial setup. In my "Mutating EgoBot" I use the Watson Conversation API to add intents and entities on the fly.