[dba-VB] Calling SP with params from C# sample

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Sat Nov 28 17:12:11 CST 2009


John,

Yes, that looks good with me.
AFAIS you are getting what is usually declared as abstract class, and it can
be inherited by custom classes, which would use their base abstract class's
"services"/utilities methods. I suppose you can go even a bit further by
combining sets of your standard parameters assignments into methods of your
base abstract class. BTW, all the methods/properties of your abstract class,
which are not planned to be called from outside of custom classes can be
declared as 'protected', all the local class level common variables (of all
kinds/types) can be also put with 'protected' declaration into your abstract
class...

--
Shamil 

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Sunday, November 29, 2009 1:29 AM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Calling SP with params from C# sample

Shamil,

I ended up creating a "stored procedure class".  This class hosts a sCmd
command object that can be 
programmed with the name of the SP, connection etc  but then included
wrapping the lines of code to 
create specific often used parameters into functions.

         /// <summary>
         /// The following code creates standard parameters used all of the
time in my stored procedures
         /// Basically just wrapper code so that I can call a function to
create specific parameters 
as needed
         ///
         /// It also allows me to completely standardize the names, data
type and length of 
parameters in the SPs etc.
         /// </summary>
         /// <param name="lstrTblName"></param>
         public void paramDBName(string lstrDBName)
         {
             sCmd.Parameters.Add(new SqlParameter( "@DBName",
SqlDbType.VarChar, 50));
             sCmd.Parameters["@DBName"].Value = lstrDBName;
         }
         public void paramTblName(string lstrTblName)
         {
             sCmd.Parameters.Add(new SqlParameter("@TblName",
SqlDbType.VarChar, 50));
             sCmd.Parameters["@TblName"].Value = lstrTblName;
         }
         public void paramErrorDesc()
         {
             sCmd.Parameters.Add(new SqlParameter("@ErrorDesc",
System.Data.SqlDbType.VarChar, 4000));
             sCmd.Parameters["@ErrorDesc"].Direction =
System.Data.ParameterDirection.Output;
         }

Then I can do things like:

             //
             //sp_AZOut_AZExportToOrdered_Append
             //
             sp = new StoredProcedure(myConnection, 
"_aDataMaster.dbo.sp_AZOut_AZExportToOrdered_Append");
             sp.paramDBName(lstrDBName);   //@DBName varchar(50) input
             sp.paramTblName(lstrTblName);   //@TblName varchar(50) input
             sp.paramErrorDesc();            //@ErrorDesc varchar(100)
output
             sp.paramErrorNo();              //@ErrorNo int output,
             sp.paramReturnValue();          //RETURN
             retValue = sp.ExecuteSP();

I believe I am on my way now.

Thanks again,

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> John --
> 
> Tons of code for C# (or VB.NET) is not an issue at all. And for C#/VB.Net
> programmers also. You'll get adapted to that reality soon, I believe.

 

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4645 (20091128) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru
 




More information about the dba-VB mailing list