Need sql for creating view set in asp.net c#

Question

I am using sqlconnection. I want to create a view and select from the view set.

Like I have shown below I have created a view called vwtopic.. from this I need to select distinct values of a column.

I cannot put in one easy statment because i need distinct values of topic column only and need to order by another column datetime..

So I am first creating a view where I am ordering by datetime and from this I am selecting distinct topic.

Problem is I am able to create a view set, but from this I am not able to select the distinct topic data to a SqlDataAdapter.. I frankly dont know the syntax.. I have not tried this before..

First part:

SqlConnection con = new SqlConnection("server=xxxx;database=wbsd;user id=***;password=***;");


SqlCommand add = new SqlCommand("CREATE VIEW vwtopic AS SELECT * FROM sr_topic_comment ORDER BY datetime DESC", con);

try
{                                             
  add.Connection.Open();
  add.ExecuteNonQuery();
  add.Connection.Close();                                             
}
catch (System.FormatException)
{
}

Second part:

String sqlcmd = "SELECT DISTINCT topic FROM vwtopic WHERE owner='" + owner + "'";
SqlDataAdapter adap = new SqlDataAdapter(sqlcmd,con);

Answer 1

Instead of creating Views in Code, use the "WITH" statement or Sub-queries this should meets your needs:

WITH [vwtopic] AS (
 SELECT * -- I recommend using each column name
 FROM [sr_topic_comment] 
 -- not sure if ORDER BY is allowed here:
 -- ORDER BY [datetime] DESC
) 
SELECT DISTINCT [topic] FROM [vwtopic] -- add WHERE, ORDER BY

Answer 2

Since the view you are creating does not have any filters defined, selecting distinct values from the view is equivalent to selecting distinct values from the table. When selecting distinct values, you can only order by those values (not the datetime column as you're attempting here). Therefore, you can do:

SqlCommand cmd = new SqlCommand("SELECT DISTINCT topic FROM sr_topic_comment WHERE owner = @owner", con);
cmd.Parameters.Add(new SqlParameter(@owner, SqlDbType.Varchar, 25));
cmd.Parameters["@owner"].Value = owner;
DataSet ds = new DataSet();
using (SqlDataAdapter adap = new SqlDataAdapter(cmd)) {
  adap.Fill(ds);
}

This will give you a DataSet filled with the distinct values from the table that meet the filter criteria (owner == the supplied owner).

Answer 3

I found the answer below will do the trick for me...

I am filling the result in dataset and calling my required column 'topic'..

SELECT DISTINCT topic,datetime FROM sr_topic_comment WHERE owner='sr' ORDER BY datetime DESC

Thank you very much for your inputs.. I learnt about WITH clause today.. cheers..