public static List <String> GetChannels()
{
List <String> entries = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT name from channels", db);
Microsoft.Data.Sqlite.SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
/*foreach(var entry in entries)
* {
* System.Diagnostics.Debug.WriteLine("------------------------------------"+entry);
* }
* //System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + entries[2]);*/
db.Close();
}
return(entries);
}
public static List <post> GetPosts(string channel)
{
List <post> posts = new List <post>();
List <string> contents = new List <string>();
List <string> titles = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT title from General", db);
//selectCommand.Parameters.AddWithValue("@channel", channel);
String selCommand = "SELECT title from " + channel;
Microsoft.Data.Sqlite.SqliteCommand selection = new Microsoft.Data.Sqlite.SqliteCommand(selCommand, db);
System.Diagnostics.Debug.WriteLine("---------------------" + channel);
Microsoft.Data.Sqlite.SqliteDataReader query = selection.ExecuteReader();
while (query.Read())
{
titles.Add(query.GetString(0));
}
System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + titles.Count());
System.Diagnostics.Debug.WriteLine("========================" + titles[0]);
Microsoft.Data.Sqlite.SqliteCommand selectCommand2 = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT content from General", db);
//selectCommand2.Parameters.AddWithValue("@channel", channel);
String selCommand2 = "SELECT content from " + channel;
Microsoft.Data.Sqlite.SqliteCommand selection2 = new Microsoft.Data.Sqlite.SqliteCommand(selCommand2, db);
Microsoft.Data.Sqlite.SqliteDataReader query2 = selection2.ExecuteReader();
while (query2.Read())
{
contents.Add(query2.GetString(0));
}
db.Close();
}
System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + titles.Count());
int k = 0;
for (int i = 0; i < titles.Count(); i++)
{
post p = new post();
p.content = contents[i];
p.title = titles[i];
posts.Add(p);
}
return(posts);
}
/// <summary>
/// 执行标准的SQL语句并返回单行数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public Row GetRow(string sql)
{
//throw new NotImplementedException();
Row row = new Row();
using (Microsoft.Data.Sqlite.SqliteCommand sqlCommand = new Microsoft.Data.Sqlite.SqliteCommand(sql, dbc)) {
using (Microsoft.Data.Sqlite.SqliteDataReader reader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.Default)) {
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//item.Set(SqlReader.GetName(i), SqlReader[i].ToString());
string szName = reader.GetName(i);
row[szName] = reader[i].ToString();
}
}
reader.Close();
}
}
return(row);
}
public static List <String> TestGet(string name)
{
List <String> entries = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT password from users WHERE username = '******'", db);
Microsoft.Data.Sqlite.SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
db.Close();
}
return(entries);
}
public static void ReadValue(SQLiteDataReader reader, int index, TypeStorage type, ICdlValueWriter writer)
{
switch (type)
{
case TypeStorage.Boolean:
writer.SetBoolean(reader.GetInt32(index) != 0);
break;
case TypeStorage.Byte:
writer.SetByte((byte)reader.GetInt32(index));
break;
case TypeStorage.Int16:
writer.SetInt16((short)reader.GetInt32(index));
break;
case TypeStorage.Int32:
writer.SetInt32((int)reader.GetInt32(index));
break;
case TypeStorage.Int64:
writer.SetInt64((long)reader.GetInt64(index));
break;
case TypeStorage.SByte:
writer.SetSByte((sbyte)reader.GetInt32(index));
break;
case TypeStorage.UInt16:
writer.SetUInt16((ushort)reader.GetInt32(index));
break;
case TypeStorage.UInt32:
writer.SetUInt32((uint)reader.GetInt32(index));
break;
case TypeStorage.UInt64:
writer.SetUInt64((ulong)reader.GetInt64(index));
break;
case TypeStorage.DateTime:
writer.SetDateTime(DateTime.Parse(reader.GetString(index), CultureInfo.InvariantCulture));
//writer.SetDateTime(DateTime.ParseExact(reader.GetString(index), "s", CultureInfo.InvariantCulture));
break;
case TypeStorage.DateTimeEx:
writer.SetDateTimeEx(DateTimeEx.ParseNormalized(reader.GetString(index)));
break;
case TypeStorage.DateEx:
writer.SetDateEx(DateEx.ParseNormalized(reader.GetString(index)));
break;
case TypeStorage.TimeEx:
writer.SetTimeEx(TimeEx.ParseNormalized(reader.GetString(index)));
break;
case TypeStorage.Decimal:
{
var dtype = reader.GetFieldType(index);
decimal value;
if (dtype == typeof(string))
{
value = Decimal.Parse(reader.GetString(index), CultureInfo.InvariantCulture);
}
else
{
value = (decimal)reader.GetDouble(index);
}
writer.SetDecimal(value);
}
break;
case TypeStorage.Float:
writer.SetFloat((float)reader.GetDouble(index));
break;
case TypeStorage.Double:
writer.SetDouble((double)reader.GetDouble(index));
break;
case TypeStorage.String:
writer.SetString(reader.GetString(index));
break;
case TypeStorage.Guid:
writer.SetGuid(new Guid(reader.GetString(index)));
break;
case TypeStorage.ByteArray:
writer.SetByteArray((byte[])reader.GetValue(index));
break;
case TypeStorage.Null:
writer.SetNull();
break;
default:
throw new Exception("DBSH-00167 Unsupported field type:" + type.ToString());
}
}
