Queries

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

Answer 1

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.

Answer 2

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

Answer 3

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