Ajit Ghodake

Ajit Ghodake

  • 1.7k
  • 73
  • 476

how to download image from database

Sep 21 2023 5:55 AM
protected void downloadbtn_Click(object sender, EventArgs e)
{
    // Read the entered employee numbers from the TextBox
    string employeeNumbersText = Employeeeno.Text;
    string[] employeeNumberArray = employeeNumbersText.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

    // Create a DataTable to store the employee numbers as TVP
    DataTable employeeNumbersTable = new DataTable();
    employeeNumbersTable.Columns.Add("EmployeeNo", typeof(long));

    foreach (string employeeNo in employeeNumberArray)
    {
        employeeNumbersTable.Rows.Add(long.Parse(employeeNo.Trim()));
    }

    // Create a connection string to your SQL Server database
    string connectionstring = ConfigurationManager.ConnectionStrings["HRMS_AdministrationConnection"].ConnectionString;

    using (SqlConnection connection = new SqlConnection(connectionstring))
    {
        connection.Open();

        // Create a command to execute the stored procedure
        using (SqlCommand cmd = new SqlCommand("usp_GetEmployeePhotoForDownload", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            // Add the TVP parameter
            SqlParameter tvpParameter = cmd.Parameters.AddWithValue("@EmployeeNo", employeeNumbersTable);
            tvpParameter.SqlDbType = SqlDbType.Structured;
            tvpParameter.TypeName = "Employeelists";

            // Execute the stored procedure
            SqlDataReader reader = cmd.ExecuteReader();
            {
                if (reader.HasRows)
                {
                    // Display the first retrieved photo
                    byte[] photoData = (byte[])reader["Photo"];
                    imgProfile.Visible = true;
                    imgProfile.ImageUrl = "data:image/jpeg;base64," + Convert.ToBase64String(photoData);
                }
                else
                {
                    // No photo found for the entered employee numbers
                    imgProfile.Visible = false;
                }
            }
        }
    }
}
CREATE TYPE Employeelists AS TABLE
(
    EmployeeNo bigint
);

GO

ALTER PROCEDURE [dbo].[usp_GetEmployeePhotoForDownload]
(
    @EmployeeNo Employeelists READONLY
) AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE #Temp_EmployeeNoPhotoList
    (
        EmployeeNo bigint,
        FileName varchar(100),
        IsPhotoGenerated char(1),
        GenerateDateTime datetime
    )

    CREATE TABLE #EmployeeNoList
    (
        EmployeeNo bigint,
        FileName varchar(100),
        Photo varbinary(max)
    )

    DECLARE @DocumentTypeID tinyint = 3

    INSERT INTO #Temp_EmployeeNoPhotoList (EmployeeNo, FileName)
    SELECT EmployeeNo, REPLACE(CAST(EmployeeNo AS varchar(10)) + '_' + FirstName + ISNULL('_' + REPLACE(LastName, '''', ''), ''), ' ', '') AS FileName
    FROM Employees E WITH(NOLOCK)
    INNER JOIN Users U WITH(NOLOCK) ON U.UserID = E.UserID AND LeavingDate IS NULL
    AND NOT EXISTS (SELECT 1 FROM #Temp_EmployeeNoPhotoList T WITH(NOLOCK) WHERE T.EmployeeNo = E.EmployeeNo)
    ORDER BY 1

    UPDATE #Temp_EmployeeNoPhotoList
    SET FileName = REPLACE(REPLACE(REPLACE(REPLACE(
        FileName,
        CHAR(9) /*tab*/,
        ''
    ),
        CHAR(10) /*newline*/,
        ''
    ),
        CHAR(13) /*carriage return*/,
        ''
    ),
        CHAR(32) /*space*/,
        ''
    )

    INSERT INTO #EmployeeNoList (EmployeeNo, FileName)
    SELECT EmployeeNo, FileName
    FROM #Temp_EmployeeNoPhotoList
    WHERE IsPhotoGenerated IS NULL
    ORDER BY 1

    /*-------------------------------------------------------- ---------------*/

    UPDATE EL
    SET Photo = FileData
    FROM #EmployeeNoList EL
    INNER JOIN Employees E WITH(NOLOCK) ON E.EmployeeNo = EL.EmployeeNo
    INNER JOIN Attachments..ReqTrack_DocumentDetails DA WITH(NOLOCK) ON DA.UserID = E.UserID AND DocumentTypeID = @DocumentTypeID

    UPDATE EL
    SET Photo = FileData
    FROM #EmployeeNoList EL
    INNER JOIN Employees E WITH(NOLOCK) ON E.EmployeeNo = EL.EmployeeNo AND EL.Photo IS NULL
    INNER JOIN Attachments..ReqTrack_DocumentDetails_Archived DA WITH(NOLOCK) ON DA.UserID = E.UserID AND DocumentTypeID = @DocumentTypeID

    UPDATE T
    SET IsPhotoGenerated = CASE WHEN Photo IS NOT NULL THEN 'Y' ELSE 'N' END,
        GenerateDateTime = GETDATE()
    FROM #EmployeeNoList EL WITH(NOLOCK)
    INNER JOIN #Temp_EmployeeNoPhotoList T WITH(NOLOCK) ON T.EmployeeNo = EL.EmployeeNo

    --SELECT*FROM #EmployeeNoList WHERE Photo IS NOT NULL
    SELECT EmployeeNo, FileName, CONTROL.dbo.DeCompressBytes(Photo) AS Photo
    FROM #EmployeeNoList EL
    WHERE Photo IS NOT NULL AND EL.EmployeeNo IN (SELECT EmployeeNo FROM Employeelists)
    DROP TABLE #EmployeeNoList
    DROP TABLE #Temp_EmployeeNoPhotoList
END

 


Answers (3)