java sqlite dbhelper_Sqlite操作类-DbHelperSQLite

1: /// 2: /// 数据访问基础类(基于SQLite)3: /// 可以用户可以修改满足自己项目的需要。4: /// 5: public abstract class DbHelperSQLite6: {7: //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.8: public static string connectionString = PubConstant.ConnectionString;9: public DbHelperSQLite()10: {11: }12:13:14: #region 公用方法15:16: public static int GetMaxID(string FieldName, string TableName)17: {18: string strsql = "select max(" + FieldName + ")+1 from " + TableName;19: object obj = GetSingle(strsql);20: if (obj == null)21: {22: return 1;23: }24: else25: {26: return int.Parse(obj.ToString());27: }28: }29: public static bool Exists(string strSql)30: {31: object obj = GetSingle(strSql);32: int cmdresult;33: if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))34: {35: cmdresult = 0;36: }37: else38: {39: cmdresult = int.Parse(obj.ToString());40: }41: if (cmdresult == 0)42: {43: return false;44: }45: else46: {47: return true;48: }49: }50: public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)51: {52: object obj = GetSingle(strSql, cmdParms);53: int cmdresult;54: if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))55: {56: cmdresult = 0;57: }58: else59: {60: cmdresult = int.Parse(obj.ToString());61: }62: if (cmdresult == 0)63: {64: return false;65: }66: else67: {68: return true;69: }70: }71:72: #endregion73:74: #region 执行简单SQL语句75:76: /// 77: /// 执行SQL语句，返回影响的记录数78: /// 79: /// SQL语句80: /// 影响的记录数81: public static int ExecuteSql(string SQLString)82: {83: using (SQLiteConnection connection = new SQLiteConnection(connectionString))84: {85: using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))86: {87: try88: {89: connection.Open();90: int rows = cmd.ExecuteNonQuery();91: return rows;92: }93: catch (System.Data.SQLite.SQLiteException E)94: {95: connection.Close();96: throw new Exception(E.Message);97: }98: }99: }100: }101:102: /// 103: /// 执行多条SQL语句，实现数据库事务。104: /// 105: /// 多条SQL语句106: public static void ExecuteSqlTran(ArrayList SQLStringList)107: {108: using (SQLiteConnection conn = new SQLiteConnection(connectionString))109: {110: conn.Open();111: SQLiteCommand cmd = new SQLiteCommand();112: cmd.Connection = conn;113: SQLiteTransaction tx = conn.BeginTransaction();114: cmd.Transaction = tx;115: try116: {117: for (int n = 0; n < SQLStringList.Count; n++)118: {119: string strsql = SQLStringList[n].ToString();120: if (strsql.Trim().Length > 1)121: {122: cmd.CommandText = strsql;123: cmd.ExecuteNonQuery();124: }125: }126: tx.Commit();127: }128: catch (System.Data.SQLite.SQLiteException E)129: {130: tx.Rollback();131: throw new Exception(E.Message);132: }133: }134: }135: /// 136: /// 执行带一个存储过程参数的的SQL语句。137: /// 138: /// SQL语句139: /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加140: /// 影响的记录数141: public static int ExecuteSql(string SQLString, string content)142: {143: using (SQLiteConnection connection = new SQLiteConnection(connectionString))144: {145: SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);146: SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);147: myParameter.Value = content;148: cmd.Parameters.Add(myParameter);149: try150: {151: connection.Open();152: int rows = cmd.ExecuteNonQuery();153: return rows;154: }155: catch (System.Data.SQLite.SQLiteException E)156: {157: throw new Exception(E.Message);158: }159: finally160: {161: cmd.Dispose();162: connection.Close();163: }164: }165: }166: /// 167: /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)168: /// 169: /// SQL语句170: /// 图像字节,数据库的字段类型为image的情况171: /// 影响的记录数172: public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)173: {174: using (SQLiteConnection connection = new SQLiteConnection(connectionString))175: {176: SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);177: SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);178: myParameter.Value = fs;179: cmd.Parameters.Add(myParameter);180: try181: {182: connection.Open();183: int rows = cmd.ExecuteNonQuery();184: return rows;185: }186: catch (System.Data.SQLite.SQLiteException E)187: {188: throw new Exception(E.Message);189: }190: finally191: {192: cmd.Dispose();193: connection.Close();194: }195: }196: }197:198: /// 199: /// 执行一条计算查询结果语句，返回查询结果(object)。200: /// 201: /// 计算查询结果语句202: /// 查询结果(object)203: public static object GetSingle(string SQLString)204: {205: using (SQLiteConnection connection = new SQLiteConnection(connectionString))206: {207: using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))208: {209: try210: {211: connection.Open();212: object obj = cmd.ExecuteScalar();213: if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))214: {215: return null;216: }217: else218: {219: return obj;220: }221: }222: catch (System.Data.SQLite.SQLiteException e)223: {224: connection.Close();225: throw new Exception(e.Message);226: }227: }228: }229: }230: /// 231: /// 执行查询语句，返回SQLiteDataReader232: /// 233: /// 查询语句234: /// SQLiteDataReader235: public static SQLiteDataReader ExecuteReader(string strSQL)236: {237: SQLiteConnection connection = new SQLiteConnection(connectionString);238: SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);239: try240: {241: connection.Open();242: SQLiteDataReader myReader = cmd.ExecuteReader();243: return myReader;244: }245: catch (System.Data.SQLite.SQLiteException e)246: {247: throw new Exception(e.Message);248: }249:250: }251: /// 252: /// 执行查询语句，返回DataSet253: /// 254: /// 查询语句255: /// DataSet256: public static DataSet Query(string SQLString)257: {258: using (SQLiteConnection connection = new SQLiteConnection(connectionString))259: {260: DataSet ds = new DataSet();261: try262: {263: connection.Open();264: SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);265: command.Fill(ds, "ds");266: }267: catch (System.Data.SQLite.SQLiteException ex)268: {269: throw new Exception(ex.Message);270: }271: return ds;272: }273: }274:275:276: #endregion277:278: #region 执行带参数的SQL语句279:280: /// 281: /// 执行SQL语句，返回影响的记录数282: /// 283: /// SQL语句284: /// 影响的记录数285: public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)286: {287: using (SQLiteConnection connection = new SQLiteConnection(connectionString))288: {289: using (SQLiteCommand cmd = new SQLiteCommand())290: {291: try292: {293: PrepareCommand(cmd, connection, null, SQLString, cmdParms);294: int rows = cmd.ExecuteNonQuery();295: cmd.Parameters.Clear();296: return rows;297: }298: catch (System.Data.SQLite.SQLiteException E)299: {300: throw new Exception(E.Message);301: }302: }303: }304: }305:306:307: /// 308: /// 执行多条SQL语句，实现数据库事务。309: /// 310: /// SQL语句的哈希表(key为sql语句，value是该语句的SQLiteParameter[])311: public static void ExecuteSqlTran(Hashtable SQLStringList)312: {313: using (SQLiteConnection conn = new SQLiteConnection(connectionString))314: {315: conn.Open();316: using (SQLiteTransaction trans = conn.BeginTransaction())317: {318: SQLiteCommand cmd = new SQLiteCommand();319: try320: {321: //循环322: foreach (DictionaryEntry myDE in SQLStringList)323: {324: string cmdText = myDE.Key.ToString();325: SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;326: PrepareCommand(cmd, conn, trans, cmdText, cmdParms);327: int val = cmd.ExecuteNonQuery();328: cmd.Parameters.Clear();329:330: trans.Commit();331: }332: }333: catch334: {335: trans.Rollback();336: throw;337: }338: }339: }340: }341:342:343: /// 344: /// 执行一条计算查询结果语句，返回查询结果(object)。345: /// 346: /// 计算查询结果语句347: /// 查询结果(object)348: public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)349: {350: using (SQLiteConnection connection = new SQLiteConnection(connectionString))351: {352: using (SQLiteCommand cmd = new SQLiteCommand())353: {354: try355: {356: PrepareCommand(cmd, connection, null, SQLString, cmdParms);357: object obj = cmd.ExecuteScalar();358: cmd.Parameters.Clear();359: if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))360: {361: return null;362: }363: else364: {365: return obj;366: }367: }368: catch (System.Data.SQLite.SQLiteException e)369: {370: throw new Exception(e.Message);371: }372: }373: }374: }375:376: /// 377: /// 执行查询语句，返回SQLiteDataReader378: /// 379: /// 查询语句380: /// SQLiteDataReader381: public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)382: {383: SQLiteConnection connection = new SQLiteConnection(connectionString);384: SQLiteCommand cmd = new SQLiteCommand();385: try386: {387: PrepareCommand(cmd, connection, null, SQLString, cmdParms);388: SQLiteDataReader myReader = cmd.ExecuteReader();389: cmd.Parameters.Clear();390: return myReader;391: }392: catch (System.Data.SQLite.SQLiteException e)393: {394: throw new Exception(e.Message);395: }396:397: }398:399: /// 400: /// 执行查询语句，返回DataSet401: /// 402: /// 查询语句403: /// DataSet404: public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)405: {406: using (SQLiteConnection connection = new SQLiteConnection(connectionString))407: {408: SQLiteCommand cmd = new SQLiteCommand();409: PrepareCommand(cmd, connection, null, SQLString, cmdParms);410: using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))411: {412: DataSet ds = new DataSet();413: try414: {415: da.Fill(ds, "ds");416: cmd.Parameters.Clear();417: }418: catch (System.Data.SQLite.SQLiteException ex)419: {420: throw new Exception(ex.Message);421: }422: return ds;423: }424: }425: }426:427:428: private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)429: {430: if (conn.State != ConnectionState.Open)431: conn.Open();432: cmd.Connection = conn;433: cmd.CommandText = cmdText;434: if (trans != null)435: cmd.Transaction = trans;436: cmd.CommandType = CommandType.Text;//cmdType;437: if (cmdParms != null)438: {439: foreach (SQLiteParameter parm in cmdParms)440: cmd.Parameters.Add(parm);441: }442: }443:444: #endregion445:446:447:448: }