locked
Queries RRS feed

  • Question

  • I have been using SQL queries that I write into the XSD file in a project then calling the queries from the code.  What I would like to know is if these queries can be written directly into the code of a class?  And if so how?  It seems to me that things would be a lot cleaner if you had your query handy where you could see it without having to open up the xsd file and finding it.

    gwboolean

    Friday, February 12, 2016 5:33 PM

Answers

  • Hi gwboolean,

    >>What I would like to know is if these queries can be written directly into the code of a class?  And if so how?

    Of cource yes. You could put your queries in the constructor of SqlCommand. Code below is for your reference.

    Dim connection As SqlConnection = New SqlConnection("yourConnectionString")
    Dim cmd As SqlCommand = New SqlCommand("Put your query here", connection)
    Try
        connection.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
    Catch ex As Exception
    
    Finally
        If (connection.State <> ConnectionState.Closed) Then
            connection.Close()
        End If
    End Try
    Best Regards,
    Li Wang

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 16, 2016 2:17 AM
  • In addition to DN

    That is the normal way. If you would open the complete part of the generated code from Linq to Entities, Linq to SQL or DataAdapters you would see that endless done. 


    Success
    Cor

    Tuesday, February 16, 2016 2:23 PM
  • I have been using SQL queries that I write into the XSD file


    Presumably that means you have been adding them to the TableAdapters using the DataSet designer.

    You can write them directly into the tableadapter class by doing something similar to the following:

    In Solution Explorer right click on the xsd file and select "Show Code"

    This will open a code file with something like this in it.

    Partial Class DrawingsDataSet
    End Class
    

    You can add code to this file in a partial class without fear that it will be overwritten by the designer should you make changes to the DataSet.

    To add a query to a TableAdapter first specify the namespace:

    Namespace DrawingsDataSetTableAdapters
    
    End Namespace
    


    You may well get a warning that the case of the name is different to that which already exists - if so fix it.

    Then within that name space create the partial class for your tableadapter:

    If you just type "partial class " then VS should list the adapters and you can select the one you want to add to.

    Then write your query - you could finish up with something like this:

    Partial Class DrawingsDataSet End Class

    Namespace DrawingsDataSetTableAdapters Partial Public Class ElementsTableAdapter Public Function GetElements(ByVal dataTable As ElementsDataTable, drawingID As Integer) As Integer Adapter.SelectCommand = New SqlCeCommand("SELECT * FROM Elements WHERE DrawingID = " & drawingID.ToString & " ORDER BY ZOrder", Connection) If ClearBeforeFill Then dataTable.Clear() End If Return Adapter.Fill(dataTable) End Function End Class

    End Namespace

    Tuesday, February 16, 2016 3:34 PM

All replies

  • Hi gwboolean,

    >>What I would like to know is if these queries can be written directly into the code of a class?  And if so how?

    Of cource yes. You could put your queries in the constructor of SqlCommand. Code below is for your reference.

    Dim connection As SqlConnection = New SqlConnection("yourConnectionString")
    Dim cmd As SqlCommand = New SqlCommand("Put your query here", connection)
    Try
        connection.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
    Catch ex As Exception
    
    Finally
        If (connection.State <> ConnectionState.Closed) Then
            connection.Close()
        End If
    End Try
    Best Regards,
    Li Wang

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 16, 2016 2:17 AM
  • In addition to DN

    That is the normal way. If you would open the complete part of the generated code from Linq to Entities, Linq to SQL or DataAdapters you would see that endless done. 


    Success
    Cor

    Tuesday, February 16, 2016 2:23 PM
  • I have been using SQL queries that I write into the XSD file


    Presumably that means you have been adding them to the TableAdapters using the DataSet designer.

    You can write them directly into the tableadapter class by doing something similar to the following:

    In Solution Explorer right click on the xsd file and select "Show Code"

    This will open a code file with something like this in it.

    Partial Class DrawingsDataSet
    End Class
    

    You can add code to this file in a partial class without fear that it will be overwritten by the designer should you make changes to the DataSet.

    To add a query to a TableAdapter first specify the namespace:

    Namespace DrawingsDataSetTableAdapters
    
    End Namespace
    


    You may well get a warning that the case of the name is different to that which already exists - if so fix it.

    Then within that name space create the partial class for your tableadapter:

    If you just type "partial class " then VS should list the adapters and you can select the one you want to add to.

    Then write your query - you could finish up with something like this:

    Partial Class DrawingsDataSet End Class

    Namespace DrawingsDataSetTableAdapters Partial Public Class ElementsTableAdapter Public Function GetElements(ByVal dataTable As ElementsDataTable, drawingID As Integer) As Integer Adapter.SelectCommand = New SqlCeCommand("SELECT * FROM Elements WHERE DrawingID = " & drawingID.ToString & " ORDER BY ZOrder", Connection) If ClearBeforeFill Then dataTable.Clear() End If Return Adapter.Fill(dataTable) End Function End Class

    End Namespace

    Tuesday, February 16, 2016 3:34 PM