C# 匯入檔案到資料庫中，檔案以資料流2進位的方式存入資料表中。
流程:
1 新增資料表來存檔案名、檔案以資料流2進位的方式存入資料表中
DATA的欄位格式為 varbinary，才能將檔案存入
CREATE TABLE [dbo].[tblFiles](
[id] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](50) NULL,
[CONTENTTYPE] [nvarchar](250) NULL,
[DATA] [varbinary](max) NULL,
CONSTRAINT [PK_tblFiles] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO2 新增WINFORM程式做檔案的上傳
3 在查詢後，用dataGridView1顯示上傳檔案的資料表內容，另外新增DataGridViewLinkColumn做檔案下載的功能
完整的CODE:
public partial class FrmDB1 : Form
{
SqlConnection sqlConn = new SqlConnection();
SqlCommand sqlComm = new SqlCommand();
string connectionString;
StringBuilder sbSql = new StringBuilder();
StringBuilder sbSqlQuery = new StringBuilder();
SqlTransaction tran;
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adapter1 = new SqlDataAdapter();
SqlCommandBuilder sqlCmdBuilder1 = new SqlCommandBuilder();
DataSet ds1 = new DataSet();
DataTable dt = new DataTable();
string tablename = null;
int rownum = 0;
int result;
int rowindex;
int ROWSINDEX;
int COLUMNSINDEX;
string ID;
public FrmDB1()
{
InitializeComponent();
SEARCH(textBox1.Text.Trim());
SETdataGridView1();
}
#region FUNCTION
public void SEARCH(string ID)
{
SqlDataAdapter adapter1 = new SqlDataAdapter();
SqlCommandBuilder sqlCmdBuilder1 = new SqlCommandBuilder();
DataSet ds1 = new DataSet();
try
{
sbSql.Clear();
if (!string.IsNullOrEmpty(ID))
{
sbSql.AppendFormat(@"
", ID);
}
else
{
sbSql.AppendFormat(@"
SELECT
[id]
,[NAME]
FROM [DB].[dbo].[tblFiles]
ORDER BY [NAME]
");
}
sqlConn = new SqlConnection(sqlsb.ConnectionString);
adapter1 = new SqlDataAdapter(@"" + sbSql, sqlConn);
sqlCmdBuilder1 = new SqlCommandBuilder(adapter1);
sqlConn.Open();
ds1.Clear();
adapter1.Fill(ds1, "ds1");
sqlConn.Close();
if (ds1.Tables["ds1"].Rows.Count >= 1)
{
dataGridView1.DataSource = ds1.Tables["ds1"];
dataGridView1.AutoResizeColumns();
}
else
{
dataGridView1.DataSource = null;
}
}
catch
{
}
finally
{
}
}
//設定下載欄
public void SETdataGridView1()
{
DataGridViewLinkColumn lnkDownload = new DataGridViewLinkColumn();
lnkDownload.UseColumnTextForLinkValue = true;
lnkDownload.LinkBehavior = LinkBehavior.SystemDefault;
lnkDownload.Name = "lnkDownload";
lnkDownload.HeaderText = "Download";
lnkDownload.Text = "Download";
dataGridView1.Columns.Insert(2, lnkDownload);
dataGridView1.CellContentClick += new DataGridViewCellEventHandler(DataGridView1_CellClick);
}
private void DataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
StringBuilder SQL = new StringBuilder();
if (e.RowIndex >= 0)
{
DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
int id = Convert.ToInt16((row.Cells["id"].Value));
byte[] bytes;
string fileName, contentType;
sqlConn = new SqlConnection(sqlsb.ConnectionString);
using (SqlConnection con = sqlConn)
{
using (SqlCommand cmd = new SqlCommand())
{
SQL.AppendFormat(@"
SELECT
[id]
,[NAME]
,[CONTENTTYPE]
,[DATA]
FROM [TKRESEARCH].[dbo].[tblFiles]
where id=@id
");
cmd.CommandText = SQL.ToString();
cmd.Parameters.AddWithValue("@id", id);
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
bytes = (byte[])sdr["DATA"];
contentType = sdr["CONTENTTYPE"].ToString();
fileName = sdr["NAME"].ToString();
Stream stream;
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "All files (*.*)|*.*";
saveFileDialog.FilterIndex = 1;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.FileName = fileName;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
stream = saveFileDialog.OpenFile();
stream.Write(bytes, 0, bytes.Length);
stream.Close();
}
}
}
con.Close();
}
}
}
private void UploadFile()
{
string FILETYPE = null;
string contentType = "";
byte[] bytes = null;
using (OpenFileDialog openFileDialog1 = new OpenFileDialog())
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
string fileName = openFileDialog1.FileName;
bytes = File.ReadAllBytes(fileName);
//Set the contenttype based on File Extension
switch (Path.GetExtension(fileName))
{
case ".doc":
contentType = "application/msword";
break;
case ".xls":
contentType = "application/vnd.ms-excel";
break;
case ".xlsx":
contentType = "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
break;
case ".pdf":
contentType = "application/pdf";
break;
case ".jpg":
contentType = "image/jpeg";
break;
case ".png":
contentType = "image/png";
break;
case ".gif":
contentType = "image/gif";
break;
case ".bmp":
contentType = "image/bmp";
break;
}
sqlConn = new SqlConnection(sqlsb.ConnectionString);
using (SqlConnection conn = sqlConn)
{
string sql = "INSERT INTO [TKRESEARCH].[dbo].[tblFiles] VALUES(@Name, @ContentType, @Data)";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@Name", Path.GetFileName(fileName));
cmd.Parameters.AddWithValue("@ContentType", contentType);
cmd.Parameters.AddWithValue("@Data", bytes);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
SEARCH(textBox1.Text.Trim());
}
}
}
#endregion
#region BUTTON
private void button1_Click(object sender, EventArgs e)
{
SEARCH(textBox1.Text.Trim());
}
private void button2_Click(object sender, EventArgs e)
{
UploadFile();
}
WINFORM畫面
上傳畫面:
按下載畫面:
自我LV~