C# Get correct filpath string for SQL update Statement

I have created a small WinForms Application (.Net 4.8, SQL Server 18). Now I want to update a picture in my database. For that I have created this method:

public void UpdateBild(string picturepath, int id)
{
    string bildupdate = string.Format("UPDATE {0} set bild = (SELECT  BulkColumn FROM OPENROWSET(BULK  N'@pic', SINGLE_BLOB) AS x) where persid = @id", Klassen.Data.Gettabellenname());

    try
    {
        SqlVerb();
        com.CommandText = bildupdate;
        com.Parameters.AddWithValue("@pic", picturepath);
        com.Parameters.AddWithValue("@id", id);
        com.ExecuteNonQuery();
    }
    catch (SqlException sqex)
    {

    }
    catch (Exception ex)
    {

    }
    finally
    {
        DBVerbindung.DBVerb.scon.Close();
    }
}

​x
 
public void UpdateBild(string picturepath, int id){    string bildupdate = string.Format("UPDATE {0} set bild = (SELECT  BulkColumn FROM OPENROWSET(BULK  N'@pic', SINGLE_BLOB) AS x) where persid = @id", Klassen.Data.Gettabellenname());​    try    {        SqlVerb();        com.CommandText = bildupdate;        com.Parameters.AddWithValue("@pic", picturepath);        com.Parameters.AddWithValue("@id", id);        com.ExecuteNonQuery();    }    catch (SqlException sqex)    {​    }    catch (Exception ex)    {​    }    finally    {        DBVerbindung.DBVerb.scon.Close();    }}​

The picturepath will be set like that from a textbox:

picturepath = tbDateiPfad.Text;

 
picturepath = tbDateiPfad.Text;​

and it will be passed to the method like:

DBVerbindung.SQLStatements upbild = new DBVerbindung.SQLStatements();
upbild.UpdateBild(picturepath, id);

 
DBVerbindung.SQLStatements upbild = new DBVerbindung.SQLStatements();upbild.UpdateBild(picturepath, id);​

If the method gets executed, I get the error ‘Picture not found’.

I think there is a problem with the file path because, if I execute the UPDATE statement directly in database with the filepath (copy from dircetory search) it is working fine.

So can you please help me, how I can pass the filepath from textbox in correct form to the paramter for the SQL query.

Answer

The filename must be specified as a string literal instead of parameter for OPENROWSET so you’ll need to specify that value in the SQL statement string:

string bildupdate = string.Format("UPDATE {0} set bild = (SELECT BulkColumn FROM OPENROWSET(BULK N'{1}', SINGLE_BLOB) AS x) WHERE persid = @id;", Klassen.Data.Gettabellenname(), picturepath);

try
{
    SqlVerb();
    com.CommandText = bildupdate;
    com.Parameters.Add("@id", SqlDbType.Int).Value = id;
    com.ExecuteNonQuery();
…

 
string bildupdate = string.Format("UPDATE {0} set bild = (SELECT BulkColumn FROM OPENROWSET(BULK N'{1}', SINGLE_BLOB) AS x) WHERE persid = @id;", Klassen.Data.Gettabellenname(), picturepath);​try{    SqlVerb();    com.CommandText = bildupdate;    com.Parameters.Add("@id", SqlDbType.Int).Value = id;    com.ExecuteNonQuery();…​

Note the table name and file name values must be obtained from trusted sources and validated since the values are not parameterized. Also, avoid AddWithValue.

Advertisement

Answer