5

I have a C#/ASP.net project has included a database that I have developed that includes a nice and convenient View that would be handy to use.

I have the SQL connection setup to a SQL Server 2008 DB I created. It seems as though it is connecting fine, but I don't understand how to actually use the View that I created without hard coding the query into the program (been told this is bad sometimes?).

This is my connection I setup:

    SqlConnection conn = null;
    conn = new SqlConnection("Data Source=raven\\sqlexpress;Initial Catalog=ucs;Integrated Security=True;Pooling=False");
    conn.Open(); 
    SqlCommand command = new SqlCommand(query, conn);

Basically, I need some code to query using this View. I can see the View and look at the results that would be obtained, but not access it in the program! The view is named "UserView". Help is much appreciated!

ImGreg
  • 2,946
  • 16
  • 43
  • 65

2 Answers2

13

You could use something like the following. But it's usually considered evil to put hardcoded SQL commands into .Net code. It's much better and safer to use stored procedures instead.

This should get you started. You can modify it to use stored procedures by

  1. changing the command.CommandType to indicate it's a stored proc call
  2. And adding the proper parameters to the command that your SP needs.
  3. Change command.CommandText to the name of your SP, thus eliminating the hardcoded SQL.

sample code below:

using (SqlConnection connection = new SqlConnection("Data Source=raven\\sqlexpress;Initial Catalog=ucs;Integrated Security=True;Pooling=False"))
{
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT * from your_view WHERE your_where_clause";

        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // process result
                reader.GetInt32(0); // get first column from view, assume it's a 32-bit int
                reader.GetString(1); // get second column from view, assume it's a string
                // etc.
            }
        }
    }
}
dcp
  • 54,410
  • 22
  • 144
  • 164
  • Thanks! This looks right, didn't realize I could just select directly from the view. However, is there an assembly reference that i should be using to allow me to use ResultSet and ExecuteQuery? Both are coming up unknown. I've added "using System.Data.SqlClient", anything else I am missing? – ImGreg Jul 06 '11 at 18:50
  • @Greg - I think ResultSet was from an earlier edit, as I changed it to used SqlDataReader instead. Does the above code not compile for you? I believe the System.Data assembly should resolve these classes for you. – dcp Jul 06 '11 at 18:55
  • @dcp - Okay there we go, noticed the first edit, but missed the next. Compiles fine, haven't tested, but it looks good though. Thanks! Just to confirm, the while loop will iterate through the rows in the view and the code inside is how you grab each columns' data? – ImGreg Jul 06 '11 at 19:01
  • @Greg - Yes, the reader.GetInt32(0), reader.GetString(1) etc. are just examples, you need to put in whatever is proper for the columns coming back in your view. But again, I would recommend using the SP, it'll be better in the long run. – dcp Jul 06 '11 at 19:03
  • @dcp - you're are correct sir. I was using this method just for starting myself off, but with the sheer number of queries I'm going to need for this program, SP is definitely the way to go. Thanks for the help! – ImGreg Jul 06 '11 at 19:16
-1

Using VS2013 add a new DataSet to your project. Drag your View from the Server Explorer to the DataSet Design Surface.

user2584621
  • 2,305
  • 2
  • 15
  • 9
  • Please provide more detail to your answer. – dwitvliet Jul 25 '14 at 17:20
  • This answer is very lazy. Add a new DataSet how exactly, from the file menu, tools menu, or by writing a specific line of code? Rather not answer if you will provide half truths. – Sizons Feb 05 '17 at 07:42