[dba-VB] c# - manipulating datasets in memory

jwcolby jwcolby at colbyconsulting.com
Fri Feb 19 14:07:47 CST 2010

"There's more ways to kill a cat (than choking it to death on butter)" as my momma used to say.

I have never used an sproc to return a dataset.  That would certainly solve the problem but it may 
not be flexible enough.

The client sometimes specifies sets of records.  "Age in (2,3,4)" will be set 1, "Age in (5,6,7)" 
will be set 2 etc.  All exported data has keycodes inserted in a specific position in the fixed 
width file.  Sometimes the keycodes change based on the set.  Sometimes the sets have to go in a 
single file, sometimes in separate files.

You get the picture.

In all cases, the seed names need to get in the file once.  If the sets have different keycodes but 
all go in a single file, then the seeds go in the only file.  If the sets have different keycodes 
but go in separate files then the seeds go in each file.

It is a heck of a job getting it all straight and all out into the correct files.  In the past I 
always used Access.  Access has certain advantages, such as the ability to generate queries right in 
Access and then use those queries for the export.  In the past I would copy an access template 
database into the target directory (where the files are going) and then open it, prepare queries, 
export, modify, export and so forth.

I am now writing a C# program to handle all things for this client and was looking to build this 
export piece as one of the modules in this shiny new application.  It is kinda sorta working but my 
"messing around" has been moved backwards into the SQL Server order database instead of in Access. 
That is OK, but I really want to try and get this whole thing much more point-and-click.

As an example, I am told as part of the order that Group A has keycode W, Group B has keycode X etc. 
  ATM I am having to manually write update queries back in SQL Server to update the actual order 
table, setting the keycode field to the right thing for the correct group.  That kind of thing.  I 
am trying to move that stuff out of the order database and into code / form in C#.

In my Access database FE for doing this, I had controls where I could specify values, keycodes etc. 
and then I build SQL on-the-fly.  VBA is much more friendly than C# for writing / troubleshooting, 
especially when I am running this all on a x64 server and don't have C# edit and continue.

John W. Colby

David McAfee wrote:
> Why not pass the seeds to a SPROC and call the sproc instead of the view?
> CREATE PROCEDURE stpMySProc (@SeedName1, @SeedName2) AS
> SELECT @SeedName1 AS SeedName1, @SeedName2 AS SeedName2, A.*
> FROM vwMyView AS A
> Now, instead of calling vwMyView, call stpMySproc.
> D

More information about the dba-VB mailing list