• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

C#使用数据库SQLite

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

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" ?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
  </startup>
</configuration>

 

 

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())
                        {
                            < 

鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
3-5asp.net C#控件基础 RadioButton2发布时间:2022-07-14
下一篇:
c#实体处理工具类发布时间:2022-07-14
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap