将SQL语句复制到可用表单中 [英] Duplicate SQL statement into a usuable form

查看:99
本文介绍了将SQL语句复制到可用表单中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试使用我的核对表框来填充所选内容的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 used Append. 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 replace

        cb.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 separate StringBuilder 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 the ON clause


这篇关于将SQL语句复制到可用表单中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