How to save a PDF file in a SQL Server column using vb.net code,by memory stream ?
2 Answers
thank you!!! my solution now is :
public void SaveFile()
{
//Try
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "pdf file|*.pdf";
if (fd.ShowDialog == System.Windows.Forms.DialogResult.OK) {
//PdfDocument1.FilePath = fd.FileName
byte[] filebyte = null;
SqlConnection con = new SqlConnection("Data Source=LOCALHOS-A4AE79\\LOCALHOST1;Initial Catalog=library_alborz;Integrated Security=True");
SqlCommand cmd = default(SqlCommand);
filebyte = System.IO.File.ReadAllBytes(fd.FileName);
cmd = new SqlCommand("Insert into pdftbl ( pdffld ) Values(@pdf)", con);
//cmd.Parameters.Add("@filepath", SqlType.VarChar).Value = txtfilepath.Text
cmd.Parameters.Add("@pdf", SqlDbType.Binary).Value = filebyte;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Interaction.MsgBox("File saved into database", MsgBoxStyle.Information);
//Catch ex As Exception
// MsgBox(Err.Description, MsgBoxStyle.Exclamation)
//End Try
}
}
------------
// load pdf file
private void Button2_Click(System.Object sender, System.EventArgs e)
{
string strsql = null;
SqlConnection con = new SqlConnection("Data Source=LOCALHOS-A4AE79\\LOCALHOST1;Initial Catalog=library_alborz;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try {
strsql = "select pdffld from pdftbl ";
da = new SqlDataAdapter(strsql, con);
da.Fill(ds, "tbl");
//Get image data from gridview column.
byte[] pdfData = Convert.ToByte(ds.Tables["tbl"].Rows[0][0]);
//Initialize pdf variable
//Read pdf data into a memory stream
using (MemoryStream ms = new MemoryStream(pdfData, 0, pdfData.Length)) {
ms.Write(pdfData, 0, pdfData.Length);
//Set pdf variable value using memory stream.
PdfDocument1.Load(ms);
PdfPageView1.Document = PdfDocument1;
PdfPageView1.Refresh();
}
} catch (Exception ex) {
MessageBox.Show(ex.ToString());
}
}
Comments
First of all you must have column of type varbinary(MAX) in your table in database. It allows you to save bytes array in it.
Next you can use such line of code to get contents of your PDF file as byte array:
IO.File.ReadAllBytes("C:\my.pdf")
2 Comments
Bogdan Sahlean
ntext , text, and **image** data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.(source)Dima
Then use varbinary(MAX). But specify MAX, not some number, since MAX allows usage of up to 2^31-1 bytes.