从模块调用SQL命令 [英] calling SQL commands from a module

问题描述

我想将我的SQL命令放入一个模块中，以便每当我有一条要执行的命令时，我都可以设置几个变量并调用该模块，从而避免重复代码并简化逻辑.像:

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 :

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

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

这是一个好主意/不寻常的主意/标准做法吗?！我认为对于简单的命令来说似乎很好(在我的这个特定项目中，什么也不会做得太复杂)，但是在传递命令和处理代码方面我遇到了麻烦.值回&如果允许的话，则为第四.

我的意思是，我可以创建一个或多个模块以不接受任何额外的命令，也可以不完全接受1个，完全不接受2个，依此类推，但是我不确定如何允许传递可变数量的参数.已处理.

有什么想法吗?

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 ?

推荐答案

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 )

查看存储过程和SQL参数

Look at stored procedures and SQL parameters

将主SQL代码放在模块中是一个坏主意:如果数据库要求发生变化，则需要重新编译和重新分发您的应用，这可能会成为严重的头痛.您最好在SQL Server本身上使用视图和存储过程，特别是对于将反复调用的核心组件.

如果确实选择使用应用程序端SQL，请使用参数查询.一段像
的代码

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

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

将针对每个不同的myLeague分别进行编译和存储.这最终需要花费很多时间:需要时间来对其他数十个具有不同名称的DELETE FROM Leagues进行排序，需要时间来编译另一个变体，需要时间将新版本存储在内存中以防再次使用，以及需要清除时间当已编译的缓存已满时，将其清除.

而是使用

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

"DELETE FROM Leagues Where LeagueName = @LeagueName"

并使用SqlParameter对象传递您的参数.您的SQL Server将只有一种变体可以编译，并且将一遍又一遍地使用该变体.

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.

这篇关于从模块调用SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！