配置addkey=sqlstringvalue=~\demo.db/ . /* ************************************* *作用：sqlliteserver操作实现 *作者：monkey *日期:2011-10-27 *网址：www.redglove.net ************************************* */ using system; using system.col
配置 .
/**************************************
* 作用：sqllite server操作实现
* 作者：monkey
* 日期: 2011-10-27
* 网址：www.redglove.net
**************************************/
using system;
using system.collections;
using system.collections.specialized;
using system.data;
using system.data.sqlite;//这个可以去网上下载
using system.configuration;
public class sqlitehelper
{
//数据库连接字符串(web.config来配置)，可以动态更改sqlstring支持多数据库.
public static string connectionstring = data source= + system.web.httpcontext.current.server.mappath(configurationmanager.appsettings[sqlstring]);
public sqlitehelper() { }
#region 公用方法
public static int getmaxid(string fieldname, string tablename)
{
string strsql = select max( + fieldname + )+1 from + tablename;
object obj = getsingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.parse(obj.tostring());
}
}
public static bool exists(string strsql)
{
object obj = getsingle(strsql);
int cmdresult;
if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.parse(obj.tostring());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
public static bool exists(string strsql, params sqliteparameter[] cmdparms)
{
object obj = getsingle(strsql, cmdparms);
int cmdresult;
if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.parse(obj.tostring());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 执行简单sql语句
///
/// 执行sql语句，返回影响的记录数
///
/// sql语句
/// 影响的记录数
public static int executesql(string sqlstring)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
using (sqlitecommand cmd = new sqlitecommand(sqlstring, connection))
{
try
{
connection.open();
int rows = cmd.executenonquery();
return rows;
}
catch (system.data.sqlite.sqliteexception e)
{
connection.close();
throw new exception(e.message);
}
}
}
}
///
/// 执行sql语句，设置命令的执行等待时间
///
///
///
///
public static int executesqlbytime(string sqlstring, int times)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
using (sqlitecommand cmd = new sqlitecommand(sqlstring, connection))
{
try
{
connection.open();
cmd.commandtimeout = times;
int rows = cmd.executenonquery();
return rows;
}
catch (system.data.sqlite.sqliteexception e)
{
connection.close();
throw new exception(e.message);
}
}
}
}
///
/// 执行多条sql语句，实现数据库事务。
///
/// 多条sql语句
public static void executesqltran(arraylist sqlstringlist)
{
using (sqliteconnection conn = new sqliteconnection(connectionstring))
{
conn.open();
sqlitecommand cmd = new sqlitecommand();
cmd.connection = conn;
sqlitetransaction tx = conn.begintransaction();
cmd.transaction = tx;
try
{
for (int n = 0; n sqlstringlist.count; n++)
{
string strsql = sqlstringlist[n].tostring();
if (strsql.trim().length > 1)
{
cmd.commandtext = strsql;
cmd.executenonquery();
}
}
tx.commit();
}
catch (system.data.sqlite.sqliteexception e)
{
tx.rollback();
throw new exception(e.message);
}
}
}
///
/// 执行带一个存储过程参数的的sql语句。
///
/// sql语句
/// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加
/// 影响的记录数
public static int executesql(string sqlstring, string content)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
sqlitecommand cmd = new sqlitecommand(sqlstring, connection);
sqliteparameter myparameter = new sqliteparameter(@content, dbtype.string);
myparameter.value = content;
cmd.parameters.add(myparameter);
try
{
connection.open();
int rows = cmd.executenonquery();
return rows;
}
catch (system.data.sqlite.sqliteexception e)
{
throw new exception(e.message);
}
finally
{
cmd.dispose();
connection.close();
}
}
}
///
/// 执行带一个存储过程参数的的sql语句。
///
/// sql语句
/// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加
/// 影响的记录数
public static object executesqlget(string sqlstring, string content)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
sqlitecommand cmd = new sqlitecommand(sqlstring, connection);
sqliteparameter myparameter = new sqliteparameter(@content, dbtype.string);
myparameter.value = content;
cmd.parameters.add(myparameter);
try
{
connection.open();
object obj = cmd.executescalar();
if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
{
return null;
}
else
{
return obj;
}
}
catch (system.data.sqlite.sqliteexception e)
{
throw new exception(e.message);
}
finally
{
cmd.dispose();
connection.close();
}
}
}
///
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
///
/// sql语句
/// 图像字节,数据库的字段类型为image的情况
/// 影响的记录数
public static int executesqlinsertimg(string strsql, byte[] fs)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
sqlitecommand cmd = new sqlitecommand(strsql, connection);
sqliteparameter myparameter = new sqliteparameter(@fs, dbtype.binary);
myparameter.value = fs;
cmd.parameters.add(myparameter);
try
{
connection.open();
int rows = cmd.executenonquery();
return rows;
}
catch (system.data.sqlite.sqliteexception e)
{
throw new exception(e.message);
}
finally
{
cmd.dispose();
connection.close();
}
}
}
///
/// 执行一条计算查询结果语句，返回查询结果（object）。
///
/// 计算查询结果语句
/// 查询结果（object）
public static object getsingle(string sqlstring)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
using (sqlitecommand cmd = new sqlitecommand(sqlstring, connection))
{
try
{
connection.open();
object obj = cmd.executescalar();
if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
{
return null;
}
else
{
return obj;
}
}
catch (system.data.sqlite.sqliteexception e)
{
connection.close();
throw new exception(e.message);
}
}
}
}
///
/// 执行查询语句，返回sqlitedatareader(使用该方法切记要手工关闭sqlitedatareader和连接)
///
/// 查询语句
/// sqlitedatareader
public static sqlitedatareader executereader(string strsql)
{
sqliteconnection connection = new sqliteconnection(connectionstring);
sqlitecommand cmd = new sqlitecommand(strsql, connection);
try
{
connection.open();
sqlitedatareader myreader = cmd.executereader();
return myreader;
}
catch (system.data.sqlite.sqliteexception e)
{
throw new exception(e.message);
}
//finally //不能在此关闭，否则，返回的对象将无法使用
//{
// cmd.dispose();
// connection.close();
//}
}
///
/// 执行查询语句，返回dataset
///
/// 查询语句
/// dataset
public static dataset query(string sqlstring)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
dataset ds = new dataset();
try
{
connection.open();
sqlitedataadapter command = new sqlitedataadapter(sqlstring, connection);
command.fill(ds, ds);
}
catch (system.data.sqlite.sqliteexception ex)
{
throw new exception(ex.message);
}
return ds;
}
}
public static dataset query(string sqlstring, string tablename)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
dataset ds = new dataset();
try
{
connection.open();
sqlitedataadapter command = new sqlitedataadapter(sqlstring, connection);
command.fill(ds, tablename);
}
catch (system.data.sqlite.sqliteexception ex)
{
throw new exception(ex.message);
}
return ds;
}
}
///
/// 执行查询语句，返回dataset,设置命令的执行等待时间
///
///
///
///
public static dataset query(string sqlstring, int times)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
dataset ds = new dataset();
try
{
connection.open();
sqlitedataadapter command = new sqlitedataadapter(sqlstring, connection);
command.selectcommand.commandtimeout = times;
command.fill(ds, ds);
}
catch (system.data.sqlite.sqliteexception ex)
{
throw new exception(ex.message);
}
return ds;
}
}
#endregion
#region 执行带参数的sql语句
///
/// 执行sql语句，返回影响的记录数
///
/// sql语句
/// 影响的记录数
public static int executesql(string sqlstring, params sqliteparameter[] cmdparms)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
using (sqlitecommand cmd = new sqlitecommand())
{
try
{
preparecommand(cmd, connection, null, sqlstring, cmdparms);
int rows = cmd.executenonquery();
cmd.parameters.clear();
return rows;
}
catch (system.data.sqlite.sqliteexception e)
{
throw new exception(e.message);
}
}
}
}
///
/// 执行多条sql语句，实现数据库事务。
///
/// sql语句的哈希表（key为sql语句，value是该语句的sqliteparameter[]）
public static void executesqltran(hashtable sqlstringlist)
{
using (sqliteconnection conn = new sqliteconnection(connectionstring))
{
conn.open();
using (sqlitetransaction trans = conn.begintransaction())
{
sqlitecommand cmd = new sqlitecommand();
try
{
//循环
foreach (dictionaryentry myde in sqlstringlist)
{
string cmdtext = myde.key.tostring();
sqliteparameter[] cmdparms = (sqliteparameter[]) myde.value;
preparecommand(cmd, conn, trans, cmdtext, cmdparms);
int val = cmd.executenonquery();
cmd.parameters.clear();
trans.commit();
}
}
catch
{
trans.rollback();
throw;
}
}
}
}
///
/// 执行一条计算查询结果语句，返回查询结果（object）。
///
/// 计算查询结果语句
/// 查询结果（object）
public static object getsingle(string sqlstring, params sqliteparameter[] cmdparms)
{
using (sqliteconnection connection = new sqliteconnection(connectionstring))
{
using (sqlitecommand cmd = new sqlitecommand())
{
try
{
preparecommand(cmd, connection, null, sqlstring, cmdparms);
object obj = cmd.executescalar();
cmd.parameters.clear();
if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
{
return null;
}
else
{
return obj;
}
}
catch (system.data.sqlite.sqliteexception e)
{
throw new exception(e.message);
}
}
}
}
///
/// 执行查询语句，返回sqlitedatareader (使用该方法切记要手工关闭sqlitedatareader和连接)
///
/// 查询语句
/// sqlitedatareader
public static sqlitedatareader executereader(string sqlstring, params sqliteparameter[] cmdparms)
{
sqliteconnection connection = new sqliteconnection(connectionstring);
sqlitecommand cmd = new sqlitecommand();
try
{
preparecommand(cmd, connection, null, sqlstring, cmdparms);
sqlitedatareader myreader = cmd.executereader();
cmd.parameters.clear();
return myreader;
}
catch (system.data.sqlite.sqliteexception e)
{
throw new exception(e.message);
}
//finally //不能在此关闭，否则，返回的对象将无法使用
//{
// cmd.dispose();
// connection.close();