Image in SQL Server 2000
-
Hi, I'm a newbie in this but I what to make a VB.NET application that adds a jpg picture to row in a SQL Server 2000 database. Thanks in advance. Dino Sousa
-
Hi, I'm a newbie in this but I what to make a VB.NET application that adds a jpg picture to row in a SQL Server 2000 database. Thanks in advance. Dino Sousa
:) 1 you can create a proc first like: create proc p_pic_i @filename nvarchar(100), @filesize int, @jpgdate image as insert into pic ( filename, filesize, filedata) values ( @filename, @filesize, @filedata) if @@rowcount = 1 and @@error = 0 return @@identity else return -1 go 2 then write a function to call this proc, sorry this is c# not vb.net public int csf_p_News_ext_i( string filename, int filesize, byte[] filedata, SqlConnection conn) { SqlCommand cmd = new SqlCommand("p_News_ext_i",conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@RETURN_VALUE",SqlDbType.Int,4); cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add("@filename",SqlDbType.NVarChar,200); cmd.Parameters["@filename"].Value = filename; cmd.Parameters.Add("@filesize",SqlDbType.Int,4); cmd.Parameters["@filesize"].Value = filesize; cmd.Parameters.Add("@filedata",SqlDbType.Image); cmd.Parameters["@filedata"].Value = filedata; int retvar = -1; try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); retvar = (int)cmd.Parameters["@RETURN_VALUE"].Value; } catch { retvar = -1; } return retvar; } 3 read stream(asp.net) Byte[] FileByteArray = new Byte[filelength]; Stream StreamObject = UpFile.InputStream; StreamObject.Read(FileByteArray,0,filelength);
-
:) 1 you can create a proc first like: create proc p_pic_i @filename nvarchar(100), @filesize int, @jpgdate image as insert into pic ( filename, filesize, filedata) values ( @filename, @filesize, @filedata) if @@rowcount = 1 and @@error = 0 return @@identity else return -1 go 2 then write a function to call this proc, sorry this is c# not vb.net public int csf_p_News_ext_i( string filename, int filesize, byte[] filedata, SqlConnection conn) { SqlCommand cmd = new SqlCommand("p_News_ext_i",conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@RETURN_VALUE",SqlDbType.Int,4); cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add("@filename",SqlDbType.NVarChar,200); cmd.Parameters["@filename"].Value = filename; cmd.Parameters.Add("@filesize",SqlDbType.Int,4); cmd.Parameters["@filesize"].Value = filesize; cmd.Parameters.Add("@filedata",SqlDbType.Image); cmd.Parameters["@filedata"].Value = filedata; int retvar = -1; try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); retvar = (int)cmd.Parameters["@RETURN_VALUE"].Value; } catch { retvar = -1; } return retvar; } 3 read stream(asp.net) Byte[] FileByteArray = new Byte[filelength]; Stream StreamObject = UpFile.InputStream; StreamObject.Read(FileByteArray,0,filelength);
Hi, It looks like a great solution especially since I want to start to use store procedures. But it covers the upload do the database part... Can you help me with the download from database part too? Thanks in advance, Dino Sousa
-
Hi, It looks like a great solution especially since I want to start to use store procedures. But it covers the upload do the database part... Can you help me with the download from database part too? Thanks in advance, Dino Sousa
asp.net, try { SqlDataReader reader = this.oCon.GetReader( @"select filesize,contenttype,filedata from file_personal (index=pk_file_personal nolock) where fileid = " + e.Item.Cells[5].Text); this.Page.Response.ContentType = (string)reader["contenttype"]; this.Page.Response.AppendHeader("Content-Disposition:", "attachment; filename=" + HttpUtility.UrlEncode(e.Item.Cells[7].Text)); this.Page.Response.OutputStream.Write((byte[])reader["filedata"], 0, (int)reader["filesize"]); this.Page.Response.End(); } catch { this.Page.RegisterStartupScript("", "alert('error!');"); return; } ASP.NET Windows APP Mobile App http://www.eReach.cn