ASP.NET Stored Procedure Call insert VarBinary into sql

Question

I have a string that is data bytes base64EncodedString from iOS which is an extremely long string

let imageStr = imageData.base64EncodedString()

I am calling a .NET Method from my ios that will call a stored procedure to insert these bytes into the database.

Here is my .NET Method, I have the data type set to VarBinary

public string PostLandGradingImages(List<Images> landingCells)
{
    try
    {
        using (connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand("PostLandGradingImages", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                for (int i = 0; i < landingCells.Count; i++)
                {
                    command.Parameters.Clear();

                    SqlParameter parameter1 = new SqlParameter("@Job_No", SqlDbType.VarChar);
                    parameter1.Value = landingCells[i].jobNo;
                    parameter1.Direction = ParameterDirection.Input;
                    command.Parameters.Add(parameter1);

                    SqlParameter parameter2 = new SqlParameter("@Image", SqlDbType.VarBinary);
                    parameter2.Value = landingCells[i].imageBytes;
                    parameter2.Direction = ParameterDirection.Input;
                    command.Parameters.Add(parameter2);

                    command.ExecuteNonQuery();
                }
            }
        }
    }
    catch (Exception e)
    {
        return e.Message.ToString();
    }

    return "All Good";
}

Here is my Image Class, notice my imageBytes is defined as a byte[]:

public class Images
{
    public string jobNo { get; set; }
    public byte[] imageBytes { get; set; }
}

The column I am inserting into is defined as varbinary(MAX)

and here is my stored procedure:

ALTER PROCEDURE [dbo].[PostLandGradingImages] 
    -- Add the parameters for the stored procedure here
    @Job_No varchar(MAX) = NULL,
    @Image varbinary(MAX) = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO LandGradingImages (Job_No, ImageBytes) VALUES (@Job_No, @Image)
END

My problem is nothing is getting inserted, I am getting this error in my catch:

Object reference not set to an instance of an object.

My question is, what am I doing wrong? Should I not be sending base64EncodedString or am I not setting my class right? or my db column?

I tried this:

byte[] bytes = System.Convert.FromBase64String(landingCells[i].imageBytes);

SqlParameter parameter2 = new SqlParameter("@Image", SqlDbType.VarBinary, 800000);
parameter2.Value = bytes;
parameter2.Direction = ParameterDirection.Input;
command.Parameters.Add(parameter2);

Still does not work :( and I changed imageBytes to string.

Answer 1

I modified your code a little to the method below. It creates a new CommandType.StoredProcedure for every Image. Also the results are returned per image, so you can see which ones failed. In your method, if you have 10 images, and the 9th failed, you would not know that.

public List<Images> PostLandGradingImages(List<Images> landingCells)
{
    //create a connection to the database
    using (SqlConnection connection = new SqlConnection(Common.connectionString))
    {
        //loop all the images
        for (int i = 0; i < landingCells.Count; i++)
        {
            //create a fresh sql command for every Image
            using (SqlCommand command = new SqlCommand("PostLandGradingImages", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                //add the parameters
                command.Parameters.Add("@Job_No", SqlDbType.VarChar).Value = landingCells[i].jobNo;
                command.Parameters.Add("@Image", SqlDbType.VarBinary).Value = landingCells[i].imageBytes;

                try
                {
                    //open the connection if closed
                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }

                    //execute the stored procedure
                    command.ExecuteNonQuery();

                    //set the save result to the image
                    landingCells[i].saveResult = true;
                }
                catch (Exception ex)
                {
                    //handle error per Image
                    landingCells[i].errorMessage = ex.Message;
                }
            }
        }
    }

    return landingCells;
}

In order to track the save result per image I've added two properties to the Image class, but this can be done in various other ways as well.

public class Images
{
    public string jobNo { get; set; }
    public byte[] imageBytes { get; set; }

    public bool saveResult { get; set; }
    public string errorMessage { get; set; }
}

A simple test was done with the following code. None of them gave a NullReference Error. Even with both properties being null, a database entry was still made.

//create a new list with Images
List<Images> landingCells = new List<Images>();

//add some dummy data
landingCells.Add(new Images() { jobNo = null, imageBytes = null });
landingCells.Add(new Images() { jobNo = "Job 1", imageBytes = null });
landingCells.Add(new Images() { jobNo = null, imageBytes = new byte[10000] });
landingCells.Add(new Images() { jobNo = "Job 2", imageBytes = new byte[10000] });

//send the images to be saved
landingCells = PostLandGradingImages(landingCells);

//loop all the images to check the result
for (int i = 0; i < landingCells.Count; i++)
{
    if (landingCells[i].saveResult == false)
    {
        //display the result for each failed image
        Label1.Text += landingCells[i].errorMessage + "<br>";
    }
}

If there is still a NullReference error, that means that your List landingCells itself is null, or an Image object within that List is null (in which case it should never have been added to the List in the first place imho). You can change the snippet easily to check for that.

Answer 2

Consider batching the queries in a transaction. Also you should validate the values provided to the method to make sure that you can call the stored procedure correctly.

public int PostLandGradingImages(List<Images> landingCells) {
    int count = 0;
    using (var connection = new SqlConnection(connectionString)) {
        connection.Open();
        //Transaction to batch the actions.
        using (var transaction = connection.BeginTransaction()) {
            foreach (var image in landingCells) {
                if (valid(image)) {//validate input properties.
                    try {
                        using (SqlCommand command = connection.CreateCommand()) {
                            command.CommandType = CommandType.StoredProcedure;
                            command.CommandText = "PostLandGradingImages";
                            command.Parameters
                                .Add("@Job_No", SqlDbType.VarChar, image.jobNo.Length)
                                .Value = image.jobNo;
                            command.Parameters
                                .Add("@Image", SqlDbType.VarBinary, image.imageBytes.Length)
                                .Value = image.imageBytes;
                            count += command.ExecuteNonQuery();
                        }
                    } catch {
                        //TODO: Log error
                    }
                }
            }
            if (landingCells.Count == count) {
                transaction.Commit();
            }
        }
    }
    return count;
}

private bool valid(Images image) {
    return image != null && String.IsNullOrWhiteSpace(image.jobNo)
        && image.imageBytes != null && image.imageBytes.Length > 0;
}