Insert a file (pdf, doc) into mssql database

Question

I would like to insert the files (doc and pdf) from a hdd to a mssql database. I have never done something like it, but I did some code, which is not good. I did a varbinary(MAX) data type of the table in the database, and I am struggling to do the insert statement.

So far I did:

                    string VnosDatoteke = "INSERT INTO Obvestilo VALUES(@IDObvestila)";                                          
                    SqlCommand cmd1 = new SqlCommand(VnosDatoteke, povezava);                    
                    cmd1.Parameters.Add("@IDObvestila", SqlDbType.Int);                    
                    cmd1.Parameters["@IDObvestila"].Value = maxID;

                    byte[] DobiDatoteko = File.ReadAllBytes(myFullPath);
                    string VnosDatoteke2 = "INSERT INTO Obvestilo FILES('" + textBoxFileName.Text + "') VALUES(@Vsebina)";

                    SqlCommand cmd2 = new SqlCommand(VnosDatoteke2, povezava);
                    cmd2.Parameters.Add(new SqlParameter("@Vsebina", DobiDatoteko));                   
                   
                    try
                    {
                        cmd1.ExecuteScalar();
                        cmd2.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

This is not such a good idea. Anyone has any better silution?

And I need to insert:

1. a new ID

2. the file it self.

Answer 1

hi,
why are you using 2 different insert statements?
1 will suffice, where you load the 1st parameter with the Id value and the 2nd with the byte array including the pdf (or doc or whatever) content..
and just perform a single ExecuteNonQuery..
but, you should even consider to use the "new" FILESTREAM feature, new in SQL Server 2008, which gives you the very same benefits and "saves" you "space" regarding the 4gb limitation of SQLExpress as filestream related data is not included in the calculation.. please keep on reading at http://technet.microsoft.com/en-us/library/bb933993.aspx
regards

---

http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools

Answer 2

I did:

byte[] DobiDatoteko = File.ReadAllBytes(myFullPath);
                string VnosDatoteke = "INSERT INTO Obvestilo VALUES(@IDObvestila, @Številka, @Ime, @Pomembnost, @Vsebina)";
                SqlCommand cmd1 = new SqlCommand(VnosDatoteke, povezava);
                cmd1.Parameters.Add("@IDObvestila", SqlDbType.Int);
                cmd1.Parameters.Add("@Številka", SqlDbType.Int);
                cmd1.Parameters.Add("@Ime", SqlDbType.VarChar, 50);
                cmd1.Parameters.Add("@Pomembnost", SqlDbType.VarChar, 50);
                cmd1.Parameters.Add("@Vsebina", SqlDbType.VarBinary);
                cmd1.Parameters["@IDObvestila"].Value = maxID;
                cmd1.Parameters["@Številka"].Value = textBoxŠtevilka.Text;
                cmd1.Parameters["@Ime"].Value = textBoxImeDatoteke.Text + "." + textBoxKončnica.Text;
                cmd1.Parameters["@Pomembnost"].Value = comboBoxPomembnost.GetItemText(comboBoxPomembnost.SelectedItem);
                cmd1.Parameters["@Vsebina"].Value = DobiDatoteko;
                cmd1.ExecuteNonQuery();

And it works :)

Good enough?

Btw, you mentioned new FILESTREAM feature. How would the code look if you consider these data I have? 

Answer 3

hi,
the code will not change at all.. the changes are in the definition of the database and related table and are completely transparent to the end user... please keep on reading the provided overview for further details..
regards

---

http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools