How can I use Razor to send BLOB fileContent to my SQL Server?-CodePudding

As the question states, I have two buttons on a web page. One opens a file dialog when clicked and allows the user to a select a CSV from their documents. The second is supposed to send the file content and a few other parameters to an Azure SQL Server. All of this is running on ASP.NET Web App within Azure Web Services. My code is below, I have tried it and I can't tell if it is registering at all, I get no output to the console and no record in my database. Any help would be greatly appreciated! (EDIT: Of course, "" in the sqlConnection is where my connection string lies)

Here is what I tried:

@using System.Data.SqlClient;
<form>
    <!-- HTML -->
    @{
        ViewData["Title"] = "Home Page";
    }

   
    <body>
        <input type="file" id="fileInput" style="display:none;" />
        <button id="selectFileButton">Select File</button>
        <label id="fileNameLabel" style="margin-left:10px;"></label>
        <button id="uploadButton">Upload</button>
    </body>

</form>

@section scripts {
    <script>
        // JavaScript
        document.querySelector("#selectFileButton").addEventListener("click", function () {
            document.querySelector("#fileInput").click();
        });

        document.querySelector("#fileInput").addEventListener("change", function () {
            var file = document.querySelector("#fileInput").files[0];
            document.querySelector("#fileNameLabel").innerText = file.name;
        });

        document.querySelector("#uploadButton").addEventListener("click", function () {
            var file = document.querySelector("#fileInput").files[0];
            if (file) {
                var reader = new FileReader();
                reader.onload = function () {
                    var parameters = {
                        data: reader.result,
                        fileName: file.name
                    };
                    Upload(parameters);
                };
                reader.readAsArrayBuffer(file);
            }
        });

      @{

    void Upload(string fileName, byte[] fileContent)
    {
          try
        {
            // Use a SqlCommandBuilder to send the data to SQL Server
            using (var sqlConnection = new SqlConnection(""))
            {           
                var sqlCommand = new SqlCommand("SET IDENTITY_INSERT BLOBS ON INSERT INTO BLOBS (BatchID, CustomerNumber, CSV, DataType) VALUES (@BatchID, @CustomerNumber, @CSV, @DataType) SET IDENTITY_INSERT BLOBS OFF", sqlConnection);
                sqlCommand.Parameters.AddWithValue("@CustomerNumber", "9002");
                sqlCommand.Parameters.AddWithValue("@CSV", fileContent);
                sqlCommand.Parameters.AddWithValue("@DataType", "TestASP");
                sqlCommand.Parameters.AddWithValue("@BatchID", 61);
                sqlCommand.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
{
    Console.WriteLine("An error occurred while uploading to the database: "   ex.Message);
    
}
    }
}

      
    </script>
}

CodePudding user response：

Create an SQL Database in Azure.

enter image description here

Created a table -tblContent in Database of azure.

enter image description here

using the below code able to connect the database and able to insert data into the table.

string readText = File.ReadAllText(filePath);

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

                using (SqlCommand command = new SqlCommand("INSERT INTO tblContent VALUES (@Id,@Content)", connection))
                {
                    command.Parameters.AddWithValue("@Id", 3);
                    command.Parameters.AddWithValue("@Content", readText);
                    command.ExecuteNonQuery();
                }
            }

enter image description here