Mark Breen
marklbreen at gmail.com
Mon Nov 2 10:18:13 CST 2009
Hello John, First of all, good luck. Secondly, you mentioned a few days ago that you referred to yourself as an SQL Wannabe, well given the work you are currently doing, I think you can no longer refer to yourself as a wannabe, you are using SQL, and no doubt will be pushing it to it's limits. *SPROCs* There are loads of samples of code and I have nothing to hand here that will assist much, but I think you will be surprised how easy it is. You will get better exmaples, but as an overview you just need to create a connection - usually referred to as cnn. create a cmd to 'hold' the sproc you wish to call join the cnn to the sproc specify the type of the cmd (no results returned, or records returned or a few other types) then you add objects which are parameters, you need one each for each param in the sproc, you can even add a param that will be referred to as OUTPUT instead of INPUT - these output params can be used to pass back single values in additional or instead of, the actual result set. you can also receive back the result code of the sproc. then you just execute the cmd object. Once you get a few lines of code that handle the above, you can copy and paste for the next five years. There are a few other things that I am not as accustomed to, but when you know that you need to read in a result set, you can use a data reader. Someone else can advise on that. when you create the cnn object, usually people pull the actual string from a app.config file, so watch out for that snipit also. BTW, FWIW, other than for binding to grids or combo's, I never used a dataset in code. *BCP* You mentioned BCP, well I have no used that since SQL 6.5, but what I have used in the SSIS in SQL 2005. It is worth taking a look, you may find that you can do a lot of your work in SSIS. It can branch and call sprocs and import and export all as a package. It can be incredibly fast and would be worth take an hour or two and read up on what you can do with SSIS. *Embeding C#* Lastly, other than an article I read in MSDN magazine, where a guy wanted to do some hashing or encryption in side SQL Server I never heard of anyone using / executing code in side SQL Serer. For performance that you need, I would, with my limited skills, keep as much of the work in TSQL - IE Sprocs, and by doing so, you will keep the mass movement of data within the db. I would love to see if anyone here actually ever used the feature of including C# Code within the db. I know that this is not samples, but you can do this, and once you get a set of lines of code that 1) create the cnn 2) create the cmd 3) add the params 4) run the sproc you will not look back. Just keep as much work as possible inside sql server. *Scheduled Tasks* Finally, once last thing to mention, if you have a smtp server, it is so, so each to create scheduled tasks, that run at predefined times and then mail you when they are finished. Running the heavy jobs nightly, can take a lot of the hard work from your daily routines. Again you can manage all this with very little actually code and have working routines in minutes. HTH. Mark 2009/11/2 jwcolby <jwcolby at colbyconsulting.com> > I am embarking on the task of running the "DB from hell" from C#. I have > several dozen stored > procedures that perform various tasks and I currently run many of them from > Access using a single > procedure provided by Charlotte. I need a similar function written in C# > (preferably) or VB.Net. > > ATM I have an Access database which has two specific forms, bound to two > tables. These two forms > each execute a set of SPs which build temp tables of data to be exported to > an external process, > build indexes to make the processes faster, BCP the data out to CSV files, > and then the reverse on > the way back in. > > I have another Access database that creates an order for the same client. > Same kind of thing, temp > tables created, indexes, fields added and so forth. > > My intention is to port each of these processes to C#. The reason is > simply that C# has a couple of > huge advantages over VBA, such as child threads and built-in capabilities > in the framework that I > have to really scrounge to make work in VBA. > > The first thing I need though is code that can execute a stored procedure, > pass parameters to it, > retrieve parameters back etc. With demo code preferably. > > Also I would like to open a conversation about how using .Net from inside > of SQL Server (2005) > works. Does anyone on the list use C# or VB.Net from inside of SQL Server? > Where is the code > stored? How to do you call it? What do you use it for? > > TIA, > > -- > John W. Colby > www.ColbyConsulting.com > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > >