将SQL语句复制到可用表单中 [英] Duplicate SQL statement into a usuable form
问题描述
我正在尝试使用我的核对表框来填充所选内容的T.或S.字段。这是我第一次尝试使用追加格式。我需要一些帮助来解决这个问题。我在下面放置了原始的SQL语句。
私人 Sub sompare_other_tables()
Dim sqlcon As 新 SqlConnection( sqlstatement)
Dim cb 作为 新系统.Text.StringBuilder( MERGE INTO)
Dim tableName As String = ListBox1.SelectedItems(Convert.ToString(tableName))
Dim tablename2 作为 字符串 = ListBox2.SelectedItems(Convert.ToString(tablename2) )
cb.AppendFormat( [{0}],tableName.Replace(< span class =code-string> ], ]]))
cb.Append( as T )
cb.AppendFormat( 使用[{0}] ,tablename2.Replace( ], ]]))
cb.Append( 作为S)
' cb.AppendFormat(,[{0 }] nvarchar(max)NULL,tableName.Replace(],]]))
cb.AppendFormat( T上的。,ta bleName.Replace( ], ]]))
cb.Append(Convert.ToString( =))
cb.Append( [{ 0}] S。,tablename2.Replace( ], ]]))
For 每个项在 CheckedListBox1.CheckedItems
Dim columnName As String = Convert.ToString(item)
cb .AppendFormat( on T。 + columnName.Replace( ], ]]))
cb.Append( = )
cb.Append( S。 + columnName.Replace( ], ]]))
下一步
Dim sql As String = cb.ToString()
sqlcon.Open()
Dim cmd As SqlClient.SqlCommand
cmd = 新 SqlClient.SqlCommand(sql,sqlcon)
MessageBox.Show(cmd.CommandText)
cmd.ExecuteNonQuery()
sqlcon.Close()
这是原始SQL语句:
合并 进入 table1 T 使用 [ table ] as S on T. [Last Name] = S. [姓氏] 和 T. [名字] = S. [名字]
当 匹配 然后 更新 设置 T. [出生] = S. [出生];
DELETE T1 FROM [表] T1 JOIN [table1] T2 ON T1。[姓氏] = T2。 [姓氏] AND T1。[名字] = T2。[名字];
我尝试过的事情:
没试过多少,因为我对附录知之甚少。如果有更好的方式来写这个陈述我很乐意听到它。
如果你看看这行
cb.AppendFormat( on T。,tableName.Replace( ], ]]))您没有在字符串中为您正在使用的值添加占位符。我希望T上有
cb.AppendFormat( > {0 } ,tableName.Replace( ], ]]))同样,你在行
cb.AppendFormat( on T。 + columnName.Replace( ], ]]))所以使用
AppendFormat
没有任何意义,你可能只是用了追加
。避免字符串连接,而是这样做:cb.AppendFormat( on T. {0 },columnName.Replace( ], ]]))现在考虑一行
cb.Append( Convert.ToString( =))
没有必要
Convert.ToString()
...=
已经是一个字符串
使用AppendFormat时,您可以在格式列表中包含多个变量,以使代码更整洁。例如,您可以替换cb.AppendFormat( [{0}],tableName.Replace( ], ]]))
cb.Append( as T)
cb.AppendFormat( using [{ 0}],tablename2.Replace( ], ]]))
cb.Append( 作为S)与
cb.AppendFormat( [{0}]为T,使用[{1}]作为S,tableName.Replace( ], ]]),tablename2.Replace( ], ]]))如果没有列表框中任何数据的好处,对其余部分进行评论有点困难。例如,不清楚为什么要用
]替换
等]
]
由于在更新和删除sql中都重复了ON子句,我会将其创建为单独的StringBuilder
,以便您可以重复使用它。 br />
这是我提出的代码,但是从列表控件插入数据后你必须检查输出以确保它是正确的:
Dim cb2 As 新 StringBuilder( MERGE INTO)
cb2 .AppendFormat( {0}为T,使用{1}作为S,tableName,tablename2)
Dim sbOn As New StringBuilder( ON)
Dim andRequired As 布尔 = 错误
对于 每个项在 CheckedListBox1.CheckedItems
如果 andRequired 然后
sbOn.Append( AND)
结束 如果
Dim columnName As String = item.ToString()
sbOn.AppendFormat( on T。[{0}] = S. [{1}],columnName,columnName)
andRequired = True
下一步
cb2.Append(sbOn.ToString())
cb2.Append(< span class =code-string> 匹配时更新集T. [Birth] = S. [Birth];)
cb2.AppendFormat( DELETE T1 FROM [{0}] T1 JOIN [{1}] T2 ,tablename2,tableName)
cb2.Append(sbOn.ToString())
Debug.Print(cb2.ToString())
注意我用过的技巧,以确保我在
ON
中没有额外的AND
条款
I am trying to use my checklist boxes to fill in T. or S. fields by what is selected. This is my first time of attempting to use append format. I am needing some help figuring this out. I have placed the original SQL statement below.
Private Sub sompare_other_tables()
Dim sqlcon As New SqlConnection("sqlstatement")
Dim cb As New System.Text.StringBuilder("MERGE INTO")
Dim tableName As String = ListBox1.SelectedItems(Convert.ToString(tableName))
Dim tablename2 As String = ListBox2.SelectedItems(Convert.ToString(tablename2))
cb.AppendFormat(" [{0}] ", tableName.Replace("]", "]]"))
cb.Append("as T")
cb.AppendFormat(" using [{0}] ", tablename2.Replace("]", "]]"))
cb.Append("As S ")
' cb.AppendFormat(", [{0}] nvarchar(max) NULL", tableName.Replace("]", "]]"))
cb.AppendFormat("on T.", tableName.Replace("]", "]]"))
cb.Append(Convert.ToString(" = "))
cb.Append("[{0}] S.", tablename2.Replace("]", "]]"))
For Each item In CheckedListBox1.CheckedItems
Dim columnName As String = Convert.ToString(item)
cb.AppendFormat("on T." + columnName.Replace("]", "]]"))
cb.Append(" = ")
cb.Append("S." + columnName.Replace("]", "]]"))
Next
Dim sql As String = cb.ToString()
sqlcon.Open()
Dim cmd As SqlClient.SqlCommand
cmd = New SqlClient.SqlCommand(sql, sqlcon)
MessageBox.Show(cmd.CommandText)
cmd.ExecuteNonQuery()
sqlcon.Close()
This is the original SQL statement:
Merge into table1 as T using [table] as S on T.[Last Name] = S.[Last Name] and T.[First Name] = S.[First Name]
When Matched then Update Set T.[Birth] = S.[Birth];
DELETE T1 FROM [table] T1 JOIN [table1] T2 ON T1.[Last Name] = T2.[Last Name] AND T1.[First Name] = T2.[First name];
What I have tried:
Haven't tried to much, since I don't know much about appends. if there is a better way to write the statement I would love to hear it.
If you have a look at the line
cb.AppendFormat("on T.", tableName.Replace("]", "]]"))You have not put a placeholder in the string for the value you are using. I would expect something like
cb.AppendFormat("on T.{0}", tableName.Replace("]", "]]"))Similarly, you have used string concatenation in the line
cb.AppendFormat("on T." + columnName.Replace("]", "]]"))so there was no point in using
AppendFormat
, you could have just usedAppend
. Avoid string concatenation and do this instead:cb.AppendFormat("on T.{0}", columnName.Replace("]", "]]"))Now consider the line
cb.Append(Convert.ToString(" = "))There is no need for the
Convert.ToString()
..." = "
is already a string
When using AppendFormat you can include several variables in the format list to make the code a little tidier. For example you could replacecb.AppendFormat(" [{0}] ", tableName.Replace("]", "]]")) cb.Append("as T") cb.AppendFormat(" using [{0}] ", tablename2.Replace("]", "]]")) cb.Append("As S ")with
cb.AppendFormat(" [{0}] as T using [{1}] as S", tableName.Replace("]", "]]"), tablename2.Replace("]", "]]"))Without the benefit of any of the data in your list boxes it's a little difficult to comment on the rest of it. For example it is unclear why you are replacing
]
with]]
etc
As the ON clause is repeated in both the update and the delete sql I would create that as a separateStringBuilder
so you can re-use it.
This is the code I came up with, but you would have to check the output to make sure it is correct after plugging in your data from the list controls:
Dim cb2 As New StringBuilder("MERGE INTO ") cb2.AppendFormat("{0} as T using {1} as S", tableName, tablename2) Dim sbOn As New StringBuilder(" ON ") Dim andRequired As Boolean = False For Each item In CheckedListBox1.CheckedItems If andRequired Then sbOn.Append(" AND ") End If Dim columnName As String = item.ToString() sbOn.AppendFormat("on T.[{0}] = S.[{1}]", columnName, columnName) andRequired = True Next cb2.Append(sbOn.ToString()) cb2.Append("When Matched then Update Set T.[Birth] = S.[Birth];") cb2.AppendFormat("DELETE T1 FROM [{0}] T1 JOIN [{1}] T2", tablename2, tableName) cb2.Append(sbOn.ToString()) Debug.Print(cb2.ToString())Note the trick I've used to make sure I don't get an extra
AND
in theON
clause
这篇关于将SQL语句复制到可用表单中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!