SQLite帮助类SQlitehelper 实现对SQLite数据的增删改查

?

public class SQLiteHelper

   {

 

       public const string sConn = "Data Source=" + @"path";

 

       /// <summary>

       /// 查询，返回object ,得到查询结果的首行首列的值，若没有首行首列返回NUll

       /// </summary>

       /// <param name="sql">查询语句</param>

       /// <param name="parameters">可选参数</param>

       /// <returns></returns>

       public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)

       {

           return ExecuteScalar(sql, CommandType.Text, parameters);

 

       }

 

       /// <summary>

       /// 查询，返回object  ，执行SQl语句，得到查询结果的首行首列，若没有首行首列返回null

       /// </summary>

       /// <param name="sql">查询语句</param>

       /// <param name="type">如何解释命令字符串</param>

       /// <param name="parameters">可选的参数</param>

       /// <returns></returns>

       public static object ExecuteScalar(string sql, CommandType type, params SQLiteParameter[] parameters)

       {

           using (SQLiteConnection conn = new SQLiteConnection(sConn))

           {

               conn.Open();

               SQLiteCommand cmd = new SQLiteCommand(sql, conn);

 

               cmd.CommandType = type;

               cmd.Parameters.AddRange(parameters);

               object obj = cmd.ExecuteScalar();

               cmd.Parameters.Clear();

               return obj;

           }

       }

 

     /// <summary>

     /// 执行增、删、改

     /// </summary>

     /// <param name="sql">sql语句</param>

     /// <param name="parameters">可选参数</param>

     /// <returns>返回int 得到受影响的行数</returns>

       public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)

       {

           return ExecuteNonQuery(sql, CommandType.Text, parameters);

       }

       /// <summary>

       /// 执行增、删、改

       /// </summary>

       /// <param name="sql">sql语句</param>

       /// <param name="type">如何解释命令字符串</param>

       /// <param name="parameters">可选参数</param>

       /// <returns></returns>

       public static int ExecuteNonQuery(string sql, CommandType type, params SQLiteParameter[] parameters)

       {

           using (SQLiteConnection conn = new SQLiteConnection(sConn))

           {

               conn.Open();

               SQLiteCommand cmd = new SQLiteCommand(sql, conn);

               cmd.CommandType = type;

               cmd.Parameters.AddRange(parameters);

               int num = cmd.ExecuteNonQuery();

               cmd.Parameters.Clear();

               return num;

           }

       }

 

 

       /// <summary>

       /// 断开式查询，查询多个列

       /// </summary>

       /// <param name="sql">sql语句</param>

       /// <param name="parameters">可选参数</param>

       /// <returns>返回DataTable类型</returns>

       public static DataTable ExecuteTable(string sql, params SQLiteParameter[] parameters)

       {

           return ExecuteTable(sql, CommandType.Text, parameters);

       }

 

       /// <summary>

       /// 断开式查询，查询结果可为多个列

       /// </summary>

       /// <param name="sql">sql语句</param>

       /// <param name="type">如何解释命令字符串</param>

       /// <param name="parameters">可选参数</param>

       /// <returns></returns>

       public static DataTable ExecuteTable(string sql, CommandType type, params SQLiteParameter[] parameters)

       {

           SQLiteConnection conn = new SQLiteConnection(sConn);

           conn.Open();

           SQLiteCommand cmd = new SQLiteCommand(sql, conn);

           cmd.CommandType = type;

           cmd.Parameters.AddRange(parameters);

           SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);

           DataSet ds = new DataSet();

           sda.Fill(ds);

           conn.Close();

           DataTable dt = ds.Tables[0];

           return dt;

       }

   }