最近,看到一个软件,软件是使用的内嵌数据库。我对这个东西没有实践过,今天突然想亲手做一做!。关于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;
}
}
}
Winform的测试界面和代码如下。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
namespace SQliteTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button2_Click(object sender, EventArgs e)
{
string Sqlstr = "INSERT INTO [Users] VALUES(@username,@mobile)";
SQLiteParameter[] canshu = new SQLiteParameter[]{
DAL.SQLiteHelper.CreateSqliteParameter("@username",DbType.String,50,this.textBox1.Text),
DAL.SQLiteHelper.CreateSqliteParameter("@mobile",DbType.String,50,this.textBox2.Text)
};
DAL.SQLiteHelper.ExecuteNonQuery(Sqlstr, canshu);
Bangding();
}
public void Bangding()
{
SQLiteParameter[] canshu1 = new SQLiteParameter[] { DAL.SQLiteHelper.CreateSqliteParameter("@id", DbType.Int32, 4, 1) };
string Sqlstr = "select * from users";
this.dataGridView1.DataSource = DAL.SQLiteHelper.ExecuteDataset(Sqlstr, canshu1).Tables[0];
}
}
}
项目文件
没有做性能测试,应该问题不大。 紧紧做入门,保存下来以便自己日后会用到。