I guess this should be straightforward.
I have a table in SQL with the following columns:
[CaseName],[Rev],[S1],[S2],[S3],[S1Im],[S2Imbef],[S2Imaft]
the last three columns are images. I want to add a complete row to the table from C#.
I have tried something like:
var filename1 = Directory.GetFiles(path, "tempgraph.jpg").SingleOrDefault();
string strp = @"insert into dbo.RunProc ([CaseName],[Rev],[S1],[S2],[S3],[S1Im],[S2Imbef],[S2Imaft]) values ('" + CaseData.Name + "','" + CaseData.RevN + "',1,0,0," + filename1 + "','" + null + "','" + null + ")";
SqlCommand cmd2 = new SqlCommand(strp, con);
cmd2.ExecuteNonQuery();
This will not work because filename1 is a string. right?
Anyway, what is the best way to write the complete row in the database?
Advertisement
Answer
I think what you are expecting is that the filename1 variable will contain the contents of the file that you want to insert into the database, but that is not the case.
What you have in the filename1 variable is simply a string representing the path to the file.
In order to insert the file’s data into the database, you need to first open the file and load the binary data that the file contains into memory. Then, you can properly insert the data into the database.
You already have the path, so you could do this to load the file contents:
byte[] imageBytes = System.IO.File.ReadAllBytes(filename1);
But then for inserting it, better to use a parameterized query (so you don’t have to worry about formatting the bytes, etc):
SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand("insert into dbo.RunProc ([CaseName],[Rev],[S1],[S2],[S3],[S1Im],[S2Imbef],[S2Imaft]) values (@caseName, @revN, 1 ,0, 0, @image, null, null)");
cmd2.Parameters.AddWithValue("caseName", CaseData.Name);
cmd2.Parameters.AddWithValue("revN", CaseData.RevN);
cmd2.Parameters.AddWithValue("image", imageBytes);
cmd2.ExecuteNonQuery();
I didn’t actually run this code because it would require a bunch of setup work, but this is the basic idea.