public static bool SaveToDB(string dataSource, string completeQuery)
{
using (SqliteConnection con = new SqliteConnection())
{
con.ConnectionString = dataSource;
try { con.Open(); }
catch (Exception ex)
{
MakeLogErrorStatic(typeof(DBReader), ex);
if (con.State.ToString() == "Open")
{
con.Close();
con.Dispose();
}
return(false);
}
// security check and close connection if necessary
if (!DBSecurity.IsSecureSQLCommand(completeQuery))
{
MakeLogWarningStatic(typeof(DBReader),
"SaveToDB: Prevented forwarding of insecure sql-command: "
+ completeQuery);
return(false);
}
using (SQLiteCommand cmd = new SQLiteCommand(completeQuery, con))
{
cmd.CommandText = completeQuery;
SQLiteDataReader rdr = null;
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
throw new Exception("Could not execute SQLiteDataReader in SaveToDB: " + ex);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
}
}
return(true);
}
static int Close(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 1);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
obj.Close();
return(0);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
/// <summary>
/// 转换实体
/// </summary>
/// <param name="read">OleDbDataReader</param>
/// <returns>TermInfo</returns>
private static List<StatisticsInfo> DataReaderToList(SqliteDataReader read)
{
List<StatisticsInfo> list = new List<StatisticsInfo>();
while (read.Read())
{
StatisticsInfo _site = new StatisticsInfo();
_site.PostCount = Convert.ToInt32(read["PostCount"]);
_site.CommentCount = Convert.ToInt32(read["CommentCount"]);
_site.VisitCount = Convert.ToInt32(read["VisitCount"]);
_site.TagCount = Convert.ToInt32(read["TagCount"]);
list.Add(_site);
}
read.Close();
return list;
}
private int _GetDataBaseSongID(string artist, string title, int defNumPlayed, SQLiteCommand command)
{
command.CommandText = "SELECT id FROM Songs WHERE [Title] = @title AND [Artist] = @artist";
command.Parameters.Add("@title", DbType.String, 0).Value = title;
command.Parameters.Add("@artist", DbType.String, 0).Value = artist;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Dispose();
return(id);
}
if (reader != null)
{
reader.Close();
}
command.CommandText = "INSERT INTO Songs (Title, Artist, NumPlayed, DateAdded) " +
"VALUES (@title, @artist, @numplayed, @dateadded)";
command.Parameters.Add("@title", DbType.String, 0).Value = title;
command.Parameters.Add("@artist", DbType.String, 0).Value = artist;
command.Parameters.Add("@numplayed", DbType.Int32, 0).Value = defNumPlayed;
command.Parameters.Add("@dateadded", DbType.Int64, 0).Value = DateTime.Now.Ticks;
command.ExecuteNonQuery();
command.CommandText = "SELECT id FROM Songs WHERE [Title] = @title AND [Artist] = @artist";
command.Parameters.Add("@title", DbType.String, 0).Value = title;
command.Parameters.Add("@artist", DbType.String, 0).Value = artist;
reader = command.ExecuteReader();
if (reader != null)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Dispose();
return(id);
}
return(-1);
}
/// <summary>
/// 转换实体
/// </summary>
/// <param name="read">SqliteDataReader</param>
/// <returns>TermInfo</returns>
private static List<StatisticsInfo> DataReaderToListSite(SqliteDataReader read)
{
var list = new List<StatisticsInfo>();
while (read.Read())
{
var site = new StatisticsInfo
{
PostCount = Convert.ToInt32(read["PostCount"]),
CommentCount = Convert.ToInt32(read["CommentCount"]),
VisitCount = Convert.ToInt32(read["VisitCount"]),
TagCount = Convert.ToInt32(read["TagCount"])
};
list.Add(site);
}
read.Close();
return list;
}
public bool GetCreditsRessource(string fileName, ref CTextureRef tex)
{
if (_Connection == null)
{
return(false);
}
bool result = false;
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "SELECT id, width, height FROM Images WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String, 0).Value = fileName;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
reader.Read();
int id = reader.GetInt32(0);
int w = reader.GetInt32(1);
int h = reader.GetInt32(2);
reader.Close();
command.CommandText = "SELECT Data FROM ImageData WHERE ImageID = @id";
command.Parameters.Add("@id", DbType.Int32).Value = id;
reader = command.ExecuteReader();
if (reader.HasRows)
{
result = true;
reader.Read();
byte[] data = _GetBytes(reader);
tex = CDraw.AddTexture(w, h, data);
}
}
if (reader != null)
{
reader.Dispose();
}
}
return(result);
}
public static void Init(string absolutePath = "cards.cdb")
{
m_cards = new Dictionary <int, Card>();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + absolutePath)){
connection.Open();
using (SQLiteCommand command = new SQLiteCommand("SELECT datas.id, ot, alias, "
+ "setcode, type, level, race, attribute, atk, def ,"
+ " name , desc FROM datas,texts WHERE datas.id=texts.id",
connection)){
using (SQLiteDataReader reader = command.ExecuteReader()){
while (reader.Read())
{
int id = reader.GetInt32(0);
int ot = reader.GetInt32(1);
int levelinfo = reader.GetInt32(5);
int level = levelinfo & 0xff;
int lscale = (levelinfo >> 24) & 0xff;
int rscale = (levelinfo >> 16) & 0xff;
Card.CardData data = new Card.CardData
{
Code = id,
Alias = reader.GetInt32(2),
Setcode = reader.GetInt64(3),
Type = reader.GetInt32(4),
Level = level,
LScale = lscale,
RScale = rscale,
Race = reader.GetInt32(6),
Attribute = reader.GetInt32(7),
Attack = reader.GetInt32(8),
Defense = reader.GetInt32(9)
};
string name = reader.GetString(10);
string desc = reader.GetString(11);
m_cards.Add(id, new Card(data, ot, name, desc));
}
reader.Close();
}
}
connection.Close();
}
}
/// <summary>
/// Clears and destroys all statements currently prepared
/// </summary>
internal void ClearCommands()
{
if (_activeReader != null)
{
_activeReader.Close();
}
if (_statementList == null)
{
return;
}
int x = _statementList.Count;
for (int n = 0; n < x; n++)
{
_statementList[n].Dispose();
}
_statementList = null;
_parameterCollection.Unbind();
}
/// <summary>
/// Clears and destroys all statements currently prepared
/// </summary>
internal void ClearCommands()
{
if (_activeReader != null)
{
SqliteDataReader reader = null;
try
{
reader = _activeReader.Target as SqliteDataReader;
}
catch
{
}
if (reader != null)
{
reader.Close();
}
_activeReader = null;
}
if (_statementList == null)
{
return;
}
int x = _statementList.Count;
for (int n = 0; n < x; n++)
{
_statementList[n].Dispose();
}
_statementList = null;
_parameterCollection.Unbind();
}
internal static void Init()
{
m_cards = new Dictionary<int, Card>();
string absolutePath = PathManager.GetCardsDb();
SQLiteConnection connection = new SQLiteConnection("Data Source=" + absolutePath);
connection.Open();
SQLiteCommand command = new SQLiteCommand("SELECT id, ot, alias, setcode, type, level, race, attribute, atk, def FROM datas", connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
int ot = reader.GetInt32(1);
int levelinfo = reader.GetInt32(5);
int level = levelinfo & 0xff;
int lscale = (levelinfo >> 24) & 0xff;
int rscale = (levelinfo >> 16) & 0xff;
Card.CardData data = new Card.CardData
{
Code = id,
Alias = reader.GetInt32(2),
Setcode = reader.GetInt64(3),
Type = reader.GetInt32(4),
Level = level,
LScale = lscale,
RScale = rscale,
Race = reader.GetInt32(6),
Attribute = reader.GetInt32(7),
Attack = reader.GetInt32(8),
Defense = reader.GetInt32(9)
};
m_cards.Add(id, new Card(data, ot));
}
reader.Close();
connection.Close();
}
public static List <string> GetColumns(string db, string table)
{
List <string> cols = new List <string>();
if (!File.Exists(db))
{
return(cols);
}
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db)){
connection.Open();
using (SQLiteCommand command = new SQLiteCommand("PRAGMA table_info(" + table + ")", connection)){
using (SQLiteDataReader reader = command.ExecuteReader()){
while (reader.Read())
{
string name = reader.GetString(1);
cols.Add(name);
}
reader.Close();
}
}
connection.Close();
}
return(cols);
}
/// <summary>
/// 数据转换
/// </summary>
/// <param name="read"></param>
/// <returns></returns>
private List<UserInfo> DataReaderToUserList(SqliteDataReader read)
{
List<UserInfo> list = new List<UserInfo>();
while (read.Read())
{
UserInfo _userinfo = new UserInfo();
_userinfo.UserId = Convert.ToInt32(read["UserId"]);
_userinfo.Type = Convert.ToInt32(read["Type"]);
_userinfo.UserName = Convert.ToString(read["UserName"]);
_userinfo.Name = Convert.ToString(read["Name"]);
_userinfo.Password = Convert.ToString(read["Password"]);
_userinfo.Email = Convert.ToString(read["Email"]);
_userinfo.SiteUrl = Convert.ToString(read["SiteUrl"]);
_userinfo.AvatarUrl = Convert.ToString(read["AvatarUrl"]);
_userinfo.Description = Convert.ToString(read["Description"]);
_userinfo.Displayorder = Convert.ToInt32(read["Displayorder"]);
_userinfo.Status = Convert.ToInt32(read["Status"]);
_userinfo.PostCount = Convert.ToInt32(read["PostCount"]);
_userinfo.CommentCount = Convert.ToInt32(read["CommentCount"]);
_userinfo.CreateDate = Convert.ToDateTime(read["CreateDate"]);
list.Add(_userinfo);
}
read.Close();
return list;
}
/// <summary>
/// 数据转换
/// </summary>
/// <param name="read"></param>
/// <returns></returns>
private static List<UserInfo> DataReaderToUserList(SqliteDataReader read)
{
var list = new List<UserInfo>();
while (read.Read())
{
var userinfo = new UserInfo
{
UserId = Convert.ToInt32(read["UserId"]),
UserType = Convert.ToInt32(read["UserType"]),
UserName = Convert.ToString(read["UserName"]),
NickName = Convert.ToString(read["NickName"]),
Password = Convert.ToString(read["Password"]),
Email = Convert.ToString(read["Email"]),
SiteUrl = Convert.ToString(read["SiteUrl"]),
AvatarUrl = Convert.ToString(read["AvatarUrl"]),
Description = Convert.ToString(read["Description"]),
SortNum = Convert.ToInt32(read["SortNum"]),
Status = Convert.ToInt32(read["Status"]),
PostCount = Convert.ToInt32(read["PostCount"]),
CommentCount = Convert.ToInt32(read["CommentCount"]),
CreateTime = Convert.ToDateTime(read["CreateTime"])
};
list.Add(userinfo);
}
read.Close();
return list;
}
public static bool LoadFromDB(ref List <List <List <object> > > results,
string completeQuery, string dataSource)
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = dataSource;
try { con.Open(); }
catch (Exception ex)
{
MakeLogErrorStatic(typeof(DBReader), ex);
if (con.State.ToString() == "Open")
{
con.Close();
con.Dispose();
}
return(false);
}
// security check and close connection if necessary
if (!DBSecurity.IsSecureSQLCommand(completeQuery))
{
MakeLogWarningStatic(typeof(DBReader),
"LoadFromDB: Prevented forwarding of insecure sql-command: "
+ completeQuery);
return(false);
}
using (SQLiteCommand cmd = new SQLiteCommand(completeQuery, con))
{
SQLiteDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
if (rdr == null)
{
return(false);
}
if (!rdr.HasRows)
{
return(true);
}
// temporary array to put all data of a row into
object[] rowArr = null;
do
{
// add new result-list
results.Add(new List <List <object> >());
while (rdr.Read())
{
// create and fill array of the temporary data row
rowArr = new object[rdr.FieldCount];
rdr.GetValues(rowArr);
results[results.Count - 1].Add(new List <object>(rowArr));
}
}while (rdr.NextResult());
}
catch (Exception ex)
{
throw new Exception("LoadFromDB: Could not execute SQLiteDataReader: " + ex);
}
finally
{
if (rdr != null)
{
rdr.Close();
rdr.Dispose();
}
}
}
// close connection if still opened
if (con.State.ToString() == "Open")
{
con.Close();
con.Dispose();
}
// everything went through without errors thrown
return(true);
}
/// <summary>
/// Converts a USDX 1.1 database into the Vocaluxe format
/// </summary>
/// <param name="filePath">Database file path</param>
/// <returns>True if succeeded</returns>
private bool _ConvertFrom110(string filePath)
{
using (var connection = new SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + filePath;
try
{
connection.Open();
}
catch (Exception)
{
return(false);
}
using (var command = new SQLiteCommand(connection))
{
//The USDX database has no column for LineNr, Medley and Duet so just fill 0 in there
command.CommandText =
"INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs";
command.ExecuteNonQuery();
var scores = new List <SData>();
var songs = new List <SData>();
command.CommandText = "SELECT id, PlayerName, Date FROM Scores";
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var data = new SData {
Id = reader.GetInt32(0), Str1 = reader.GetString(1)
};
Int64 ticks = 0;
try
{
ticks = reader.GetInt64(2);
}
catch {}
data.Ticks = _UnixTimeToTicks((int)ticks);
scores.Add(data);
}
reader.Close();
}
command.CommandText = "SELECT id, Artist, Title FROM Songs";
reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var data = new SData {
Id = reader.GetInt32(0), Str1 = reader.GetString(1), Str2 = reader.GetString(2)
};
songs.Add(data);
}
}
if (reader != null)
{
reader.Dispose();
}
SQLiteTransaction transaction = connection.BeginTransaction();
// update Title and Artist strings
foreach (SData data in songs)
{
command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id";
command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2;
command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
// update player names
foreach (SData data in scores)
{
command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id";
command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
transaction.Commit();
//Delete old tables after conversion
command.CommandText = "DROP TABLE IF EXISTS us_scores;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_songs;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_statistics_info;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_users_info;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_webs;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_webs_stats;";
command.ExecuteNonQuery();
//This versioning is not used in Vocaluxe so reset it to 0
command.CommandText = "PRAGMA user_version = 0";
command.ExecuteNonQuery();
}
}
return(true);
}
/// <summary>
/// 数据转换
/// </summary>
/// <param name="read"></param>
/// <returns></returns>
private List<PostInfo> DataReaderToCommentList(SqliteDataReader read)
{
List<PostInfo> list = new List<PostInfo>();
while (read.Read())
{
PostInfo postinfo = new PostInfo();
postinfo.PostId = StringHelper.ObjectToInt(read["PostId"]);
postinfo.CategoryId = StringHelper.ObjectToInt(read["CategoryId"]);
postinfo.Title = Convert.ToString(read["Title"]);
postinfo.Summary = Convert.ToString(read["Summary"]);
postinfo.Content = Convert.ToString(read["Content"]);
postinfo.Slug = Convert.ToString(read["Slug"]);
postinfo.UserId = StringHelper.ObjectToInt(read["UserId"]);
postinfo.CommentStatus = StringHelper.ObjectToInt(read["CommentStatus"]);
postinfo.CommentCount = StringHelper.ObjectToInt(read["CommentCount"]);
postinfo.ViewCount = StringHelper.ObjectToInt(read["ViewCount"]);
postinfo.Tag = Convert.ToString(read["Tag"]);
postinfo.UrlFormat = StringHelper.ObjectToInt((read["UrlFormat"]));
postinfo.Template = Convert.ToString(read["Template"]);
postinfo.Recommend = StringHelper.ObjectToInt(read["Recommend"]);
postinfo.Status = StringHelper.ObjectToInt(read["Status"]);
postinfo.TopStatus = StringHelper.ObjectToInt(read["TopStatus"]);
postinfo.HideStatus = StringHelper.ObjectToInt(read["HideStatus"]);
postinfo.CreateDate = Convert.ToDateTime(read["CreateDate"]);
postinfo.UpdateDate = Convert.ToDateTime(read["UpdateDate"]);
list.Add(postinfo);
}
read.Close();
return list;
}
private bool _ImportData(string sourceDBPath)
{
#region open db
using (var connSource = new SQLiteConnection())
{
connSource.ConnectionString = "Data Source=" + sourceDBPath;
try
{
connSource.Open();
}
catch (Exception e)
{
CLog.Error("Error on import high score data. Can't open source database \"" + sourceDBPath + "\" (" + e.Message + ")");
return(false);
}
#endregion open db
using (var cmdSource = new SQLiteCommand(connSource))
{
#region import table scores
cmdSource.CommandText = "SELECT SongID, PlayerName, Score, LineNr, Date, Medley, Duet, ShortSong, Difficulty FROM Scores";
SQLiteDataReader source = cmdSource.ExecuteReader();
if (source == null)
{
return(false);
}
if (source.FieldCount == 0)
{
source.Close();
return(true);
}
while (source.Read())
{
int songid = source.GetInt32(0);
string player = source.GetString(1);
int score = source.GetInt32(2);
int linenr = source.GetInt32(3);
long date = source.GetInt64(4);
int medley = source.GetInt32(5);
int duet = source.GetInt32(6);
int shortsong = source.GetInt32(7);
int diff = source.GetInt32(8);
string artist, title;
DateTime dateadded;
int numplayed;
if (_GetDataBaseSongInfos(songid, out artist, out title, out numplayed, out dateadded, sourceDBPath))
{
AddScore(player, score, linenr, date, medley, duet, shortsong, diff, artist, title, numplayed, _FilePath);
}
}
#endregion import table scores
source.Close();
}
}
return(true);
}
/// <summary>
/// 数据转换
/// </summary>
/// <param name="read"></param>
/// <returns></returns>
private static List<CommentInfo> DataReaderToCommentList(SqliteDataReader read)
{
var list = new List<CommentInfo>();
while (read.Read())
{
var comment = new CommentInfo
{
CommentId = Convert.ToInt32(read["CommentId"]),
ParentId = Convert.ToInt32(read["ParentId"]),
PostId = Convert.ToInt32(read["PostId"]),
UserId = Convert.ToInt32(read["UserId"]),
Author = Convert.ToString(read["Author"]),
Email = Convert.ToString(read["Email"]),
AuthorUrl = Convert.ToString(read["AuthorUrl"]),
Contents = Convert.ToString(read["Contents"]),
EmailNotify = Convert.ToInt32(read["EmailNotify"]),
IpAddress = Convert.ToString(read["IpAddress"]),
CreateTime = Convert.ToDateTime(read["CreateTime"]),
Approved = Convert.ToInt32(read["Approved"])
};
list.Add(comment);
}
read.Close();
return list;
}
public bool GetCover(string coverPath, ref CTextureRef tex, int maxSize)
{
if (_Connection == null)
{
return(false);
}
if (!File.Exists(coverPath))
{
CLog.LogError("Can't find File: " + coverPath);
return(false);
}
lock (_Mutex)
{
//Double check here because we may have just closed our connection
if (_Connection == null)
{
return(false);
}
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "SELECT id, width, height FROM Cover WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String).Value = coverPath;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
reader.Read();
int id = reader.GetInt32(0);
int w = reader.GetInt32(1);
int h = reader.GetInt32(2);
reader.Close();
command.CommandText = "SELECT Data FROM CoverData WHERE CoverID = @id";
command.Parameters.Add("@id", DbType.Int32).Value = id;
reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
byte[] data2 = _GetBytes(reader);
reader.Dispose();
tex = CDraw.EnqueueTexture(w, h, data2);
return(true);
}
command.CommandText = "DELETE FROM Cover WHERE id = @id";
command.Parameters.Add("@id", DbType.Int32).Value = id;
command.ExecuteNonQuery();
}
if (reader != null)
{
reader.Close();
}
}
}
// At this point we do not have a mathing entry in the CoverDB (either no Data found and deleted or nothing at all)
// We break out of the lock to do the bitmap loading and resizing here to allow multithreaded loading
Bitmap origin = CHelper.LoadBitmap(coverPath);
if (origin == null)
{
return(false);
}
Size size = origin.GetSize();
if (size.Width > maxSize || size.Height > maxSize)
{
size = CHelper.FitInBounds(new SRectF(0, 0, maxSize, maxSize, 0), (float)size.Width / size.Height, EAspect.LetterBox).SizeI;
Bitmap tmp = origin.Resize(size);
origin.Dispose();
origin = tmp;
}
byte[] data;
try
{
data = new byte[size.Width * size.Height * 4];
BitmapData bmpData = origin.LockBits(origin.GetRect(), ImageLockMode.ReadOnly, PixelFormat.Format32bppArgb);
Marshal.Copy(bmpData.Scan0, data, 0, data.Length);
origin.UnlockBits(bmpData);
}
finally
{
origin.Dispose();
}
tex = CDraw.EnqueueTexture(size.Width, size.Height, data);
lock (_Mutex)
{
//Double check here because we may have just closed our connection
if (_Connection == null)
{
return(false);
}
if (_TransactionCover == null)
{
_TransactionCover = _Connection.BeginTransaction();
}
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "INSERT INTO Cover (Path, width, height) VALUES (@path, @w, @h)";
command.Parameters.Add("@w", DbType.Int32).Value = size.Width;
command.Parameters.Add("@h", DbType.Int32).Value = size.Height;
command.Parameters.Add("@path", DbType.String).Value = coverPath;
command.ExecuteNonQuery();
command.CommandText = "SELECT id FROM Cover WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String).Value = coverPath;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Dispose();
command.CommandText = "INSERT INTO CoverData (CoverID, Data) VALUES (@id, @data)";
command.Parameters.Add("@id", DbType.Int32).Value = id;
command.Parameters.Add("@data", DbType.Binary).Value = data;
command.ExecuteNonQuery();
return(true);
}
}
}
return(false);
}
/// <summary>
/// 转换实体
/// </summary>
/// <param name="read">SqliteDataReader</param>
/// <returns>CategoryInfo</returns>
private static List<CategoryInfo> DataReaderToList(SqliteDataReader read)
{
List<CategoryInfo> list = new List<CategoryInfo>();
while (read.Read())
{
CategoryInfo category = new CategoryInfo();
category.CategoryId = Convert.ToInt32(read["termid"]);
// category.Type = Convert.ToInt32(read["Type"]);
category.Name = Convert.ToString(read["Name"]);
category.Slug = Convert.ToString(read["Slug"]);
category.Description = Convert.ToString(read["Description"]);
category.Displayorder = Convert.ToInt32(read["Displayorder"]);
category.Count = Convert.ToInt32(read["Count"]);
category.CreateDate = Convert.ToDateTime(read["CreateDate"]);
list.Add(category);
}
read.Close();
return list;
}
/// <summary>
/// 转换实体
/// </summary>
/// <param CateName="read">SqliteDataReader</param>
/// <param name="read"></param>
/// <returns>TagInfo</returns>
private static List<TagInfo> DataReaderToListTag(SqliteDataReader read)
{
var list = new List<TagInfo>();
while (read.Read())
{
var tag = new TagInfo
{
TagId = Convert.ToInt32(read["categoryid"]),
CateName = Convert.ToString(read["CateName"]),
Slug = Convert.ToString(read["Slug"]),
Description = Convert.ToString(read["Description"]),
SortNum = Convert.ToInt32(read["SortNum"]),
PostCount = Convert.ToInt32(read["PostCount"]),
CreateTime = Convert.ToDateTime(read["CreateTime"])
};
// tag.Type = Convert.ToInt32(read["Type"]);
list.Add(tag);
}
read.Close();
return list;
}
/// <summary>
/// 数据转换
/// </summary>
/// <param name="read"></param>
/// <returns></returns>
private List<CommentInfo> DataReaderToCommentList(SqliteDataReader read)
{
List<CommentInfo> list = new List<CommentInfo>();
while (read.Read())
{
CommentInfo comment = new CommentInfo();
comment.CommentId = Convert.ToInt32(read["CommentId"]);
comment.ParentId = Convert.ToInt32(read["ParentId"]);
comment.PostId = Convert.ToInt32(read["PostId"]);
comment.UserId = Convert.ToInt32(read["UserId"]);
comment.Name = Convert.ToString(read["Name"]);
comment.Email = Convert.ToString(read["Email"]);
comment.SiteUrl = Convert.ToString(read["SiteUrl"]);
comment.Content = Convert.ToString(read["Content"]);
comment.EmailNotify = Convert.ToInt32(read["EmailNotify"]);
comment.IpAddress = Convert.ToString(read["IpAddress"]);
comment.CreateDate = Convert.ToDateTime(read["CreateDate"]);
comment.Approved = Convert.ToInt32(read["Approved"]);
list.Add(comment);
}
read.Close();
return list;
}
/// <summary>
/// 转换实体
/// </summary>
/// <param LinkName="read">SqliteDataReader</param>
/// <param name="read"></param>
/// <returns>LinkInfo</returns>
private static List<LinkInfo> DataReaderToListLink(SqliteDataReader read)
{
var list = new List<LinkInfo>();
while (read.Read())
{
var link = new LinkInfo
{
LinkId = Convert.ToInt32(read["Linkid"]),
Type = Convert.ToInt32(read["Type"]),
LinkName = Convert.ToString(read["LinkName"]),
LinkUrl = Convert.ToString(read["LinkUrl"]),
Target = Convert.ToString(read["Target"]),
Description = Convert.ToString(read["Description"]),
SortNum = Convert.ToInt32(read["SortNum"]),
Status = Convert.ToInt32(read["Status"]),
CreateTime = Convert.ToDateTime(read["CreateTime"])
};
if (read["Position"] != DBNull.Value)
{
link.Position = Convert.ToInt32(read["Position"]);
}
list.Add(link);
}
read.Close();
return list;
}
/// <summary>
/// 转换实体
/// </summary>
/// <param name="read">SqliteDataReader</param>
/// <returns>CategoryInfo</returns>
private static List<CategoryInfo> DataReaderToListCate(SqliteDataReader read)
{
var list = new List<CategoryInfo>();
while (read.Read())
{
var category = new CategoryInfo
{
CategoryId = Convert.ToInt32(read["categoryid"]),
ParentId = Jqpress.Framework.Utils.TypeConverter.ObjectToInt(read["ParentId"],0),
CateName = Convert.ToString(read["CateName"]),
Slug = Convert.ToString(read["Slug"]),
Description = Convert.ToString(read["Description"]),
SortNum = Convert.ToInt32(read["SortNum"]),
PostCount = Convert.ToInt32(read["PostCount"]),
CreateTime = Convert.ToDateTime(read["CreateTime"])
};
// category.Type = Convert.ToInt32(read["Type"]);
list.Add(category);
}
read.Close();
return list;
}
//public int GetCount(int tagId, bool incUncategorized)
//{
// string cmdText = "select count(1) from [loachs_posts] where [tag] like '%" + tagId + "}%'";
// if (!incUncategorized)
// {
// cmdText = "select count(1) from [loachs_posts] where [categoryid]>0 and [tag] like '%" + tagId + "}%'";
// }
// return Convert.ToInt32(SqliteDbHelper.ExecuteScalar(cmdText));
//}
/// <summary>
/// 转换实体
/// </summary>
/// <param name="read">OleDbDataReader</param>
/// <returns>TagInfo</returns>
private static List<TagInfo> DataReaderToList(SqliteDataReader read)
{
List<TagInfo> list = new List<TagInfo>();
while (read.Read())
{
TagInfo tag = new TagInfo();
tag.TagId = Convert.ToInt32(read["termid"]);
// tag.Type = Convert.ToInt32(read["Type"]);
tag.Name = Convert.ToString(read["Name"]);
tag.Slug = Convert.ToString(read["Slug"]);
tag.Description = Convert.ToString(read["Description"]);
tag.Displayorder = Convert.ToInt32(read["Displayorder"]);
tag.Count = Convert.ToInt32(read["Count"]);
tag.CreateDate = Convert.ToDateTime(read["CreateDate"]);
list.Add(tag);
}
read.Close();
return list;
}
/// <summary>
/// 转换实体
/// </summary>
/// <param name="read">OleDbDataReader</param>
/// <returns>LinkInfo</returns>
private static List<LinkInfo> DataReaderToList(SqliteDataReader read)
{
List<LinkInfo> list = new List<LinkInfo>();
while (read.Read())
{
LinkInfo link = new LinkInfo();
link.LinkId = Convert.ToInt32(read["Linkid"]);
link.Type = Convert.ToInt32(read["Type"]);
link.Name = Convert.ToString(read["Name"]);
link.Href = Convert.ToString(read["Href"]);
if (read["Position"] != DBNull.Value)
{
link.Position = Convert.ToInt32(read["Position"]);
}
link.Target = Convert.ToString(read["Target"]);
link.Description = Convert.ToString(read["Description"]);
link.Displayorder = Convert.ToInt32(read["Displayorder"]);
link.Status = Convert.ToInt32(read["Status"]);
link.CreateDate = Convert.ToDateTime(read["CreateDate"]);
list.Add(link);
}
read.Close();
return list;
}
