calling SQL commands from a module

Question

4.00/5 (1 vote)

See more:

, +

I want to put my SQL commands into a Module, so that whenever I have a command to execute, I can set up a couple of variables and call the Module, therefore avoiding repeat code and streamlining my logic. Something like :

VB

mySqlExpression = "DELETE FROM Leagues Where LeagueName = '" & myLeague & "'"
            mySqlToReturn = "N" 'No return is needed
            myVbExp = ""
            Call RunSQL(mySqlExpression, mySqlToReturn, myVbExp)

VB

Module SQLCmd
    Public Function RunSQL(mySqlLine As String, myRetOpts As String, myVbStr As String)
        mySqlCmd = New SqlCeCommand(mySqlLine, mySqlConn)
        If mySqlConn.State = ConnectionState.Closed Then mySqlConn.Open()
        Dim mySqlRdr As SqlCeDataReader = mySqlCmd.ExecuteReader()
        mySqlCmd.ExecuteNonQuery()
        If myRetOpts = "N" Then 
            'do nothing
        Else
            '??? Extra commands here
        End If
        mySqlConn.Close()
    End Function
End Module

Is this a good idea / an unusual idea / standard practice ?!? I think it seems fine for simple commands (and in this particular project of mine nothing too complicated is going to be done) but I am having trouble when it comes to passing commands & values back & forth if I allow it to be generic.

What I mean is, I can create one or more modules to accept no extra commands or 1 exactly, 2 exactly, etc, but I'm not sure how I would go about allowing a variable number of parameters to be passed & processed.

Any thoughts ?

Posted 19-Jul-12 23:19pm

Gary Heath

Add a Solution

2 solutions

Solution 1

VB

dim sqlcmd as new sqlclient.sqlcommand( strCommand, strConnection )

dim param as sqlclient.sqlparameter

param = new sqlClient.sqlparameter( "@Param", int )
param.value = value
sqlcmd.parameters.add( param )

Look at stored procedures and SQL parameters

Posted 20-Jul-12 4:00am

tiggerc

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gregory Gadow · Accepted Answer · 2012-07-20T04:16:00

It is kind of a bad idea to put your main SQL code in a module: if your database requirements change you will need to recompile and redistribute your app, which can become a major headache. You are better off using views and stored procedures on the SQL server itself, especially for core components that will get called repeatedly.

If you do chose to go with using app-side SQL, USE PARAMETERIZED QUERIES. A piece of code like

VB

"DELETE FROM Leagues Where LeagueName = '" & myLeague & "'"

will get compiled and stored separately for EACH different myLeague. This ends up taking a lot of cycles: time to sort through the dozens of other DELETE FROM Leagues with different names, time to compile yet another variation, time to store that new version in memory in case it gets used again, and time to clear out the compiled cache when it gets full.

Instead, use

VB

"DELETE FROM Leagues Where LeagueName = @LeagueName"

and pass your parameter in with a SqlParameter object. Your SQL Server will have only one variant to compile, and will use that one variant over and over again.