最近学习了下sqlite的使用,记录一下。
关于.net中的配置可以看 :数据本地存储方案之SQLite:http://www.cnblogs.com/luminji/archive/2010/12/19/1910396.html
这篇文字说的很详细。
首先
sqlite数据库本身是一个文本文件,所以不用考虑服务器上或者客户端要安装什么工具的问题,一般相对与access比较的话,就是access要安装了才能够用。
其次
sqlite的管理工具很多,网上搜一下很多,sqlitestudio是个轻量级的管理工具,我用Navicat for SQLite。当然还有其他的管理工具,凭个人喜好。
接着
开始贴代码,
关于SQLiteHelper类,我比较懒,抄的上面那个链接的,但是还是贴出来.
SQLiteHelper public class SQLiteHelper { /// <summary> /// ConnectionString样¨´例:阰Datasource=Test.db3;Pooling=true;FailIfMissing=false /// </summary> public static string path = System.AppDomain.CurrentDomain.BaseDirectory + "Dal\\x2010.dll"; public static string ConnectionString = "Data Source="+path+";Pooling=true;FailIfMissing=false"; private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params SQLiteParameter[] p) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Parameters.Clear(); cmd.Connection = conn; cmd.CommandText = cmdText; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 30; if (p != null) { foreach (object parm in p) //cmd.Parameters.AddWithValue(string.Empty, parm); cmd.Parameters.Add(parm); } } public static DataSet ExecuteQuery(string cmdText, params SQLiteParameter[] p) { using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand command = new SQLiteCommand()) { DataSet ds = new DataSet(); PrepareCommand(command, conn, cmdText, p); SQLiteDataAdapter da = new SQLiteDataAdapter(command); da.Fill(ds); return ds; } } } public static int ExecuteNonQuery(string cmdText, params SQLiteParameter[] p) { using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand command = new SQLiteCommand()) { PrepareCommand(command, conn, cmdText, p); return command.ExecuteNonQuery(); } } } public static SQLiteDataReader ExecuteReader(string cmdText, params SQLiteParameter[] p) { using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand command = new SQLiteCommand()) { PrepareCommand(command, conn, cmdText, p); return command.ExecuteReader(CommandBehavior.CloseConnection); } } } public static object ExecuteScalar(string cmdText, params SQLiteParameter [] p) { using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand command = new SQLiteCommand()) { PrepareCommand(command, conn, cmdText, p); return command.ExecuteScalar(); } } }
老一套,关于增删改查:
在数据库中建立的一个表:
sqlCREATE TABLE "tb_nodes" ( "id" INTEGER, "fid" INTEGER, "NodeName" TEXT(50), "Remark" TEXT(500), PRIMARY KEY ("id" ASC) );
/// <summary> /// 添ャィェ加ó一?条?数簓据Y /// </summary> /// <param name="node"></param> /// <returns></returns> public int NodeAdd(Model.Nodes node) { if (node != null) { string sql = "insert into tb_Nodes(fid,NodeName,Remark) values (@fid,@NodeName,@Remark);select last_insert_rowid();"; SQLiteParameter[] parameter = { new SQLiteParameter("@fid",node.fid), new SQLiteParameter("@NodeName",node .NodeName), new SQLiteParameter("@Remark",node .Remark) }; object i = Dal.SQLiteHelper.ExecuteScalar(sql, parameter); if (i != null) return Convert.ToInt16(i); else return 0; } else return 0; } /// <summary> /// 修T改?一ー?条?数簓据Y /// </summary> /// <param name="node"></param> /// <returns></returns> public int NodeUpdate(Model.Nodes node) { if (node != null) { string sql = "update tb_Nodes set fid=@fid,NodeName=@NodeName,Remark=@Remark where id=@id "; SQLiteParameter[] parameter = { new SQLiteParameter("@id",node.id ), new SQLiteParameter("@fid",node.fid), new SQLiteParameter("@NodeName",node .NodeName), new SQLiteParameter("@Remark",node .Remark) }; int i = Dal.SQLiteHelper.ExecuteNonQuery(sql, parameter); return i; } else return 0; } /// <summary> /// 删ヲ?除yid为aNodeId的?数簓据Y /// </summary> /// <param name="NodeId"></param> /// <returns></returns> public int NodeDelete(int NodeId) { string sql = "delete from tb_Nodes where id=@NodeId"; SQLiteParameter[] parameter = { new SQLiteParameter("@NodeId",NodeId) }; int i= Dal.SQLiteHelper.ExecuteNonQuery(sql,parameter); return i; }
是不是发现和sql server的操作很相似呢。
有个错误操作:
在操作的时候如果数据库没有的话,它会自动的为你建立一个数据库,当数据库访问路径名不小心写错的时候,由于被新建了一个数据库,不会抛出数据库不存在的错误 ,会抛出 no such table: [tableName] 的错误