Sql reader returning DBnull for every row

Question

I am trying to display pictures from my SQLSEVER database on my website. My users have a picture field with a picture datatype. If the picture column is null, then I want the picture displayed to be a egg.jpg, but right now for every person their picture is egg.jpg, even if they have a picture in the database. Here is my method.

 public string getImageUrl()
      {
    System.Data.SqlClient.SqlConnection sc = new              System.Data.SqlClient.SqlConnection();

    sc.ConnectionString = "Server =MRCOMPUTER2\\SQLEXPRESS; Database = WBL;Trusted_Connection=Yes;";
    sc.Open();
    System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
    insert.Connection = sc;
    insert.CommandText = "SELECT profilePicture from SystemUser";

    insert.ExecuteNonQuery();
    SqlDataReader reader = insert.ExecuteReader();
    string url = "";
    while (reader.Read())
    {

        if ( !DBNull.Value.Equals(reader[0]))
        {
            url = "data:Image / png; base64," + Convert.ToBase64String((byte[])reader[0]);

        }

        else {

            url = "images/egg.jpg";
        } 

    }
    return url; 

}

Answer 1

Your code returns the image for the last user in your table.

Here:

insert.CommandText = "SELECT profilePicture from SystemUser";

you select all users from the table (not just the one you currently show). Then:

while (reader.Read())
{
    ...
    url = ...
    ...
}

you re-assign url inside every iteration of your while loop. This is semantically equivalent to:

url = ... /* The value determined from the last record of the reader. */

Thus, all your users show the same image - the one of the last user in your table.

Answer 2

You SELECT statement is attached into a ExecuteNonQuery?

Take it off.

Just perform the READER statement...

Answer 3

Try this:

        static void Main(string[] args)
        {
            string connectionString = "Server=.;Database=AA;Trusted_Connection=True;";

            /*
            CREATE TABLE [dbo].[SystemUser]
            (
                [ProfilePicture] [varbinary](max) NULL
            )
            */

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = @"
INSERT [AA].[dbo].[SystemUser] ([ProfilePicture]) VALUES (@ProfilePicture);
INSERT [AA].[dbo].[SystemUser] ([ProfilePicture]) VALUES (NULL);
";

                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                byte[] bytes = File.ReadAllBytes(@"1.jpg");
                command.Parameters.AddWithValue("@ProfilePicture", bytes);

                connection.Open();
                command.ExecuteNonQuery();
            }


            DataSet ds = new DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = @"
SELECT TOP 1000 [ProfilePicture] FROM [AA].[dbo].[SystemUser];
";

                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                connection.Open();
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds);
            }

            var rows = ds.Tables[0].Rows.Cast<DataRow>();
            foreach (DataRow row in rows)
            {
                byte[] bytes = row.Field<byte[]>(0);
                if (bytes != null)
                {
                    string fileName = Guid.NewGuid().ToString("N") + ".jpg";
                    File.WriteAllBytes(fileName, bytes);
                }
            }
        }

Answer 4

Can you try using the name of the column such as

var Val = (String)reader["column name"];

Also, try something like this to test:

while (reader.Read())
            {

                var testVal = reader.GetString(0);
                Var testVal2 = reader.GetString(1);