C#中如何使用SQLite数据库
来源:互联网 发布:java lucene 教程 编辑:程序博客网 时间:2024/05/17 07:12
1.首先需要一下两个文件
2.它的工具类如下
class SQLiteHelper
{
public static SQLiteConnection conn = null;
public static string tableName = "msg_record";
/**
* 获取数据库链接
*/
public static SQLiteConnection getSQLConnection()
{
SQLiteConnection conn = null;
string dbPath = "Data Source =" + Environment.CurrentDirectory + "/MsgRecord.db";
conn = new SQLiteConnection(dbPath);//创建数据库实例,指定文件位置
return conn;
}
/**
* 释放链接
*/
public static void releaseSQLConnection()
{
if (conn != null)
{
conn.Close();
}
}
/**
* 获取SQLcmd
*/
public static SQLiteCommand getSQLCommand()
{
if (conn == null)
{
conn = getSQLConnection();
}
string dbPath = "Data Source =" + Environment.CurrentDirectory + "/MsgRecord.db";
conn = new SQLiteConnection(dbPath);//创建数据库实例,指定文件位置
SQLiteCommand cmd = new SQLiteCommand(conn);
return cmd;
}
/// <summary>
/// 对SQLite数据库执行增删改操作,返回受影响的行数。
/// </summary>
/// <param name="sql">要执行的增删改的SQL语句</param>
/// <param name="parameters">执行增删改语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
{
int affectedRows = 0;
using (SQLiteConnection connection = new SQLiteConnection(getSQLConnection()))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
affectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
}
return affectedRows;
}
//根据uin和userName查询结果
public static List<MsgDetail> findMsgDetails(string uin,string userName,string RemarkName)
{
string sql = "select * from " + tableName + " where uin ='" + uin + "' and ( userName = '" + userName + "'or remarkName = '"+RemarkName+"')";
SQLiteConnection connection = new SQLiteConnection(getSQLConnection());
connection.Open();
SQLiteCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
SQLiteDataReader reader = cmd.ExecuteReader();
List<MsgDetail> data = null;
if(reader.HasRows){
data = new List<MsgDetail>();
while(reader.Read()){
MsgDetail detail = new MsgDetail();
detail.content = reader.GetString(2);
detail.type = reader.GetString(3);
detail.data = reader.GetString(5);
detail.toNickName = reader.GetString(6);
detail.nickName = reader.GetString(7);
detail.direct = reader.GetInt32(9);
data.Add(detail);
}
}
return data;
}
//创建表
public static void createTable()
{
SQLiteConnection conn = null;
string dbPath = "Data Source =" + Environment.CurrentDirectory + "/MsgRecord.db";
conn = new SQLiteConnection(dbPath);//创建数据库实例,指定文件位置
conn.Open();//打开数据库,若文件不存在会自动创建
SQLiteCommand cmdInsert = new SQLiteCommand(conn);
string sql = "CREATE TABLE IF NOT EXISTS " + tableName + "(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,"
+ "uin varchar(255),content varchar(255)," +
"type varchar(255),remarkName varchar(255),data varchar(255),toNickName varchar(255),nickName varchar(255),userName varchar(255),direct integer)";
cmdInsert.CommandText = sql;
cmdInsert.ExecuteNonQuery();
conn.Close();
}
public static string createInsertSql(WxBean tempWx,string remarkName,string FromUserName,int direct,string Content,bool isFile)
{
SelfBean selfData = tempWx.getSelfConfig();
string friendNickName = tempWx.getFriendNickNameByUserName(FromUserName);
if (friendNickName == null)
friendNickName = "";
friendNickName = friendNickName.Replace("<span class=\"", "").Replace("\"></span>", "").Replace(" ", "");
RegexOptions options = RegexOptions.None;
Regex regex = new Regex(@"[ ]{2,}", options);
friendNickName = regex.Replace(friendNickName, @" ");
string friendRemarkName = tempWx.getFriendRemarkNameByUserName(FromUserName);
if (friendRemarkName == null)
friendRemarkName = "";
string nickName = tempWx.getSelfConfig().NikeName;
if (nickName == null)
nickName = "";
nickName = nickName.Replace("<span class=\"", "").Replace("\"></span>", "");
nickName = regex.Replace(nickName, @" ");
string sql = null;
if(!isFile)
sql = "insert into " + SQLiteHelper.tableName + "(uin,content,type,remarkName,data,toNickName,NickName,userName,direct) values('" + selfData.Uin + "','" + Content + "','1','" + remarkName + "','" + DateTime.Now.ToString() + "','" + nickName + "','" + friendNickName + "','" + FromUserName + "'," + direct + ")";
else
sql = "insert into " + SQLiteHelper.tableName + "(uin,content,type,remarkName,data,toNickName,NickName,userName,direct) values('" + selfData.Uin + "','" + Content + "','2','" + remarkName + "','" + DateTime.Now.ToString() + "','" + nickName + "','" + friendNickName + "','" + FromUserName + "'," + direct + ")";
return sql;
}
}
- C#中如何使用SQLite数据库
- c#中使用SQLite数据库
- C#中使用SQLite数据库
- 如何使用c#操作SQLite数据库
- C# 程序中使用 SQLite 数据库
- C# 程序中使用SQLite数据库
- C# winform中使用sqlite数据库
- 如何在Java中使用SQLite数据库
- C#使用SQLite数据库
- C#中使用SQLite
- C#中应用sqlite数据库
- C#中使用SQLite数据库简介(上)
- C#中使用SQLite数据库简介(下)
- c#中使用轻量级数据库sqlite开发总结
- C#使用SQLite数据库问题
- C#使用SQLite数据库详解
- C#使用SQLite数据库详解
- C# 使用sqlite 轻量级数据库
- Mysql主从复制
- 【Spring】Spring Framework Reference Documentation中文版11
- hdu 1237&&九度oj 1019 简单计算器
- Android提示版本更新
- Android Studio插件整理(二)
- C#中如何使用SQLite数据库
- servlet-api与tomcat(可爱的tom猫)
- 动态生成的html元素绑定click事件
- 关于服务器时钟不同步的问题
- 0303
- Spring 使用Junit的MockMvc 写测试用例
- 元素居中的n种方法
- 有向边框RotatedRect的绘制 以及外边框计算
- 动态顺序表----C语言实现