检索与System.Data.SQLite和C＃中的BLOB

本文介绍了检索与System.Data.SQLite和C＃中的BLOB的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我是新手中的 SQLITE 的，我目前使用的 System.Data.SQLite.dll 版本 1.0.89.0 在 C＃项目。我的数据库包含了一个简单的表文件有下列：I'm newbie in SQLITE and I'm currently using System.Data.SQLite.dll version 1.0.89.0 in a C# project.My database contains a simple table 'files' with following columns: [ID] VARCHAR（50）NOT NULL [块] INTEGER NOT NULL [内容] BLOB NOT NULL [大小] INTEGER NOT NULL [date_ins] DATETIME NOT NULL[id] VARCHAR(50) NOT NULL[chunk] INTEGER NOT NULL[content] BLOB NOT NULL[size] INTEGER NOT NULL[date_ins] DATETIME NOT NULL PRIMARY KEY （ID，块）我创建了一个类（ OfflineStorage ），在此表中添加和检索文件作为的 BLOBS 的。保存方法效果不错，但加载生成一个 InvalidCastException的在 GetStream 扩展方法。I created a class (OfflineStorage) to add and retrieve files in this table as BLOBS.Save method works ok, but Load generate an InvalidCastException on GetStream extended method.public class OfflineStorage{ private static string l_strConnectionTemplate = "Data Source={0};Version=3;Password=\"{1}\";"; private string l_strConnection; private int SQLITE_MAX_BLOB_LENGTH; private string l_strCreateTable = @"CREATE TABLE IF NOT EXISTS [files]" + "( " + " [id] VARCHAR(50) NOT NULL, " + " [chunk] INTEGER NOT NULL, " + " [content] BLOB NOT NULL, " + " [size] INTEGER NOT NULL, " + " [date_ins] DATETIME NOT NULL, " + " PRIMARY KEY(id,chunk) " + ")"; private string l_strSelectQuery = @"SELECT chunk, content, size FROM files WHERE id = @id ORDER BY chunk"; private string l_strUpdateQuery = @"UPDATE files SET content = content || @new_content, size = size + @size WHERE id = @id AND chunk = @chunk"; private string l_strInsertQuery = @"INSERT INTO files(id, chunk, content, size, date_ins) VALUES(@id, @chunk, @new_content, @size, DATETIME('now'))"; public OfflineStorage(string strFilename, string strPassword = "") { SQLiteConnection l_objConnection = null; if (!File.Exists(strFilename)) { l_strConnection = string.Format(l_strConnectionTemplate, strFilename, ""); SQLiteConnection.CreateFile(strFilename); l_objConnection = new SQLiteConnection(l_strConnection); l_objConnection.SetPassword(strPassword); l_objConnection.Close(); } l_strConnection = string.Format(l_strConnectionTemplate, strFilename, strPassword); l_objConnection = getConnection(); using (SQLiteCommand l_objCommand = new SQLiteCommand(l_strCreateTable, l_objConnection)) { l_objCommand.ExecuteNonQuery(); } SQLITE_MAX_BLOB_LENGTH = 1000000; CloseConnection(l_objConnection); } private SQLiteConnection getConnection() { SQLiteConnection l_objConnection = null; try { l_objConnection = new SQLiteConnection(l_strConnection); l_objConnection.Open(); return l_objConnection; } catch (Exception ex) { CloseConnection(l_objConnection); throw new OfflineStorageException("Local Service open db error.", ex); } } private void CloseConnection(SQLiteConnection objConnection) { if (objConnection != null) { objConnection.Close(); objConnection = null; } } public long Load(string strID, Stream objStream) { if (!objStream.CanWrite) throw new NotSupportedException("Stream not writable."); SQLiteConnection l_objConnection = getConnection(); // Columns Identifier (name of file) SQLiteParameter l_objID = new SQLiteParameter("@id", DbType.String); l_objID.Value = strID; SQLiteCommand l_objCommand = new SQLiteCommand(l_strSelectQuery, l_objConnection); l_objCommand.Parameters.Add(l_objID); // Load File Records SQLiteDataReader l_objReader; try { l_objReader = l_objCommand.ExecuteReader(); } catch (Exception ex) { CloseConnection(l_objConnection); throw new OfflineStorageException("SQLite exception.", ex); } long l_lFileLength = 0; // Complete file length int l_iDBChunk = -1; // Current chunk on database int l_iChunk = 0; // Current 'sub chunk' long l_lChunkLength = -1; // Current 'sub chunk' length try { // For each record of current file selected by identifier while (l_objReader.Read()) { l_iDBChunk = l_objReader.GetInt32(0); // Chunk ID l_lChunkLength = l_objReader.GetInt64(2); // Chunk size Trace.Assert(l_iChunk == l_iDBChunk); // Compare expected Chunck with Database ID Chunk l_lFileLength += l_objReader.GetStream(objStream, 1, l_lChunkLength); // Load chunk l_iChunk++; } } catch (Exception ex) { string l_strMessage = string.Format("SQLite exception on file {0}, DB chunk {1}: \n{2}", strID, l_iDBChunk, ex.Message); throw new OfflineStorageException(l_strMessage, ex); } finally { l_objReader.Close(); l_objCommand.Dispose(); CloseConnection(l_objConnection); } if (l_iChunk < 1) { string l_strMessage = string.Format("File {0} not readed on db.", strID); throw new OfflineStorageException(l_strMessage); } return l_lFileLength; } public void Save(string strID, Stream objStream, bool bOverwrite = false) { const int CHUNK_SIZE = 8 * 1024; if (!objStream.CanRead) throw new NotSupportedException("Stream not readable."); long l_lOldPosition = objStream.Position; SQLiteConnection l_objConnection = getConnection(); byte[] lar_byBuffer = new byte[CHUNK_SIZE]; SQLiteParameter l_objID = new SQLiteParameter("@id", DbType.String); l_objID.Value = strID; SQLiteParameter l_objContent = new SQLiteParameter("@new_content", DbType.Binary); l_objContent.Value = lar_byBuffer; SQLiteParameter l_objChunk = new SQLiteParameter("@chunk", DbType.Int32); SQLiteParameter l_objSize = new SQLiteParameter("@size", DbType.Int32); SQLiteCommand l_objCommand = new SQLiteCommand(l_strInsertQuery, l_objConnection); l_objCommand.Parameters.Add(l_objID); l_objCommand.Parameters.Add(l_objContent); l_objCommand.Parameters.Add(l_objChunk); l_objCommand.Parameters.Add(l_objSize); int l_iReturn, l_lBytesRead; int l_iChunk = 0; // Current 'sub chunk' int l_iDBChunk = 0; // Current chunk on database long l_lDBChunkLength = 0; // Current length of chunk l_objChunk.Value = l_iDBChunk; //Transaction using (SQLiteTransaction l_objTransaction = l_objConnection.BeginTransaction()) { // Read File from stream while ((l_lBytesRead = objStream.Read(lar_byBuffer, 0, lar_byBuffer.Length)) > 0) { // Check for next Chunk if ((l_lDBChunkLength + l_lBytesRead) >= SQLITE_MAX_BLOB_LENGTH) { l_objCommand.CommandText = l_strInsertQuery; l_iChunk = 0; // reset 'sub chunk' counter l_lDBChunkLength = 0; // reset chunk size l_iDBChunk++; // increase chunk ID l_objChunk.Value = l_iDBChunk; } l_lDBChunkLength += l_lBytesRead; // Increase length of chunk l_objContent.Size = l_lBytesRead; // Length of Content field l_objSize.Value = l_lBytesRead; // Chunk lenght (write on 'size' column) #region WRITE try { l_iReturn = l_objCommand.ExecuteNonQuery(); if (l_iChunk == 0) { l_objCommand.CommandText = l_strUpdateQuery; } } catch (Exception ex) { l_objTransaction.Rollback(); CloseConnection(l_objConnection); string l_strMessage = string.Format("SQLite exception on file {0}, DB chunk {1}, chunk {2}: \n{3}", strID, l_iDBChunk, l_iChunk, ex.Message); throw new OfflineStorageException(l_strMessage, ex); } if (l_iReturn != 1) { l_objTransaction.Rollback(); CloseConnection(l_objConnection); string l_strMessage = string.Format("DB chunk {1}, chunk {2} of file {0} not inserted on db.", strID, l_iDBChunk, l_iChunk); throw new OfflineStorageException(l_strMessage); } #endregion WRITE l_iChunk++; } l_objTransaction.Commit(); } l_objCommand.Dispose(); CloseConnection(l_objConnection); objStream.Position = l_lOldPosition; }} DB数据读取器扩展类：DB Data Reader Extended class:public static class DbDataReaderExtension{ public static long GetStream(this DbDataReader objReader, System.IO.Stream objStream, int iIndex = 0, long lFileLength = -1) { const int CHUNK_SIZE = 7 * 1024; byte[] lar_byBuffer = new byte[CHUNK_SIZE]; // Buffer long l_lBytesRead; // Bytes readed from SQLite database column long l_lFieldOffset = 0; // Field offset on database column long l_lBytesRemainig = lFileLength; while ((l_lBytesRead = objReader.GetBytes(iIndex, l_lFieldOffset, lar_byBuffer, 0, lar_byBuffer.Length)) > 0) { l_lFieldOffset += l_lBytesRead; // prepare next offset if (l_lBytesRemainig > 0) // check if a FileLength was set { l_lBytesRemainig -= l_lBytesRead; // remove readed bytes if (l_lBytesRemainig < 0) // Cut last bytes not valid if file is bigger than column size l_lBytesRead += l_lBytesRemainig; } // write only valid bytes objStream.Write(lar_byBuffer, 0, (int)l_lBytesRead); } return lFileLength < 0 ? l_lFieldOffset : lFileLength; }}我发现，产生这种异常，因为的ReadBytes方法（SQLiteDataReader）调用VerifyTypeI found this exception is generated because ReadBytes method (SQLiteDataReader) call VerifyTypeprivate TypeAffinity VerifyType(int i, DbType typ){ CheckClosed(); CheckValidRow(); TypeAffinity affinity = GetSQLiteType(i).Affinity; switch (affinity) { ... case TypeAffinity.Text: if (typ == DbType.SByte) return affinity; if (typ == DbType.String) return affinity; if (typ == DbType.SByte) return affinity; if (typ == DbType.Guid) return affinity; if (typ == DbType.DateTime) return affinity; if (typ == DbType.Decimal) return affinity; break; case TypeAffinity.Blob: ... } throw new InvalidCastException();}在此功能是没有预料到的典型值等于为的 DbType.Binary 的和亲和力的等于 TypeAffinity.Text 的。谁能帮我理解这个问题？谢谢In this function isn't expected that typ is equal to DbType.Binary and affinity is equal to TypeAffinity.Text.Can anyone help me to understand this problem?Thank you推荐答案确定，这里就是我已经添加和检索斑点。ok, here's how i have added and retrieved blobs.我想这一切都取决于byte []的是否是可管理的大小。这个工作对小物体被序列化到数据库中。i suppose it all depends on whether the byte[] is a manageable size. This worked for small objects being serialized to the database.使用GetDataTable来获取数据，然后有问题的行提取的字节数组以下内容：use GetDataTable to get the data and then on the row in question extract the byte array with the following: public byte[] getByteArray(DataRow row, int offset) { object blob = row[offset]; if (blob == null) return null; byte[] arData = (byte[])blob; return arData; }这是我如何添加它们： private System.Object syncLock = new System.Object(); public int ExecuteNonQueryWithBlob(string sql, string blobFieldName, byte[] blob) { lock (syncLock) { try { using (var c = new SQLiteConnection(dbConnection)) { using (var cmd = new SQLiteCommand(sql, c)) { cmd.Connection.Open(); cmd.Parameters.AddWithValue("@" + blobFieldName, blob); return cmd.ExecuteNonQuery(); } } } catch (Exception e) { Console.WriteLine(e.Message); return 0; } } } public DataTable GetDataTable(string sql) { lock (syncLock) { try { DataTable dt = new DataTable(); using (var c = new SQLiteConnection(dbConnection)) { c.Open(); using (SQLiteCommand cmd = new SQLiteCommand(sql, c)) { using (SQLiteDataReader rdr = cmd.ExecuteReader()) { dt.Load(rdr); return dt; } } } } catch (Exception e) { Console.WriteLine(e.Message); return null; } } } 这篇关于检索与System.Data.SQLite和C＃中的BLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！