前言
数据库的操作也是程序设计中的家常便饭了,关系型数据库中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;}}