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