Upload binary data the Database

14 years ago By planetsourcecode

Some times we have the requirement to upload an image to the database in binary form. As uploading a image file to the database has many different benefits as compare to uploading files to the Web Server Folders So let’s starts from the database table design The fields includes in table are: 1) ID – Primary Key 2) Filename – Store name of the file. 3) Mime- what type of file is it. 4) Date- date and time of file uploaded 5) Binarydata- it will store the data in binary form


/****** Object:  Table [dbo].[BinaryData]    Script Date: 02/28/2009 10:47:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BinaryData](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [filename] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [mime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [date] [datetime] NULL,
   [binarydata] [varbinary](max) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

And next create a new website in Visual Studio IDE name it “binary-data-upload”, by default it will add a “default.aspx” file .In this add the following controls 1) Literal -lit_Status 2) TextBox - FileName 3) FileUpload - FileToUpload1 4) Button - btn_Upload Double click on the button on the default.aspx Add the following codes to that particular method


protected void btn_Upload_Click(object sender, EventArgs e)
{
if (FileToUpload1.PostedFile == null || string.IsNullOrEmpty(FileToUpload1.PostedFile.FileName) || FileToUpload1.PostedFile.InputStream == null)
{
lit_Status.Text = "Error!!!";
return;
}
else
{
//lblMsg.Text = "";
string extension = Path.GetExtension(FileToUpload1.PostedFile.FileName).ToLower();
// Response.Write(extension);
string filetype = null;
switch (extension)
{
case ".gif":
filetype = "image/gif";
break;

case ".jpg":
case ".jpeg":
case "jpe":
filetype = "image/jpeg";
break;

case ".png":
filetype = "image/png";
break;

default:
lit_Status.Text = "Invalid Extension";
return;
}
using(SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["Strcon"].ConnectionString))
{
try{
const string strSQL="insert into dbo.BinaryData(filename, mime, date, binarydata) values(@filename, @mime, @date, @binarydata)";
SqlCommand cmd=new SqlCommand(strSQL,con);
cmd.Parameters.AddWithValue("@filename",FileName.Text);
cmd.Parameters.AddWithValue("@mime", filetype);

byte[] imagebytes = new byte[FileToUpload1.PostedFile.InputStream.Length +1];
FileToUpload1.PostedFile.InputStream.Read(imagebytes, 0, imagebytes.Length);

cmd.Parameters.AddWithValue("@date",DateTime.Now);
cmd.Parameters.AddWithValue("@binarydata",imagebytes);

con.Open();
cmd.ExecuteNonQuery();
con.Close();

}
catch
{
con.Close();
}

}



}
}

We in this code we are doing these things 1). Check for the file exists 2). What type of extension( select from predefined format) 3). Insert the values into the database. About the author: Planet Source Code is a place for all developer providing free source codes, articles, complete projects,complete application in PHP, C/C++, Javascript, Visual Basic, Cobol, Pascal, ASP/VBScript, AJAX, SQL, Perl, Python, Ruby, Mobile Development

Add new comment

19 views