C#对Sqllite操作

前言

数据库的操作也是程序设计中的家常便饭了，关系型数据库中Sqlite3是非常轻量级别的，所以这个数据在小型应用程序的设计中占用很高的比重。当然我这里描述的是1.0版本，也是最原始的方案，大型应用开发中一般选择EF进行桥接，当然我认为其低层逻辑是一致，只是由它帮忙写好了调用的逻辑。

一、连接对象

SQLiteConnection,DbConnection 的 SQLite 实现，位于模块System.Data.SQLite.dll中。重要属性是connectionString，指向需要操作的数据库文件名。具有丰富的事件，可以在需要时进行关联，处理一些业务逻辑。

主要方法,Open打开，Close关闭，但都是没有返回值的，失败是直接出抛出异常，需要开发者自己捕获后处理。

二、命令对象

SQLiteCommand，DbCommand的Sqlite实现。重要属性CommandText，此命令所基于的命令文本，Connection属性关联连接对象。重要方法：ExecuteNonQuery，执行该命令并返回受其影响的插入/更新的行数； ExecuteScalar，执行该命令并返回结果集第一行的第一列（如果存在），如果未返回结果集，则返回 null。

三、命令参数对象

SQLiteParameter，DbParameter 的 SQLite 实现。主要在Sql命令字符串中添加对应占位信息。

 cmdText = "insert t_PlaneData(sn,f_ID,f_Flatness,f_Var_dis,f_Min_dis,f_Max_dis,f_Avg_dis,f_Result)" +
        " value(@sn,@f_ID,@f_Flatness,@f_Var_dis,@f_Min_dis,@f_Max_dis,@f_Avg_dis,@f_Result)";
 parameters = new MySqlParameter[]
 {
         new MySqlParameter("@sn","163748595jhfkk"),
         new MySqlParameter("@f_ID","1823-11"),
         new MySqlParameter("@f_Flatness","NG"),
         new MySqlParameter("@f_Var_dis","163748595jhfkk"),
         new MySqlParameter("@f_Min_dis","1823-11"),
         new MySqlParameter("@f_Max_dis","NG"),
         new MySqlParameter("@f_Avg_dis","163748595jhfkk"),
         new MySqlParameter("@f_Result","1823-11")
 };

四、几种数据库查找方式

1、执行命令无返回值

  //执行命令无返回值
  private static int ExecuteNonQuery(string commandText, params MySqlParameter[] commandParameters)
  {
      conn.Open();

      MySqlCommand command = new MySqlCommand();
      command.Connection = conn;
      command.CommandText = commandText;
      if (commandParameters != null)
          command.Parameters.AddRange(commandParameters);
      int val = command.ExecuteNonQuery();

      command.Dispose();
      conn.Close();

      return val;
  }

2、查询并返回Datatbale

// 查询并返回datatable
private static DataTable ExecuteDataTable(string commandText, params MySqlParameter[] commandParameters)
{
    conn.Open();

    MySqlCommand command = new MySqlCommand();
    command.Connection = conn;
    command.CommandText = commandText;
    if (commandParameters != null)
        command.Parameters.AddRange(commandParameters);
    // 开始读取
    MySqlDataAdapter adapter = new MySqlDataAdapter(command);
    DataTable data = new DataTable();
    adapter.Fill(data);
    // dispose
    adapter.Dispose();
    command.Dispose();
    conn.Close();

    return data;
}

五、调用示例

1、创建表单

        //创建表单
        public void CreateProduct()
        {
            string str = "create table if not exists t_Product(" +
                        "SN varchar(60) primary key," +
                        "Type char(10) not null," +
                        "DateTime timestamp default current_timestamp()," +
                        "State char(2));";
            CreateTable(str);
        }

3、2、插入数据

 //增加数据
 public void InsertProductInfo()
 {
     string str = "insert t_Product(SN,Type,State) value(@SN,@Type,@State)";
     Random random = new Random();　　//放循环体外初始化
     int temp = random.Next(1, 100000);
     string sn = DateTime.Now.ToLongTimeString();
     MySqlParameter[] parameters = new MySqlParameter[]
     {
         new MySqlParameter("@SN",temp+sn),
         new MySqlParameter("@Type","1823-11"),
         new MySqlParameter("@State","NG")
     };
     ExecuteNonQuery(str, parameters);
 }

