C# 使用数据库SQLite

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())
                        {
                            int index = dgv.Rows.Add();
                            dgv.Rows[index].Cells[0].Value = dr["work_station"].ToString();
                            dgv.Rows[index].Cells[1].Value = dr["work_x"].ToString();
                            dgv.Rows[index].Cells[2].Value = dr["work_y"].ToString();
                            dgv.Rows[index].Cells[3].Value = dr["store_station"].ToString();
                            dgv.Rows[index].Cells[4].Value = dr["store_x"].ToString();
                            dgv.Rows[index].Cells[5].Value = dr["store_y"].ToString();
                            dgv.Rows[index].Cells[6].Value = dr["can_charger"].ToString();

                        }


                    }

                }


            }



        }


        public List<string> getWorkStations()
        {
            List<string> lst = new List<string>();

            string sql = "select work_station 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())
                        {
                            lst.Add(dr["work_station"].ToString().Trim());                        

                        }


                    }

                }


            }

            return lst;
        }

        public StationInfo getWorkStationInfo(string workStation)
        {
            StationInfo stationInfo = new StationInfo();
            string sql = "select work_station,work_x,work_y,store_station,store_x,store_y,can_charger from station "
                + "where work_station='"+ workStation+"'";
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {

                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {

                        while (dr.Read())
                        {
                            stationInfo.Work_station = dr["work_station"].ToString();
                            stationInfo.Work_x =double.Parse( dr["work_x"].ToString());
                            stationInfo.Work_y = double.Parse(dr["work_y"].ToString());
                            stationInfo.Store_station = dr["store_station"].ToString();
                            stationInfo.Store_x = double.Parse(dr["store_x"].ToString());
                            stationInfo.Store_y = double.Parse(dr["store_y"].ToString());
                            stationInfo.Can_charge = int.Parse(dr["can_charger"].ToString());

                        }


                    }

                }


            }

            return stationInfo;

        }


    }
}

 

 

posted @ 2010-11-01 15:48  ike_li  阅读(792)  评论(0)  编辑  收藏  举报