.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Fast read only, forward only cursor from managed code.

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server

I am refining some design after testing other parts of it, this is all going well but we need to now improve the data reading part, doing a bulk "SELECT *" was fine for testing, but is no good for massive tables.

We have a managaed stored proc, coded in C#, we use this to copy a table over a network to a special server of our own, this is an ocassional one-off operation.

I want to recode this stored proc, so that we enumerate a row at a time (or 'n' rows, e.g. 10, 20 etc) and send these over the network.

Doing this iteratively is the goal, I read a little recently about Server Side Cursors which seem ideal, especially for tables with 500,000 rows +

Does ADO.NET provide any managed classes for doing this?

We use .Net 3.5, Visual Studio 2008 and SQL Server 2008 R2.

Performance is key, you can assume that the database is not being updated when this operation is run.




View Complete Post

More Related Resource Links

ADO Recordset server-side forward-only, read-only cursor asynchronously cache all records?


We are using ADO, and currently create our recordsets using adUseClient and adOpenStatic since that way we can guarantee all the results will be loaded from the server and cached into the client memory without holding locks open on the server. 

However there are great performance benefits to adUseServer and adOpenForwardOnly! I have tried using adAsyncFetch and adAsyncExecute to load things in the background when necessary.

Unfortunately the trouble is that despite the value that I've set the CacheSize to, when the recordset is opened it does not load all of the records into memory. This is a concern since some operations on each record in the client application may take a while or even block waiting on user input. While this occurs, this holds locks on the tables in SQL Server until we read through all the records.

I have tried setting CacheSize to see if it would load everything all at once, but to no avail.

Since all the pieces seem to be here, I don't quite understand why the server-side cursor can't simply load everything into memory in the background the same way the (static) client-side cursor does.

I've tried some of the recordset's dynamic properties (like Maximum Open Records, which is readonly) to no avail either.

I know ADO is a bit dated now, but I would be overjoyed if anyone could guide me to a solution

CLR Inside Out: Best Practices For Managed And Native Code Interoperability


There are many factors to consider when building your app with both managed and native code. Find out how to employ interop and how to choose the interop that's right for you.

Jesse Kaplan

MSDN Magazine January 2009

CLR Inside Out: Marshaling between Managed and Unmanaged Code


Marshaling is an important part of .NET interop. It allows you to call into unmanaged code from managed code. This column will help you get started.

Yi Zhang and Xiaoying Guo

MSDN Magazine January 2008

Parallel Performance: Optimize Managed Code For Multi-Core Machines


While multi-core processors have become increasingly common, most applications still fail to take advantage of multiple cores. Here's an overview of creating multithreaded applications that benefit from these new processors.

Daan Leijen and Judd Hall

MSDN Magazine October 2007

Debug Leaky Apps: Identify And Prevent Memory Leaks In Managed Code


When is the .NET Garbage Collector unable to reclaim memory? The answer might surprise you. Stay tuned.

James Kovacs

MSDN Magazine January 2007

Smart Storage: Protect Your Data Via Managed Code And The Windows Vista Smart Card APIs


Smart cards are a compelling alternative to the reliance on passwords, which are the weakest link in authentication systems. Get the Windows smart card programming basics here.

Dan Griffin

MSDN Magazine November 2006

C++ at Work: Managed Code in Visual Studio 2005


Many of you are no doubt in the process of upgrading to Visual Studio® 2005, so I thought now would be a good time to relate some of my own experiences with the new compiler. What took me so long? Hey, I'm a retro kind of guy! Better late than never!.

Paul DiLascia

MSDN Magazine June 2006

Stay Alert: Use Managed Code To Generate A Secure Audit Trail


In today's security-conscious environments, a reliable audit trail is a valuable forensic tool The Windows Server 2003 operating system provides features that let you enable a wide range of applications to make use of auditing functionality. This article looks at auditing from the operating system perspective and describes a sample managed code implementation that will allow you to add auditing to your own server applications.

Mark Novak

MSDN Magazine October 2005

Security: Manipulate Privileges in Managed Code Reliably, Securely, and Efficiently


