private Person BuildUserStructure(SQLiteDataReader reader)
{
string name = reader.GetString(0);
string fullName = reader.GetString(1);
long personId = reader.GetInt64(2);
return new Person(name, fullName, personId);
}
protected override Exemplar InitEntryByReader(System.Data.SQLite.SQLiteDataReader reader)
{
Exemplar exemplar = new Exemplar();
ulong id = System.Convert.ToUInt64(reader.GetInt32(reader.GetOrdinal("id")));
string loanPeriodAsString = reader.GetString(reader.GetOrdinal("loanPeriod"));
DateTime loanPeriod = new DateTime();
if (loanPeriodAsString != null || loanPeriodAsString != "")
{
loanPeriod = DateTime.Parse(loanPeriodAsString);
}
string stateString = reader.GetString(reader.GetOrdinal("state"));
BookStates state = (BookStates)Enum.Parse(typeof(BookStates), stateString, true);
string signatur = reader.GetString(reader.GetOrdinal("signatur"));
ulong customerId = System.Convert.ToUInt64(reader.GetInt32(reader.GetOrdinal("customerID")));
ulong bookId = System.Convert.ToUInt64(reader.GetInt32(reader.GetOrdinal("bookID")));
exemplar.ExemplarId = id;
exemplar.LoanPeriod = loanPeriod;
exemplar.State = state;
exemplar.Signatur = signatur;
exemplar.BookId = bookId;
return(exemplar);
}
/* Returns a Activity using a SQLiteDataReader and a dictionary mapping the attributes to a index.
*/
private Activity ActivityExtractor(SQLiteDataReader reader, Dictionary<string, int> attributeIndexDict)
{
return new Activity(reader.GetInt32(attributeIndexDict["activityId"]),
reader.GetInt32(attributeIndexDict["seqNum"]), reader.GetString(attributeIndexDict["workoutName"]),
reader.GetInt32(attributeIndexDict["numSets"]), reader.GetInt32(attributeIndexDict["workoutVersion"]),
reader.GetString(attributeIndexDict["exerciseName"]), reader.GetInt32(attributeIndexDict["exerciseVersion"]));
}
private Expense BuildStructure(SQLiteDataReader reader)
{
long expenseId = reader.GetInt64(0);
long accountId = reader.GetInt64(1);
long amount = reader.GetInt64(2);
long categoryId = reader.GetInt64(3);
DateTime date = DateTime.Parse(reader.GetString(4));
string description = reader.GetString(5);
return new Expense(expenseId, accountId, amount, categoryId, date, description);
}
private TranslationMemory ReadTranslationMemory(SQLiteDataReader reader)
{
TranslationMemory tm = new TranslationMemory();
tm.tmid = reader.GetInt32(0); // tmid used for update
tm.japanese = GetSegmentFromXml(reader.GetString(4)); // source_segment
tm.chinese = GetSegmentFromXml(reader.GetString(6)); // target_segment
tm.tags += reader.GetString(8) + Environment.NewLine; // user
tm.tags += reader.GetString(9) + Environment.NewLine; // date
tm.tmname = Path.GetFileName(this.sourcePath);
return tm;
}
private Guest[] HydrateGuests(SQLiteDataReader reader) {
var guests = new List<Guest>();
while (reader.Read()) {
var guest = new Guest();
guest.Name = reader.GetString(1);
guest.ReservationDate = DateTime.Parse(reader.GetString(2));
guest.Attending = bool.Parse(reader.GetString(3));
guest.Underage = bool.Parse(reader.GetString(4));
guests.Add(guest);
}
return guests.ToArray();
}
public string printToConsole(SQLiteDataReader readerDB, int index)
{
if (readerDB.IsDBNull(index))
{
//return "NULL";
return "";
}
else
{
String dataObject = readerDB.GetFieldType(index).ToString();
switch (dataObject)
{
case "System.Int32":
return readerDB.GetInt32(index).ToString();
case "System.DateTime":
DateTime date = readerDB.GetDateTime(index);
return Convert.ToString(date);
case "System.String":
return readerDB.GetString(index);
default:
return "Unknown";
}
}
}
protected void ReadDvbData(SQLiteDataReader r, IDictionary<string, int> field, DataRoot dataRoot,
IDictionary<string, bool> encryptionInfo)
{
string longName, shortName;
this.GetChannelNames(r.GetString(field["channel_label"]), out longName, out shortName);
this.Name = longName;
this.ShortName = shortName;
this.RecordOrder = r.GetInt32(field["channel_order"]);
this.FreqInMhz = (decimal)r.GetInt32(field["frequency"]) / 1000;
int serviceType = r.GetInt32(field["dvb_service_type"]);
this.ServiceType = serviceType;
this.OriginalNetworkId = r.GetInt32(field["onid"]);
this.TransportStreamId = r.GetInt32(field["tsid"]);
this.ServiceId = r.GetInt32(field["sid"]);
int bits = r.GetInt32(field["list_bits"]);
this.Favorites = this.ParseFavorites(bits);
if ((this.SignalSource & SignalSource.Sat) != 0)
{
int satId = r.GetInt32(field["sat_id"]);
var sat = dataRoot.Satellites.TryGet(satId);
if (sat != null)
{
this.Satellite = sat.Name;
this.SatPosition = sat.OrbitalPosition;
int tpId = satId * 1000000 + (int)this.FreqInMhz;
var tp = dataRoot.Transponder.TryGet(tpId);
if (tp != null)
{
this.SymbolRate = tp.SymbolRate;
}
}
}
this.Encrypted = encryptionInfo.TryGet(this.Uid);
}
public ICollection <IFinancialProduct> UpdateTheListView(ICollection <IFinancialProduct> listProducts)
{
try
{
connection.Open();
cmd = new System.Data.SQLite.SQLiteCommand(connection);
//cmd.Prepare();
//connection.CreateTable<Stock>();
//connection.CreateTable<Fund>();
cmd.CommandText = "SELECT * FROM Stock";
try
{
allRead = cmd.ExecuteReader();
while (allRead.Read())
{
listProducts.Add(new Stock(allRead.GetInt32(0), allRead.GetString(1), allRead.GetString(2), allRead.GetString(3)));
//listProducts.Add(new Stock((int)allRead["Id"], (string)allRead["categoria"], (string)allRead["name"], (string)allRead["code"])); //Isso não converte.
}
}
catch (Exception e)
{
MessageBox.Show("Erro ao montar a lista de ações: " + e.Message);
return(null);
}
cmd = new System.Data.SQLite.SQLiteCommand(connection);
//cmd.Prepare();
//connection.CreateTable<Stock>();
//connection.CreateTable<Fund>();
cmd.CommandText = "SELECT * FROM Fund";
try
{
allRead = cmd.ExecuteReader();
while (allRead.Read())
{
listProducts.Add(new Fund(allRead.GetInt32(0), allRead.GetString(1), allRead.GetString(2), allRead.GetString(3), allRead.GetString(4)));
//listProducts.Add(new Fund(
//(int)allRead["Id"],
//(string)allRead["categoria"],
//(string)allRead["name"],
//(string)allRead["code"])); //Isso não converte.
}
}
catch (Exception e)
{
MessageBox.Show("Erro ao montar a lista de Fundos: " + e.Message);
return(null);
}
}
catch (Exception e)
{
MessageBox.Show("Erro ao acessar Banco de Dados: " + e);
return(null);
}
finally
{
connection.Close();
}
return(listProducts);
}
public JsonRow(SQLiteDataReader result)
{
rowData = new JObject();
for (int i = 0; i < result.FieldCount; i++)
{
string column = result.GetString(i);
rowData[column] = result[i].ToString();
}
}
//-------------------------------------------------------------------------------------------------------------------
//prints out the whole table in meesage box/ can do console output
public void ReadOutTable()//read out the whole table
{
sqlite_cmd.CommandText = "SELECT * FROM Emotions";
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
// Print out the content of the text field
// System.Console.WriteLine(sqlite_datareader["text"]); //used for console output
string myReader = sqlite_datareader.GetString(0);
System.Windows.MessageBox.Show(myReader);
}
}
internal static void MapDataReaderToContentDto(SQLiteDataReader dataReader, out ContentDto contentDto)
{
contentDto = new ContentDto();
contentDto.ContentId = dataReader.GetInt64((int) ContentDao.ContentsField.ContentId);
contentDto.UrlId = dataReader.GetInt64((int) ContentDao.ContentsField.UrlId);
contentDto.ContentType = (ContentTypesEnum) dataReader.GetInt64((int) ContentDao.ContentsField.ContentType);
contentDto.Element = dataReader.GetString((int) ContentDao.ContentsField.Element);
contentDto.Element = HttpUtility.UrlDecode(contentDto.Element);
contentDto.Line = dataReader.GetInt64((int) ContentDao.ContentsField.Line);
contentDto.LinePosition = dataReader.GetInt64((int) ContentDao.ContentsField.LinePosition);
}
public void execute(ref ObservableCollection<TagedItem> _TagedItemCollection)
{
sqliteCon.Open();
createCommand = new SQLiteCommand(query, sqliteCon);
createCommand.ExecuteNonQuery();
dataReader = createCommand.ExecuteReader();
_TagedItemCollection.Clear();
while (dataReader.Read())
{
_TagedItemCollection.Add(new TagedItem
{
FileName = dataReader.GetString(2),
FileExension = dataReader.GetString(3),
ModifyDate = dataReader.GetString(4)
});
}
sqliteCon.Close();
}
private void SqliteTest(object sender, RoutedEventArgs e)
{
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.CacheSize = 1024;
// connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "INSERT INTO test VALUES('a','b')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
protected void Button1_Click(object sender, EventArgs e)
{
con = new SQLiteConnection("Data Source=e:\\db\\project.s3db");
con.Open();
SQLiteCommand command = new SQLiteCommand("Select * from train", con);
reader = command.ExecuteReader();
//SQLiteDataAdapter ad=new SQLiteDataAdapter(,con);
//GridView gv = new GridView();
String str = "<table><tr><th>train no</th><th>train name</th><th>train timing</th><th>avaliable tickets</th><th>total tickets</th><th>cost</th></tr>";
while (reader.Read())
{
str += "</tr><td>" + reader.GetValue(0) + "</td>";
str += "<td>" + reader.GetString(1) + "</td>";
str += "<td>" + reader.GetString(2) + "</td>";
str += "<td>" + reader.GetValue(3) + "</td>";
str += "<td>" + reader.GetValue(4) + "</td>";
str += "<td>" + reader.GetValue(5) + "</td></tr>";
}
str += "</table>";
traindiv.InnerHtml = str;
con.Close();
}
public string[] getLatestMsgFromUser(string uid)
{
this.clearCmd();
string username = this.getUsernameById(Convert.ToInt16(uid));
string[] msgAr = new string[3];
msgAr[0] = username;
cmd.CommandText="Select msg,sendTimestamp from msgLog where userid="+uid+" ORDER BY sendTimestamp DESC LIMIT 0,1";
reader=cmd.ExecuteReader();
reader.Read();
msgAr[1] = Convert.ToString(reader.GetDateTime(1));
msgAr[2] = reader.GetString(0);
return msgAr;
}
//TODO: Doesn't accept single quote
//TODO: improve focus lost checking
//TODO: Consider making the database with unique index key!! so you can delete an item based on table selection/ or
//TODO: change the delete method to accept string from the grid.
//TODO: Make delete button dissapear when nothing is selected/ or make it read delete parameter from the grid.
//TODO: Make append/change function.
//TODO: Test github new master branch
public void CountRecords()
{
string command = "SELECT * FROM PasterData";
sqlite_connection.Open();
SQLiteCommand sqlite_command = sqlite_connection.CreateCommand();
sqlite_command.CommandText = command;
sqlite_datareader = sqlite_command.ExecuteReader();
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
string myreader = sqlite_datareader.GetString(0);
if (_maxNumber < Convert.ToInt32(myreader))
{
_maxNumber = Convert.ToInt32(myreader);
}
_count++;
}
}
public static object ReadDateTime(SQLiteDataReader reader, int idx)
{
try
{
if (!reader.IsDBNull(idx))
{
string val = reader.GetString(idx);
if(val.Length != 0)
{
return DateTime.Parse(val);
}
}
return new DateTime(1970, 1, 1);
}
catch (Exception)
{
return new DateTime(1970, 1, 1);
}
}
// 获取表的id
public List <string> LocalGetIdsOfTable(string table, string baseName, string order)
{
List <string> resultsStringList;
using (SQLiteConnection conn = new SQLiteConnection(LocalConnStr))
{
string sql = "SELECT " + baseName + " FROM " + table + " " + order;//建表语句
using (SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn))
{
cmdCreateTable.CommandText = sql;
conn.Open();
System.Data.SQLite.SQLiteDataReader reader = cmdCreateTable.ExecuteReader();
resultsStringList = new List <string>();
while (reader.Read())
{
resultsStringList.Add(reader.GetString(0));
}
reader.Close();
}
}
return(resultsStringList);
}
void ReadHeader()
{
SQLiteCommand command = _connection.CreateCommand();
command.CommandText = "SELECT key, value FROM header;";
_reader = command.ExecuteReader();
while (_reader.Read())
{
var key = _reader.GetString(0);
var value = _reader.GetValue(1);
if (key.ToLower() == "clientbuild")
{
int build;
if (int.TryParse(value.ToString(), out build))
SetBuild(build);
break;
}
}
_reader.Close();
}
/// <summary>
/// 读取权限列表
/// </summary>
/// <returns>权限列表的name与权限</returns>
public List <Tuple <string, int> > ReadAuthorityTable()
{
try
{
using (var conn = new System.Data.SQLite.SQLiteConnection())
{
var connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand())
{
var collect = new List <Tuple <string, int> >();
cmd.Connection = conn;
string sql = "SELECT * from control_authority";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
collect.Add(new Tuple <string, int>(reader.GetString(0), reader.GetInt32(1)));
}
conn.Close();
return(collect);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private static Outfit createOutfit(SQLiteDataReader reader)
{
SQLiteCommand command;
if (!reader.Read()) {
return null;
}
Outfit outfit = new Outfit();
outfit.permanent = true;
outfit.id = reader.GetInt32(0);
outfit.title = reader.GetString(1);
outfit.name = reader.GetString(2);
outfit.premium = reader.GetBoolean(3);
outfit.tibiastore = reader.GetBoolean(4);
// Outfit Images
command = new SQLiteCommand(String.Format("SELECT male, addon, image FROM OutfitImages WHERE outfitid={0}", outfit.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
bool male = reader.GetBoolean(0);
int addon = reader.GetInt32(1);
Image image = Image.FromStream(reader.GetStream(2));
if (male) {
outfit.maleImages[addon] = image;
} else {
outfit.femaleImages[addon] = image;
}
}
command = new SQLiteCommand(String.Format("SELECT questid FROM QuestOutfits WHERE outfitid={0}", outfit.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
outfit.questid = reader.GetInt32(0);
}
return outfit;
}
private void ReadAnalogData(SQLiteDataReader r, IDictionary<string, int> field)
{
this.Name = r.GetString(field["channel_label"]);
this.FreqInMhz = (decimal)r.GetInt32(field["frequency"]) / 1000000;
}
void TestSQLite2()
{
try
{
//创建一个数据库文件
string datasource = "./test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
// connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password text)";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
SQLiteCommand cmd2 = new SQLiteCommand("INSERT INTO test(username, password) VALUES('dotnetthink', ?)", conn);
cmd2.Parameters.Add("password");
byte[] password = new byte[] { 1, 2, 3, 4, 5 };
cmd2.Parameters["password"].Value = password;
cmd2.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private static NPC createNPC(SQLiteDataReader reader)
{
SQLiteCommand command;
if (!reader.Read()) {
return null;
}
NPC npc = new NPC();
npc.permanent = true;
npc.id = reader.GetInt32(0);
npc.name = reader["name"].ToString();
npc.city = reader["city"].ToString();
if (!reader.IsDBNull(3) && !reader.IsDBNull(4) && !reader.IsDBNull(5)) {
npc.pos.x = reader.GetInt32(3);
npc.pos.y = reader.GetInt32(4);
npc.pos.z = reader.GetInt32(5);
}
npc.image = Image.FromStream(reader.GetStream(6));
npc.job = reader.IsDBNull(7) ? "" : reader.GetString(7);
if (npc.image.RawFormat.Guid == ImageFormat.Gif.Guid) {
int frames = npc.image.GetFrameCount(FrameDimension.Time);
if (frames == 1) {
Bitmap new_bitmap = new Bitmap(npc.image);
new_bitmap.MakeTransparent();
npc.image.Dispose();
npc.image = new_bitmap;
}
}
// special case for rashid: change location based on day of the week
if (npc != null && npc.name == "Rashid") {
command = new SQLiteCommand(String.Format("SELECT city, x, y, z FROM RashidPositions WHERE day='{0}'", DateTime.Now.DayOfWeek.ToString()), mainForm.conn);
reader = command.ExecuteReader();
if (reader.Read()) {
npc.city = reader["city"].ToString();
npc.pos.x = reader.GetInt32(1);
npc.pos.y = reader.GetInt32(2);
npc.pos.z = reader.GetInt32(3);
}
}
command = new SQLiteCommand(String.Format("SELECT itemid, value FROM SellItems WHERE vendorid={0}", npc.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
ItemSold sellItem = new ItemSold();
sellItem.itemid = reader.GetInt32(0);
sellItem.npcid = npc.id;
sellItem.price = reader.GetInt32(1);
npc.sellItems.Add(sellItem);
}
command = new SQLiteCommand(String.Format("SELECT itemid, value FROM BuyItems WHERE vendorid={0}", npc.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
ItemSold buyItem = new ItemSold();
buyItem.itemid = reader.GetInt32(0);
buyItem.npcid = npc.id;
buyItem.price = reader.GetInt32(1);
npc.buyItems.Add(buyItem);
}
command = new SQLiteCommand(String.Format("SELECT spellid,knight,druid,paladin,sorcerer FROM SpellNPCs WHERE npcid={0}", npc.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
SpellTaught t = new SpellTaught();
t.npcid = npc.id;
t.spellid = reader.GetInt32(0);
t.knight = reader.GetBoolean(1);
t.druid = reader.GetBoolean(2);
t.paladin = reader.GetBoolean(3);
t.sorcerer = reader.GetBoolean(4);
npc.spellsTaught.Add(t);
}
command = new SQLiteCommand(String.Format("SELECT DISTINCT questid FROM QuestNPCs WHERE npcid={0}", npc.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Quest q = getQuest(reader.GetInt32(0));
npc.involvedQuests.Add(q);
}
command = new SQLiteCommand(String.Format("SELECT destination,cost,notes FROM NPCDestinations WHERE npcid={0}", npc.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Transport t = new Transport();
t.destination = reader.GetString(0);
t.cost = reader.GetInt32(1);
t.notes = reader.GetString(2);
npc.transportOffered.Add(t);
}
return npc;
}
private static Mount createMount(SQLiteDataReader reader)
{
if (!reader.Read()) {
return null;
}
Mount mount = new Mount();
mount.permanent = true;
mount.id = reader.GetInt32(0);
mount.title = reader.GetString(1);
mount.name = reader.GetString(2);
int tameitem = reader.IsDBNull(3) ? DATABASE_NULL : reader.GetInt32(3);
if (tameitem > 0) mount.tameitemid = tameitem;
else mount.tameitemid = -1;
int tamecreature = reader.IsDBNull(4) ? DATABASE_NULL : reader.GetInt32(4);
if (tamecreature > 0) mount.tamecreatureid = tamecreature;
else mount.tamecreatureid = -1;
mount.speed = reader.GetInt32(5);
mount.tibiastore = reader.GetBoolean(6);
mount.image = Image.FromStream(reader.GetStream(7));
return mount;
}
/* Returns an Exercise using a SQLiteDataReader and a dictionary mapping the attributes to a index.
*/
private Exercise ExerciseExtractor(SQLiteDataReader reader, Dictionary<string, int> attributeIndexDict)
{
return new Exercise(reader.GetString(attributeIndexDict["exerciseName"]),
reader.GetString(attributeIndexDict["format"]), reader.GetInt32(attributeIndexDict["exerciseVersion"]),
reader.GetInt32(attributeIndexDict["deleted"]));
}
/* Returns a Workout using a SQLiteDataReader and a dictionary mapping the attributes to a index.
*/
private Workout WorkoutExtractor(SQLiteDataReader reader, Dictionary<string, int> attributeIndexDict)
{
return new Workout(reader.GetString(attributeIndexDict["workoutName"]),
reader.GetInt32(attributeIndexDict["workoutVersion"]));
}
private DataTable convertToDataTable(SQLiteDataReader reader)
{
DataTable table = new DataTable();
DataTable dtb = new DataTable();
table = reader.GetSchemaTable();
for (int x = 0; x < table.Rows.Count; x++)
{
string colNome = table.Rows[x]["ColumnName"].ToString();
dtb.Columns.Add(colNome, typeof(string));
}
dtb.AcceptChanges();
while (reader.Read())
{
DataRow dr = dtb.NewRow();
for (int i = 0; i < table.Rows.Count; i++)
{
string colNome = table.Rows[i]["ColumnName"].ToString();
string colType = table.Rows[i]["DataTypeName"].ToString();
string valor = string.Empty;
if (colType.ToUpper().Equals("NUMBER") || colType.ToUpper().Equals("INT") || colType.ToUpper().Equals("INTERGER"))
{
try
{
long Lvalor = reader.GetInt64(i);
if (Lvalor != null)
valor = Lvalor.ToString();
}
catch (InvalidCastException inv)
{
valor = string.Empty;
}
}
else if (colType.ToUpper().Equals("VARCHAR2") || colType.ToUpper().Equals("VARCHAR") || colType.ToUpper().Equals("TEXT"))
valor = reader.GetString(i).ToString();
dr[colNome] = valor;
}
dtb.Rows.Add(dr);
}
dtb.AcceptChanges();
return dtb;
}
private static ServerData ResultToServerData(SQLiteDataReader result)
{
ServerData serverToReturn = null;
Guid id = result.GetGuid(0);
string name = result.GetString(1);
string description = result.GetString(2);
string location = result.GetString(3);
byte[] imageBytes = (byte[])result.GetValue(4);
SensorDescriptionsData sensorData = new SensorDescriptionsData();
SensorDefinition[] sensor = new SensorDefinition[4] { new SensorDefinition(), new SensorDefinition(), new SensorDefinition(), new SensorDefinition()};
for (int s = 0; s < 4; s++)
{
// Do 4 sensor descriptions and 4 fields for each
sensor[s].Name = (string)SQLHelper.ValueIfNull(result, s + 5, "");
sensor[s].Unit = (string)SQLHelper.ValueIfNull(result, s + 9, "");
sensor[s].ID = Convert.ToInt16(SQLHelper.ValueIfNull(result, s + 13, 255));
sensor[s].Range = Convert.ToInt16(SQLHelper.ValueIfNull(result, s + 17, 255));
sensorData.Add(sensor[s]);
}
serverToReturn = new ServerData(id, name, location, description, sensorData, imageBytes);
return serverToReturn;
}
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());
}
}
private void button1_Click(object sender, EventArgs e)
{
//创建一个数据库文件
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES(’dotnetthink’,'mypassword’)";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
private static Spell createSpell(SQLiteDataReader reader)
{
SQLiteCommand command;
if (!reader.Read()) {
return null;
}
Spell spell = new Spell();
spell.permanent = true;
spell.id = reader.GetInt32(0);
spell.name = reader["name"].ToString();
spell.words = reader["words"].ToString();
spell.element = reader.IsDBNull(3) ? "Unknown" : reader.GetString(3);
spell.cooldown = reader.IsDBNull(4) ? DATABASE_NULL : reader.GetInt32(4);
spell.premium = reader.GetBoolean(5);
spell.promotion = reader.GetBoolean(6);
spell.levelrequired = reader.GetInt32(7);
spell.goldcost = reader.GetInt32(8);
spell.manacost = reader.GetInt32(9);
spell.knight = reader.GetBoolean(10);
spell.paladin = reader.GetBoolean(11);
spell.sorcerer = reader.GetBoolean(12);
spell.druid = reader.GetBoolean(13);
spell.image = Image.FromStream(reader.GetStream(14));
command = new SQLiteCommand(String.Format("SELECT npcid, knight, druid, paladin, sorcerer FROM SpellNPCs WHERE spellid={0}", spell.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
SpellTaught t = new SpellTaught();
t.npcid = reader.GetInt32(0);
t.spellid = spell.id;
t.knight = reader.GetBoolean(1);
t.druid = reader.GetBoolean(2);
t.paladin = reader.GetBoolean(3);
t.sorcerer = reader.GetBoolean(4);
spell.teachNPCs.Add(t);
}
return spell;
}
private string SafeStr(SQLiteDataReader reader, int idx)
{
try
{
if (reader.IsDBNull(idx))
return "";
else
return reader.GetString(idx);
}
catch (Exception)
{
return "";
}
}
private static WorldObject createWorldObject(SQLiteDataReader reader)
{
if (!reader.Read()) {
return null;
}
WorldObject o = new WorldObject();
o.title = reader.GetString(0);
o.name = reader.GetString(1);
o.image = Image.FromStream(reader.GetStream(2)); ;
return o;
}
public List <Magazyn> GetSearchMagazyn(String searchTxt)
{
string sql = @"SELECT KopertaGUID,
NrKoperty,
NrPlomby,
DataOdbioru,
GodzinaOdbioru,
DataKopii,
KopertaNrPro,
Przekazal,
Zwrocil,
NazwaKlienta,
NIP,
AdresKlienta,
KodPocztowy,
Miasto,
KonwojentImie,
KonwojentNazwisko,
DataZwrotu,
CompanyGUID
FROM Magazyn
WHERE CompanyGUID = @CompanyGUID
AND
(NrKoperty LIKE @searchTxt OR
NrPlomby LIKE @searchTxt OR
DataOdbioru LIKE @searchTxt OR
GodzinaOdbioru LIKE @searchTxt OR
DataKopii LIKE @searchTxt OR
KopertaNrPro LIKE @searchTxt OR
Przekazal LIKE @searchTxt OR
Zwrocil LIKE @searchTxt OR
NazwaKlienta LIKE @searchTxt OR
NIP LIKE @searchTxt OR
AdresKlienta LIKE @searchTxt OR
KodPocztowy LIKE @searchTxt OR
Miasto LIKE @searchTxt OR
KonwojentImie LIKE @searchTxt OR
KonwojentNazwisko LIKE @searchTxt OR
DataZwrotu LIKE @searchTxt)
"
;
List <Magazyn> retList = new List <Magazyn>();
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@CompanyGUID", GlobalVariables.CurrentCompany.CompanyGUID));
paramList.Add(new SQLiteParameter("@searchTxt", "%" + searchTxt + "%"));
NGSConnector connector = new NGSConnector();
System.Data.SQLite.SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
while (reader.Read())
{
Magazyn m = new Magazyn();
m.FillData(
reader.IsDBNull(0) ? "" : reader.GetString(0),
reader.IsDBNull(1) ? "" : reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? "" : reader.GetString(3),
reader.IsDBNull(4) ? "" : reader.GetString(4),
reader.IsDBNull(5) ? "" : reader.GetString(5),
reader.IsDBNull(6) ? "" : reader.GetString(6),
reader.IsDBNull(7) ? "" : reader.GetString(7),
reader.IsDBNull(8) ? "" : reader.GetString(8),
reader.IsDBNull(9) ? "" : reader.GetString(9),
reader.IsDBNull(10) ? "" : reader.GetString(10),
reader.IsDBNull(11) ? "" : reader.GetString(11),
reader.IsDBNull(12) ? "" : reader.GetString(12),
reader.IsDBNull(13) ? "" : reader.GetString(13),
reader.IsDBNull(14) ? "" : reader.GetString(14),
reader.IsDBNull(15) ? "" : reader.GetString(15),
reader.IsDBNull(16) ? "" : reader.GetString(16),
reader.IsDBNull(17) ? "" : reader.GetString(17)
);
retList.Add(m);
}
reader = null;
return(retList);
}
private static HuntingPlace createHunt(SQLiteDataReader reader)
{
SQLiteCommand command;
if (!reader.Read()) {
return null;
}
HuntingPlace huntingPlace = new HuntingPlace();
huntingPlace.permanent = true;
huntingPlace.id = reader.GetInt32(0);
huntingPlace.name = reader["name"].ToString();
huntingPlace.level = reader.IsDBNull(2) ? DATABASE_NULL : reader.GetInt32(2);
huntingPlace.exp_quality = reader.IsDBNull(3) ? DATABASE_NULL : reader.GetInt32(3);
huntingPlace.loot_quality = reader.IsDBNull(4) ? DATABASE_NULL : reader.GetInt32(4);
string imageName = reader.GetString(5).ToLower();
Creature cr = getCreature(imageName);
if (cr != null) {
huntingPlace.image = cr.GetImage();
} else {
NPC npc = getNPC(imageName);
if (npc != null) {
huntingPlace.image = npc.GetImage();
} else {
throw new Exception("Unrecognized npc or creature image.");
}
}
huntingPlace.city = reader["city"].ToString();
// Hunting place coordinates
command = new SQLiteCommand(String.Format("SELECT x, y, z FROM HuntingPlaceCoordinates WHERE huntingplaceid={0}", huntingPlace.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Coordinate c = new Coordinate();
c.x = reader.IsDBNull(0) ? DATABASE_NULL : reader.GetInt32(0);
c.y = reader.IsDBNull(1) ? DATABASE_NULL : reader.GetInt32(1);
c.z = reader.IsDBNull(2) ? DATABASE_NULL : reader.GetInt32(2);
huntingPlace.coordinates.Add(c);
}
// Hunting place directions
command = new SQLiteCommand(String.Format("SELECT beginx, beginy, beginz,endx, endy, endz, ordering, description, settings FROM HuntDirections WHERE huntingplaceid={0} ORDER BY ordering", huntingPlace.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Directions d = new Directions();
d.huntingplaceid = huntingPlace.id;
d.begin = new Coordinate(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2));
d.end = new Coordinate(reader.GetInt32(3), reader.GetInt32(4), reader.GetInt32(5));
d.ordering = reader.GetInt32(6);
d.description = reader["description"].ToString();
d.settings = reader.GetString(8);
huntingPlace.directions.Add(d);
}
// Hunting place creatures
command = new SQLiteCommand(String.Format("SELECT creatureid FROM HuntingPlaceCreatures WHERE huntingplaceid={0}", huntingPlace.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int creatureid = reader.GetInt32(0);
huntingPlace.creatures.Add(creatureid);
}
// Hunting place requirements
command = new SQLiteCommand(String.Format("SELECT questid, requirementtext FROM HuntRequirements WHERE huntingplaceid={0}", huntingPlace.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Requirements r = new Requirements();
r.huntingplaceid = huntingPlace.id;
int questid = reader.IsDBNull(0) ? DATABASE_NULL : reader.GetInt32(0);
r.quest = questIdMap[questid];
r.notes = reader["requirementtext"].ToString();
huntingPlace.requirements.Add(r);
}
return huntingPlace;
}
/* Returns an Exercise using a SQLiteDataReader and a dictionary mapping the attributes to a index.
*/
private Date DateExtractor(SQLiteDataReader reader, Dictionary<string, int> attributeIndexDict)
{
return new Date(reader.GetInt32(attributeIndexDict["day"]),
reader.GetInt32(attributeIndexDict["month"]), reader.GetInt32(attributeIndexDict["year"]),
reader.GetString(attributeIndexDict["workoutName"]), reader.GetInt32(attributeIndexDict["workoutVersion"]),
reader.GetString(attributeIndexDict["comments"]));
}
private static Item createItem(SQLiteDataReader reader)
{
SQLiteCommand command;
if (!reader.Read()) {
return null;
}
Item item = new Item();
item.permanent = true;
item.id = reader.GetInt32(0);
item.displayname = reader.GetString(1);
item.actual_value = reader.IsDBNull(2) ? DATABASE_NULL : reader.GetInt64(2);
item.vendor_value = reader.IsDBNull(3) ? DATABASE_NULL : reader.GetInt64(3);
item.stackable = reader.GetBoolean(4);
item.capacity = reader.IsDBNull(5) ? DATABASE_NULL : reader.GetFloat(5);
item.category = reader.IsDBNull(6) ? "Unknown" : reader.GetString(6);
item.discard = reader.GetBoolean(7);
item.convert_to_gold = reader.GetBoolean(8);
item.look_text = reader.IsDBNull(9) ? String.Format("You see a {0}.", item.displayname) : reader.GetString(9);
item.title = reader.GetString(10);
item.currency = reader.IsDBNull(11) ? DATABASE_NULL : reader.GetInt32(11);
item.image = Image.FromStream(reader.GetStream(12));
if (item.image.RawFormat.Guid == ImageFormat.Gif.Guid) {
int frames = item.image.GetFrameCount(FrameDimension.Time);
if (frames == 1) {
Bitmap new_bitmap = new Bitmap(item.image);
new_bitmap.MakeTransparent();
item.image.Dispose();
item.image = new_bitmap;
}
}
command = new SQLiteCommand(String.Format("SELECT vendorid, value FROM SellItems WHERE itemid={0}", item.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
ItemSold sellItem = new ItemSold();
sellItem.itemid = item.id;
sellItem.npcid = reader.GetInt32(0);
sellItem.price = reader.GetInt32(1);
item.sellItems.Add(sellItem);
}
command = new SQLiteCommand(String.Format("SELECT vendorid, value FROM BuyItems WHERE itemid={0}", item.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
ItemSold buyItem = new ItemSold();
buyItem.itemid = item.id;
buyItem.npcid = reader.GetInt32(0);
buyItem.price = reader.GetInt32(1);
item.buyItems.Add(buyItem);
}
command = new SQLiteCommand(String.Format("SELECT creatureid, percentage, min, max FROM CreatureDrops WHERE itemid={0}", item.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
ItemDrop itemDrop = new ItemDrop();
itemDrop.itemid = item.id;
itemDrop.creatureid = reader.GetInt32(0);
itemDrop.percentage = reader.IsDBNull(1) ? DATABASE_NULL : reader.GetFloat(1);
if (itemDrop.percentage > 100) {
itemDrop.min = 1;
itemDrop.max = (int)(itemDrop.percentage / 100.0 * 2.0);
itemDrop.percentage = 100;
} else {
itemDrop.min = Math.Max(reader.GetInt32(2), 1);
itemDrop.max = Math.Max(reader.GetInt32(3), itemDrop.min);
}
item.itemdrops.Add(itemDrop);
}
command = new SQLiteCommand(String.Format("SELECT questid FROM QuestRewards WHERE itemid={0}", item.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
item.rewardedBy.Add(getQuest(reader.GetInt32(0)));
}
command = new SQLiteCommand(String.Format("SELECT property, value FROM ItemProperties WHERE itemid={0}", item.id), mainForm.conn);
reader = command.ExecuteReader();
while (reader.Read()) {
string property = reader.GetString(0);
switch(property) {
case "Voc":
item.vocation = reader.GetString(1);
break;
case "Level":
item.level = reader.GetInt32(1);
break;
case "Def":
item.defensestr = reader["value"].ToString();
if (!int.TryParse(item.defensestr, out item.defense)) {
item.defense = int.Parse(item.defensestr.Split(' ')[0]);
}
break;
case "Attrib":
item.attrib = reader.GetString(1);
break;
case "Atk":
item.attack = reader.GetInt32(1);
break;
case "Atk+":
item.atkmod = reader.GetInt32(1);
break;
case "Hit+":
string str = reader["value"].ToString();
int.TryParse(str, out item.hitmod);
break;
case "Arm":
item.armor = reader.GetInt32(1);
break;
case "Range":
item.range = reader.GetInt32(1);
break;
case "Type":
item.type = reader.GetString(1);
break;
}
}
return item;
}
