110,566
社区成员
发帖
与我相关
我的任务
分享
public static DataTable ExecuteTable(string sql, params SQLiteParameter[] param)
{
DataTable dt = new DataTable();
using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, str))
{
if (param != null)
{
sda.SelectCommand.Parameters.AddRange(param);
}
sda.Fill(dt);
}
return dt;
}
public static int ExecuteNonQuery(string sql, params SQLiteParameter[] param)
{
using(SQLiteConnection con = new SQLiteConnection(str))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
con.Open();
if(param != null)
{
cmd.Parameters.AddRange(param);
}
return cmd.ExecuteNonQuery();
}
}
}
<connectionStrings>
<add connectionString="Data Source=cater.db;Version=3;Pooling=True" name="conStr"/>
</connectionStrings>
public int DeleteMemmberByMemberId(int memmberId)
{
string sql = "UPDATE `MemmberInfo` SET `DelFlag`=1 WHERE `MemmberId`=1";
return SqliteHelper.ExecuteNonQuery(sql);
}
public static int ExecuteNonQuery(string sql, params SQLiteParameter[] param)
{
try
{
using (SQLiteConnection con = new SQLiteConnection(str))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
con.Open();
if (param != null)
{
cmd.Parameters.AddRange(param);
}
return cmd.ExecuteNonQuery();
}
}
}
catch (SQLiteException se)
{
//捕获异常
//断点试了下,还是报了那个:Unalbe to open the database file
Console.WriteLine(se.Message + " \n\n" + se.Source + "\n\n" + se.StackTrace + "\n\n" + se.Data);
return 0;
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SQLite;
using System.Data;
namespace ThreeFloors.DAL
{
public class SqliteHelper
{
//连接字符串
private static readonly string str = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
/// <summary>
/// 增删改
/// 20180723
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">sql参数</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string sql, params SQLiteParameter[] param)
{
try
{
using (SQLiteConnection con = new SQLiteConnection(str))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
con.Open();
if (param != null)
{
cmd.Parameters.AddRange(param);
}
return cmd.ExecuteNonQuery();
}
}
}
catch (SQLiteException se)
{
//捕获异常
//断点试了下,还是报了那个:Unalbe to open the database file
Console.WriteLine(se.Message + " \n\n" + se.Source + "\n\n" + se.StackTrace + "\n\n" + se.Data);
return 0;
}
}
/// <summary>
/// 查询
/// 20180723
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">sql参数</param>
/// <returns>首行首列</returns>
public static object ExecuteScalar(string sql, params SQLiteParameter[] param)
{
using(SQLiteConnection con = new SQLiteConnection(str))
{
using(SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
con.Open();
if (param != null)
{
cmd.Parameters.AddRange(param);
}
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 多行查询
/// 20180723
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">sql参数</param>
/// <returns>SQLiteDateReader</returns>
public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] param)
{
using (SQLiteConnection con = new SQLiteConnection(str))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
if (param != null)
{
cmd.Parameters.AddRange(param);
}
try
{
con.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
con.Close();
con.Dispose();
throw ex;
}
}
}
}
/// <summary>
/// 查询多行数据
/// 20180723
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">sql参数</param>
/// <returns>一个表</returns>
public static DataTable ExecuteTable(string sql, params SQLiteParameter[] param)
{
DataTable dt = new DataTable();
using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, str))
{
if (param != null)
{
sda.SelectCommand.Parameters.AddRange(param);
}
sda.Fill(dt);
}
return dt;
}
}
}
public static bool CreateBook(Book book)
{
try
{
SQLiteConnection conn = new SQLiteConnection("Data Source=db/Books.sqlite;");
conn.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Book(ID, BookName, Price, Rowguid) VALUES(@ID1, @BookName1, @Price1, @Rowguid1)";
cmd.Parameters.Add(new SQLiteParameter("ID1", book.ID));
cmd.Parameters.Add(new SQLiteParameter("BookName1", book.BookName));
cmd.Parameters.Add(new SQLiteParameter("Price1", book.Price));
cmd.Parameters.Add(new SQLiteParameter("Rowguid1", book.Rowguid));
int i = cmd.ExecuteNonQuery();
return i == 1;
}
catch (SQLiteException se)
{
MessageBox.Show(se.Message + " \n\n" + se.Source + "\n\n" + se.StackTrace + "\n\n" + se.Data);
return false;
}
catch (ArgumentException ae)
{
MessageBox.Show(ae.Message + " \n\n" + ae.Source + "\n\n" + ae.StackTrace + "\n\n" + ae.Data);
return false;
}
catch (Exception ex)
{
//Do any logging operation here if necessary
MessageBox.Show(ex.Message + "\n\n" + ex.Source + "\n\n" + ex.StackTrace + "\n\n" + ex.Data);
return false;
}
}