Обсуждение: Issue in save and retreive file in postgres

Поиск
Список
Период
Сортировка

Issue in save and retreive file in postgres

От
Vishwas Dwivedi
Дата:
Greetings,

I am a software programmer and  working on a .net web application(ASP.NET 4.0, c# ).
I am new to postgres and for my current project I am using postgres as database.


In this need functionality to upload a pdf file  to database and then retrieve it again from database.
I have taken bytea column in my table to store binary file data. I am taking byte array to convert the data.

When I try to retrieve this pdf file from database ,its not working.
Please see the below code and suggest me some solution.

Here is my code:
3. ##########   Database table ############

CREATE TABLE testimage
(
  id integer,
  img bytea
)

2. ##########   function to save file in database 
protected int UploadDocumentsPostgres()
    {
        int result = 0;
        if (fuDocuments.HasFile)
        {
            fuDocuments.SaveAs(Server.MapPath("Temp/") + fuDocuments.FileName);

            using (FileStream pgFileStream = new FileStream(Server.MapPath("Temp/" + fuDocuments.FileName), FileMode.Open, FileAccess.Read))
            {
                using (BinaryReader pgReader = new BinaryReader(new BufferedStream(pgFileStream)))
                {
                    byte[] pgByteA = pgReader.ReadBytes(Convert.ToInt32(pgFileStream.Length));
                   
                    NpgsqlConnection cn = new NpgsqlConnection("server=195.100.1.100;port=5433;database=DTMS;user id=postgres;password=admin@123");
                   
                    NpgsqlCommand cmd = new NpgsqlCommand();
                    cmd.Connection = cn;
                    cn.Open();
                    string sql = "insert into testimage values(@id,@data)";

                    cmd.Parameters.Add("@id", NpgsqlTypes.NpgsqlDbType.Integer, 1).Value = 1;
                    cmd.Parameters.Add("@data", NpgsqlTypes.NpgsqlDbType.Bytea, pgByteA.Length).Value = pgByteA;
                   
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    cn.Close();

                }
            }
        }
        return result;
    }


3.    ###################   Retrieve file from database and save in temp folder ########################
DataTable dt = new DataTable();
        NpgsqlConnection cn = new NpgsqlConnection("server=195.100.1.100;port=5433;database=DTMS;user id=postgres;password=admin@123");
        NpgsqlDataAdapter sda = new NpgsqlDataAdapter("select * from testimage", cn);
        sda.Fill(dt);


        if (dt.Rows.Count > 0)
        {
            byte[] doc = (byte[])(dt.Rows[0]["img"]);
            FileStream fs = new System.IO.FileStream(Server.MapPath("Temp/") + "temp.PDF", FileMode.Create, FileAccess.Write);
            BinaryWriter bw = new BinaryWriter(new BufferedStream(fs));

            bw.Write(doc, 0, doc.Length);
            bw.Flush();
            bw.Close();
            fs.Close();
        }
--
Thanks & Regards
Vishwas Dwivedi
Contact No: +91-9460968854


Re: Issue in save and retreive file in postgres

От
Craig Ringer
Дата:
On 06/15/2012 09:16 PM, Vishwas Dwivedi wrote:
Greetings,

I am a software programmer and  working on a .net web application(ASP.NET 4.0, c# ).
I am new to postgres and for my current project I am using postgres as database.


In this need functionality to upload a pdf file  to database and then retrieve it again from database.
I have taken bytea column in my table to store binary file data. I am taking byte array to convert the data.

When I try to retrieve this pdf file from database ,its not working.

Error messages?

What happens instead of what you expect to?

After the save, if you examine the data in the database using psql or PgAdmin-III, what do you see?

What PostgreSQL version are you using?

--
Craig Ringer