C# ASP.NET image Read/Write as BLOB varbinary (MAX)

C# ASP.NET image Read/Write as BLOB varbinary (MAX)



DATABASE

CREATE TABLE [dbo].[AngularEmployee](

       [EmployeeID] [int] IDENTITY(1,1) NOT NULL,

       [FirstName] [nvarchar](50) NULL,

       [LastName] [nvarchar](50) NULL,

       [EmpCode] [nvarchar](50) NULL,

       [Position] [nvarchar](50) NULL,

       [Picture] [varbinary](max) NULL,

 CONSTRAINT [PK_AngularEmployee] PRIMARY KEY CLUSTERED

(

       [EmployeeID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

C# ASP CODE

<form id="form1" runat="server">

    <div>

        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

        <asp:FileUpload ID="FileUpload1" runat="server" />

        <asp:Button ID="SAVE" runat="server" Text="Button" OnClick="SAVE_Click" />

        <br />

        <asp:Button ID="VIeW" runat="server" Text="View Image" OnClick="VIEW_Click" />

        <br />

        <asp:Image ID="imgTest" runat="server" />

    </div>

    </form>

C# CODE

protected void SAVE_Click(object sender, EventArgs e)

    {

        string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);

        string contentType = FileUpload1.PostedFile.ContentType;

        using (Stream fs = FileUpload1.PostedFile.InputStream)

        {

            using (BinaryReader br = new BinaryReader(fs))

            {

                byte[] bytes = br.ReadBytes((Int32)fs.Length);

                using (SqlConnection con = new SqlConnection("Data Source=????;Initial Catalog=??????;User ID=??????;Password=??????;Connection Timeout=900"))

                {

                    using (SqlCommand cmd = new SqlCommand("update [dbo].[AngularEmployee] set LastName=@L1 , Position=@P1, Picture = @Picture where EmployeeID='"+ TextBox1.Text + "'", con))

                    {

                        cmd.Connection = con;

                        cmd.Parameters.AddWithValue("@L1", filename);

                        cmd.Parameters.AddWithValue("@P1", contentType);

                        cmd.Parameters.AddWithValue("@Picture", bytes);

                        con.Open();

                        cmd.ExecuteNonQuery();

                        con.Close();

                    }

                }

            }

        }

    }

    protected void VIEW_Click(object sender, EventArgs e)

    {

        SqlConnection con = new SqlConnection("Data Source=????;Initial Catalog=??????;User ID=??????;Password=??????;Connection Timeout=900");

        DataTable dt = new DataTable();

        SqlDataAdapter da = new SqlDataAdapter("select * from [dbo].[AngularEmployee] where EmployeeID='" + TextBox1.Text + "'", con);

        da.Fill(dt);

        byte[] bytes = (byte[])dt.Rows[0]["Picture"];

        string type = Convert.ToString(dt.Rows[0]["Position"]);

        string str64 = Convert.ToBase64String(bytes);

        imgTest.ImageUrl = "data:" + type + ";base64," + str64;

    }