I want to upload file to SQL Server database and download it again from web browser (any file: xlsx, pdf, etc).
Here is my HTML markup :
<input type="file" class="form-control-file" id="fileupload1" onchange="showname()" />
Javascript convert to binary :
var filename;
var contentType;
var filestream;
var fixarray;
const fileByteArray = [];
function showname() {
var name = document.getElementById('fileupload1');
filename = name.files.item(0).name;
contentType = name.files.item(0).type;
var reader = new FileReader();
reader.readAsArrayBuffer(name.files[0]);
reader.onload = function () {
filestream = reader.result;
fixarray = new Uint8Array(filestream);
for (let i = 0; i < fixarray.length; i++) {
fileByteArray.push(fixarray[i]);
}
};
}
My AJAX Call (events.id successfully called from other function) :
$.ajax({
type: "POST",
dataType: "json",
contentType: "application/json; charset=utf-8",
url: "confirmationcomplete.aspx/finish",
data: '{ id: ' + JSON.stringify(events.id) + ',contentType: "' + contentType + '",name: "' + filename + '",binaryy: "' + fileByteArray + '"}',
success: function (data) {
alert('Success upload');
},
error: function (xhr, status, error) {
alert(xhr.responseText);
}
})
Backend code (C#) :
[WebMethod]
public static void finish(int id, string contentType, string name, string binaryy)
{
string str = @"Data Source=172.16.111.45\SQLEXPRESS;Initial Catalog=dashboard;User ID=sa;Password=sdfgh";
SqlConnection cn2 = new SqlConnection(str);
SqlCommand cmd7 = new SqlCommand("[sp_uploadfile]", cn2);
cmd7.Parameters.Add("@eventID", SqlDbType.Int);
cmd7.Parameters["@eventID"].Value = id;
cmd7.Parameters.Add("@contentType", SqlDbType.VarChar);
cmd7.Parameters["@contentType"].Value = contentType;
cmd7.Parameters.Add("@name", SqlDbType.VarChar);
cmd7.Parameters["@name"].Value = name;
byte[] byteArray = Encoding.UTF8.GetBytes(binaryy);
cmd7.Parameters.Add("@byte", SqlDbType.VarBinary,-1);
cmd7.Parameters["@byte"].Value = byteArray;
cmd7.CommandType = CommandType.StoredProcedure;
try
{
cn2.Open();
cmd7.ExecuteNonQuery();
cn2.Close();
}
catch (Exception ex)
{
}
}
Stored procedure :
CREATE PROCEDURE [dbo].[sp_uploadfile]
@eventID int,
@contentType varchar(255),
@name varchar(255),
@byte varbinary(MAX)
AS
BEGIN
INSERT INTO eventsScheduler
VALUES (@eventID, @contentType, @name, @byte)
END
From all of my code above, the upload file process to SQL Server database was successful.
But when I am trying to download it from browser, it throws an error
Excel cannot open the file ... because the file format or extension is not valid
Button download :
<asp:LinkButton runat="server" ID="btndownload" OnClick="DownloadFile" CommandArgument="<%#hdID%>">Download Report</asp:LinkButton>
Here is my download code :
protected void DownloadFile(object sender, EventArgs e)
{
int a = Convert.ToInt32(hdID.Value);
byte[] bytes;
string contentType, fileName;
string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select EventID,contentType,filename, report from [eventsScheduler] where EventID=@id";
cmd.Parameters.AddWithValue("@id", a);
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
bytes = (byte[])sdr["report"];
contentType = sdr["contentType"].ToString();
fileName = sdr["filename"].ToString();
}
con.Close();
}
}
if (bytes.Length != 0)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = contentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
else
{
}
}
I assume the problem was at converting the binary from file upload in javascript code.
So I am trying to create one new upload control (using asp control) and try to upload the file directly :
ASP Control :
<asp:FileUpload ID="fileUpload_NewProblem" runat="server" Width="100%" />
Button Upload :
<asp:LinkButton runat="server" OnClick="Unnamed_Click">Upload</asp:LinkButton>
Button action :
protected void Unnamed_Click(object sender, EventArgs e)
{
uploadFile();
}
Backend Code :
public void uploadFile()
{
id = Convert.ToInt32(Request["id"]);
int ids = id; // int.Parse((sender as LinkButton).CommandArgument);
string filename = Path.GetFileName(fileUpload_NewProblem.PostedFile.FileName);
string contentType = fileUpload_NewProblem.PostedFile.ContentType;
using (Stream fs = fileUpload_NewProblem.PostedFile.InputStream)
{
using (BinaryReader br = new BinaryReader(fs))
{
byte[] bytes = br.ReadBytes((Int32)fs.Length);
string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = " INSERT INTO eventsScheduler VALUES (@eventID,@contentType,@name,@byte )";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@eventID", ids);
cmd.Parameters.AddWithValue("@name", filename);
cmd.Parameters.AddWithValue("@contentType", contentType);
cmd.Parameters.AddWithValue("@byte", bytes);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
}
}
}
}
}
}
And when I upload the file using the second method (Without AJAX), I successfully downloaded the file using same download code.
My question is: how to convert file to binary using javascript in the same way with the C#?
sp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!