/// <summary>
/// 保存数据日志
/// </summary>
/// <param name="tableName">保存表名称</param>
/// <param name="m"></param>
public void Save(string tableName)
{
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
string sql = "INSERT INTO " + tableName + " (ctime,pkg_type, pkg_len, msg_type, msg_len, body) VALUES(@ctime,@pkg_type, @pkg_len, @msg_type, @msg_len, @body)";
SQLiteParameter[] parameters =
{
new SQLiteParameter("@ctime", DbType.DateTime2),
new SQLiteParameter("@pkg_type", DbType.String),
new SQLiteParameter("@pkg_len", DbType.Int32, 4),
new SQLiteParameter("@msg_type", DbType.Int32, 4),
new SQLiteParameter("@msg_len", DbType.Int32, 4),
new SQLiteParameter("@body", DbType.String)
};
parameters[0].Value = DateTime.Now;
parameters[1].Value = this.pkgType;
parameters[2].Value = this.pkgLen;
parameters[3].Value = this.msgType;
parameters[4].Value = this.msgLen;
parameters[5].Value = this.GetBodyByte();
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sql, parameters);
cmd.ExecuteNonQuery();
conn.Close();
}
/// <summary>
/// 获取全部数据
/// </summary>
/// <returns></returns>
public static List <MhxyMapRegion> GetMapAll()
{
List <MhxyMapRegion> ret = new List <MhxyMapRegion>();
string sql = "select * from mhxy_map_region";
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MhxyMapRegion m = new MhxyMapRegion();
m.id = Convert.ToInt32(ds.Tables[0].Rows[i]["id"].ToString());
m.map_id = Convert.ToInt32(ds.Tables[0].Rows[i]["map_id"].ToString());
m.name = ds.Tables[0].Rows[i]["name"].ToString();
m.x = Convert.ToInt32(ds.Tables[0].Rows[i]["x"].ToString());
m.y = Convert.ToInt32(ds.Tables[0].Rows[i]["y"].ToString());
m.max_x = Convert.ToInt32(ds.Tables[0].Rows[i]["max_x"].ToString());
m.max_y = Convert.ToInt32(ds.Tables[0].Rows[i]["max_y"].ToString());
ret.Add(m);
}
return(ret);
}
protected override void OnCreate(Bundle bundle)
{
TabLayoutResource = Resource.Layout.Tabbar;
ToolbarResource = Resource.Layout.Toolbar;
base.OnCreate(bundle);
SQLiteUtil sql = new SQLiteUtil(this);
Database.Repository dl = new Database.Repository(sql);
Updater.Update(dl);
global::Xamarin.Forms.Forms.Init(this, bundle);
Xamarin.Forms.DependencyService.Register <ToastNotification>(); // Register your dependency
// If you are using Android you must pass through the activity
//ToastNotification.Init(this);
ToastNotification.Init(this, new PlatformOptions()
{
SmallIconDrawable = Android.Resource.Drawable.IcDialogInfo
});
LoadApplication(new App(dl));
}
public static void Add(MhxyType mt)
{
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
string sql = "INSERT INTO mhxy_type (type_id, name, is_show,prefix,filert_rule) VALUES(@type_id, @name, @is_show,@prefix,@filert_rule)";
SQLiteParameter[] parameters =
{
new SQLiteParameter("@type_id", DbType.Int32, 4),
new SQLiteParameter("@name", DbType.String),
new SQLiteParameter("@is_show", DbType.Int32, 4),
new SQLiteParameter("@prefix", DbType.String),
new SQLiteParameter("@filert_rule", DbType.String)
};
parameters[0].Value = mt.TypeId;
parameters[1].Value = mt.Name;
parameters[2].Value = mt.IsShow;
parameters[3].Value = mt.prefix;
parameters[4].Value = mt.filertRule;
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sql, parameters);
cmd.ExecuteNonQuery();
conn.Close();
}
public static List <MhxyAxis> GetIDList(int flag_id, int type)
{
List <MhxyAxis> ret = new List <MhxyAxis>();
string sql = "select * from mhxy_axis where flag_id=" + flag_id.ToString() + " and type=" + type.ToString();
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MhxyAxis ms = new MhxyAxis();
ms.id = Convert.ToInt32(ds.Tables[0].Rows[i]["id"].ToString());
ms.flag_id = Convert.ToInt32(ds.Tables[0].Rows[i]["flag_id"].ToString());
ms.x = Convert.ToInt32(ds.Tables[0].Rows[i]["x"].ToString());
ms.y = Convert.ToInt32(ds.Tables[0].Rows[i]["y"].ToString());
ms.nx = Convert.ToInt32(ds.Tables[0].Rows[i]["nx"].ToString());
ms.ny = Convert.ToInt32(ds.Tables[0].Rows[i]["ny"].ToString());
ms.remarks = ds.Tables[0].Rows[i]["remarks"].ToString();
ret.Add(ms);
}
return(ret);
}
public bool Insert(Member member)
{
try
{
using (var stt = SQLiteUtil.GetIntances().Connection.Prepare("INSERT INTO Members (FirstName, LastName, Avatar, Phone, " +
"Address, Introduction, Gender, Birthday, Email, Password) VALUES (?,?,?,?,?,?,?,?,?,?)"))
{
stt.Bind(1, member.FirstName);
stt.Bind(2, member.LastName);
stt.Bind(3, member.Avatar);
stt.Bind(4, member.Phone);
stt.Bind(5, member.Address);
stt.Bind(6, member.Introduction);
stt.Bind(7, member.Gender);
stt.Bind(8, member.Birthday);
stt.Bind(9, member.Email);
stt.Bind(10, member.Password);
stt.Step();
return(true);
}
}
catch (Exception ex)
{
}
return(false);
}
public static MhxyConfig GetID(int id)
{
MhxyConfig ret = new MhxyConfig();
string sql = "select * from mhxy_cfg where id=" + id.ToString();
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ret.id = Convert.ToInt32(ds.Tables[0].Rows[i]["id"].ToString());
ret.hp = Convert.ToInt32(ds.Tables[0].Rows[i]["hp"].ToString());
ret.mp = Convert.ToInt32(ds.Tables[0].Rows[i]["mp"].ToString());
ret.hp_mp_auto = Convert.ToInt32(ds.Tables[0].Rows[i]["hp_mp_auto"].ToString());
ret.bb_hp = Convert.ToInt32(ds.Tables[0].Rows[i]["bb_hp"].ToString());
ret.bb_mp = Convert.ToInt32(ds.Tables[0].Rows[i]["bb_mp"].ToString());
ret.bb_hp_mp_auto = Convert.ToInt32(ds.Tables[0].Rows[i]["bb_hp_mp_auto"].ToString());
ret.skill = Convert.ToInt32(ds.Tables[0].Rows[i]["skill"].ToString());
ret.bb_skill = Convert.ToInt32(ds.Tables[0].Rows[i]["bb_skill"].ToString());
ret.skill_auto = Convert.ToInt32(ds.Tables[0].Rows[i]["skill_auto"].ToString());
ret.bb_skill_auto = Convert.ToInt32(ds.Tables[0].Rows[i]["bb_skill_auto"].ToString());
}
return(ret);
}
/// <summary>
/// 获取全部NPC数据
/// </summary>
/// <returns></returns>
public static List <MhxyNPC> GetAll()
{
List <MhxyNPC> ret = new List <MhxyNPC>();
string sql = "select * from mhxy_npc";
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MhxyNPC mt = new MhxyNPC();
mt.map_id = Convert.ToInt32(ds.Tables[0].Rows[i]["map_id"].ToString());
mt.name = ds.Tables[0].Rows[i]["name"].ToString();
mt.npc_id = Convert.ToInt32(ds.Tables[0].Rows[i]["npc_id"].ToString());
mt.x = Convert.ToInt32(ds.Tables[0].Rows[i]["x"].ToString());
mt.y = Convert.ToInt32(ds.Tables[0].Rows[i]["y"].ToString());
ret.Add(mt);
}
return(ret);
}
public async void SaveCityToDB(List <City> cityList)
{
await Task.Run(() => {
ShowStatusText($"开始保存城市数据");
using (SQLiteUtil sqlite = new SQLiteUtil(dbPath))
{
var sql = "Delete From City";
sqlite.ExecuteNonQuery(sql);
foreach (var item in cityList)
{
sql = "Insert Into City (ProvinceID,CityID,CityName,CityPinYin) Values (@ProvinceID,@CityID,@CityName,@CityPinYin)";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[]
{
new System.Data.SQLite.SQLiteParameter("@ProvinceID", item.ProvinceID),
new System.Data.SQLite.SQLiteParameter("@CityID", item.CityID),
new System.Data.SQLite.SQLiteParameter("@CityName", item.CityName),
new System.Data.SQLite.SQLiteParameter("@CityPinYin", item.CityPinYinName)
};
sqlite.ExecuteNonQuery(sql, parameters);
ShowStatusText($"保存{item.CityName}数据完成");
Task.Delay(5);
}
}
ShowStatusText($"保存城市数据完成");
});
}
/// <summary>
/// 获取全部数据
/// </summary>
/// <returns></returns>
public static List <MhxySkill> GetAll()
{
List <MhxySkill> ret = new List <MhxySkill>();
string sql = "select * from mhxy_skill";
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MhxySkill ms = new MhxySkill();
ms.SkillId = Convert.ToInt32(ds.Tables[0].Rows[i]["skill_id"].ToString());
ms.Name = ds.Tables[0].Rows[i]["name"].ToString();
ms.SkillType = Convert.ToInt32(ds.Tables[0].Rows[i]["skill_type"].ToString());
ms.Factions = Convert.ToInt32(ds.Tables[0].Rows[i]["factions"].ToString());
ms.hp = Convert.ToInt32(ds.Tables[0].Rows[i]["hp"].ToString());
ms.mp = Convert.ToInt32(ds.Tables[0].Rows[i]["mp"].ToString());
ret.Add(ms);
}
return(ret);
}
public void Save()
{
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
string sql = "INSERT INTO mhxy_map_region (map_id,name,x,y,max_x,max_y) VALUES(@map_id,@name,@x,@y,@max_x,@max_y)";
SQLiteParameter[] parameters =
{
new SQLiteParameter("@map_id", DbType.Int32, 4),
new SQLiteParameter("@name", DbType.String),
new SQLiteParameter("@x", DbType.Int32, 4),
new SQLiteParameter("@y", DbType.Int32, 4),
new SQLiteParameter("@max_x", DbType.Int32, 4),
new SQLiteParameter("@max_y", DbType.Int32, 4),
};
parameters[0].Value = map_id;
parameters[1].Value = name;
parameters[2].Value = x;
parameters[3].Value = y;
parameters[4].Value = max_x;
parameters[5].Value = max_y;
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sql, parameters);
cmd.ExecuteNonQuery();
conn.Close();
}
private void ClearRecord()
{
using (SQLiteUtil sqlite = new SQLiteUtil(dbPath))
{
var sql = "Delete From Result";
sqlite.ExecuteNonQuery(sql);
}
}
// 插入一批日志记录
int AppendOperLogLines(
SQLiteConnection connection,
List <OperLogLine> lines,
out string strError)
{
strError = "";
using (SQLiteCommand command = new SQLiteCommand("",
connection))
{
StringBuilder text = new StringBuilder(4096);
int i = 0;
foreach (OperLogLine line in lines)
{
text.Append(
" INSERT INTO records(operation, action, itembarcode, location, readerbarcode, librarycode, opertime) "
+ " VALUES(@operation" + i
+ ", @action" + i
+ ", @itembarcode" + i
+ ", @location" + i
+ ", @readerbarcode" + i
+ ", @librarycode" + i
+ ", @opertime" + i + ")"
+ " ; ");
SQLiteUtil.SetParameter(command,
"@operation" + i,
line.Operation);
SQLiteUtil.SetParameter(command,
"@action" + i,
line.Action);
SQLiteUtil.SetParameter(command,
"@itembarcode" + i,
line.ItemBarcode);
SQLiteUtil.SetParameter(command,
"@location" + i,
line.ItemLocation);
SQLiteUtil.SetParameter(command,
"@librarycode" + i,
line.LibraryCode);
SQLiteUtil.SetParameter(command,
"@opertime" + i,
line.OperTime);
i++;
}
command.CommandText = text.ToString();
int nCount = command.ExecuteNonQuery();
}
return(0);
}
/// <summary>
/// 修改状态
/// </summary>
/// <param name="typeId"></param>
/// <param name="value"></param>
/// <returns>返回影响行数</returns>
public static int UpdateShow(int typeId, int value)
{
string sql = "update mhxy_type set is_show=" + value.ToString() + " where type_id=" + typeId.ToString();
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sql);
int ret = cmd.ExecuteNonQuery();
conn.Close();
return(ret);
}
/// <summary>
/// 获取任务全部数据
/// </summary>
/// <param name="mapId"></param>
/// <param name="targetId"></param>
/// <returns></returns>
public static List <MhxyRouterRec> GetRouterAll(int mapId, int targetId)
{
List <MhxyMapExit> exlist = MhxyMapExit.GetAll();
List <MhxyNPC> npcList = MhxyNPC.GetAll();
List <MhxyRouterRec> ret = new List <MhxyRouterRec>();
string sql = "select * from mhxy_router_rec where map_id=" + mapId.ToString() + " and target_id=" + targetId.ToString() + " order by sort asc";
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MhxyRouterRec mt = new MhxyRouterRec();
mt.id = Convert.ToInt32(ds.Tables[0].Rows[i]["id"].ToString());
mt.map_id = Convert.ToInt32(ds.Tables[0].Rows[i]["map_id"].ToString());
mt.exit_id = Convert.ToInt32(ds.Tables[0].Rows[i]["exit_id"].ToString());
mt.sort = Convert.ToInt32(ds.Tables[0].Rows[i]["sort"].ToString());
mt.is_type = Convert.ToInt32(ds.Tables[0].Rows[i]["is_type"].ToString());
mt.target_id = Convert.ToInt32(ds.Tables[0].Rows[i]["target_id"].ToString());
mt.remarks = ds.Tables[0].Rows[i]["remarks"].ToString();
//装载出口信息
for (int k = 0; k < exlist.Count; k++)
{
if (mt.exit_id == exlist[k].id)
{
mt.mapExit = exlist[k];
}
}
//装载NPC
if (mt.is_type == 0)
{
for (int k = 0; k < npcList.Count; k++)
{
if (mt.target_id == npcList[k].npc_id)
{
mt.npc = npcList[k];
}
}
}
ret.Add(mt);
}
return(ret);
}
public bool Insert(Student student)
{
try
{
using (var stt = SQLiteUtil.GetIntances().Connection.Prepare("INSERT INTO Student (Name) VALUES (?)"))
{
stt.Bind(1, student.Name);
stt.Step();
return(true);
}
}
catch (Exception ex)
{
}
return(false);
}
public string[] ToSqliteTypes(Dictionary <string, ESQLiteAttributeType> extraAttributes = null)
{
string[] types = new string[_FieldInfos.Length];
for (int i = 0, length = _FieldInfos.Length; i < length; ++i)
{
var field = _FieldInfos[i];
if (extraAttributes != null && extraAttributes.ContainsKey(field.Name))
{
types[i] = SQLiteUtil.ToSqliteType(field.FieldType, extraAttributes[field.Name]);
}
else
{
types[i] = SQLiteUtil.ToSqliteType(field.FieldType);
}
}
return(types);
}
private async Task SaveResult(Result result)
{
await Task.Run(() => {
using (SQLiteUtil sqlite = new SQLiteUtil(dbPath))
{
var sql = "Insert Into Result (CityID,Html,RestaurentName,AverageSpend,Keyword) values (@CityID,@Html,@RestaurentName,@AverageSpend,@Keyword)";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[]
{
new System.Data.SQLite.SQLiteParameter("@CityID", result.CityID),
new System.Data.SQLite.SQLiteParameter("@Html", result.Html),
new System.Data.SQLite.SQLiteParameter("@RestaurentName", result.RestaurentName),
new System.Data.SQLite.SQLiteParameter("@AverageSpend", result.AverageSpend),
new System.Data.SQLite.SQLiteParameter("@Keyword", result.Keyword)
};
sqlite.ExecuteNonQuery(sql, parameters);
}
});
}
public static bool CheckTypeId(int typeId)
{
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
string sql = "select type_id from mhxy_type where type_id=" + typeId.ToString();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
DataTable dt = ds.Tables[0];
int count = dt.Rows.Count;
if (count > 0)
{
return(true);
}
return(false);
}
public static void Delete(int id)
{
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
string sql = "delete from mhxy_map_region where id=@id";
SQLiteParameter[] parameters =
{
new SQLiteParameter("@id", DbType.Int32, 4),
};
parameters[0].Value = id;
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sql, parameters);
cmd.ExecuteNonQuery();
conn.Close();
}
/// <summary>
/// 获取全部数据
/// </summary>
/// <returns></returns>
public static List <MhxyMapExit> GetAll()
{
List <MhxyMap> maps = MhxyMap.GetAll();
List <MhxyMapExit> ret = new List <MhxyMapExit>();
string sql = "select * from mhxy_map_exit";
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MhxyMapExit mt = new MhxyMapExit();
mt.id = Convert.ToInt32(ds.Tables[0].Rows[i]["id"].ToString());
mt.map_id = Convert.ToInt32(ds.Tables[0].Rows[i]["map_id"].ToString());
mt.to_map_id = Convert.ToInt32(ds.Tables[0].Rows[i]["to_map_id"].ToString());
mt.x = Convert.ToInt32(ds.Tables[0].Rows[i]["x"].ToString());
mt.y = Convert.ToInt32(ds.Tables[0].Rows[i]["y"].ToString());
mt.wait_x = Convert.ToInt32(ds.Tables[0].Rows[i]["wait_x"].ToString());
mt.wait_y = Convert.ToInt32(ds.Tables[0].Rows[i]["wait_y"].ToString());
mt.remarks = ds.Tables[0].Rows[i]["remarks"].ToString();
mt.npc_id = Convert.ToInt32(ds.Tables[0].Rows[i]["npc_id"].ToString());
mt.call_npc_option = Convert.ToInt32(ds.Tables[0].Rows[i]["call_npc_option"].ToString());
for (int j = 0; j < maps.Count; j++)
{
if (mt.map_id == maps[j].id)
{
mt.myMap = maps[j];
}
if (mt.to_map_id == maps[j].id)
{
mt.toMap = maps[j];
}
}
ret.Add(mt);
}
return(ret);
}
public static void Save(MhxyConfig mc)
{
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
string sql = "INSERT INTO mhxy_cfg (id,hp,mp,hp_mp_auto,bb_hp,bb_mp,bb_hp_mp_auto,skill,skill_auto,bb_skill,bb_skill_auto) VALUES(@id,@hp,@mp,@hp_mp_auto,@bb_hp,@bb_mp,@bb_hp_mp_auto,@skill,@skill_auto,@bb_skill,@bb_skill_auto)";
SQLiteParameter[] parameters =
{
new SQLiteParameter("@id", DbType.Int32, 4),
new SQLiteParameter("@hp", DbType.Int32, 4),
new SQLiteParameter("@mp", DbType.Int32, 4),
new SQLiteParameter("@hp_mp_auto", DbType.Int32, 4),
new SQLiteParameter("@bb_hp", DbType.Int32, 4),
new SQLiteParameter("@bb_mp", DbType.Int32, 4),
new SQLiteParameter("@bb_hp_mp_auto", DbType.Int32, 4),
new SQLiteParameter("@skill", DbType.Int32, 4),
new SQLiteParameter("@skill_auto", DbType.Int32, 4),
new SQLiteParameter("@bb_skill", DbType.Int32, 4),
new SQLiteParameter("@bb_skill_auto", DbType.Int32, 4),
};
parameters[0].Value = mc.id;
parameters[1].Value = mc.hp;
parameters[2].Value = mc.mp;
parameters[3].Value = mc.hp_mp_auto;
parameters[4].Value = mc.bb_hp;
parameters[5].Value = mc.bb_mp;
parameters[6].Value = mc.bb_hp_mp_auto;
parameters[7].Value = mc.skill;
parameters[8].Value = mc.skill_auto;
parameters[9].Value = mc.bb_skill;
parameters[10].Value = mc.bb_skill_auto;
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sql, parameters);
cmd.ExecuteNonQuery();
conn.Close();
}
/// <summary>
/// 获取全部数据
/// </summary>
/// <returns></returns>
public static List <MhxyType> GetAll()
{
string sql = "select * from mhxy_type";
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
List <MhxyType> ret = new List <MhxyType>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MhxyType mt = new MhxyType();
mt.TypeId = Convert.ToInt32(ds.Tables[0].Rows[i]["type_id"].ToString());
mt.Name = ds.Tables[0].Rows[i]["name"].ToString();
mt.IsShow = Convert.ToInt32(ds.Tables[0].Rows[i]["is_show"].ToString());
mt.prefix = ds.Tables[0].Rows[i]["prefix"].ToString();
mt.filertRule = ds.Tables[0].Rows[i]["filert_rule"].ToString();
ret.Add(mt);
}
return(ret);
}
public async void SaveProvinceToDB(List <Province> provinceList)
{
await Task.Run(() => {
ShowStatusText($"开始保存省份信息");
using (SQLiteUtil sqlite = new SQLiteUtil(dbPath))
{
var sql = "Delete From Province";
sqlite.ExecuteNonQuery(sql);
foreach (var item in provinceList)
{
sql = "Insert Into Province (ProvinceID,ProvinceName) Values (@ProvinceID,@ProvinceName)";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[]
{
new System.Data.SQLite.SQLiteParameter("@ProvinceID", item.ProvinceID),
new System.Data.SQLite.SQLiteParameter("@ProvinceName", item.ProvinceName)
};
sqlite.ExecuteNonQuery(sql, parameters);
}
}
ShowStatusText($"保存省份信息完成");
});
}
public static MhxyNPC GetNPCID(int npc_id)
{
MhxyNPC mt = new MhxyNPC();
string sql = "select * from mhxy_npc where npc_id=" + npc_id.ToString();
SQLiteConnection conn = SQLiteUtil.GetConn();
conn.Open();
DataSet ds = SQLiteHelper.ExecuteDataSet(conn, sql, null);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
mt.map_id = Convert.ToInt32(ds.Tables[0].Rows[i]["map_id"].ToString());
mt.name = ds.Tables[0].Rows[i]["name"].ToString();
mt.npc_id = Convert.ToInt32(ds.Tables[0].Rows[i]["npc_id"].ToString());
mt.x = Convert.ToInt32(ds.Tables[0].Rows[i]["x"].ToString());
mt.y = Convert.ToInt32(ds.Tables[0].Rows[i]["y"].ToString());
}
return(mt);
}
private void m_buttonTest4DBSqlite_Click(object sender, EventArgs e)
{
//1.创建数据库
SQLiteUtil.createDB("testSqlite.db");
//2.创建表
var strCreateTableSql = @"CREATE TABLE tbl_Config
(ModelName TEXT,
NodePath TEXT,
NodeValue TEXT,
UpdateTime DATETIME,
PRIMARY KEY (ModelName,NodePath));";
SQLiteUtil.createDT("tbl_Config", strCreateTableSql);
//3.插入数据
var strUpsertSql = "REPLACE INTO tbl_Config(ModelName,NodePath,NodeValue,UpdateTime)" +
"VALUES(@ModelName,@NodePath,@NodeValue,@UpdateTime)";
Dictionary <string, object> oUpsertParams = new Dictionary <string, object>();
oUpsertParams.Add("ModelName", "ModelName");
oUpsertParams.Add("NodePath", "NodePath");
oUpsertParams.Add("NodeValue", Convert.ToString("NodeValue"));
oUpsertParams.Add("UpdateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
SQLiteUtil.execSQL(strUpsertSql, oUpsertParams);
//4.查询
var strSelectSql = "SELECT ModelName,NodePath,NodeValue,UpdateTime FROM tbl_Config " +
"WHERE ModelName=@ModelName AND NodePath=@NodePath";
Dictionary <string, object> oSelectParams = new Dictionary <string, object>();
oSelectParams.Add("ModelName", "ModelName");
oSelectParams.Add("NodePath", "NodePath");
DataTable dt = SQLiteUtil.execDataTable(strSelectSql, oSelectParams);
if (dt.Rows.Count > 0)
{
Console.WriteLine(dt.Rows[0]["NodeValue"]);
}
//5.batch insert
SQLiteUtil.execQueryTrans((oTrans) =>
{
for (int i = 0; i < 10; i++)
{
var strBatchCreateSql = "REPLACE INTO tbl_Config(ModelName,NodePath,NodeValue,UpdateTime)" +
"VALUES(@ModelName,@NodePath,@NodeValue,@UpdateTime)";
Dictionary <string, object> ups = new Dictionary <string, object>();
ups.Add("ModelName", "ModelName");
ups.Add("NodePath", "ModelName/NodePath" + i);
ups.Add("NodeValue", Convert.ToString(i));
ups.Add("UpdateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
SQLiteUtil.execQueryTransCallBack(oTrans, strBatchCreateSql, ups);
}
});
//6.batch select
var strBatchSelectSql = "SELECT ModelName,NodePath,NodeValue,UpdateTime FROM tbl_Config " +
"WHERE ModelName=@ModelName";
Dictionary <string, object> oBatchSelectParams = new Dictionary <string, object>();
oBatchSelectParams.Add("ModelName", "ModelName");
DataTable dtBatch = SQLiteUtil.execDataTable(strBatchSelectSql, oBatchSelectParams);
if (dtBatch.Rows.Count > 0)
{
foreach (DataRow oRows in dtBatch.Rows)
{
Console.WriteLine(oRows["NodeValue"]);
}
}
}
internal string GetJsonOfSQL(string sql)
{
return(SQLiteUtil.GetTableFromSqlite(conn, sql));
}
public void LoadOfflineCities()
{
OfflineCities = new ObservableCollection <CityInformation>(SQLiteUtil.GetInformation());
}
