Get Distinct Column Name in List

Question

I have a SQL Query like this

SELECT  DISTINCT CustomerName FROM  Customers

It is returning list of distinct customerNames,

How can I get this in list in C#

Currently I am getting the result in DataTable and then extract in list for only my required column.

private List<String> GetDistinctCustomerNames()
{
   var dataTable = new DataTable("ResultDataTable");
   using (var sqlCommand = new SqlCommand())
   {
      // set the connection for the commnad
       sqlCommand.Connection = sqlConnection;

       // assign the insert query as a text to the sql command
        sqlCommand.CommandText = "SELECT  DISTINCT CustomerName FROM  Customers";

        using (var sqlDataAdapter = new SqlDataAdapter())
        {
           sqlDataAdapter.SelectCommand = sqlCommand;
           dataTable.Load(sqlCommand.ExecuteReader());
        }

        if (dataTable.Columns.Contains("CustomerName"))
        {
           return (from DataRow dataRow in dataTable.Rows select dataRow["CustomerName"].ToString()).ToList();
        }

  return null;
}

but I don't feel it is a good solution.

If you add Dapper you could do something like this: IList MyList = Dapper.SqlMapper.Query( sqlConnnection, "SELECT DISTINCT CustomerName FROM Customers;").ToList(); — Richard Hansell, Jul 24 '14 at 13:25
You could receive the output as XML and use an XML parser to create the list for you. Perhaps more elegant though maybe not as efficient. — Pieter Geerkens, Jul 24 '14 at 13:26
Better options are suggested in this SO [question](http://stackoverflow.com/questions/5613192/c-sharp-fill-generic-list-from-sqldatareader) — Baga, Jul 24 '14 at 13:31
Thanks @Baga, The Answer by Darin Dimitrov in the link you posted was something I was looking for. — Pankaj, Jul 24 '14 at 13:43

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

You should more googling OR https://stackoverflow.com/a/19406319/2089368 ...

edited May 23 '17 at 11:45

Community

1
1

answered Jul 24 '14 at 13:26

itfake

112
1
5

score 0 · Answer 2 · answered Jul 24 '14 at 13:26

Instead of having your return for that I would do a foreach

ForEach(var element in DataTable.Rows)
   if(element.CustomerName != NULL)
      list.add(element.ToString()) 
      //To string is only needed if the db stores it as not an nvarchar or something like that

return list

score 0 · Answer 3 · 2014-07-24T13:46:21.417

0

After you get the list from the method, you can apply the below code.

 // Get distinct elements and convert into a list again.
List<string> distinct = list.Distinct().ToList();

edited Jul 24 '14 at 13:46

answered Jul 24 '14 at 13:35

I will get a Distinct List Only from SQL because of my Distinct Query, I wanted to avoid DataTable iteration. – Pankaj Jul 24 '14 at 13:42
@Pankaj. Try this link, please combine top 1 and distinct together.http://stackoverflow.com/questions/20086243/sql-server-top1-with-distinct – Jul 24 '14 at 17:49

Get Distinct Column Name in List

3 Answers3