binary 固定长度的二进制数据，其最大长度为 8,000 个字节。 varbinary 可变长度的二进制数据，其最大长度为 8,000 个字节。 image 可变长度的二进制数据，其最大长度为 2^31 - 1 (2,147,483,647) 个字节
二进制类型主要用来存储图片，PDF文档等二进制文件，通常情况下，可以将图片，PDF文档都可以存储在文件系统中，然后在数据库中存储这些文件的路径，这种方式存储比直接存储在数据库中简单，但是访问速度比存储在数据库中慢。实际编码中，使用二进制类型并不多。
eg: 使用数据库存储读取图片的例子
//上传图片
private void btnUpload_Click(object sender, EventArgs e)
{
try
{
string filePath = "";
byte[] bytes = null;
FileStream fs = null;
filePath = pictureBox1.ImageLocation;
if (string.IsNullOrWhiteSpace(filePath))
{
MessageBox.Show("...");
return;
}
if (filePath.ToLower().Contains(".png") || filePath.ToLower().Contains(".jpg"))
{
fs = new FileStream(filePath, FileMode.Open);
bytes = new byte[fs.Length];
fs.Read(bytes, 0, bytes.Length);
using (SqlConnection con = new SqlConnection(
"Data Source=.;Initial Catalog=TestDB;Integrated Security=True;"))
{
con.Open();
SqlCommand cmd = new SqlCommand($"INSERT INTO ImageStore (image,file_path,file_name) VALUES (@image,@file_path,@file_name)", con);
cmd.CommandType = CommandType.Text;
SqlParameter para1 = new SqlParameter("@image", SqlDbType.Image);
SqlParameter para2 = new SqlParameter("@file_path", SqlDbType.NVarChar);
SqlParameter para3 = new SqlParameter("@file_name", SqlDbType.NVarChar);
para1.Value = bytes;
para2.Value = filePath;
para3.Value = filePath.Split('\')[filePath.Split('\').Length - 1];
cmd.Parameters.Add(para1);
cmd.Parameters.Add(para2);
cmd.Parameters.Add(para3);
int res = cmd.ExecuteNonQuery();
if (res > 0) MessageBox.Show("SUCCESS");
if (res <= 0) MessageBox.Show("ERROR");
}
}
else
{
MessageBox.Show("ERROR");
}
}
catch (Exception)
{
throw;
}
}
//打开文件
private void btnOpen_Click(object sender, EventArgs e)
{
try
{
string filePath = "";
OpenFileDialog openFile = new OpenFileDialog();
openFile.Title = "Upload image";
openFile.Filter = "All Files|*.*|PNG|*.png|JPG|*.jpg";
DialogResult dr = openFile.ShowDialog();
if (dr == DialogResult.OK)
{
filePath = openFile.FileName;
pictureBox1.SizeMode = PictureBoxSizeMode.Zoom;
pictureBox1.ImageLocation = filePath;
}
}
catch (Exception)
{
throw;
}
}
//下载图片
private void btnDownLoad_Click(object sender, EventArgs e)
{
FileStream fs = null;
SqlDataAdapter adapter = null;
string filePath = AppDomain.CurrentDomain.BaseDirectory + "temp.jpg";
try
{
DataSet ds = new DataSet();
byte[] bytes = null;
//byte[] bytesRead = new byte[2048];
//string strByte = null;
using (SqlConnection con = new SqlConnection(
"Data Source=.;Initial Catalog=TestDB;Integrated Security=True;"))
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM ImageStore WHERE id = 4", con);
cmd.CommandType = CommandType.Text;
adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
if (ds.Tables[0].Rows.Count <= 0)
{
MessageBox.Show("ERROR");
return;
}
else
{
bytes = (byte[])ds.Tables[0].Rows[0]["image"];
if (bytes.Length > 0)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
fs = new FileStream(filePath, FileMode.OpenOrCreate);
for (int i = 0; i < bytes.Length; i++)
{
fs.WriteByte(bytes[i]);
}
}
pictureBox2.ImageLocation = filePath;
pictureBox2.SizeMode = PictureBoxSizeMode.Zoom;
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (fs != null)
{
fs.Close();
}
if (adapter != null)
{
adapter.Dispose();
}
}
}
在使用数据库结束后，一定要关闭FileStream对象、SqlDataAdapter对象、SqlConnection等。