Question

如何读取SQL server数据库中varbinary字段内容

Answer 1

如果把照片直接保存在SQL Server数据库中，微软推荐用varbinary(MAX)字段。下面的代码演示了用C#操作varbinary(MAX)字段的基本方法。
1、新增记录
private void btnBrowse_Click(object sender, EventArgs e)//浏览照片
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.Filter = "*.jpg(jpg文件)|*.jpg|*.gif|*.gif";
dlg.FilterIndex = 1;
if (dlg.ShowDialog()==DialogResult.OK)
{
textBox3.Text = dlg.FileName;
pictureBox1.Image = Image.FromFile(dlg.FileName);
}
}
//新增记录
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
{
String sql = "insert into emp(name,age,photo) values(@name,@age,@photo)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@name", textBox1.Text);
cmd.Parameters.AddWithValue("@age", Convert.ToInt32(textBox2.Text));
//byte[] b;
//using(FileStream fs=new FileStream(textBox3.Text,FileMode.Open,FileAccess.Read))
//{
// b = new byte[fs.Length];
// fs.Read(b, 0, (int)fs.Length);
//}
byte[] b;
if (textBox3.Text != "")
{
b = File.ReadAllBytes(textBox3.Text);
cmd.Parameters.AddWithValue("@photo", b);
}
else
{
cmd.Parameters.AddWithValue("@photo", System.Data.SqlTypes.SqlBinary.Null);
}
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
2、显示记录信息并提供修改功能
private void FormDetail_Load(object sender, EventArgs e)//显示记录详细信息
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
{
String sql = "select * from emp where id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if(dr.Read())
{
textBox1.Text = dr[1].ToString();
textBox2.Text = dr[2].ToString();
if (!dr.IsDBNull(3))//防止照片字段为空
{
System.Data.SqlTypes.SqlBytes bytes = dr.GetSqlBytes(3);
pictureBox1.Image=Image.FromStream(bytes.Stream);//显示照片
}
}
}
}
}
private void btnSave_Click(object sender, EventArgs e)//更新记录
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
{
byte[] b;
if (textBox3.Text != "")//需要更新照片
{
String sql = "update emp set name=@name,age=@age,photo=@photo where id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@name", textBox1.Text);
cmd.Parameters.AddWithValue("@age", Convert.ToInt32(textBox2.Text));
cmd.Parameters.AddWithValue("@id", id);
b = File.ReadAllBytes(textBox3.Text);
cmd.Parameters.AddWithValue("@photo", b);
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else//不需要更新照片
{
String sql = "update emp set name=@name,age=@age where id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@name", textBox1.Text);
cmd.Parameters.AddWithValue("@age", Convert.ToInt32(textBox2.Text));
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}

Answer 2

cast(字段 as 你要的类型)，例如：
cast(col as varchar(max))
已经测试，可以使用