在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
1.数据库下载地址 http://sqlite.phxsoftware.com/ 2.下载完成添加引用System.Data.SQLite.dll 3.SQLite操作通用类
代码
using System;
using System.Collections.Generic; using System.Text; using System.Data.SQLite; using System.Data; using System.Data.Common; namespace PNet { class SQLiteDBHelper { private string connectionString = string.Empty; /// <summary> /// 构造函数 /// </summary> /// <param name="dbPath">SQLite数据库文件路径</param> public SQLiteDBHelper(string dbPath) { this.connectionString = "Data Source=" + dbPath; } /// <summary> /// 判断SQLite数据库表是否存在 /// </summary> /// <param name="dbPath">要创建的SQLite数据库文件路径</param> public bool IsTableExist(string tableName) { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='"+tableName+"'"; int iaaa= Convert.ToInt32(command.ExecuteScalar()); if (Convert.ToInt32(command.ExecuteScalar()) == 0) { return false; } else { return true; } } } } /// <summary> /// 创建SQLite数据库文件 /// </summary> /// <param name="dbPath">要创建的SQLite数据库文件路径</param> public static void CreateDB(string dbPath, string sql) { using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) { connection.Open(); using (SQLiteCommand command = new SQLiteCommand(connection)) { // command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)"; command.CommandText = sql; command.ExecuteNonQuery(); //command.CommandText = "DROP TABLE Demo"; //command.ExecuteNonQuery(); } } } /// <summary> /// 对SQLite数据库执行增删改操作,返回受影响的行数。 /// </summary> /// <param name="sql">要执行的增删改的SQL语句</param> /// <param name="parameters">执行增删改语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters) { int affectedRows = 0; using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { 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; } /// <summary> /// 执行一个查询语句,返回一个关联的SQLiteDataReader实例 /// </summary> /// <param name="sql">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters) { SQLiteConnection connection = new SQLiteConnection(connectionString); SQLiteCommand command = new SQLiteCommand(sql, connection); if (parameters != null) { command.Parameters.AddRange(parameters); } connection.Open(); return command.ExecuteReader(CommandBehavior.CloseConnection); } /// <summary> /// 执行一个查询语句,返回一个包含查询结果的DataTable /// </summary> /// <param name="sql">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters) { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { if (parameters != null) { command.Parameters.AddRange(parameters); } SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); DataTable data = new DataTable(); adapter.Fill(data); return data; } } } /// <summary> /// 执行一个查询语句,返回查询结果的第一行第一列 /// </summary> /// <param name="sql">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param> /// <returns></returns> public Object ExecuteScalar(string sql, SQLiteParameter[] parameters) { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { if (parameters != null) { command.Parameters.AddRange(parameters); } SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); DataTable data = new DataTable(); adapter.Fill(data); return data; } } } /// <summary> /// 查询数据库中的所有数据类型信息 /// </summary> /// <returns></returns> public DataTable GetSchema() { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); DataTable data = connection.GetSchema("TABLES"); connection.Close(); //foreach (DataColumn column in data.Columns) //{ // Console.WriteLine(column.ColumnName); //} return data; } } /// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public DataSet Query(string SQLString) { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SQLite.SQLiteException ex) { throw new Exception(ex.Message); } return ds; } } } }
4.使用举例
代码
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 CleanFileServer { public partial class FrmUser : Form { public FrmUser() { InitializeComponent(); } private string dbPath = Environment.CurrentDirectory + "\\" + "USERDB.db3"; private void btnAdd_Click(object sender, EventArgs e) { lblMsg.Text = ""; string userName = txtUserName.Text.Trim(); string pwd = txtPwd.Text.Trim(); if (userName == "") { lblMsg.Text = "用户名不能为空!"; return; } if (pwd == "") { lblMsg.Text = "密码不能为空!"; return; } CreateTable(); InsertData(userName, pwd); ShowData(); txtUserName.Text = ""; txtPwd.Text = ""; } private void CreateTable() { //如果不存在改数据库文件,则创建该数据库文件 SQLiteDBHelper db = new SQLiteDBHelper(dbPath); if (!db.IsTableExist("USER")) { string sql = "CREATE TABLE USER(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,UserName varchar(30),Password varchar(50),Level varchar(2),AddDate datetime)"; db.ExecuteNonQuery(sql, null); } } private void InsertData(string userName, string pwd) { string sql = "INSERT INTO USER(UserName,Password,AddDate)values(@UserName,@Password,@AddDate)"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@UserName",userName), new SQLiteParameter("@Password",pwd), new SQLiteParameter("@AddDate",DateTime.Now) }; db.ExecuteNonQuery(sql, parameters); } private void ShowData() { string sql = "select UserName,Password,AddDate from User order by id desc"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); DataSet ds=db.Query(sql); gdvUser.DataSource = ds.Tables["ds"]; } private void ReadData() { string id; string userName; string pwd; string addDate; //查询从50条起的20条记录 //string sql = "select * from User order by id desc limit 50 offset 20"; string sql = "select id,UserName,Password,AddDate from User order by id desc"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); using (SQLiteDataReader dr = db.ExecuteReader(sql, null)) { while (dr.Read()) { id = dr["id"].ToString(); userName = dr["UserName"].ToString(); pwd = dr["Password"].ToString(); addDate = dr["AddDate"].ToString(); } } } private void FrmUser_Load(object sender, EventArgs e) { lblMsg.Text = ""; ShowData(); } private void btnDelete_Click(object sender, EventArgs e) { lblMsg.Text = ""; string userName = txtUserName.Text.Trim(); string pwd = txtPwd.Text.Trim(); if (userName == "") { return; } try { DialogResult dlR = MessageBox.Show(this, "确定要删除吗?", "请确认", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign); if (dlR == DialogResult.Yes) { string sql = "delete from User where UserName=@UserName "; SQLiteDBHelper sqlHelper = new SQLiteDBHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@UserName",userName) }; sqlHelper.ExecuteNonQuery(sql, parameters); lblMsg.Text = "成功删除!"; ShowData(); } } catch (Exception ex) { lblMsg.Text = ex.Message; } }
5.出现错误 混合模式程序集是针对“v2.0.50727”版的运行时生成的,在没有配置其他信息的情况下,无法在 4.0 运行时中加载该程序集。 在App.config添加 <?xml version="1.0" encoding="utf-8" ?>
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SQLite; using System.Data; using System.Windows.Forms; namespace Dispatcha_PadWin10 { public class DBHelper { private string connectionString = "Data Source=" + Environment.CurrentDirectory + "\\" + "db.db"; public void ExecuteNonQuery(string sql) { using (SQLiteConnection conn = new SQLiteConnection(connectionString)) { conn.Open(); using (SQLiteCommand command = new SQLiteCommand(conn)) { command.CommandText = sql; command.ExecuteNonQuery(); } } } public bool IsWorkStationExist(string work_station) { bool isExist = false; string sql = "select work_station from station where work_station='" + work_station + "'"; using (SQLiteConnection conn = new SQLiteConnection(connectionString)) { conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandText = sql; using (SQLiteDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { isExist= dr.Read(); } } } return isExist; } public void LoadStationToDgv(DataGridView dgv) { string sql = "select work_station,work_x,work_y,store_station,store_x,store_y,can_charger from station"; using (SQLiteConnection conn = new SQLiteConnection(connectionString)) { conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand(sql,conn)) { using (SQLiteDataReader dr = cmd.ExecuteReader()) { while( dr.Read()) { < |
请发表评论