How to Upload Multiple File Using Typetable in sp web api

Question

i need a web api in which multiple file upload using Type table

Sandeep Kumar · Answer

Where is issue can u see my code public List Pms_INSERT_UPDATE_DATA(JObject jsonParam, int UserId) { DataTable DtPhoto = new DataTable(); DtPhoto.Columns.Add("FilePath", typeof(System.String)); DtPhoto.Columns.Add("PhotoTag", typeof(System.String)); List returnVal = new List (); int ID = (int)jsonParam["ID"]; int StationId = (int)jsonParam["StationId"]; int UnitId = (int)jsonParam["UnitId"]; int OutageTypeId = (int)jsonParam["OutageTypeId"]; int ZoneId = (int)jsonParam["ZoneId"]; string FinYear = (string)jsonParam["FinYear"]; string Photo_Tag = (string)jsonParam["PhotoTag"]; var httpRequest = HttpContext.Current.Request; var filePath = string.Empty; foreach (string file in httpRequest.Files) { var postedFile = httpRequest.Files[file]; string Extension = Path.GetExtension(postedFile.FileName); filePath = FilePath + Guid.NewGuid() + Extension; postedFile.SaveAs(filePath); DtPhoto.Rows.Add(filePath, Photo_Tag); try { using (SqlConnection con = new SqlConnection(strConnString)) { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = DBProcedure.SP_InsertUpdatePmsData; cmd.Parameters.Add("@PmsDataID ", SqlDbType.Int).Value = ID; cmd.Parameters.Add("@StationID", SqlDbType.Int).Value = StationId; cmd.Parameters.Add("@UnitID", SqlDbType.Int).Value = UnitId; cmd.Parameters.Add("@ZoneID", SqlDbType.Int).Value = ZoneId; cmd.Parameters.Add("@OutageTypeID", SqlDbType.Int).Value = OutageTypeId; cmd.Parameters.Add("@FinYear", SqlDbType.NVarChar).Value = FinYear; cmd.Parameters.Add("@ImageList", SqlDbType.Structured).Value = DtPhoto; cmd.Parameters.Add("@Photo_Tag", SqlDbType.NVarChar).Value = Photo_Tag; cmd.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = UserId; cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Value = 0; cmd.Parameters["@ReturnID"].Direction = ParameterDirection.InputOutput; cmd.Parameters["@ReturnPmsDataID"].Direction = ParameterDirection.InputOutput; cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); returnVal.Add(Convert.ToInt32(cmd.Parameters["@ReturnVal"].Value.ToString())); } } } catch (Exception ex) { } return returnVal; }

Sandeep Kumar · Answer

my type table CREATE TYPE [dbo].[ImageListType] AS TABLE( [FilePath] [varchar](500) NULL, [PhotoTag] [varchar](50) NULL )

Amit Mohanty · Answer

Define the table type in SQL Server: CREATE TYPE FileTableType AS TABLE ( FileName VARCHAR(100), FileData VARBINARY(MAX) ) Create a stored procedure that accepts the table type as a parameter: CREATE PROCEDURE UploadFiles @Files FileTableType READONLY AS BEGIN SET NOCOUNT ON; CREATE TABLE #TempFiles ( FileId INT IDENTITY(1,1), FileName VARCHAR(100), FileData VARBINARY(MAX) ) INSERT INTO #TempFiles (FileName, FileData) SELECT FileName, FileData FROM @Files DECLARE @FileId INT DECLARE @FileName VARCHAR(100) DECLARE @FileData VARBINARY(MAX) DECLARE fileCursor CURSOR FOR SELECT FileId, FileName, FileData FROM #TempFiles OPEN fileCursor FETCH NEXT FROM fileCursor INTO @FileId, @FileName, @FileData WHILE @@FETCH_STATUS = 0 BEGIN -- Perform necessary operations on each file -- For example, save the file to a directory or store file information in a database FETCH NEXT FROM fileCursor INTO @FileId, @FileName, @FileData END CLOSE fileCursor DEALLOCATE fileCursor -- Clean up temporary table DROP TABLE #TempFiles END Define the UploadedFile class to represent each uploaded file: public class UploadedFile { public string FileName { get; set; } public byte[] FileData { get; set; } } Your web API project, define an endpoint that accepts multiple file uploads using the table type parameter: [HttpPost] public IActionResult UploadFiles([FromBody] List files) { // Convert the list of files to a DataTable DataTable dataTable = new DataTable(); dataTable.Columns.Add("FileName", typeof(string)); dataTable.Columns.Add("FileData", typeof(byte[])); foreach (var file in files) { dataTable.Rows.Add(file.FileName, file.FileData); } using (SqlConnection connection = new SqlConnection("YourConnectionString")) { connection.Open(); using (SqlCommand command = new SqlCommand("UploadFiles", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@Files", dataTable); command.ExecuteNonQuery(); } } return Ok(); } You may need to modify the code based on your specific requirements, such as error handling, file validation, and saving files to a specific location or store file information in a database.