Contents tagged with ADO.NET

  • Limit Rows In DataTable or DataSet

    I wrote some quick and dirty ADO.NET code to go against an RSS feed instead of a flat XML file today.  In the process I had to figure a way to limit the number of rows returned by the function, which returns a DataTable.  The simplest method I found was this one, which uses the DataTable.Select() method.  Using this technique, you could also pass in a sort parameter (second parameter to Select()) which would let you grab the top N rows from the DataTable after sorting it on whichever column you wished (syntax for sortexpression is "column" which defaults to ascending or "column DESC" for descending).  And of course you can also do a filter, etc., but I didn't … more

  • Using PowerShell to Automate a SQL Task

    I have a very large table in a SQL database that I need to clean up some old data on.  I've already copied all of the data to another table in another database with a different schema.  I have a legacy application that still uses the old data, but the data goes back for years, and now that it's in the new system, I'm willing to remove at least everything up until the new system started running alongside the old one. Deleting thousands of rows in SQL Server is an annoyingly difficult task on a disk-deficient server because inevitably the transaction log fills up and the delete statement fails.  If I had plenty of disk space, I could get round this issue through cleverness like … more

  • Managing XSD Files - if you must have them

    For an admin application I've been working on, we're using a third party reporting tool to serve up reports to our users over the Internet.  The reports have a nice designer that works with XSD files in Visual Studio.  Not particularly fond of XSDs as a means of data access, I thought I would limit the damage by having only one of them (and worse yet, the XSD file(s) have to be in the root of the website).  Unfortunately, by the time we were up to our 5th or 6th report and we had about 10 DataTables and TableAdapters in the XSD file, it was generating upwards of 10,000 lines of code.  This resulted in delays of 10 or more seconds just to open or save the XSD file, with … more

  • Force Refresh of DataSourceControl

    I had a situation where I was using a DataSourceControl (actually an LLBLGenProDataSource2,which inherits from DataSourceControl) bound to a grid, and I wanted the grid to refresh its contents whenever I added or deleted an item from it.  I discovered a neat trick noted here, which is that if you touch the SelectParameters collection of the DataSourceControl, it will cause it to DataBind in its PreRender stage.  So in my case, wherever I wanted it to rebind my grid, I would simply do this: PaymentDataSource.SelectParameters.Add("foo", "0"); // hack to force grid to rebind In my case I wasn't using any parameters, and this is probably not the *best* way to do this, but it's 2am and … more

  • LLBLGen v2 Released

    Frans announced yesterday that LLBLGen 2.0 has been released!  I’ve been using LLBLGen happily for several years now.  It is a very solid ORM tool and I’m looking forward to using the new version going forward.  You can read a list of new features and enhancements here.  If you haven’t used LLBLGen before, I recommend downloading the tool and the sample Pet Shop application that Frans has written which uses his tool under the covers, to see how it is put to use.  In less than an hour, you should have a good understanding of the product and its capabilities. more

  • Get Date From DateTime In SQL

    Something I need to do from time to time is get just the date part of a datetime value in SQL.  I found a cool way to do it on SQLJunkies today. select convert(varchar,DateColumn,101) The 101 means “mm/dd/yyyy” format, but there are a bunch of other codes you can use.  108 will return just the time “hh:mm:ss” for instance. Update: 101 includes 4 digit year 'yyyy'.  A code of 1 would apparently be "mm/dd/yy", according to user comments.  Thanks! more

  • New Site: DotNetSlackers

    A new site I'm using to push me updates of interesting blogs is  Sign up for their mail service, and you'll get a single daily email with that day's posts arranged by keyword category.  I've been pretty lax in firing up my RSS aggregator client, so this is a good way for me to at least make sure I'm not missing anything big in the .NET blogging world.  Another similar site worth checking out, if you're a VB-er, is, although I'm a bit late in announcing that one. more

  • Old Databases and Sql Query Notifications (cache invalidation)

    Julie and I were having issues with getting Sql Query Notifications to work for ASP.NET cache dependencies (for our DevConnections talks).  The trick was this: sp_dbcmptlevel yourdatabasename,90 Julie did most of the research on this one, with help from Sushil Chordia and Leonid Tsybert from Microsoft.  She has more on her blog, but I'm noting here so I might remember this next time. more

  • More on SQL Server Query Notifications

    I found this post by Rushi Desai on Invalidating cached result sets using SQL Server Query Notification.  It's from March so it's a bit out of date, but it has some working ASP.NET sample code (well, working in Beta 2, I presume, but pretty close for RTM). more

  • Working on Caching Presentation

    I'm working on my caching presentation for Fall ASP.NET Connections and a few user group presentations.  Those few who read my blog can enjoy some early looks at my research, which I'll detail here for my own future reference (and yours!). First of all I only recently found a great article on Query Notifications in ADO.NET 2.0 which goes into depth about how Query Notifications work in SQL Server 2005.  This architecture is used by the SqlCacheDependency in ASP.NET 2.0's Cache when it is working with a SQL Server 2005 database (an alternate technique is used with SQL7/2000).  It's a great article and one I'm definitely going to reference in my updated slide deck. … more