本方法适用sqlite和sqlserver,应用时,只需要写好sql语句,并把参数写到数组里,代入以下方法即可。 比如:
sqlparemeter[] paras=new sqlparemeter();//sql语句的参数构造器
{
new sqlparemeter("@qq",qq);
new sqlparemeter("@pwd",pwd);
}
sql=" delete from user where qq=@qq and pwd=@pwd' " //此处已经将参数格式化了,不怕sql注入攻击
int no=sqlhelp.sqliteNoQuery(sql,paras);
if(no>0)
{
删除成功!
}
以上就完成了sql帮助类的调用了。
sqlhelp.cs sql帮助类:
static string dbPath="connstr";
/// <summary>
/// sqlite和sqlserver通用的 批量增、删、改方法
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="paras">参数数组</param>
/// <returns></returns>
public static int sqliteNoQuery(string sql, params SQLiteParameter[] paras) //params表示该参数可写可不写
{
int queryNo = 0;
using (SQLiteConnection conn = new SQLiteConnection(dbPath))
{
sqliter.OpenDB();
SQLiteCommand command = new SQLiteCommand(sql, conn);
command.Parameters.AddRange(paras);//加入参数数组
queryNo= command.ExecuteNonQuery();
// sqliter.CloseDB(); //using内的closed方法可以省略,因为using自带dispose接口
}
return queryNo;
}
/// <summary>
/// 返回第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static object sqliteScare(string sql, params SQLiteParameter[] paras)
{
object obj = null;
using (SQLiteConnection conn = new SQLiteConnection(dbPath))
{
sqliter.OpenDB();
SQLiteCommand command = new SQLiteCommand(sql, conn);
command.Parameters.AddRange(paras);//加入参数数组
obj = command.ExecuteNonQuery();
}
return obj;
}
/// <summary>
/// 以游标形式返回多行多列
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static SQLiteDataReader sqliteReader(string sql, params SQLiteParameter[] paras)
{
SQLiteConnection conn = new SQLiteConnection(dbPath);
sqliter.OpenDB();
SQLiteCommand command = new SQLiteCommand(sql, conn);
command.Parameters.AddRange(paras);//加入参数数组
SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);//执行完sql语句,自行关闭连接
return reader;
}
/// <summary>
/// 将查询结果存在临时表里返回
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="paras">查询参数数组</param>
public static DataTable sqlitequery(string sql, params SQLiteParameter[] paras)
{
DataTable dt = new DataTable();
using (SQLiteConnection conn = new SQLiteConnection(dbPath))
{
sqliter.OpenDB();
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLiteDataAdapter adpt = new SQLiteDataAdapter(command);
adpt.Fill(dt);
}
return dt;
}