word excel文件 存入数据库 实战
上传：
private void Insert2017(HttpContext context)
{
if (context.Request.Files["fileword"].ContentLength > 0)
{
if (context.Request["OUId"] != null &&
context.Request["OUName"] != null &&
context.Request["PersonId"] != null &&
context.Request["PersonName"] != null &&
context.Request["DocType"] != null)
{
string OUId = context.Request["OUId"].ToString();
string OUCODE = context.Request["OUCODE"].ToString();
string OUName = context.Request["OUName"].ToString();
string PersonId = context.Request["PersonId"].ToString();
string PersonName = context.Request["PersonName"].ToString();
string DocType = context.Request["DocType"].ToString();
string Remarks = context.Request["Remarks"].ToString();
string FileName = Path.GetFileName(context.Request.Files["fileword"].FileName);
string DownLoadUrl = context.Server.MapPath("~/ProjectMgt/Doc2017") + "\\" + OUName + CheckLoadUrl(DocType) + GetFileName(DocType, OUName, context, true);
string LoadUrl = "";
string flagUrl = context.Server.MapPath("~/ProjectMgt/Doc2017") + "\\" + OUName;
string nameflag = CheckFileName(FileName, DocType, OUName);
if (nameflag == "校验成功")
{
if (Directory.Exists(flagUrl + CheckLoadUrl(DocType)))
{
LoadUrl = context.Server.MapPath("~/ProjectMgt/Doc2017") + "\\" + OUName + CheckLoadUrl(DocType) + FileName;
}
else
{
Directory.CreateDirectory(flagUrl);
Directory.CreateDirectory(flagUrl + "\\常规2017");
LoadUrl = context.Server.MapPath("~/ProjectMgt/Doc2017") + "\\" + OUName + CheckLoadUrl(DocType) + FileName;
}
try
{
//InsertData(OUCODE, OUId, OUName, PersonId, PersonName, FileName, LoadUrl, Remarks, DocType, DownLoadUrl);
var fileinfo = context.Request.Files["fileword"];
Stream inputStream = fileinfo.InputStream;
int contentLength = fileinfo.ContentLength;
byte[] content = new byte[contentLength];
inputStream.Read(content, 0, content.Length);
inputStream.Close();
InsertData(OUCODE, OUId, OUName, PersonId, PersonName, FileName, LoadUrl, Remarks, DocType, DownLoadUrl,contentLength,content);
//context.Response.Write("{success:true, files:'上传成功'}");
if (File.Exists(LoadUrl))
{
File.Delete(LoadUrl);
LoadUrl = context.Server.MapPath("~/ProjectMgt/Doc2017") + "\\" + OUName + CheckLoadUrl(DocType) + GetFileName(DocType, OUName, context, true);
context.Request.Files["fileword"].SaveAs(LoadUrl);
context.Response.Write("{success:true, files:'文件替换成功'}");
}
else
{
LoadUrl = context.Server.MapPath("~/ProjectMgt/Doc2017") + "\\" + OUName + CheckLoadUrl(DocType) + GetFileName(DocType, OUName, context, true);
context.Request.Files["fileword"].SaveAs(LoadUrl);
context.Response.Write("{success:true, files:'上传成功'}");
}
}
finally
{
context.Response.End();
}
}
else
{
context.Response.Write("{success:false, files:'" + nameflag + "'}");
context.Response.End();
}
}
else
{
context.Response.Write("{success:false, files:'上传失败'}");
context.Response.End();
}
}
else
{
context.Response.Write("{success:false, files:'上传失败'}");
context.Response.End();
}
}
private bool InsertData(string oucode, string ouid, string ouname,
string personid, string personname, string filename,
string loadurl, string remarks, string doctype,
string downloadurl,int contentLength, byte[] content)
{
ExtFacade ef = new ExtFacade();
Guid id = Guid.NewGuid();
string OUId = ouid;
string OUCODE = oucode;
string OUName = ouname;
string PersonId = personid;
string PersonName = personname;
string FileName = filename;
string LoadUrl = loadurl;
DateTime SubmitTime = DateTime.Now;
string Remarks = remarks;
string DocType = doctype;
string DownLoadUrl = downloadurl;
byte[] Content = content;
int ContentLength = contentLength;
string DownLoadFileName = downloadurl.Substring(downloadurl.LastIndexOf("\\")+1);
string ContentType = GetContentType(DownLoadFileName);
//string query = @"INSERT INTO tbl_Net_SecurityCheck_2017 (
// id,
// OUID,
// OUName,
// PersonId,
// PersonName,
// FileName,
// LoadUrl,
// SubmitTime,
// Remarks,
// DocType,
// DownloadUrl,
// OUCODE,
// Content,
// ContentLength,
// DownLoadFileName,
// ContentType
// )
// VALUES
// (
// @id,
// @OUId,
// @OUName,
// @PersonId,
// @PersonName,
// @FileName,
// @LoadUrl,
// @SubmitTime,
// @Remarks,
// @DocType,
// @DownLoadUrl,
// @OUCODE,
// @Content,
// @ContentLength,
// @DownLoadFileName,
// @ContentType
// )";
string query = @"INSERT INTO tbl_Net_SecurityCheck_2017 (
id,
OUID,
OUName,
PersonId,
PersonName,
FileName,
LoadUrl,
SubmitTime,
Remarks,
DocType,
DownloadUrl,
OUCODE,
Content,
ContentLength,
DownLoadFileName,
ContentType
)
VALUES
(
@id,
@OUId,
@OUName,
@PersonId,
@PersonName,
@FileName,
@LoadUrl,
@SubmitTime,
@Remarks,
@DocType,
@DownLoadUrl,
@OUCODE,
@Content,
@ContentLength,
@DownLoadFileName,
@ContentType
)";
//ef.ExeBySQLFile();
//ef.ExeBySQLText(query);
using (SqlConnection _Connection = new SqlConnection(AppConfig.ConnectionString))
{
_Connection.Open();
//SqlDALBase dalbase = new SqlDALBase(_Connection, _Connection.BeginTransaction());
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = _Connection;
cmd.CommandText = query;
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@id", id));
cmd.Parameters.Add(new SqlParameter("@OUId", OUId));
cmd.Parameters.Add(new SqlParameter("@OUName", OUName));
cmd.Parameters.Add(new SqlParameter("@PersonId", PersonId));
cmd.Parameters.Add(new SqlParameter("@PersonName", PersonName));
cmd.Parameters.Add(new SqlParameter("@FileName", FileName));
cmd.Parameters.Add(new SqlParameter("@LoadUrl", LoadUrl));
cmd.Parameters.Add(new SqlParameter("@SubmitTime", SubmitTime));
cmd.Parameters.Add(new SqlParameter("@Remarks", Remarks));
cmd.Parameters.Add(new SqlParameter("@DocType", DocType));
cmd.Parameters.Add(new SqlParameter("@DownLoadUrl", DownLoadUrl));
cmd.Parameters.Add(new SqlParameter("@OUCODE", OUCODE));
cmd.Parameters.Add(new SqlParameter("@Content", SqlDbType.Image));
cmd.Parameters.Add(new SqlParameter("@ContentLength", ContentLength));
cmd.Parameters.Add(new SqlParameter("@DownLoadFileName", DownLoadFileName));
cmd.Parameters.Add(new SqlParameter("@ContentType", ContentType));
cmd.Parameters["@Content"].Value = Content;
int i = cmd.ExecuteNonQuery();
//return cmd.ExecuteNonQuery() > 0 ? true : false;
//List<SqlParameter> parameters = new List<SqlParameter>();
//parameters.Add(new SqlParameter("@id", id));
//parameters.Add(new SqlParameter("@OUId", OUId));
//parameters.Add(new SqlParameter("@OUName", OUName));
//parameters.Add(new SqlParameter("@PersonId", PersonId));
//parameters.Add(new SqlParameter("@PersonName", PersonName));
//parameters.Add(new SqlParameter("@FileName", FileName));
//parameters.Add(new SqlParameter("@LoadUrl", LoadUrl));
//parameters.Add(new SqlParameter("@SubmitTime", SubmitTime));
//parameters.Add(new SqlParameter("@Remarks", Remarks));
//parameters.Add(new SqlParameter("@DocType", DocType));
//parameters.Add(new SqlParameter("@DownLoadUrl", DownLoadUrl));
//parameters.Add(new SqlParameter("@OUCODE", OUCODE));
//parameters.Add(new SqlParameter("@Content", SqlDbType.Image));
//parameters.Add(new SqlParameter("@ContentLength", ContentLength));
//parameters.Add(new SqlParameter("@DownLoadFileName", DownLoadFileName));
//parameters.Add(new SqlParameter("@ContentType", ContentType));
//parameters[12].Value = Content;
//_Connection.(query, parameters, CommandType.Text);
}
}
return true;
}
下载：
private void DownLoad2017(HttpContext context)
{
//客户端保存的文件名
if (context.Request["DocType"] != null && context.Request["OUName"] != null)
{
string fileName = GetFileName(context.Request["DocType"].ToString(), context.Request["OUName"].ToString(), context, false);
string filePath = context.Request["DownLoadUrl"].ToString();//路径
string fileId = context.Request["FileId"].ToString();//路径
//以字符流的形式下载文件
if (!string.IsNullOrEmpty(filePath))
{
try
{
//FileStream fs = new FileStream(filePath, FileMode.Open);
byte[] bytes = GetContent(fileId);
context.Response.ContentType = "application/octet-stream";
//通知浏览器下载文件而不是打开
context.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
context.Response.BinaryWrite(bytes);
context.Response.Flush();
context.Response.End();
}
catch
{
}
}
}
else {
}
}
private byte[] GetContent(string fileId)
{
ExtFacade ef = new ExtFacade();
string query = string.Format(@"SELECT Content from tbl_Net_SecurityCheck_2017 where id='{0}'", fileId);
var content = ef.ExecuteScalarBySQLText(query);
if (content == null)
{
return new byte[0];
}
else
{
return (byte[])content;
}
}
