Search code examples
c#asp.netjsondatatableasmx

fill datatable column with database values as images

I want to retrieve list of users names from database and display it in datatable as images and when hover on any image, it displayed the user name. My code display developer name column as a list of developers separated by comma. enter image description here

Here is my code:

Project class

public class Projects
    {
        public int project_number { get; set; }
        public string project_name { get; set; } 
        public string developer_name { get; set; }
        public  Image team_members { get; set; }
    }

json

columns: [
     { 'data': 'project_name' },
     { 'data': 'developer_name' },
     {
        "data": "team_members",
        "render": function (data, type, full, meta) {
        var markup = '';
        for (var i = 0; i < data; i++) {
           markup += '<img src="images/img.jpg" class="avatar" title="'++'">' }
       return markup;
       }
   }             
]

asmx:

    public void GetProjects()
        {
            SqlConnection conn = new SqlConnection("connection statement");
            conn.Open();

            var projects = new List<Projects>();

            using (var cmd = new SqlCommand("getProjects", conn) { CommandType = CommandType.StoredProcedure })
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        var Project = new Projects
                        {
                            project_number = Convert.ToInt32(dr[1].ToString()),
                            project_name = dr[2].ToString(),
                        };
                        projects.Add(Project);

                        using (SqlCommand com = new SqlCommand("SELECT user_name FROM Users WHERE user_id IN (SELECT user_id FROM User_Projects WHERE project_number = @project_number)", conn))
                        {
                            com.Parameters.AddWithValue("@project_number", Project.project_number);
                            List<string> developerNames = new List<string>();

            using (SqlDataReader rdrDev = com.ExecuteReader())
                            {
                                if (rdrDev.HasRows)
                                {
                                    while (rdrDev.Read())
                                    {
developerNames.Add((string)rdrDev["user_name"]);
                                    }
                                }
                            }
                   Project.developer_name = string.Join(", ", developerNames);
                        }
                    }
                }
                var js = new JavaScriptSerializer();
                Context.Response.Write(js.Serialize(projects));
            }

        }
Solution

  • You can use parameter render in column declaration. Split developers name and process it to correct generate markup:

    {
   "data": "developer_name",
   "render": function (data, type, full, meta) {
       var markup = '';
       var developers = data.split(',');
       for (var i = 0; i < developers.length; i++) {
           markup +='<img src="images/img.jpg" class="avatar" title="' + developers[i] + '">'
       });
       return markup ;
   }
}