C# 内嵌数据库 SQLite
C# 内嵌数据库 SQLite
最近,看到一个软件,软件是使用的内嵌数据库。我对这个东西没有实践过,今天突然想亲手做一做!。关于SQLIte的资料我就不多说了,网上都有。我自己也整理了一部分,基本上可以对SQLite有个全面的了解了。我这里就不废话了,直接上我自己的代码。
1:首先要先下载一个SQLite的C#开发的支持的东西。下载地址:sqllite下载地址:tp://sourceforge.net/projects/sqlite-dotnet2/files/SQLite%20for%20ADO.NET%202.0/1.0.66.0/SQLite-1.0.66.0-setup.exe/download
点击下载后,安装就行。(注意:安装的时候最好不要开着vs2008)
2:打开vs2008,新建一个winform应用程序
3: 打开“服务器资源管理器” 在这里右击“数据连接”---->添加连接 ------》更改 -----》更改数据源为:“SQLLite Database FIle“----->确定 ---->Browse选择你的数据库,(注意选择的文件一定是以后缀“.db”结束的,如果你还没建库,那么你就随便建一个以后缀为"db"结束的文件,放到你项目的bin目录的debug中(很重要,程序发布的路径))
我从网上下载的一个SQLite helper 根本就用不成,经过我的修补终于可以用了。
- using System;
- using System.Collections.Generic;
- using System.Text;
- using System.Web;
- using System.Configuration;
- using System.Data;
- using System.Data.SQLite;
- namespace DAL
- {
- public class SQLiteHelper
- {
- /// <summary>
- /// 获得连接对象
- /// </summary>
- /// <returns></returns>
- public static SQLiteConnection GetSQLiteConnection()
- {
- SQLiteConnection conn=new SQLiteConnection(@"data source=E:\SQliteTest\SQliteTest\bin\Debug\JxyDb.db");
- conn.SetPassword("123456");//数据库密码
- return conn;
- }
- private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] 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 (SQLiteParameter parm in p)
- {
- cmd.Parameters.AddWithValue(parm.ParameterName, parm.Value);
- }
- }
- }
- public static DataSet ExecuteDataset(string cmdText, params object[] p)
- {
- DataSet ds = new DataSet();
- SQLiteCommand command = new SQLiteCommand();
- using (SQLiteConnection connection = GetSQLiteConnection())
- {
- PrepareCommand(command, connection, cmdText, p);
- SQLiteDataAdapter da = new SQLiteDataAdapter(command);
- da.Fill(ds);
- }
- return ds;
- }
- public static DataRow ExecuteDataRow(string cmdText, params object[] p)
- {
- DataSet ds = ExecuteDataset(cmdText, p);
- if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
- return ds.Tables[0].Rows[0];
- return null;
- }
- /// <summary>
- /// 返回受影响的行数
- /// </summary>
- /// <param name="cmdText">a</param>
- /// <param name="commandParameters">传入的参数</param>
- /// <returns></returns>
- public static int ExecuteNonQuery(string cmdText, params object[] p)
- {
- SQLiteCommand command = new SQLiteCommand();
- using (SQLiteConnection connection = GetSQLiteConnection())
- {
- PrepareCommand(command, connection, cmdText, p);
- return command.ExecuteNonQuery();
- }
- }
- /// <summary>
- /// 返回SqlDataReader对象
- /// </summary>
- /// <param name="cmdText"></param>
- /// <param name="commandParameters">传入的参数</param>
- /// <returns></returns>
- public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
- {
- SQLiteCommand command = new SQLiteCommand();
- SQLiteConnection connection = GetSQLiteConnection();
- try
- {
- PrepareCommand(command, connection, cmdText, p);
- SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
- return reader;
- }
- catch
- {
- connection.Close();
- throw;
- }
- }
- /// <summary>
- /// 返回结果集中的第一行第一列,忽略其他行或列
- /// </summary>
- /// <param name="cmdText"></param>
- /// <param name="commandParameters">传入的参数</param>
- /// <returns></returns>
- public static object ExecuteScalar(string cmdText, params object[] p)
- {
- SQLiteCommand cmd = new SQLiteCommand();
- using (SQLiteConnection connection = GetSQLiteConnection())
- {
- PrepareCommand(cmd, connection, cmdText, p);
- return cmd.ExecuteScalar();
- }
- }
- /// <summary>
- /// 分页
- /// </summary>
- /// <param name="recordCount"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <param name="cmdText"></param>
- /// <param name="countText"></param>
- /// <param name="p"></param>
- /// <returns></returns>
- public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params object[] p)
- {
- if (recordCount < 0)
- recordCount = int.Parse(ExecuteScalar(countText, p).ToString());
- DataSet ds = new DataSet();
- SQLiteCommand command = new SQLiteCommand();
- using (SQLiteConnection connection = GetSQLiteConnection())
- {
- PrepareCommand(command, connection, cmdText, p);
- SQLiteDataAdapter da = new SQLiteDataAdapter(command);
- da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
- }
- return ds;
- }
- /**/
- /// <summary>
- /// 放回一个SQLiteParameter
- /// </summary>
- /// <param name="name">参数名字</param>
- /// <param name="type">参数类型</param>
- /// <param name="size">参数大小</param>
- /// <param name="value">参数值</param>
- /// <returns>SQLiteParameter的值</returns>
- public static SQLiteParameter CreateSqliteParameter(string name, DbType type, int size, object value)
- {
- SQLiteParameter parm = new SQLiteParameter(name, type, size);
- parm.Value = value;
- return parm;
- }
- }
- }
|
评论暂时关闭