sql 获得blob oracle_C# 实现数据库blob字段的存取

1、用C#实现Oracle数据库BLOB字段与本地照片文件的存取操作

1、从数据库读取BLOB数据写入本地文件

// strid 主键字段名 strzp 照片字段名 strtb 数据库表名 strpath 存放照片的本地路径

byte[] img = new byte[0];

OracleCommand cmd = new OracleCommand();

OracleDataReader rs;

cmd.Connection = conn;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select " + strid + ", " + strzp + " from " + strtb;

rs = cmd.ExecuteReader();

while (rs.Read())

{

img = (byte[])rs[1];

System.IO.File.WriteAllBytes(strpath + "" + rs[0].ToString() + ".jpg", img);

}

rs.Close();

2、将本地图片文件存入数据库

//参数说明：数据库表名，主键字段名，照片字段名，写入主键值，本地照片文件名，含完整路径

private Boolean insertpic(string d_tb, string d_id, string d_zp, string id, string filename)

{

int result = 0;

string strsql = "insert into " + d_tb + "(" + d_id + "," + d_zp + ") values('" + id + "', :pic)";

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.CommandText = strsql;

FileStream fs = System.IO.File.OpenRead(filename);

byte[] imagebyte = new byte[fs.Length];

fs.Read(imagebyte, 0, (int)fs.Length);

cmd.Parameters.Add(new OracleParameter(":pic", imagebyte));

try

{

result = cmd.ExecuteNonQuery();

}

catch

{result = 0; }

if (result == 1)

return true;

else

return false;

}

原文链接：https://blog.csdn.net/shaowl111/article/details/80521129

2、用C#实现MySql数据库BLOB字段的读取

读数据：

public List GetList(string typeAuthorization)

{

string selectAll = “需要执行的SQl语句”

List authorizationInfoList = null;

using (MySqlDataReader reader = DAL.MySqlHelper.ExecuteReader(CommandType.Text, selectAll))

{

if (reader != null)

{

authorizationInfoList = new List();

while (reader.Read())

{

AuthorizationInfo info = new AuthorizationInfo();

info.ID = reader.GetInt32(0);

info.InstrumentID = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);

//此处省略其他字段……..

info.MachineID_crypt = reader.IsDBNull(17) ? null : (byte[])reader.GetValue(17);

authorizationInfoList.Add(info);

}

}

}

return authorizationInfoList;

}

写数据：

public int Add(AuthorizationInfo authorizationInfo, string Type)

{

string sqlStr = “执行的sql语句”;

MySqlParameter[] mySqlParameters =

{

new MySqlParameter("@MachineID_crypt",MySqlDbType.Blob),

};

mySqlParameters[10].Value = authorizationInfo.MachineID_crypt;

object obj = DAL.MySqlHelper.ExecuteScalar(DAL.MySqlHelper.Conn, CommandType.Text, sqlStr, mySqlParameters);

if (obj == null)

{

return 0;

}

else

{

return Convert.ToInt32(obj);

}

}