using cursors in c#.net

Question

Hi every body I have created a cursor in sql but i dont know how to use it in c #.net i want the cursor to fetch next row i can do that in sql server but i dont know how to do it c# can any one help thanks in advance

Ricky · Answer

I'm not really sure I understand exactly but will try to offer some help. Cursors are slow but is performance an issue? Are you trying to bind 180000 rows to a datatable? Or are you saying you want to bind one row at a time!? You could use a stored procedure select the rows into a temp table with a cursor or for loop etc then use a data reader to step through each record and add it as a dararow or you could use an SqlAdapter and bind it to a datatable but I think that's what you've tried to do and you are running out of memory? What are you planning to do with this huge datatable? Use LINQ perhaps?

Kenneth Crismon · Answer

I have a different twist on this cursor issue. I have a table with an XML column some of the XML is really large and there are over 180000 rows of data. I wrote a small quick and dirty tool to pull this data back and export it as XML for future processing It all worked in my test ~1000 rows BUT when I bind the datatable to the SQL I run out of memory and it is when the XML row is being run through System.XMl.XmlDocument. This all makes sense during the bind operation the datatable creates a row and as part of the row bind for the XML column it rightly creates a XmlDocument object as a column. It runs out of memory though. So... I imidiately thought of a Cursor strategy where by I can bind one row at a time to a System.Data.DataRow and export the row that way. I do not know how to do that? Managed C# stored proc?? YUK!!! Will the data reader idea work does it actually ONLY bind one row at a time? I seem to remember that was done programatically andd the default was 25 rows. Anyway let me know what your thoughts are on this.

Vijaya Kadiyala · Answer

Cursors can cerate performance issues in SQL Server. So dont use it. What is your requirement? You can use Stored procedures to return the data to C# application!! Thanks -- Vijaya Kadiyala www.DotNetVJ.Com

Sreekanth · Answer

but there is no cahnce for u to select data fromdatabase inside while loop.without closing reader..so most of the cases ists apblm. yo avoid this. use different dataset.and retrivce data to dataset and continue.step

aru v · Answer

Hi In sql cursor is Forward only Record Set or its equivalent to a table but you are able to navigate only forward. In dotnet datareader does the same thing. Thanks aru

Sreekanth · Answer

i think u can do that using sqldatareader. joust read each row. for eg: db is my connection. db.openconnection(); select is my function for selecting any column f db.select(select * from table); while( db.reader.Read()) { do ur calculation ? } its not..actually cusor do in sql..but one can do it by using this