Display multiple values from database to gridview

Question

I have the following class

public class FileDetails
{
public string FileName { get; set; }
public string FileDescription { get; set; }
public string FileLocation { get; set; }
}
public FileDetails() { }

public FileDetails(string fileName, string fileDescription, string fileLocation)
{
    FileName = fileName;
    FileDescription = fileDescription;
    FileLocation = fileLocation;
}

I have a function which reads these values from DB. Once the values have been read I display them on a gridview. The problem I am having is the values get displayed, however the FileName is repeated over and over. I have 5 different FileName which are stored in the database rather than displaying all 5 it displays the first FileName 5 times.

The function which reads the values looks like this

public FileDetails FilesInfo()
{
    string userName = HttpContext.Current.User.Identity.Name;

    using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT FileName,FileDescription,FileLocation FROM Files WHERE UserName=@UserName"))
        {
            cmd.Parameters.AddWithValue("UserName", userName);
            cmd.Connection = connection;
            connection.Open();
            using(SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    FileName = reader["FileName"].ToString();
                    FileDescription = reader["FileDescription"].ToString();
                    FileLocation = reader["FileLocation"].ToString();
                }
            }
        }
    }
    return new FileDetails(FileName,FileDescription,FileLocation);
}

And at the Page_Load in a different file I have the following code to display it on the gridview

 FileDetails info = new FileDetails();
    if (info.FilesInfo().FileLocation != null)
    {
        string[] filePaths = Directory.GetFiles(info.FilesInfo().FileLocation);

        List<FileDetails> files = new List<FileDetails>();
        foreach (string filePath in filePaths)
        {
            string filename = Path.GetFileName(filePath);
            files.Add(new FileDetails()
            {
                FileName = filename,
                FileDescription = info.FileDescription,
                FileLocation = filePath
            });
        }
        GridView1.DataSource = files;
        GridView1.DataBind();
    }
}

Thanks in advance for all your help and support

Answer 1

The FilesInfo method should be changed to return a collection of FileDetails objects, I used a generic list here, but you could use a dataset, or datatable, or array, or whatever you like. Something like this:

public List<FileDetails> FilesInfo()
{
string userName = HttpContext.Current.User.Identity.Name;
List<FileDetails> FileDetailsList = new List<FileDetails>();

using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("SELECT FileName,FileDescription,FileLocation FROM Files WHERE UserName=@UserName"))
    {
        cmd.Parameters.AddWithValue("UserName", userName);
        cmd.Connection = connection;
        connection.Open();
        using(SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                FileDetails f = new FileDetails();
                f.FileName = reader["FileName"].ToString();
                f.FileDescription = reader["FileDescription"].ToString();
                f.FileLocation = reader["FileLocation"].ToString();

                FileDetailsList.Add(f);
            }
        }
    }
}
return FileDetailsList;
}

Then in your page load, you can do something like this:

List<FileDetails> info = FilesInfo();
        if(info.Count > 0)
        {
            GridView1.DataSource = info;
            GridView1.DataBind();
        }