替代T-SQL作为关键字。

Please see the code below:

请看下面的代码:

Imports System.Data.SqlClient
Imports System.Configuration

    Public Class Form1
        Private _ConString As String
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim objDR As SqlDataReader
            Dim objCommand As SqlCommand
            Dim objCon As SqlConnection
            Dim id As Integer
            Try
                _ConString = ConfigurationManager.ConnectionStrings("TestConnection").ToString
                objCon = New SqlConnection(_ConString)
                objCommand = New SqlCommand("SELECT Person.URN, Car.URN FROM Person INNER JOIN Car ON Person.URN = Car.URN AND PersonID=1")
                objCommand.Connection = objCon
                objCon.Open()
                objDR = objCommand.ExecuteReader(ConnectionState.Closed)
                Do While objDR.Read
                    id = objDR("URN") 'line 19
                Loop
                objDR.Close()
            Catch ex As Exception
                Throw
            Finally

            End Try

        End Sub


End Class

Please see line 19. Is it possible to do something similar to this:

请参见线19。是否有可能做类似的事情:

objDR("Person.URN")

When I do: objDR("URN") it returns the CarURN and not the Person URN. I realise that one solution would be to use the SQL AS keyword i.e.:

当我这样做时:objDR(“URN”)返回的是CarURN，而不是Person URN。我意识到一个解决方案是使用SQL作为关键字。

SELECT Person.URN As PersonURN, Car.URN AS CarURN FROM Person INNER JOIN Car ON Person.URN = Car.URN AND PersonID=1

and then: objDR("PersonURN")

然后:objDR(“PersonURN”)

However, I want to avoid this if possible because of the way the app is designed i.e. it would involve a lot of hard coding.

但是，如果可能的话，我想避免这一点，因为这款应用程序的设计方式就是需要大量的硬编码。

3 个解决方案

#1

You can look up the column by index, rather than by name. Either of the following would work:

您可以通过索引查找列，而不是按名称查找。以下两种方法都适用:

id = objDR(0)
id = objDR.GetInt32(0)

Otherwise, your best bet is the "As" keyword or double-quotes (which are the ansi standard) to create an alias.

否则，最好的方法是“As”关键字或双引号(即ansi标准)来创建别名。

#2

In your case, becuase of the inner join, you do not need to include both in the select clause, as equality is required, so only include either one.

在您的案例中，由于内部连接的原因，您不需要在select子句中包含这两个部分，因为需要相等，所以只包含其中一个。

So either

所以要么

SELECT Person.URN 
FROM   Person INNER JOIN 
       Car ON Person.URN = Car.URN AND PersonID=1

或

SELECT Car.URN 
FROM   Person INNER JOIN 
       Car ON Person.URN = Car.URN AND PersonID=1

#3

If you already are changing the query, and cannot for some reason change the other code, then create a view with the column names as you like, then select from that view.

如果您已经更改了查询，并且由于某些原因不能更改其他代码，那么可以使用您喜欢的列名创建一个视图，然后从该视图中选择。

#1