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);// disposeadapter.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();}// 查询并返回datatablepublic 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);// disposeadapter.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;}}