I have tables and a lot of sotred procedure that work with sql database.
For demonstration purposes I want to load data into memory (maybe dataset that i can then store in session- the demnonstration is limited so server memory cap won't be a problem ?) from my sql tables and manipulate it with my stored procedures.
Is it possible? Or i need to rewrite all my stored procedures or even replace them with code that works with data set?
If you wish for SQL Server to manage your table in memory, there is no guaranteed way to.
You can create a table variable and fill it with the dataset, and it'll probably be stored in memory.
Check this answer for more information:
http://stackoverflow.com/questions/27835/does-ms-sql-support-in-memory-tables
Aditionally, with some server configuration you may be able to put your tempdb into a ramdisk, which would effectively give you leeway to operate not just with table variables and hope, but you can store your dataset in a temporary table and be sure it'll be in RAM. Check this Microsoft's article:
http://support.microsoft.com/?scid=kb;en-us;917047&x=17&y=9
EDIT: I would expect that if the dataset fits in server memory (and their configured per process limit) it would be stored in server's memory. But that's just an educated guess as I'm not familiar with ASP.NET's architecture
You can in theory load your entire database in memory and even store a copy of the database for each session. Whether that is a sound thing to do, is a different discussion. All successful ASP applications I hear of are stateless.
Its absolutely impossible to have SQL Server manipulate your process memory where the ADO.Net data sets reside, through Transact-SQL procedures or any other technology. You are going to have to rewrite all your procedures as CLR methods in your ASP.Net application to operate on the ADO.Net data sets.