Sqlite中创建数据库，创建表，并实现增删改查功能

上代码，前提是要引用sqlite的dll啊！百度上很多，，，，，，

 public static string _onPath = @"C:\OnLineMonitor.db3";
    /// <summary>
    /// 新建数据库文件
    /// </summary>
    /// <param name="dbPath">数据库文件路径及名称</param>
    /// <returns>新建成功，返回true，否则返回false</returns>
    static public void NewDbFile()
    {
        try
        {
            if (!File.Exists(_onPath))
            {
                SQLiteConnection.CreateFile(_onPath);    //通过这一句代码就可以创建一个sqlite数据库了
                NewTable("RealTimeData");
                NewTableWanager("ParameterManager");
                NewTableWaring("WaringManager");
            }
        }
        catch (Exception ex)
        {
            throw new Exception("新建数据库文件" + _onPath + "失败：" + ex.Message);
        }
    }
    /// <summary>
    /// 创建实时数据表
    /// </summary>
    /// <param name="dbPath">指定数据库文件</param>
    /// <param name="tableName">表名称</param>
    static public void NewTable(string tableName)
    {
        string ColumnsRows = "DateTime varchar,CellValue varchar,V varchar,R varchar,C varchar,GroupValue varchar,COM varchar,StartDate varchar,SUMDate varchar,Interval varchar";    //sqlite 执行语句创建表并添加字段，字段类型 （varchar）
        SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + _onPath);
        if (sqliteConn.State != System.Data.ConnectionState.Open)
        {
            sqliteConn.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = "CREATE TABLE " + tableName + "(" + ColumnsRows + ")";
            cmd.ExecuteNonQuery();
        }
        sqliteConn.Close();
    }

在表中给相应的字段添加数据

  public static void AddWaringStars(DataTable Stars)//通过table添加数据
    {
        if (!FrmMain._onPath) return;   //选择否时，不保存数据
        var time = DateTime.Now.ToLocalTime().ToString();
        SQLiteConnection conn = new SQLiteConnection("data source=" + _onPath);
        conn.Open();
        SQLiteCommand cmd = new SQLiteCommand(conn);
        for (int i = 0; i < Stars.Rows.Count - 1; i++)
        {
            cmd.CommandText = "insert into WaringManager(CellValue,WaringTime,WaringLocation,WaringMessage,WaringValue,isTF) values(@CellValue,@WaringTime,@WaringLocation,@WaringMessage,@WaringValue,@isTF)";
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                                           new SQLiteParameter("@CellValue",Stars.Rows[i].ItemArray[0]),
                                           new SQLiteParameter("@WaringTime",Stars.Rows[i].ItemArray[1]),
                                           new SQLiteParameter("@WaringLocation",Stars.Rows[i].ItemArray[2]),
                                           new SQLiteParameter("@WaringMessage",Stars.Rows[i].ItemArray[3]),
                                           new SQLiteParameter("@WaringValue",Stars.Rows[i].ItemArray[4]),
                                           new SQLiteParameter("@isTF","1"),
                                           };
            cmd.Parameters.AddRange(parameters);
            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }

查询数据

    /// <summary>
    /// 获取时间点的数据
    /// </summary>
    /// <param name="timedian"></param>
    /// <returns></returns>
    public static DataTable SelectData(string timedian, string com, string group)
    {
        if (!FrmMain._onPath) return null;   //选择否时，不保存数据
        DataTable _tab = new DataTable();
        SQLiteConnection conn = new SQLiteConnection("data source=" + _onPath);
        conn.Open();
        var sql = string.Format("Select * from RealTimeData where DateTime='{0}' and GroupValue='{1}' and COM='{2}'", timedian, group, com);   通过sql查询语句的条件查询整个表
        SQLiteDataAdapter adp = new SQLiteDataAdapter(sql, conn);
        DataSet set = new DataSet();
        adp.Fill(set, "table");
        _tab = set.Tables[0];
        conn.Close();
        return _tab;
    }

删除数据 当然了也是通过条件语句删除

    /// <summary>
    /// 根据 组和com来删除数据
    /// </summary>
    /// <param name="group"></param>
    /// <param name="com"></param>
    public static void DeleteRealTimeDataTree(string group, string com)
    {
        string sql = string.Format("delete from realtimedata where  GroupValue='{0}' and COM='{1}'", group, com);
        SQLiteConnection conn = new SQLiteConnection("data source=" + _onPath);
        conn.Open();
        SQLiteCommand cmd = new SQLiteCommand(conn);
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
        conn.Close();

    }

修改语句

    /// <summary>
    /// 在RealTimeData中根据数据 每条的获取时间 和 总的起始时间来添加总时间（SUM）
    /// </summary>
    public static void UpdateRealTimeDataSUM()
    {
        try
        {
            SQLiteConnection conn = new SQLiteConnection("data source=" + _onPath);
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(conn);
            cmd.CommandText = "update RealTimeData set SUMDate=@SUMDate where DateTime='" + time + "' and StartDate='" + UcParameterCorrect.SQLstartdates + "'";
            SQLiteParameter[] parametrs = new SQLiteParameter[] { new SQLiteParameter("@SUMDate", sumdate) };
            cmd.Parameters.AddRange(parametrs);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception)
        {

        }
    }

其实就是换汤不换药，只是换个sql查询语句 ，对于数据库小白来说，应该能简单的认识到每个语句的作用了