3、事务方式插入多条数据

  /// <summary>
  /// 执行多条SQL语句，实现数据库事务。
  /// </summary>mysql数据库
  public void ExecuteSqlTran()
  {
      conn.Open();
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      MySqlTransaction tx = conn.BeginTransaction();
      cmd.Transaction = tx;
      try
      {
          string cmdText;//sql语句
          MySqlParameter[] parameters;//相应参数

          //1、插入信息概要
          GetSqlAbs(out cmdText, out parameters);

          //封装对象
          cmd.CommandText = cmdText;
          cmd.Parameters.AddRange(parameters);
          //执行命令
          cmd.ExecuteNonQuery();
          cmd.Parameters.Clear();

          //2、插入检测表1数据
          GetSqlAper(out cmdText, out parameters);
          cmd.CommandText = cmdText;
          cmd.Parameters.AddRange(parameters);
          cmd.ExecuteNonQuery();
          cmd.Parameters.Clear();

          //3、插入检测表2数据

          GetSqlPlan(out cmdText, out parameters);
          cmd.CommandText = cmdText;
          cmd.Parameters.AddRange(parameters);
          cmd.ExecuteNonQuery();
          cmd.Parameters.Clear();

          //4、插入检测表3数据
          GetSqlProfile(out cmdText, out parameters);
          cmd.CommandText = cmdText;
          cmd.Parameters.AddRange(parameters);
          cmd.ExecuteNonQuery();
          cmd.Parameters.Clear();

          tx.Commit();//提交
      }
      catch (MySqlException E)
      {
          tx.Rollback();
          throw new Exception(E.Message);
      }
  }

六、完整帮助类

  class SqlHelp
  {
      private SqlHelp() { }
      public static string ConnectionString= ConfigurationManager.AppSettings["SQLite"].ToString();
      private static SQLiteConnection conn = new SQLiteConnection(ConnectionString);
      



      public static int CreateTable(string commandText)
      {
          conn.Open();
          SQLiteCommand cmd = new SQLiteCommand();
          cmd.Connection = conn;
          cmd.CommandText = commandText;
          int val = cmd.ExecuteNonQuery();

          conn.Close();
          return val;
      }

      public static int ExecuteNonQuery(string commandText, params SQLiteParameter[] commandParameters)
      {
          
          conn.Open();

          SQLiteCommand command = new SQLiteCommand();
          command.Connection = conn;
          command.CommandText = commandText;
          if (commandParameters != null)
              command.Parameters.AddRange(commandParameters);
          int val = command.ExecuteNonQuery();

          command.Dispose();
          conn.Close();

          return val;
       }

      public static int ExecuteNonQuery(SQLiteCommand command, string commandText, params SQLiteParameter[] commandParameters)
      {
          if (command.Connection.State == ConnectionState.Closed)
              command.Connection.Open();
          command.CommandText = commandText;
          command.Parameters.Clear();
          if (commandParameters != null)
              command.Parameters.AddRange(commandParameters);
          return command.ExecuteNonQuery();
      }


      // 查询并返回datatable
      public static DataTable ExecuteDataTable(string commandText, params SQLiteParameter[] commandParameters)
      {
          conn.Open();

          SQLiteCommand command = new SQLiteCommand();
          command.Connection = conn;
          command.CommandText = commandText;
          if (commandParameters != null)
              command.Parameters.AddRange(commandParameters);
          // 开始读取
          SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
          DataTable data = new DataTable();
          adapter.Fill(data);
          // dispose
          adapter.Dispose();
          command.Dispose();
          conn.Close();

          return data;
      }


      // 创建参数
      public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
      {
          SQLiteParameter parameter = new SQLiteParameter();
          parameter.DbType = parameterType;
          parameter.ParameterName = parameterName;
          parameter.Value = parameterValue;
          return parameter;
      }

      public static int IsTableExists(string tableName)
      {
          conn.Open();

          SQLiteCommand command = new SQLiteCommand();
          command.Connection = conn;
          command.CommandText = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='" + tableName + "'";
          int val = Convert.ToInt32(command.ExecuteScalar());

          command.Dispose();
          conn.Close();

          return val;
      }

      public static int CountNum(string str)
      {
          conn.Open();
          SQLiteCommand command = new SQLiteCommand();
          command.Connection = conn;
          command.CommandText = str;
          int val = Convert.ToInt32(command.ExecuteScalar());

          command.Dispose();
          conn.Close();

          return val;
      }

  }