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