如何简化一些代码 [英] how streamline some codes

问题描述

我可以简化这些代码

is it possible for me to streamline these codes

if station1, update qty in stock_record table and insert bal into bincard table 
if station2, update qty1 in stock_record table and insert bal1 into bincard table 
if station3, update qty2 in stock_record table and insert bal2 into bincard table 

以下完整代码

full code below

If cboStation.Text = "STATION1" Then
            Dim strQuery13 As String
            conn.Open()
            strQuery13 = "UPDATE stock_record SET qty= '" & txtStkQty.Text & " ' where stkcode = '" & txtStkCode.Text & "'"
            recordinsert = New SqlCommand(strQuery13, conn)
            recordinsert.ExecuteNonQuery()
            conn.Close()

            Dim strQuery3 As String
            conn.Open()
            strQuery3 = "insert into bincard(stkcode,transdate,stkin,stkout,bal,cond,station,staff)Values('" & txtStkCode.Text & "','" & dtpInvDate.Value.ToString("yyyy/MM/dd") & "','0','" & txtQty.Text & "','" & txtStkQty.Text & "','SALES','" & cboStation.Text & "','" & frmMenu2.Label2.Text & "')"
            recordinsert = New SqlCommand(strQuery3, conn)
            recordinsert.ExecuteNonQuery()
            conn.Close()

        ElseIf cboStation.Text = "STATION2" Then
            Dim strQuery13 As String
            conn.Open()
            strQuery13 = "UPDATE stock_record SET qty1= '" & txtStkQty.Text & " ' where stkcode = '" & txtStkCode.Text & "'"
            recordinsert = New SqlCommand(strQuery13, conn)
            recordinsert.ExecuteNonQuery()
            conn.Close()

            Dim strQuery3 As String
            conn.Open()
            strQuery3 = "insert into bincard(stkcode,transdate,stkin,stkout,bal,bal1,cond,station,staff)Values('" & txtStkCode.Text & "','" & dtpInvDate.Value.ToString("yyyy/MM/dd") & "','0','" & txtQty.Text & "','0','" & txtStkQty.Text & "','SALES','" & cboStation.Text & "','" & frmMenu2.Label2.Text & "')"
            recordinsert = New SqlCommand(strQuery3, conn)
            recordinsert.ExecuteNonQuery()
            conn.Close()

        ElseIf cboStation.Text = "STATION3" Then
            Dim strQuery13 As String
            conn.Open()
            strQuery13 = "UPDATE stock_record SET qty2= '" & txtStkQty.Text & " ' where stkcode = '" & txtStkCode.Text & "'"
            recordinsert = New SqlCommand(strQuery13, conn)
            recordinsert.ExecuteNonQuery()
            conn.Close()

            Dim strQuery3 As String
            conn.Open()
            strQuery3 = "insert into bincard(stkcode,transdate,stkin,stkout,bal,bal2,cond,station,staff)Values('" & txtStkCode.Text & "','" & dtpInvDate.Value.ToString("yyyy/MM/dd") & "','0','" & txtQty.Text & "','0','" & txtStkQty.Text & "','SALES','" & cboStation.Text & "','" & frmMenu2.Label2.Text & "')"
            recordinsert = New SqlCommand(strQuery3, conn)
            recordinsert.ExecuteNonQuery()
            conn.Close()
        End If

推荐答案

您要查找的短语是代码重构 [ ^ ]



这是我如何看待你的代码的演练...



当您查看这样的代码时，请尝试查看相似之处，或者在这种情况下，有多少差异。考虑移动它们，这样您就不必重新输入相同的代码行。例如，步骤1将注意到唯一的区别在于strQuery13和strQuery3所以整洁的代码看起来像这样

The phrase you are looking for is Code Refactoring[^]

Here is a walkthrough of how I looked at your code...

When you look at code like this try to see the similarities, or in this case, how few differences there are. Consider moving them so you don't have to keep retyping the same lines of code. For example step 1 would be to notice that the only differences are in strQuery13 and strQuery3 so tidy the code up to look like this

Dim strQuery13 As String
Dim strQuery3 As String

        If cboStation.Text = "STATION1" Then
            strQuery13 = "UPDATE stock_record SET qty= '" & txtStkQty.Text & " ' where stkcode = '" & txtStkCode.Text & "'"
            strQuery3 = "insert into bincard(stkcode,transdate,stkin,stkout,bal,cond,station,staff)Values('" & txtStkCode.Text & "','" & dtpInvDate.Value.ToString("yyyy/MM/dd") & "','0','" & txtQty.Text & "','" & txtStkQty.Text & "','SALES','" & cboStation.Text & "','" & frmMenu2.Label2.Text & "')"

        ElseIf cboStation.Text = "STATION2" Then
            strQuery13 = "UPDATE stock_record SET qty1= '" & txtStkQty.Text & " ' where stkcode = '" & txtStkCode.Text & "'"
            strQuery3 = "insert into bincard(stkcode,transdate,stkin,stkout,bal,bal1,cond,station,staff)Values('" & txtStkCode.Text & "','" & dtpInvDate.Value.ToString("yyyy/MM/dd") & "','0','" & txtQty.Text & "','0','" & txtStkQty.Text & "','SALES','" & cboStation.Text & "','" & frmMenu2.Label2.Text & "')"

        ElseIf cboStation.Text = "STATION3" Then
            strQuery13 = "UPDATE stock_record SET qty2= '" & txtStkQty.Text & " ' where stkcode = '" & txtStkCode.Text & "'"
            strQuery3 = "insert into bincard(stkcode,transdate,stkin,stkout,bal,bal2,cond,station,staff)Values('" & txtStkCode.Text & "','" & dtpInvDate.Value.ToString("yyyy/MM/dd") & "','0','" & txtQty.Text & "','0','" & txtStkQty.Text & "','SALES','" & cboStation.Text & "','" & frmMenu2.Label2.Text & "')"
        End If

        conn.Open()
        recordinsert = New SqlCommand(strQuery13, conn)
        recordinsert.ExecuteNonQuery()
        conn.Close()

        conn.Open()
        recordinsert = New SqlCommand(strQuery3, conn)
        recordinsert.ExecuteNonQuery()
        conn.Close()

这使得它更容易阅读。



现在让我们回到那些SQL查询...首先要注意的是你让自己容易受到攻击通过直接在查询中插入文本来进行SQL注入。您应该使用 sql参数 [ ^ ]。这是第一个查询作为示例

That makes it all a bit easier to read.

Now let's go back to those sql queries ... first thing to notice is that you have left yourself vulnerable to sql injection by inserting text directly into your queries. You should use sql parameters[^] instead. Here's the first query as an example

strQuery13 = "UPDATE stock_record SET qty= @StkQty where stkcode = @StkCode"
recordinsert.Parameters.Add(New SqlParameter("StkQty", txtStkQty.Text))
recordinsert.Parameters.Add(New SqlParameter("StkCode", txtStkCode.Text))

当你做所有这些时，你会看到更多的东西站点之间很常见。

在strQuery13中，唯一不同的是你要更新的列的名称，所以让我们做一个参数

When you do all of them you will see even more things that are common between the stations.
In strQuery13 the only thing that is different is the name of the column that you are updating, so lets make that a parameter as well

recordinsert.Parameters.Add(New SqlParameter("colname", "qty"))

表示strQuer对于所有三种情况，y13都可以来自If语句块并且只是

which means that strQuery13 can come out of the If statement-block and just be

strQuery13 = "UPDATE stock_record SET @colname= @StkQty where stkcode = @StkCode"

。 />


当我看到strQuery3时，我注意到它的三个版本非常相似，但是对于第1站你只需设置列[bal]，对于第2站你设置列[ bal] = 0并为[bal1]输入一个值，对于Station 3，设置列[bal] = 0并为[bal2]输入一个值。



这意味着对于Station 1 bal1和bal2将为NULL;对于Station 2，bal2将为NULL，而对于Station 3，bal1将为NULL。当您尝试读取这些值时，这可能会导致您出现问题。更好的方法是始终填充所有三列 - 使用所需的值或使用0.因此strQuery3变为

for all three cases.

When I look at strQuery3 I notice that all three versions of that are very similar, but for station 1 you just set column [bal], for Station 2 you set column [bal] = 0 and put a value in for [bal1] and for Station 3 you set column [bal]=0 and put a value in for [bal2].

This means that for Station 1 bal1 and bal2 will be NULL; for Station 2 bal2 will be NULL and for Station 3 bal1 will be NULL. This might cause you problems later when you are trying to read these values back. A better approach would be to always populate all three columns - either with the required value or with 0. So strQuery3 becomes

strQuery3 = "insert into bincard(stkcode,transdate,stkin,stkout,bal,bal1,bal2,cond,station,staff)"
strQuery3 += "Values('@StkCode,@InvDate,'0',@Qty,@bal,@bal1,@bal2,'SALES',@Station,@Label2)"

这让我回过头来看看strQuery13 ......乍一看我以为我可以用qty，qty1和qty2做类似的事情，但后来我意识到这是一个UPDATE语句，所以这些列可能已经有了一个值。因此，做同样的事情不是一个好主意。



这是本练习的重点，不要试图一次做所有事情，但要回去并再看看。诀窍是知道何时是停止的好时机。除非你需要最先进的性能（甚至可能不是那样！），否则不要为了保存几行而在代码中失去清晰度。



我到了这个阶段......

That made me go back and have a look at strQuery13 ... at first glance I thought I could do something similar with qty, qty1 and qty2, but then I realised that is an UPDATE statement so those columns could already have a value in them. Doing the same thing would therefore NOT be a good idea.

This is the point of this exercise, don't try to do everything at once, but do go back and have another look. The trick is to know when is a good time to stop. Don't lose clarity in your code for the sake of saving a couple of lines unless you need cutting-edge performance (and possibly not even then!)

This got me to this stage ...

Dim bal As Double = 0   'balance for Station 1 - Must be iniitialised to 0
Dim bal1 As Double = 0  'balance for Station 2 - Must be iniitialised to 0
Dim bal2 As Double = 0  'balance for Station 3 - Must be iniitialised to 0
Dim colname As String

If cboStation.Text = "STATION1" Then
    colname = "qty"
    bal = txtStkQty.Text
ElseIf cboStation.Text = "STATION2" Then
    colname = "qty1"
    bal1 = txtStkQty.Text
ElseIf cboStation.Text = "STATION3" Then
    colname = "qty2"
    bal2 = txtStkQty.Text
End If

'Query13
Dim strQuery13 As String = "UPDATE stock_record SET @colname= @StkQty where stkcode = @StkCode"
recordinsert = New SqlCommand(strQuery13, conn)

recordinsert.Parameters.Add(New SqlParameter("StkQty", txtStkQty.Text))
recordinsert.Parameters.Add(New SqlParameter("colname", colname))
recordinsert.Parameters.Add(New SqlParameter("StkCode", txtStkCode.Text))

conn.Open()
recordinsert.ExecuteNonQuery()
conn.Close()

'Query3
Dim strQuery3 As String = "insert into bincard(stkcode,transdate,stkin,stkout,bal,bal1,bal2,cond,station,staff)"
strQuery3 += "Values('@StkCode,@InvDate,'0',@Qty,@bal,@bal1,@bal2,'SALES',@Station,@Label2)"
recordinsert = New SqlCommand(strQuery3, conn)

recordinsert.Parameters.Add(New SqlParameter("StkCode", txtStkCode.Text))
recordinsert.Parameters.Add(New SqlParameter("InvDate", dtpInvDate.Value.ToString("yyyy/MM/dd"))
recordinsert.Parameters.Add(New SqlParameter("Station", cboStation.Text))
recordinsert.Parameters.Add(New SqlParameter("Label", frmMenu2.Label2.Text))
recordinsert.Parameters.Add(New SqlParameter("Qty", txtQty.Text))
recordinsert.Parameters.Add(New SqlParameter("Bal", bal))
recordinsert.Parameters.Add(New SqlParameter("Bal1", bal1))
recordinsert.Parameters.Add(New SqlParameter("Bal2", bal2))

conn.Open()
recordinsert.ExecuteNonQuery()
conn.Close()

现在这甚至都不会编译...我有一些问题超过余额和例如，在原始sql中将数量设置为字符串。在这里可能有一两个奇怪的拼写错误，但希望我能让你朝正确的方向走下去

Now this won't even compile ... I have some issues over balances and quantities being set up as strings in your original sql for example. And there may be the odd spelling mistake or two in here but hopefully I've set you off in the right direction

这篇关于如何简化一些代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！