[dba-VB] SQL Server 2005: Running stored procedures from C# / VBA

jwcolby jwcolby at colbyconsulting.com
Mon Nov 2 11:51:47 CST 2009


Mark,

 > First of all, good luck.

Uh-Ohhh... is that a warning?  ;)

I refer to myself as a wannabe simply because to this point SQL Server has been a small part of my 
day.  As a sole proprietor my day contains so many different kinds of work that I find it hard to be 
an expert in something as complex as SQL Server.  There are so many people on these lists that spend 
all day every day in SQL Server that I will truly never be anything other than a wannabe - from 
their perspective.

I have always been more programmer than anything.  I come at the world from the perspective of "how 
can I write code to make my job easier".  Luckily I have enough programming background that I can 
generally accomplish that.  Right now I am trying to switch my environment of choice from Access to 
C#, and BOY IS THAT TOUGH.  Programming is always about spending enough time doing it that the 
syntax becomes second nature.  C# is nowhere close to second nature yet.  I can taste the power 
though, so tantalizing.  ;)

I am using the Idera free backup stuff.  Very powerful, recommended.  It took me a couple of hours 
to install on my two servers and write some stored procedures to make them work. Now I really want 
to be able to select one specific database to backup or restore.  To do that I have to be able to:

See/select a database on a server.
Run a stored procedure that does the backup.

How simple is that eh?

I just spent the morning learning about the DMO object, the listview control and writing the code to 
read out the databases and display them in the listview.  I still have to find and get working the 
code that executes the stored procedure.

The problem is not that this is hard, it is that I am not fluent in C# yet.

But I am getting there, sloooowly but surely.

John W. Colby
www.ColbyConsulting.com


Mark Breen wrote:
> 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
>>
>>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 



More information about the dba-VB mailing list