SqliteHelper帮助类 及 创建db数据库、表，插入带有图片的数据
这个Console程序，原本是从一个专门的数据库中获取更新后的专利信息，然后向db数据库文件中插入专利信息，最后生成一个同步更新专利信息的文件。
客户端会到一个固定的地方下载这个更新文件，然后通过客户端一个Console程序读取db数据库文件，将更新的专利信息导入到Sql Server数据库中。
现在，这个程序让我改成了一个简单的添加用户信息的程序，只是一个测试程序。
个人总结了一下SqliteHelper类，自认为还算可以，欢迎大家来指点、补充。
SqliteHelper
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SQLite;
namespace dbTest
{
/// <summary>
/// SqlLite数据库帮助类
/// </summary>
public class SqliteDBHelper
{
/// <summary>
/// 执行对数据库的增删改操作
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="sql">执行的SQL语句</param>
/// <param name="parms">参数集合，无参数可传null</param>
/// <returns>影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, string sql, SQLiteParameter[] parms)
{
SQLiteCommand cmd = null;
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
cmd = new SQLiteCommand(sql, conn);
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
conn.Open();
int value = cmd.ExecuteNonQuery();
conn.Close();
return value;
}
}
/// <summary>
/// 执行对数据的查询操作ExecuteScalar
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="sql">执行的SQL语句</param>
/// <param name="parms">参数集合，无参数可传null</param>
/// <returns>object</returns>
public static object ExecuteScalar(string connectionString, string sql, SQLiteParameter[] parms)
{
SQLiteCommand cmd = new SQLiteCommand();
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
cmd.Connection = conn;
cmd.CommandText = sql;
if (parms != null)
{
foreach (SQLiteParameter parm in parms)
{
cmd.Parameters.Add(parm);
}
}
conn.Open();
object value = cmd.ExecuteScalar();
conn.Close();
return value;
}
}
/// <summary>
/// 执行对数据的查询操作ExecuteReader
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="sql">执行的SQL语句</param>
/// <param name="parms">参数集合，无参数可传null</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteReader(string connectionString, string sql, SQLiteParameter[] parms)
{
DataTable dt = new DataTable();
SQLiteCommand cmd = new SQLiteCommand();
SQLiteConnection conn = new SQLiteConnection(connectionString);
cmd.Connection = conn;
cmd.CommandText = sql;
if (parms != null)
{
foreach (SQLiteParameter parm in parms)
{
cmd.Parameters.Add(parm);
}
}
conn.Open();
using (SQLiteDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>
/// 执行对数据的查询操作ExecuteReader，关闭数据库连接
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="sql">执行的SQL语句</param>
/// <param name="parms">参数集合，无参数可传null</param>
/// <param name="conn">SQLiteConnection</param>
/// <returns>SQLiteDataReader</returns>
public static SQLiteDataReader ExecuteDataReader(string connectionString, string sql, SQLiteParameter[] parms, out SQLiteConnection conn)
{
DataTable dt = new DataTable();
SQLiteCommand cmd = new SQLiteCommand();
conn = new SQLiteConnection(connectionString);
cmd.Connection = conn;
cmd.CommandText = sql;
if (parms != null)
{
foreach (SQLiteParameter parm in parms)
{
cmd.Parameters.Add(parm);
}
}
conn.Open();
SQLiteDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
}
}
【创建db数据库、表】
//取得数据库所在位置连接字符串
protected static string ConnectionString = String.Format("data source={0}", @"D:\data\sqlite.db");
/// <summary>
/// 创建db数据库
/// </summary>
/// <returns>布尔值，是否创建成功</returns>
public bool CreateDatabase()
{
Console.WriteLine("创建db数据库中...");
try
{
FileInfo DatabaseFile = new FileInfo(@"D:\data\sqlite.db");
if (!DatabaseFile.Exists)
{
if (!DatabaseFile.Directory.Exists)
{
DatabaseFile.Directory.Create();
}
SQLiteConnection.CreateFile(DatabaseFile.FullName);
CreateTable();
Console.WriteLine("创建db数据库成功！");
}
}
catch (Exception e)
{
Console.WriteLine("创建db数据库失败..." + e.Message);
}
return File.Exists(@"D:\data\sqlite.db");
}
/// <summary>
/// 向db数据库中插入表
/// </summary>
private void CreateTable()
{
//创建测试用户信息表
StringBuilder userTable = new StringBuilder();
userTable.Append("CREATE TABLE Users");
userTable.Append("(");
userTable.Append("ID int NOT NULL,");
userTable.Append("UserNumber varchar(512) NULL,");
userTable.Append("UserName varchar(512) NULL,");
userTable.Append("Age int NULL,");
userTable.Append("Birthday datetime NULL");
userTable.Append(")");
SqliteDBHelper.ExecuteNonQuery(ConnectionString, userTable.ToString(), null);
//创建测试用户头像信息表
StringBuilder photoTable = new StringBuilder();
photoTable.Append("CREATE TABLE UserPhotos");
photoTable.Append("(");
photoTable.Append("UserId int NULL,");
photoTable.Append("PhotoName varchar(2048) NULL,");
photoTable.Append("PhotoUrl varchar(8000) NULL,");
photoTable.Append("PhotoSourcePath varchar(8000) NULL");
photoTable.Append(")");
SqliteDBHelper.ExecuteNonQuery(ConnectionString, photoTable.ToString(), null);
}
【插入用户、头像信息】
/// <summary>
/// 添加用户信息（返回执行的行数）
/// </summary>
/// <param name="model">实体</param>
/// <param name="tableName">表名</param>
/// <returns>返回执行的行数</returns>
public int InsertUser(UserInfo model, string tableName)
{
string sql = "insert into " + tableName + " (ID,UserNumber,UserName,Age,Birthday) values (@ID,@UserNumber,@UserName,@Age,@Birthday)";
SQLiteParameter[] paras = new SQLiteParameter[5];
paras[0] = new SQLiteParameter() { ParameterName = "@ID", Value = model.Id };
paras[1] = new SQLiteParameter() { ParameterName = "@UserNumber", Value = model.UserNumber };
paras[2] = new SQLiteParameter() { ParameterName = "@UserName", Value = model.UserName };
paras[3] = new SQLiteParameter() { ParameterName = "@Age", Value = model.Age };
paras[4] = new SQLiteParameter() { ParameterName = "@Birthday", Value = model.Birthday };
//返回值
int count = SqliteDBHelper.ExecuteNonQuery(ConnectionString, sql, paras);
if (count > 0)
{
PhotoInfo info = new PhotoInfo();
info.UserId = model.Id;
info.PhotoName = model.UserNumber;
//头像文件的源路径
string imageFilePath = @"D:\image\";
string[] fileList = Directory.GetFileSystemEntries(imageFilePath);
//遍历所有的文件和目录
foreach (string file in fileList)
{
if (Path.GetFileName(file).Split('.')[0] == model.UserNumber)
{
info.PhotoUrl = ImageToByte(file);
info.PhotoSourcePath = file;
if (InsertPhoto(info, "userphotos") > 0)
{
Console.WriteLine(model.UserName + "用户头像插入成功。");
}
else
{
Console.WriteLine(model.UserName + "用户头像插入失败。");
}
}
}
}
return count;
}
/// <summary>
/// 插入头像
/// </summary>
/// <param name="model">实体</param>
/// <param name="tableName">表名</param>
/// <returns>返回执行的行数</returns>
private int InsertPhoto(PhotoInfo model, string tableName)
{
string sql = "insert into " + tableName + " (UserId,PhotoName,PhotoUrl,PhotoSourcePath) values (@UserId,@PhotoName,@PhotoUrl,@PhotoSourcePath)";
SQLiteParameter[] parms = new SQLiteParameter[4];
parms[0] = new SQLiteParameter() { ParameterName = "@UserId", Value = model.UserId };
parms[1] = new SQLiteParameter() { ParameterName = "@PhotoName", Value = model.PhotoName };
parms[2] = new SQLiteParameter() { ParameterName = "@PhotoUrl", Value = model.PhotoUrl };
parms[3] = new SQLiteParameter() { ParameterName = "@PhotoSourcePath", Value = model.PhotoSourcePath };
int count = SqliteDBHelper.ExecuteNonQuery(ConnectionString, sql, parms);
return count;
}
/// <summary>
/// 将图片转换成二进制流
/// </summary>
/// <param name="imagePath">图片路径</param>
/// <returns>byte[]</returns>
private byte[] ImageToByte(string imagePath)
{
FileStream fs = new FileStream(imagePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
byte[] imageByte = br.ReadBytes((int)fs.Length);
br.Close();
fs.Close();
return imageByte;
}
【实体类 UserInfo】
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace dbTest
{
[Serializable]
public class UserInfo
{
private int _id;
private string _userNumber;
private string _userName;
private int _age;
private DateTime _birthday;
public int Id
{
get { return _id; }
set { _id = value; }
}
public string UserNumber
{
get { return _userNumber; }
set { _userNumber = value; }
}
public string UserName
{
get { return _userName; }
set { _userName = value; }
}
public int Age
{
get { return _age; }
set { _age = value; }
}
public DateTime Birthday
{
get { return _birthday; }
set { _birthday = value; }
}
}
}
【实体类 PhotoInfo】
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace dbTest
{
[Serializable]
public class PhotoInfo
{
private int _userId;
private string _photoName;
private byte[] _photoUrl;
private string _photoSourcePath;
public int UserId
{
get { return _userId; }
set { _userId = value; }
}
public string PhotoName
{
get { return _photoName; }
set { _photoName = value; }
}
public byte[] PhotoUrl
{
get { return _photoUrl; }
set { _photoUrl = value; }
}
public string PhotoSourcePath
{
get { return _photoSourcePath; }
set { _photoSourcePath = value; }
}
}
}
最后是一个简单的调用。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace dbTest
{
class Program
{
static void Main(string[] args)
{
SqliteDAL dal = new SqliteDAL();
dal.CreateDatabase();
UserInfo user = new UserInfo();
user.Id = 1;
user.UserNumber = "001";
user.UserName = "admin";
user.Age = 26;
user.Birthday = Convert.ToDateTime("1985-05-16");
dal.InsertUser(user, "users");
Console.ReadLine();
}
}
}
浙公网安备 33010602011771号