C#封装的Sqlite访问类实例
程序员文章站
2022-06-03 18:49:51
本文实例讲述了c#封装的sqlite访问类。分享给大家供大家参考。具体分析如下:
c#封装的sqlite访问类,要访问sqlite这下简单了,直接调用此类中的方法即可...
本文实例讲述了c#封装的sqlite访问类。分享给大家供大家参考。具体分析如下:
c#封装的sqlite访问类,要访问sqlite这下简单了,直接调用此类中的方法即可
using system; using system.collections; using system.collections.specialized; using system.data; using system.configuration; using system.data.sqlite; namespace dal { internal abstract class dbhelpersqlite { public static string connectionstring = "data source=" + appdomain.currentdomain.basedirectory + @"dataleaf.db;version=3;"; public dbhelpersqlite() { } #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语句 /// <summary> /// 执行sql语句,返回影响的记录数 /// </summary> /// <param name="sqlstring">sql语句</param> /// <returns>影响的记录数</returns> 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); } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">多条sql语句</param> 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); } } } /// <summary> /// 执行带一个存储过程参数的的sql语句。 /// </summary> /// <param name="sqlstring">sql语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> 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(); } } } /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strsql">sql语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> 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(); } } } /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="sqlstring">计算查询结果语句</param> /// <returns>查询结果(object)</returns> 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); } } } } /// <summary> /// 执行查询语句,返回sqlitedatareader /// </summary> /// <param name="strsql">查询语句</param> /// <returns>sqlitedatareader</returns> 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); } } /// <summary> /// 执行查询语句,返回dataset /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> 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; } } #endregion #region 执行带参数的sql语句 /// <summary> /// 执行sql语句,返回影响的记录数 /// </summary> /// <param name="sqlstring">sql语句</param> /// <returns>影响的记录数</returns> 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); } } } } /// <summary> /// 执行多条sql语句,实现数据库事务。 /// </summary> /// <param name="sqlstringlist">sql语句的哈希表(key为sql语句,value是该语句的sqliteparameter[])</param> 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; } } } } /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="sqlstring">计算查询结果语句</param> /// <returns>查询结果(object)</returns> 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); } } } } /// <summary> /// 执行查询语句,返回sqlitedatareader /// </summary> /// <param name="strsql">查询语句</param> /// <returns>sqlitedatareader</returns> 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); } } /// <summary> /// 执行查询语句,返回dataset /// </summary> /// <param name="sqlstring">查询语句</param> /// <returns>dataset</returns> public static dataset query(string sqlstring, params sqliteparameter[] cmdparms) { using (sqliteconnection connection = new sqliteconnection(connectionstring)) { sqlitecommand cmd = new sqlitecommand(); preparecommand(cmd, connection, null, sqlstring, cmdparms); using (sqlitedataadapter da = new sqlitedataadapter(cmd)) { dataset ds = new dataset(); try { da.fill(ds, "ds"); cmd.parameters.clear(); } catch (system.data.sqlite.sqliteexception ex) { throw new exception(ex.message); } return ds; } } } private static void preparecommand(sqlitecommand cmd, sqliteconnection conn, sqlitetransaction trans, string cmdtext, sqliteparameter[] cmdparms) { if (conn.state != connectionstate.open) conn.open(); cmd.connection = conn; cmd.commandtext = cmdtext; if (trans != null) cmd.transaction = trans; cmd.commandtype = commandtype.text;//cmdtype; if (cmdparms != null) { foreach (sqliteparameter parm in cmdparms) cmd.parameters.add(parm); } } #endregion } }
希望本文所述对大家的c#程序设计有所帮助。
上一篇: 香油煎鸡蛋能治咳嗽吗,有哪些可以治咳嗽的食疗方法呢?
下一篇: 无向图