利用mysql实现上传和下载_通过数据库上传下载附件

usingSystem;usingSystem.Collections.Generic;usingSystem.Collections.ObjectModel;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.IO;usingSystem.Text;usingSystem.Windows;usingSystem.Windows.Forms;usingSystem.Windows.Input;namespaceFileToDataBaseTest

{public partial classMainWindow : Window

{publicMainWindow()

{

InitializeComponent();this.DataContext = newMainWindowViewModel();

}

}public classMainWindowViewModel : ViewModelBase

{publicMainWindowViewModel()

{

UploadFiles= new ObservableCollection();

SearchFile();

}private stringfilePath;public stringFilePath

{get { returnfilePath; }set{

filePath=value;

OnPropertyChanged("FilePath");

}

}public ObservableCollection UploadFiles { get; set; }public UploadFileViewModel SelectedFileViewModel { get; set; }private UploadFile file = null;#region 载入文件

privateICommand loadFileCommand;publicICommand LoadFileCommand

{get{if (loadFileCommand == null)

{

loadFileCommand= new RelayCommand(param =>LoadFile());

}returnloadFileCommand;

}

}public voidLoadFile()

{

OpenFileDialog dialog= newOpenFileDialog();if (dialog.ShowDialog() ==DialogResult.OK)

{

FilePath=dialog.FileName;

FileInfo fileInfo= newFileInfo(@FilePath);

file= newUploadFile();

file.FileSize=fileInfo.Length;

file.FileName=fileInfo.Name;

file.FileType=fileInfo.Extension;

file.FileByteCode= new byte[file.FileSize];

FileStream stream= newFileStream(FilePath, FileMode.Open, FileAccess.Read);

BinaryReader reader= newBinaryReader(stream);

reader.Read(file.FileByteCode,0, (int)file.FileSize);

}

}#endregion

#region 保存文件

privateICommand saveCommand;publicICommand SaveCommand

{get{if (saveCommand == null)

{

saveCommand= new RelayCommand(param =>SaveFile());

}returnsaveCommand;

}

}public voidSaveFile()

{

StringBuilder sqlStr= newStringBuilder();

sqlStr.AppendFormat("INSERT INTO FileDetails");

sqlStr.AppendFormat("( fName, fSize, fType, files )");

sqlStr.AppendFormat("VALUES ( @fName,");

sqlStr.AppendFormat("@fSize,");

sqlStr.AppendFormat("@fType,");

sqlStr.AppendFormat("@files");

sqlStr.AppendFormat(")");

Dictionary paramss = new Dictionary();

paramss.Add("@fName", file.FileName);

paramss.Add("@fSize", file.FileSize);

paramss.Add("@fType", file.FileType);

paramss.Add("@files", file.FileByteCode);int num =DataBaseHelper.SqlExcuteNoQueryWithParamters(sqlStr.ToString(), paramss);if (num > 0)

{

System.Windows.MessageBox.Show("保存成功");

}

}#endregion

#region 查询文件

public voidSearchFile()

{

UploadFiles.Clear();

StringBuilder sbSql= newStringBuilder();

sbSql.AppendLine("SELECT fName ,");

sbSql.AppendLine("fSize ,");

sbSql.AppendLine("fType");

sbSql.AppendLine("FROM FileDetails");

DataTable dt=DataBaseHelper.GetDataTable(sbSql.ToString());foreach (var row indt.Rows)

{

DataRow dr=(DataRow)row;

UploadFile file= newUploadFile();

file.FileName= (string)dr["fName"];

file.FileSize= (long)dr["fSize"];

file.FileType= (string)dr["fType"];

UploadFileViewModel fileViewModel= newUploadFileViewModel(file);

UploadFiles.Add(fileViewModel);

}

}#endregion}public classUploadFileViewModel : ViewModelBase

{publicUploadFileViewModel(UploadFile uploadFile)

{this.uploadFile =uploadFile;

}privateUploadFile uploadFile;public stringFileName

{get { returnuploadFile.FileName; }set { uploadFile.FileName =value; }

}public longFileSize

{get { returnuploadFile.FileSize; }set { uploadFile.FileSize =value; }

}public stringFileType

{get { returnuploadFile.FileType; }set { uploadFile.FileType =value; }

}publicByte[] FileByteCode

{get { returnuploadFile.FileByteCode; }set { uploadFile.FileByteCode =value; }

}#region 下载文件

privateICommand downloadFileCommand;publicICommand DownloadFileCommand

{get{if (downloadFileCommand == null)

{

downloadFileCommand= new RelayCommand(param =>DownloadFile());

}returndownloadFileCommand;

}

}public voidDownloadFile()

{

SaveFileDialog sfd= newSaveFileDialog();

sfd.FileName= this.FileName;string extension = this.FileType.Replace(".", "");

sfd.Filter= string.Format("*.{0}|*.{0}", extension);if (sfd.ShowDialog() ==DialogResult.OK)

{string savePath =sfd.FileName;

StringBuilder sbSql= newStringBuilder();

sbSql.AppendFormat("SELECT files");

sbSql.AppendFormat("FROM FileDetails");

sbSql.AppendFormat("WHERE fName = '{0}'", this.FileName);byte[] fileBytes = DataBaseHelper.SqlExcuteScalar(sbSql.ToString()) as byte[];

FileStream stream= newFileStream(savePath, FileMode.CreateNew);

BinaryWriter writter= newBinaryWriter(stream);

writter.Write(fileBytes,0, (int)this.FileSize);

stream.Close();

writter.Close();

System.Windows.MessageBox.Show("下载完成");

}

}#endregion}public classUploadFile

{public string FileName { get; set; }public long FileSize { get; set; }public string FileType { get; set; }public byte[] FileByteCode { get; set; }

}public classViewModelBase : INotifyPropertyChanged

{public eventPropertyChangedEventHandler PropertyChanged;public void OnPropertyChanged(stringpropertyName)

{

PropertyChangedEventHandler changedEvent=PropertyChanged;if (changedEvent != null)

{

changedEvent.Invoke(this, newPropertyChangedEventArgs(propertyName));

}

}

}public classDataBaseHelper

{public staticSqlConnection GetConn()

{string connStr = string.Format("server=.;uid=sa;pwd=sql@2013;database=SaveAnyFile");

SqlConnection conn= newSqlConnection(connStr);returnconn;

}public static DataTable GetDataTable(stringcommmandText)

{

SqlCommand command= newSqlCommand();

SqlConnection conn=GetConn();

command.Connection=conn;

command.CommandType=CommandType.Text;

command.CommandText=commmandText;

SqlDataAdapter adapter= newSqlDataAdapter(command);

DataTable dataTable= newDataTable();

conn.Open();

adapter.Fill(dataTable);

conn.Close();returndataTable;

}public static int SqlExcuteNoQueryWithParamters(string commmandText, Dictionaryparameter)

{

SqlCommand command= newSqlCommand();

SqlConnection conn=GetConn();

command.Connection=conn;

command.CommandType=CommandType.Text;

command.CommandText=commmandText;foreach (var param inparameter)

{

command.Parameters.AddWithValue(param.Key, param.Value);

}

conn.Open();int num =command.ExecuteNonQuery();

conn.Close();returnnum;

}public static object SqlExcuteScalar(stringcommandText)

{

SqlCommand command= newSqlCommand();

SqlConnection conn=GetConn();

command.Connection=conn;

command.CommandType=CommandType.Text;

command.CommandText=commandText;

conn.Open();object obj =command.ExecuteScalar();

conn.Close();returnobj;

}

}

}