private string InsertDB(string bitPath, BitInfo info, SqliteHelper.SqliteHelper mysql)
{
string sql = "select * from u_picture where u_picture.picture_file='" + bitPath + "';";
DataTable dt = mysql.DBReadTable(sql);
if (dt == null)
{
return("连接数据库失败");
}
else if (dt.Rows.Count == 0)
{
sql = "insert into u_picture(picture_name,picture_file,picture_title,picture_time,picture_author,picture_level,picture_info,picture_key,picture_person,picture_org,picture_type,picture_marker,picture_marktime,picture_width,picture_height,picture_hr,picture_vr,picture_bit,picture_gps,picture_size,picture_format)values('" + info.文件名称 + "','" + bitPath + "','" + info.标题 + "','" + info.时间 + "','" + info.作者 + "','" + info.评级 + "','" + info.摘要 + "','" + info.关键词 + "','" + info.人物 + "','" + info.机构 + "','" + info.分类 + "','" + info.标引人 + "','" + info.标引时间 + "','" + info.图片宽 + "','" + info.图片高 + "','" + info.水平分辨率 + "','" + info.垂直分辨率 + "','" + info.位深度 + "','" + info.gps信息 + "','" + info.图片大小 + "','" + info.图片格式 + "');";
int res = mysql.DBUpdateInsert(sql);
if (res == -1)
{
return("写入数据失败");
}
}
else if (dt.Rows.Count == 1)
{
sql = "update u_picture set picture_name='" + info.文件名称 + "',picture_title='" + info.标题 + "',picture_time='" + info.时间 + "',picture_author='" + info.作者 + "',picture_level='" + info.评级 + "',picture_info='" + info.摘要 + "',picture_key='" + info.关键词 + "', picture_person='" + info.人物 + "',picture_org='" + info.机构 + "',picture_type='" + info.分类 + "',picture_marker='" + info.标引人 + "',picture_marktime='" + info.标引时间 + "',picture_width='" + info.图片宽 + "',picture_height='" + info.图片高 + "',picture_hr='" + info.水平分辨率 + "',picture_vr='" + info.垂直分辨率 + "',picture_bit='" + info.位深度 + "',picture_gps='" + info.gps信息 + "',picture_size='" + info.图片大小 + "',picture_format='" + info.图片格式 + "' where id='" + dt.Rows[0]["id"].ToString() + "';";
int res = mysql.DBUpdateInsert(sql);
if (res != 1)
{
return("写入数据库失败");
}
}
else
{
return("数据库数据有误，存在多条相同的记录");
}
return(string.Empty);
}
private BitInfo GetBitInfo(string bitPath)
{
BitInfo bitinfo = null;
SqliteHelper.SqliteHelper mysql = new SqliteHelper.SqliteHelper();
if (!mysql.DBConnect(_localDBname))
{
throw new Exception("连接数据库失败");
}
string sql = "select * from u_picture where u_picture.picture_file='" + bitPath + "';";
DataTable bitDt = mysql.DBReadTable(sql);
mysql.DBDisConnect();
if (bitDt == null)
{
throw new Exception("查询数据库失败");
}
else if (bitDt.Rows.Count == 0)
{
bitinfo = new BitInfo();
GetBitmapInfo(bitPath, bitinfo);
}
else if (bitDt.Rows.Count == 1)
{
BitInfo[] bitinfos = DataTableConvert.TableToT <BitInfo>(bitDt);
bitinfo = bitinfos[0];
}
else
{
throw new Exception("数据库数据有误，存在多条相同的记录");
}
return(bitinfo);
}
/// <summary>
/// 下载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDownload_MouseUp(object sender, MouseEventArgs e)
{
if (_pathArray == null)
{
return;
}
FolderBrowserDialog gbd = new FolderBrowserDialog();
if (gbd.ShowDialog() == DialogResult.OK)
{
string path = gbd.SelectedPath + "\\记录表_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
SqliteHelper.SqliteHelper mysql = new SqliteHelper.SqliteHelper();
if (!mysql.DBConnect(_localDBname))
{
MessageBox.Show("查询数据库失败");
}
try
{
string content = string.Join("','", _pathArray);
string sql = "select picture_name as 文件名称, picture_file as 路径, picture_title as 标题,picture_time as 时间,picture_author as 作者,picture_level as 评级,picture_info as 摘要,picture_key as 关键词,picture_person as 人物,picture_org as 机构,picture_type as 分类,picture_marker as 标引人,picture_marktime as 标引时间,picture_width as 图片宽,picture_height as 图片高,picture_hr as 水平分辨率,picture_vr as 垂直分辨率,picture_bit as 位深度,picture_gps as gps信息,picture_size as 图片大小,picture_format as 图片格式 from u_picture where picture_file in ('" + content + "')";
DataTable dt = mysql.DBReadTable(sql);
if (dt == null)
{
throw new Exception("查询数据库失败");
}
bool res = ExcelHandler.Write(path, dt);
if (!res)
{
throw new Exception("导出表格失败");
}
else
{
MessageBox.Show("导出成功");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
mysql.DBDisConnect();
}
}
}