When the author was faced with implementing support for changing a security descriptor on an object, he noticed there was not support for that operation in .NET. So he devised two solutions to the problem: the first, simpler one, is tailored to the .NET Framework 1.1 and can be used today. The second solution incorporates several advanced features available only in the .NET Framework 2.0. Both are presented here.

Mark Novak

MSDN Magazine March 2005

Smart Tags: Realize the Potential of Office 2003 by Creating Smart Tags in Managed Code


While you may well be excited about the prospect of building managed smart tags, there is little information available to help you create them using .NET. In this article the author fills in the blanks. Along the way he discusses the Microsoft Office Smart Tag List XML schema, advanced managed smart tags for Office 2003 and Office XP, and deploying these features in an organization.

Ben Waldron

MSDN Magazine February 2005

Memory Lane: Rediscover the Lost Art of Memory Optimization in Your Managed Code


Managed applications rely on the garbage collector in the .NET Framework to allocate and clean up memory. The little CPU time spent performing garbage collection (GC) is usually a fair trade-off for not having to worry about memory management. But for applications in which CPU time and memory are precious resources, minimizing the time spent garbage collecting can greatly improve application performance and robustness. Find out how to manage memory all over again.

Erik Brown

MSDN Magazine January 2005

.NET Internals: Examine Running Processes Using Both Managed and Unmanaged Code


There are plenty of times when you need to get information on running processes, not the least of which is during performance tuning. Using the techniques in this article and special .NET classes you'll see how to get a process' ID, name, priority, number of threads, kernel handle, and memory consumption, as well as its user-mode, kernel-mode, and total elapsed running time and put them to use in a custom app called AssemblyBrowser.

Christophe Nasarre

MSDN Magazine October 2004

CLR Debugging: Improve Your Understanding of .NET Internals by Building a Debugger for Managed Code


With the release of .NET, the Microsoft scripting strategy has evolved. Script engines can now compile or interpret code for the Microsoft common language runtime (CLR) instead of integrating debugging capabilities directly into apps through Active Scripting. With that change comes a new set of published services for including debugging functionality in a custom host application. You'll see how to use the debugging services published in the .NET Framework to create a full-featured CLR debugger that allows you to set breakpoints, view call stacks, browse variables, view processes, enumerate threads, and perform other important debugging tasks.

Mike Pellegrino

MSDN Magazine November 2002

WMI and .NET: System.Management Lets You Take Advantage of WMI APIs within Managed Code


Visual Studio .NET and the Microsoft .NET Framework SDK provide a new set of APIs and tools that let you consume Windows Management Instrumentation (WMI) data and events from managed .NET applications. After presenting an overview of what's new for WMI in the .NET Framework and the Visual Studio .NET environment, the author provides an in-depth exploration of the Management Extensions in Visual Studio .NET for Server Explorer. These extensions help you develop management-aware software and come in handy in a variety of distributed application development scenarios.

Zina Pozen

MSDN Magazine May 2002

Visual C++ .NET: Tips and Tricks to Bolster Your Managed C++ Code in Visual Studio .NET


Developers using the Managed Extensions for C++ have more options than those using other languages because C++ is a lower-level language. However, this means an increase in code complexity. This article discusses a few of the more complex issues facing developers, such as operator overloading, managed types and unmanaged code, and boxing. Also covered are the is operator, the using statement, and string conversions. The author points out the flexibility of Managed Extensions for C++ and outlines the additional effort that is required for you to take advantage of its increased power and flexibility.

Tomas Restrepo

MSDN Magazine February 2002

C# and the Web: Writing a Web Client Application with Managed Code in the Microsoft .NET Framework


When the author wanted to build a middleware Web client to connect to other applications over the Internet, he realized that the XMLHttpRequest COM object was not sufficient for his purposes. In order to build a Web client using managed code, the author had to use the HTTPWebRequest and HTTPWebResponse classes provided by the Microsoft .NET framework. These classes are used in the sample project as a substitute for the less powerful XMLHttpRequest COM object, allowing the author to build a full-featured Web client. They also take advantage of all the benefits that the CLR and managed code have to offer.

Avi Ben-Menahem

MSDN Magazine September 2001

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend