public static void LoadLanguageLinkData(ReadOnlyCollection<IWebElement> weListPrimary)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=C:\\Users\\Don\\Music\\Documents\\SeleniumProjects\\SeleniumDemoCSharp\\SeleniumDemoTests\\src\\selenium\\site\\wikipedia\\wikidata\\wikidata.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
Debug.WriteLine(sh.ExecuteScalar("select Title from ResultLanguageLinks;"));
conn.Close();
}
}
Debug.WriteLine("============================================");
foreach (IWebElement we in weListPrimary)
{
Debug.WriteLine("id=" + we.GetAttribute("id"));
Debug.WriteLine("class=" + we.GetAttribute("class"));
Debug.WriteLine("lang=" + we.GetAttribute("lang"));
Debug.WriteLine("dir=" + we.GetAttribute("dir"));
Debug.WriteLine("title=" + we.GetAttribute("title"));
Debug.WriteLine("href=" + we.GetAttribute("href"));
Debug.WriteLine("data-convert-hans=" + we.GetAttribute("data-convert-hans"));
Debug.WriteLine("Text=" + we.Text);
Debug.WriteLine("============================================");
}
}
public void ReinitializeDatabase(string subscriberId)
{
Dictionary<string, string> dbSchema = JsonConvert.DeserializeObject<Dictionary<string, string>>(wsClient.GetFullDBSchema(JsonConvert.SerializeObject(subscriberId)));
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
foreach (KeyValuePair<string, string> entry in dbSchema)
if (!entry.Key.StartsWith("00000"))
{
sh.Execute(entry.Value);
}
sh.Commit();
}
catch (Exception ex)
{
sh.Rollback();
throw ex;
}
conn.Close();
}
}
}
private int SaveOrder(string Production, int OrderQty, string Consignee, string Phone, string sheng, string shi, string xian, string Address, string Description)
{
int vcount = 0;
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.SetPassword(config.DBPwd);
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
var dic = new Dictionary<string, object>();
dic["Production"] = Production;
dic["OrderQty"] = OrderQty;
dic["Consignee"] = Consignee;
dic["Phone"] = Phone;
dic["sheng"] = sheng;
dic["shi"] = shi;
dic["xian"] = xian;
dic["Address"] = Address;
dic["Description"] = Description;
vcount = sh.Insert("T_Order", dic);
conn.Close();
}
}
return vcount;
}
// http://stackoverflow.com/questions/7002806/system-data-datasetextensions-in-mono
public static IEnumerable<DataPoint> LoadData(string dataSource, string tableName)
{
try
{
string sql = String.Format("select * from {0}", tableName) ;
using (SQLiteConnection conn = new SQLiteConnection(dataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable res = sh.Select(sql);
IEnumerable<DataPoint> ms = from row in res.AsEnumerable()
orderby row["responsetime"]
select new DataPoint(Convert.ToDouble(row["count"]), Convert.ToDouble(row["responsetime"]));
;
conn.Close();
return ms;
}
}
}
catch (Exception ex)
{
Console.Error.WriteLine(ex.ToString());
return (IEnumerable<DataPoint>)null;
}
}
private void frmAtleta_Load(object sender, EventArgs e)
{
tbAtletas listanomes = new tbAtletas.getNomes();
try
{
using (SQLiteConnection gatlDBconn = new SQLiteConnection("data source=C:\\Users\\Mario Lima\\Documents\\Visual Studio 2015\\Projects\\GestorAtletismo_v2\\GestorAtletismo_v2\\gestatl.db3"))
{
using (SQLiteCommand comando = new SQLiteCommand())
{
comando.Connection = gatlDBconn;
gatlDBconn.Open();
SQLiteHelper sh = new SQLiteHelper(comando);
DataTable Atletas = sh.Select("SELECT nome_atleta FROM atleta ORDER BY nome_atleta;");
foreach (DataRow linha in Atletas.Rows)
{
foreach (var campo in linha.ItemArray)
{
lstboxAtletas.Items.Add(campo.ToString());
}
}
}
}
}
catch (Exception erro)
{
MessageBox.Show("Erro: " + erro.ToString());
}
}
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
int countCurrent = 0;
string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在
try
{
Int32.Parse((string)value);
countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent);
conn.Close();
if (countCurrent > 0)
{
return "更新";
}
else
{
return "保存";
}
}
catch (Exception)
{
return "保存";
}
}
}
}
public ReturnObj run(string dbfile, List<QueryObj> queries)
{
var ret = new ReturnObj();
try
{
using (SQLiteConnection conn = new SQLiteConnection(dbfile))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
foreach (QueryObj query in queries)
{
if (query != null)
{
switch (query.caseid)
{
case 0: ret.datatable = sh.Select(query.select, query.selectionary); break; //select
case 1: sh.CreateTable(query.newtable); break; // create tables
case 2: ret.datatable = sh.GetTableStatus(); break; // get table status
case 3: sh.Update(query.table, query.dictionary, query.selectionary); tryGetId(ref ret, query.selectionary, 3, null); break; //update item, pass id for record
case 4: sh.Insert(query.table, query.dictionary); tryGetId(ref ret, null, 4, sh.LastInsertRowId()); break; //insert item, get id
case 5: query.dictionary["itemid"] = ret.id; sh.Insert(query.table, query.dictionary); break; //insert record with last item id
case 6: sh.Insert(query.table, query.dictionary); break; //simple insert
case 7: query.dictionary["childid"] = ret.id; sh.Insert(query.table, query.dictionary); break; //insert new parent
case 8: sh.UpdateTableStructure(query.newtable.TableName, query.newtable); break;
}
}
}
sh.Commit();
conn.Close();
}
catch (Exception ex)
{
sh.Rollback();
ret.completed = false;
return ret;
}
}
}
ret.completed = true;
return ret;
}
catch (Exception ex)
{
ret.completed = false;
return ret;
}
}
/// <summary>
/// 判断库是否为加密库
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
private bool CheckLegacyDBIsKeyed(SQLiteHelper keyedDB)
{
try
{
keyedDB.ExecuteNonQuery("PRAGMA user_version;", ConnectionState.KeepOpen);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 打开数据库
/// </summary>
public static void OpenDB(){
conn = new SQLiteConnection(config.DataSource);//连接数据库，config.DataSource数据库连接字符串
Log("dbpath:" + config.DataSource); //打印日志
cmd = new SQLiteCommand();
cmd.Connection = conn;
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
Log("conn_open");
}
sqliteHelper = new SQLiteHelper(cmd);
Log("new sqliteHelper");
}
public string GetOrderList(int pageRows,int page)
{
Dictionary<string, object> pageResult = new Dictionary<string, object>();
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.SetPassword(config.DBPwd);
conn.Open();
//conn.ChangePassword(config.DBPwd);
SQLiteHelper sh = new SQLiteHelper(cmd);
//总记录数
string totalSql = "Select count(1) From T_order";
long total = (long)sh.ExecuteScalar(totalSql);
int offset = (page - 1) * pageRows;
string sql = string.Format(@"SELECT ID,[Production]
,[Consignee]
,[Phone]
,[sheng]
,[shi]
,[xian]
,[Address]
,[Description],
case when status = 1 then
'√'
else
' '
end as status,
BuildTime
FROM [T_Order]
Order by Status asc ,buildTime desc
limit {0} offset {1} ",pageRows,offset);
DataTable dt = sh.Select(sql);
conn.Close();
pageResult.Add("total", total);
pageResult.Add("rows", dt);
}
}
return JSON.Encode(pageResult);
}
public void DeleteQuery(string Table, string Id)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.Execute("Delete from " + Table + " WHERE ID = '" + Id + "';");
conn.Close();
}
}
}
public void ExecuteSQL(string ExecuteScript)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.Execute(ExecuteScript + ";");
conn.Close();
}
}
}
/// <summary>
/// 递归列举出指定目录的所有文件 /// </summary>
public void ListFiles(FileSystemInfo info)
{
{
if (!info.Exists) return;
DirectoryInfo dir = info as DirectoryInfo; //不是目录
if (dir == null) return;
FileSystemInfo[] files = dir.GetFileSystemInfos();
for (int i = 0; i < files.Length; i++)
{
FileInfo file = files[i] as FileInfo; //是文件
if (file != null)
{
if ((file.FullName.Substring(file.FullName.LastIndexOf(".")) == ".db"))
{
Config.DatabaseFile = file.FullName;
this.statusStrip1.Text = Config.DataSource;
try
{
using (SQLiteConnection conn = new SQLiteConnection(Config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable dt = sh.GetTableList();
dataGridView1.DataSource = dt;
conn.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
//else
//{
// ListFiles(files[i]);
//}
}
}
}
protected void btnOK_Click(object sender, EventArgs e)
{
string loginName = txtUser.Text;
string pwd = txtPwd.Text;
int count = 0;
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.SetPassword(config.DBPwd);
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
Dictionary<string,object> para = new Dictionary<string,object>();
para.Add("@LoginName",loginName);
para.Add("@PassWord",pwd);
count = sh.ExecuteScalar<int>("select count(1) from T_User Where LoginName=@LoginName And PassWord=@PassWord;",para);
conn.Close();
}
}
if (count > 0)
{
FormsAuthentication.SetAuthCookie(loginName, false);
if(string.IsNullOrEmpty(Request["ReturnUrl"]))
{
Response.Redirect("~/Admin/Order.aspx");
}
else
{
Response.Redirect(Request["ReturnUrl"]);
}
System.Web.Security.FormsAuthentication.RedirectFromLoginPage(loginName, false);
}
else
{
Response.Write("<script>alert('用户名或密码错误！')</script>");
}
}
public static void createTable(string dataSource, string tableName)
{
using (SQLiteConnection conn = new SQLiteConnection(dataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.DropTable(tableName);
SQLiteTable tb = new SQLiteTable(tableName);
tb.Columns.Add(new SQLiteColumn("id", true)); // auto increment
tb.Columns.Add(new SQLiteColumn("count"));
tb.Columns.Add(new SQLiteColumn("responsetime", ColType.Decimal));
sh.CreateTable(tb);
conn.Close();
}
}
}
/// <summary>
/// 处理遗留版本动作
/// </summary>
protected override void OnDoLegacy()
{
//======处理数据库加密, 转库操作======
var isKeyDB = true;
//1.判断库是否为加密库
using (var db = new SQLiteHelper(SQLConfig.ConnectionString))
{
isKeyDB = CheckLegacyDBIsKeyed(db);
}
//2.如果为不加密的库设置密码
if (!isKeyDB)
{
using (var db = new SQLiteHelper(SQLConfig.NormalConnectionString))
{
var conn = db.DbConnection as SQLiteConnection;
conn.Open();
conn.ChangePassword(SQLConfig.DBConnectionKey);
}
}
}
/// <summary>
/// 验证lib文件是不是一个有效的库文件
/// </summary>
/// <param name="DataSource"></param>
/// <returns></returns>
public static bool Validate(string DataSource)
{
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
try
{
DataTable dt = sh.Select("select * from QuestionInfo");
return true;
}
catch (Exception)
{
return false;
}
}
}
}
// http://stackoverflow.com/questions/7002806/system-data-datasetextensions-in-mono
public static IEnumerable<DataPoint> LoadData(string dataSource, string tableName)
{
try
{
string sql = String.Format("select * from {0}", tableName) ;
using (SQLiteConnection conn = new SQLiteConnection(dataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable res = sh.Select(sql);
IEnumerable<DataPoint> ms = from row in res.AsEnumerable()
orderby row["responsetime"]
select new DataPoint(Convert.ToDouble(row["count"]), Convert.ToDouble(row["responsetime"]));
;
// DataSetExtensions is not available for some reason, loop through the data
/*
*/
List<DataPoint> ms2 = new List<DataPoint>();
foreach (DataRow row in res.Rows) {
ms2.Add(new DataPoint(Convert.ToDouble(row["count"]), Convert.ToDouble(row["responsetime"])));
}
conn.Close();
return ms;
}
}
}
catch (Exception ex)
{
Console.Error.WriteLine(ex.ToString());
return (IEnumerable<DataPoint>)null;
}
}
public static bool InsertPlotData(string dataSource, string tableName, Dictionary<string, object> dic)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(dataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.Insert(tableName, dic);
conn.Close();
return true;
}
}
}
catch (Exception ex)
{
Console.Error.WriteLine(ex.ToString());
return false;
}
}
/// <summary>
/// 此处设置datasource较为方便所以不再调整
/// </summary>
/// <param name="sender">事件触发者</param>
/// <param name="e">jsonArgs</param>
/// <returns></returns>
public bool query(object sender, EventArgs e)
{
DataTable dt;
using (SQLiteConnection conn = new SQLiteConnection(string.Format("data source={0}",DataBase.Replace(@"\", @"/"))))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
try
{
dt = sh.Select(((jsonArges)e).querystr);
((jsonArges)e).dt= dt;
}
catch (Exception ex)
{
dt = new DataTable();
dt.Columns.Add("Error");
dt.Rows.Add(ex.ToString());
((jsonArges)e).dt= dt;
}
}
}
foreach (DataRow i in dt.Rows)
((jsonArges)e).logp.Add(
new geopoint(
i.ItemArray[1].ToString(),
double.Parse(i.ItemArray[2].ToString()),
double.Parse(i.ItemArray[3].ToString())
));
return true;
}
public int DeleteOrder(string orderId)
{
int count = 0;
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.SetPassword(config.DBPwd);
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
string sql = @"Delete From [T_Order] Where ID = @ID";
Dictionary<string, object> para = new Dictionary<string, object>();
para.Add("@ID", orderId);
count = sh.Execute(sql,para);
conn.Close();
}
}
return count;
}
public int SaveInfo(HomeModel home)
{
string sql = "insert into info(name, increment,distance,site,time,fet)values('" + home.Name + "','" + home.Increment + "','" + home.Distance + "','" + home.Site + "','" + home.Time + "','" + home.Fet + "')";
return(SQLiteHelper.ExecuteNonQuery(sql));
}
public QuestionInfo GetAllQuestionInfo(int questionNumber, string DataSource)
{
//Config.DataBaseFile = Path.Combine(System.Windows.Forms.Application.StartupPath, "MyData.db");
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable dt = sh.Select("select * from QuestionInfo where Q_Num=@questionNumber;", new SQLiteParameter[] { new SQLiteParameter("questionNumber", questionNumber) });
foreach (DataRow dr in dt.Rows)
{
int otherOption;
int musterEnter;
int repeat;
int jump;
QuestionInfo questionInfo = new QuestionInfo();
questionInfo.QuestionNumber = dr["Q_Num"].ToString();
questionInfo.QuestionTypeID = Convert.ToInt32(dr["TypeID"]);
questionInfo.QuestionContent = dr["Q_Content"].ToString();
questionInfo.QuestionField = dr["Q_Field"].ToString();
questionInfo.QuestionLable = dr["Q_Lable"].ToString();
questionInfo.OptionsCount = dr["Q_OptionsCount"].ToString();
otherOption = Convert.ToInt32(dr["Q_OtherOption"]);
if (otherOption==0)
{
questionInfo.OtherOption = false;
}
else
{
questionInfo.OtherOption = true;
}
questionInfo.ValueLable = dr["Q_ValueLable"].ToString();
questionInfo.DataTypeID = Convert.ToInt32(dr["DataTypeID"]);
questionInfo.ValueRange = dr["Q_ValueRange"].ToString();
questionInfo.Pattern = dr["Q_Pattern"].ToString();
musterEnter = Convert.ToInt32(dr["Q_MustEnter"]);
if (musterEnter == 0)
{
questionInfo.IsMustEnter = false;
}
else
{
questionInfo.IsMustEnter = true;
}
repeat = Convert.ToInt32(dr["Q_Repeat"]);
if (repeat == 0)
{
questionInfo.IsRepeat = false;
}
else
{
questionInfo.IsRepeat = true;
}
jump = Convert.ToInt32(dr["Q_Jump"]);
if (jump == 0)
{
questionInfo.IsJump = false;
}
else
{
questionInfo.IsJump = true;
}
questionInfo.JumpConditions = dr["Q_JumpConditions"].ToString();
questionInfo.JumpTarget = dr["Q_JumpTarget"].ToString();
questionInfo.DataBaseFile = DataSource.Substring(12);
//if (DataSource == string.Format("data source={0}", Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), "MyData.db")))
//{
int count = 0;
string sqlString = String.Format("select count(*) from QuestionInfo where Q_Field='{0}' COLLATE NOCASE", questionInfo.QuestionField.ToLower());//判断当前字段是否存在,注意字段值一定要加单引号
count = sh.ExecuteScalar<int>(sqlString);
if (count==1)
{
IsQuestionFieldExist.Instance.IsExist = true;
}
else
{
IsQuestionFieldExist.Instance.IsExist = false;
}
//}
return questionInfo;
}
conn.Close();
return null;
}
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(tdps.Model.NSRXXDS model, object trans)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update NSRXXDS set ");
strSql.Append("SSHY=@SSHY,");
strSql.Append("SSYH=@SSYH,");
strSql.Append("QYDJZCL=@QYDJZCL,");
strSql.Append("NSLX=@NSLX,");
strSql.Append("SFTYHSJG=@SFTYHSJG,");
strSql.Append("ZGSWJG=@ZGSWJG,");
strSql.Append("ZGSWJG_MC=@ZGSWJG_MC,");
strSql.Append("NSR_SWJG_DM=@NSR_SWJG_DM,");
strSql.Append("JKBZ=@JKBZ,");
strSql.Append("XY=@XY,");
strSql.Append("LSGX=@LSGX,");
strSql.Append("NSRBM=@NSRBM,");
strSql.Append("NSRZT=@NSRZT,");
strSql.Append("ZGY=@ZGY");
strSql.Append("SSHY_MC=@SSHY_MC,");
strSql.Append("QYDJZCL_MC=@QYDJZCL_MC,");
strSql.Append("LSGX_MC=@LSGX_MC");
strSql.Append(" where NSRSBH=@NSRSBH ");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@NSRSBH", DbType.String, 20),
new SQLiteParameter("@SSHY", DbType.String, 60),
new SQLiteParameter("@SSYH", DbType.String, 50),
new SQLiteParameter("@QYDJZCL", DbType.String, 60),
new SQLiteParameter("@NSLX", DbType.String, 50),
new SQLiteParameter("@SFTYHSJG", DbType.Int32, 4),
new SQLiteParameter("@ZGSWJG", DbType.String, 20),
new SQLiteParameter("@ZGSWJG_MC", DbType.String, 100),
new SQLiteParameter("@NSR_SWJG_DM", DbType.String, 20),
new SQLiteParameter("@JKBZ", DbType.Int32, 4),
new SQLiteParameter("@XY", DbType.String, 50),
new SQLiteParameter("@LSGX", DbType.String, 100),
new SQLiteParameter("@NSRBM", DbType.String, 20),
new SQLiteParameter("@NSRZT", DbType.String, 2),
new SQLiteParameter("@ZGY", DbType.String, 30),
new SQLiteParameter("@SSHY_MC", DbType.String, 50),
new SQLiteParameter("@QYDJZCL_MC", DbType.String, 50),
new SQLiteParameter("@LSGX_MC", DbType.String, 50)
};
parameters[0].Value = model.NSRSBH;
parameters[1].Value = model.SSHY;
parameters[2].Value = model.SSYH;
parameters[3].Value = model.QYDJZCL;
parameters[4].Value = model.NSLX;
parameters[5].Value = model.SFTYHSJG;
parameters[6].Value = model.ZGSWJG;
parameters[7].Value = model.ZGSWJG_MC;
parameters[8].Value = model.NSR_SWJG_DM;
parameters[9].Value = model.JKBZ;
parameters[10].Value = model.XY;
parameters[11].Value = model.LSGX;
parameters[12].Value = model.NSRBM;
parameters[13].Value = model.NSRZT;
parameters[14].Value = model.ZGY;
parameters[15].Value = model.SSHY_MC;
parameters[16].Value = model.QYDJZCL_MC;
parameters[17].Value = model.LSGX_MC;
SQLiteHelper.ExecuteNonQueryByTransaction(strSql.ToString(), (SQLiteTransaction)trans, parameters);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public tdps.Model.SBXX GetModel(int SBXXID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select SBXXID,DLZH,NSRSBH,SZ,SBZLCODE,SBZLMC,SSQQ,SSQZ,SBBWJ,BBZT,TBSJ,SBJSZT,SLSJ,CLSJ,JSSJ,SBQNF,SBQYF,FileName,SBCS,SSQLX,SBSE,SBLSH,DYMM,SheetZT,SBFSSJ,CELLVERSION,DKFPRQQ,DKFPRQZ,SJBS,BBTXCS,CWBBFSJG,CWBB_FileName from SBXX ");
strSql.Append(" where SBXXID=@SBXXID ");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@SBXXID", DbType.Int32, 4)
};
parameters[0].Value = SBXXID;
tdps.Model.SBXX model = new tdps.Model.SBXX();
DataSet ds = SQLiteHelper.ExecuteDataSet(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["SBXXID"].ToString() != "")
{
model.SBXXID = int.Parse(ds.Tables[0].Rows[0]["SBXXID"].ToString());
}
model.DLZH = ds.Tables[0].Rows[0]["DLZH"].ToString();
model.NSRSBH = ds.Tables[0].Rows[0]["NSRSBH"].ToString();
model.SZ = ds.Tables[0].Rows[0]["SZ"].ToString();
model.SBZLCODE = ds.Tables[0].Rows[0]["SBZLCODE"].ToString();
model.SBZLMC = ds.Tables[0].Rows[0]["SBZLMC"].ToString();
model.SSQQ = ds.Tables[0].Rows[0]["SSQQ"].ToString();
model.SSQZ = ds.Tables[0].Rows[0]["SSQZ"].ToString();
model.SBBWJ = ds.Tables[0].Rows[0]["SBBWJ"].ToString();
model.BBZT = ds.Tables[0].Rows[0]["BBZT"].ToString();
model.TBSJ = ds.Tables[0].Rows[0]["TBSJ"].ToString();
model.SBJSZT = ds.Tables[0].Rows[0]["SBJSZT"].ToString();
model.SLSJ = ds.Tables[0].Rows[0]["SLSJ"].ToString();
model.CLSJ = ds.Tables[0].Rows[0]["CLSJ"].ToString();
model.JSSJ = ds.Tables[0].Rows[0]["JSSJ"].ToString();
model.SBQNF = ds.Tables[0].Rows[0]["SBQNF"].ToString();
model.SBQYF = ds.Tables[0].Rows[0]["SBQYF"].ToString();
model.FileName = ds.Tables[0].Rows[0]["FileName"].ToString();
model.SBFSSJ = ds.Tables[0].Rows[0]["SBFSSJ"].ToString();
if (ds.Tables[0].Rows[0]["SBCS"].ToString() != "")
{
model.SBCS = int.Parse(ds.Tables[0].Rows[0]["SBCS"].ToString());
}
if (ds.Tables[0].Rows[0]["SSQLX"].ToString() != "")
{
model.SSQLX = int.Parse(ds.Tables[0].Rows[0]["SSQLX"].ToString());
}
if (ds.Tables[0].Rows[0]["SBSE"].ToString() != "")
{
model.SBSE = decimal.Parse(ds.Tables[0].Rows[0]["SBSE"].ToString());
}
model.SBLSH = ds.Tables[0].Rows[0]["SBLSH"].ToString();
model.DYMM = ds.Tables[0].Rows[0]["DYMM"].ToString();
model.SheetZT = ds.Tables[0].Rows[0]["SheetZT"].ToString();
model.CELLVERSION = ds.Tables[0].Rows[0]["CELLVERSION"].ToString();
model.DKFPRQQ = ds.Tables[0].Rows[0]["DKFPRQQ"].ToString();
model.DKFPRQZ = ds.Tables[0].Rows[0]["DKFPRQZ"].ToString();
model.SJBS = ds.Tables[0].Rows[0]["SJBS"].ToString();
model.BBTXCS = ds.Tables[0].Rows[0]["BBTXCS"].ToString();
model.CWBBFSJG = ds.Tables[0].Rows[0]["CWBBFSJG"].ToString();
model.CWBBFileName = ds.Tables[0].Rows[0]["CWBB_FileName"].ToString();
return(model);
}
else
{
return(null);
}
}
private void GetChangesFromServer(string subscriberId)
{
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable tables = sh.Select("select tbl_Name from sqlite_master where type='table';");
foreach (DataRow table in tables.Rows)
{
try
{
sh.BeginTransaction();
var request = new RestRequest("Sync/{subscriberUUID}/{tableName}", Method.GET);
request.AddUrlSegment("subscriberUUID", subscriberId);
request.AddUrlSegment("tableName", table["tbl_Name"].ToString());
request.AddHeader("Accept", "*/*");
IRestResponse response = wsClient.Execute(request);
List <DataObject> tablesData = JsonConvert.DeserializeObject <List <DataObject> >(response.Content);
foreach (DataObject tableData in tablesData)
{
if (tableData.SyncId > 0)
{
sh.Execute(tableData.TriggerDeleteDrop);
sh.Execute(tableData.TriggerInsertDrop);
sh.Execute(tableData.TriggerUpdateDrop);
XmlDocument xmlRecords = new XmlDocument();
xmlRecords.LoadXml(tableData.Records);
XPathNavigator oRecordsPathNavigator = xmlRecords.CreateNavigator();
XPathNodeIterator oRecordsNodesIterator = oRecordsPathNavigator.Select("/records/r");
foreach (XPathNavigator oCurrentRecord in oRecordsNodesIterator)
{
string action = oCurrentRecord.GetAttribute("a", "");
XmlDocument xmlRecord = new XmlDocument();
xmlRecord.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\"?><columns>" + oCurrentRecord.InnerXml + "</columns>");
XPathNavigator oColumnsPathNavigator = xmlRecord.CreateNavigator();
XPathNodeIterator oColumnsNodesIterator = oColumnsPathNavigator.Select("/columns/c");
int coumnsCount = oColumnsNodesIterator.Count;
SQLiteParameter[] parameters = new SQLiteParameter[coumnsCount];
int idx = 0;
foreach (XPathNavigator oCurrentColumn in oColumnsNodesIterator)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.Value = oCurrentColumn.InnerXml;
parameters[idx] = parameter;
idx++;
}
switch (action)
{
case "1": //insert
sh.Execute(tableData.QueryInsert, parameters);
break;
case "2": //update
sh.Execute(tableData.QueryUpdate, parameters);
break;
case "3": //delete
sh.Execute(tableData.QueryDelete + "?", parameters);
break;
}
}
sh.Execute(tableData.TriggerDelete);
sh.Execute(tableData.TriggerInsert);
sh.Execute(tableData.TriggerUpdate);
request = new RestRequest("CommitSync/{syncId}", Method.GET);
request.AddUrlSegment("syncId", tableData.SyncId.ToString());
request.AddHeader("Accept", "*/*");
IRestResponse responseCommit = wsClient.Execute(request);
}
}
sh.Commit();
}
catch (Exception ex)
{
sh.Rollback();
throw ex;
}
}
conn.Close();
}
}
}
private void SimpleEntryApplication_Closing(object sender, System.ComponentModel.CancelEventArgs e)
{
string dataBaseFile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), "MyData.db");
if (File.Exists(dataBaseFile))
{
string DataSource = string.Format("data source={0}", dataBaseFile);
try
{
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
int count = sh.ExecuteScalar<int>("select count(*) from QuestionInfo");
if (count > 0)
{
var DialogResult = MessageBoxPlus.Show(App.Current.MainWindow, "建库的库文件没有保存，是否返回保存?", "警告", MessageBoxButton.YesNo, MessageBoxImage.Warning);
if (DialogResult == MessageBoxResult.Yes)
{
e.Cancel = true;
}
else
{
sh.ExecuteScalar("delete from QuestionInfo");
sh.ExecuteScalar("update sqlite_sequence SET seq = 0 where name ='QuestionInfo'");
}
}
}
}
}
catch (Exception ex)
{
LogWritter.Log(ex,"程序退出错误");
e.Cancel = false;
}
}
else
{
MessageBoxPlus.Show(App.Current.MainWindow,"丢失文件“Mydata.db”","错误",MessageBoxButton.OK,MessageBoxImage.Error);
e.Cancel = false;
}
}
private DataTable selectFromDB(string query)
{
DataTable dt;
using (SQLiteConnection conn = new SQLiteConnection(CONNECT)) {
using (SQLiteCommand cmd = new SQLiteCommand()) {
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
dt = sh.Select(query);
conn.Close();
}
}
return dt;
}
public static void querySystemDetail()
{
using (SQLiteCommand sqlCommand = new SQLiteCommand())
{
string CommandText = "SELECT * " +
"FROM zSystem_Detail ";
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
SQLiteHelper sqlhelper = new SQLiteHelper(sqlCommand);
DataTable dt = sqlhelper.Select(CommandText);
zSystemDetailModel.SYSTEM_ID = dt.Rows[0]["SYSTEM_ID"].ToString();
zSystemDetailModel.SYSTEM_NAME = dt.Rows[0]["SYSTEM_NAME"].ToString();
zSystemDetailModel.SYSTEM_FULLNAME = dt.Rows[0]["SYSTEM_FULLNAME"].ToString();
zSystemDetailModel.SYSTEM_VERSION = dt.Rows[0]["SYSTEM_VERSION"].ToString();
zSystemDetailModel.SYSTEM_VERSION_NAME = dt.Rows[0]["SYSTEM_VERSION_NAME"].ToString();
zSystemDetailModel.SYSTEM_TIMEZONE = dt.Rows[0]["SYSTEM_TIMEZONE"].ToString();
zSystemDetailModel.SYSTEM_LAST_SYNC_TIME = dt.Rows[0]["SYSTEM_LAST_SYNC_TIME"].ToString();
zSystemDetailModel.SYSTEM_LAST_SYNC_DATE = dt.Rows[0]["SYSTEM_LAST_SYNC_DATE"].ToString();
sqlConnection.Close();
/*
* var myEnumerable = dt.AsEnumerable();
*
* List<AccountModel> myClassList =
* (from item in myEnumerable
* select new AccountModel
* {
* account_id = item.Field<string>("account_id"),
* account_name = item.Field<string>("account_name"),
* account_roles = item.Field<string>("account_roles")
* }).ToList();
*/
}
//string CommandText = "SELECT * " +
// "FROM zSystem_Detail ";
//sqlConnection.Open();
//sqlCommand = sqlConnection.CreateCommand();
//using (SQLiteCommand cmd = new SQLiteCommand(CommandText, DatabaseConnection.sqlConnection))
//{
// using (SQLiteDataReader rdr = cmd.ExecuteReader())
// {
// while (rdr.Read())
// {
// zSystemDetailModel.SYSTEM_ID = (rdr["SYSTEM_ID"].ToString());
// zSystemDetailModel.SYSTEM_NAME = (rdr["SYSTEM_NAME"].ToString());
// zSystemDetailModel.SYSTEM_FULLNAME = (rdr["SYSTEM_FULLNAME"].ToString());
// zSystemDetailModel.SYSTEM_VERSION = (rdr["SYSTEM_VERSION"].ToString());
// zSystemDetailModel.SYSTEM_VERSION_NAME = (rdr["SYSTEM_VERSION_NAME"].ToString());
// zSystemDetailModel.SYSTEM_TIMEZONE = (rdr["SYSTEM_TIMEZONE"].ToString());
// zSystemDetailModel.SYSTEM_LAST_SYNC_TIME = (rdr["SYSTEM_LAST_SYNC_TIME"].ToString());
// zSystemDetailModel.SYSTEM_LAST_SYNC_DATE = (rdr["SYSTEM_LAST_SYNC_DATE"].ToString());
// }
// }
//}
//sqlConnection.Close();
}
public int UpdateRecord(Source item)
{
return(SQLiteHelper.ExecuteNonQuery(CommandType.Text, "update source set name='" + item.Name + "',othername='" + item.OtherName + "',code='" + item.Code + "' where id=" + item.ID));
}
public void EnterExecuteChanged()
{
try
{
if (OrderVersion.DefaultLot > OrderVersion.MaxLot && OrderVersion.MaxLot > 0)
{
MessageBox.Show("下单默认数量不能大于最大数量!", "操作提示", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}
CommParameterSetting.StopLossModel.BuyNum = StopLossModelViewModel.BuyNum;
CommParameterSetting.StopLossModel.CkBuy = StopLossModelViewModel.CkBuy;
CommParameterSetting.StopLossModel.CkSell = StopLossModelViewModel.CkSell;
CommParameterSetting.StopLossModel.SellNum = StopLossModelViewModel.SellNum;
CommParameterSetting.StopLossModel.StopLossDelegation = StopLossModelViewModel.StopLossDelegation;
CommParameterSetting.StopLossModel.StopLossPrice = StopLossModelViewModel.StopLossPrice;
IniHelper.ProfileWriteValue("CheckFullStop", "BuyNum", CommParameterSetting.StopLossModel.BuyNum.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("CheckFullStop", "CkBuy", CommParameterSetting.StopLossModel.CkBuy.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("CheckFullStop", "CkSell", CommParameterSetting.StopLossModel.CkSell.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("CheckFullStop", "SellNum", CommParameterSetting.StopLossModel.SellNum.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("CheckFullStop", "StopLossDelegation", CommParameterSetting.StopLossModel.StopLossDelegation.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("CheckFullStop", "StopLossPrice", CommParameterSetting.StopLossModel.StopLossPrice.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("CheckFullStop", "StopProfitDelegation", CommParameterSetting.StopLossModel.StopProfitDelegation.ToString(), IniHelper.parameterSetting);
//加载自动止盈止损 自动盈亏是用；分割行，分割列；
if (Aslmvm != null)
{
string para = "";
lock (CommParameterSetting.AutoStopLossModel)
{
CommParameterSetting.AutoStopLossModel.Clear();
}
List <string> AgreementList = new List <string>();
foreach (AutoStopLossModelViewModel item in Aslmvm)
{
if (string.IsNullOrEmpty(item.Agreement) || string.IsNullOrEmpty(item.VarietySelectedItem) || string.IsNullOrEmpty(item.Direction))
{
continue;
}
AutoStopLossModel asmvm = new AutoStopLossModel();
asmvm.Variety = item.VarietySelectedItem;
asmvm.Agreement = item.Agreement;
asmvm.Direction = item.Direction == "买" ? "B" : "S";
asmvm.FloatingProfitAndLoss = item.FloatingProfitAndLoss;
asmvm.StopLossPotion = item.StopLossPotion;
asmvm.StopProfitPotion = item.StopProfitPotion;
CommParameterSetting.AutoStopLossModel.Add(asmvm);
if (!AgreementList.Contains(asmvm.Agreement))
{
AgreementList.Add(asmvm.Agreement);
}
para += item.VarietySelectedItem + "," + item.Agreement + "," + asmvm.Direction + "," + item.FloatingProfitAndLoss + "," + item.StopLossPotion + "," + item.StopProfitPotion + ";";
//将新增的添加到现有的集合中
if (!ContractVariety.ContracPostionID.ContainsKey(asmvm.Agreement))
{
ContractVariety.ContracPostionID.Add(asmvm.Agreement, new List <string>());
}
}
List <string> tempKeys = ContractVariety.ContracPostionID.Keys.ToList();
string Agreements = "";
foreach (string item in tempKeys)
{
if (!AgreementList.Contains(item))
{
lock (ContractVariety.ContracPostionID)
{
ContractVariety.ContracPostionID.Remove(item);
}
Agreements += item + ",";
}
}
if (!string.IsNullOrEmpty(Agreements))
{
int count = SQLiteHelper.ExecuteNonQuery(SQLiteHelper.DBPath, CommandType.Text, "delete from AutoStopLoss where ContractID in ('" + Agreements.TrimEnd(',') + "')");
}
IniHelper.ProfileWriteValue("AutoCheckFullStop", "AutoStopLoss", para.TrimEnd(';'), IniHelper.parameterSetting);
}
//}
//下单版设置
CommParameterSetting.OrderVersion.BeforeOrderEnter = OrderVersion.BeforeOrderEnter;
CommParameterSetting.OrderVersion.DefaultLot = OrderVersion.DefaultLot;
CommParameterSetting.OrderVersion.MaxLot = OrderVersion.MaxLot;
IniHelper.ProfileWriteValue("OrderVersion", "BeforeOrderEnter", CommParameterSetting.OrderVersion.BeforeOrderEnter.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("OrderVersion", "DefaultLot", CommParameterSetting.OrderVersion.DefaultLot.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("OrderVersion", "MaxLot", CommParameterSetting.OrderVersion.MaxLot.ToString(), IniHelper.parameterSetting);
if (OrderVersion.DefaultLot > 0)
{
TransactionViewModel.Instance().FTNum = OrderVersion.DefaultLot;
}
if (OrderVersion.MaxLot > 0)
{
TransactionViewModel.Instance().MaxLot = OrderVersion.MaxLot;
}
//快捷键设置
CommParameterSetting.ShortcutKey.LockMenu = ShortcutKey.LockMenu;
CommParameterSetting.ShortcutKey.BuyOpen = ShortcutKey.BuyOpen;
CommParameterSetting.ShortcutKey.Clearance = ShortcutKey.Clearance;
CommParameterSetting.ShortcutKey.ClosingBuy = ShortcutKey.ClosingBuy;
CommParameterSetting.ShortcutKey.ClosingSell = ShortcutKey.ClosingSell;
CommParameterSetting.ShortcutKey.Revoke = ShortcutKey.Revoke;
CommParameterSetting.ShortcutKey.SellOpen = ShortcutKey.SellOpen;
IniHelper.ProfileWriteValue("ShortcutKey", "LockMenu", CommParameterSetting.ShortcutKey.LockMenu.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "BuyOpen", CommParameterSetting.ShortcutKey.BuyOpen.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "Clearance", CommParameterSetting.ShortcutKey.Clearance.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "ClosingBuy", CommParameterSetting.ShortcutKey.ClosingBuy.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "ClosingSell", CommParameterSetting.ShortcutKey.ClosingSell.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "Revoke", CommParameterSetting.ShortcutKey.Revoke.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "SellOpen", CommParameterSetting.ShortcutKey.SellOpen.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "IntBuyOpen", CommParameterSetting.ShortcutKey.IntBuyOpen.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "IntClearance", CommParameterSetting.ShortcutKey.IntClearance.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "IntClosingBuy", CommParameterSetting.ShortcutKey.IntClosingBuy.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "IntClosingSell", CommParameterSetting.ShortcutKey.IntClosingSell.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "IntRevoke", CommParameterSetting.ShortcutKey.IntRevoke.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("ShortcutKey", "IntSellOpen", CommParameterSetting.ShortcutKey.IntSellOpen.ToString(), IniHelper.parameterSetting);
//消息提示
CommParameterSetting.MessageAlert.OrderAlert = MessageAlert.OrderAlert;
CommParameterSetting.MessageAlert.RevokeAlert = MessageAlert.RevokeAlert;
CommParameterSetting.MessageAlert.TradeAlert = MessageAlert.TradeAlert;
IniHelper.ProfileWriteValue("MessageAlert", "OrderAlert", CommParameterSetting.MessageAlert.OrderAlert.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("MessageAlert", "RevokeAlert", CommParameterSetting.MessageAlert.RevokeAlert.ToString(), IniHelper.parameterSetting);
IniHelper.ProfileWriteValue("MessageAlert", "TradeAlert", CommParameterSetting.MessageAlert.TradeAlert.ToString(), IniHelper.parameterSetting);
this.Close();
}
catch (Exception ex)
{
LogHelper.Info(ex.Message);
}
}
public int InsertRecord(Source item)
{
return(SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into source (name,otherName,code)values('" + item.Name + "','" + item.OtherName + "','" + item.Code + "')"));
}
public SQLiteAdapter openToWrite() /* throws android.database.SQLException */
{
sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
sqLiteDatabase = sqLiteHelper.getWritableDatabase();
return(this);
}
public Main()
{
InitializeComponent();
Text = Application.ProductName + " " + Application.ProductVersion;
timerCheckJig.Interval = 1000 * Convert.ToUInt16(ConfigurationManager.AppSettings["refreshTime"]);
#region UI
//测试模式
if (ConfigurationManager.AppSettings["testMode"] == "SQLite")
{
new DBView().Show();
}
//gbLeft
gbOvenLeft.Text = gbOvenLeft.Name = ConfigurationManager.AppSettings["ovenLeftName"];
int[] ovenLeftLocationArr = Array.ConvertAll(ConfigurationManager.AppSettings["ovenLeftLocation"].Split(','), int.Parse);
int[] ovenLeftSizeArr = Array.ConvertAll(ConfigurationManager.AppSettings["ovenLeftSize"].Split(','), int.Parse);
Point ovenLeftLocation = new Point(ovenLeftLocationArr[0], ovenLeftLocationArr[1]);
Size ovenLeftSize = new Size(ovenLeftSizeArr[0], ovenLeftSizeArr[1]);
int ovenLeftRow = Convert.ToUInt16(ConfigurationManager.AppSettings["ovenLeftRow"]);
int ovenLeftColumn = Convert.ToUInt16(ConfigurationManager.AppSettings["ovenLeftColumn"]);
CreatOvenUI(gbOvenLeft, ovenLeftLocation, ovenLeftSize, ovenLeftRow, ovenLeftColumn);
//gbRight
gbOvenRight.Text = gbOvenRight.Name = ConfigurationManager.AppSettings["ovenRightName"];
int[] ovenRightLocationArr = Array.ConvertAll(ConfigurationManager.AppSettings["ovenRightLocation"].Split(','), int.Parse);
int[] ovenRightSizeArr = Array.ConvertAll(ConfigurationManager.AppSettings["ovenRightSize"].Split(','), int.Parse);
Point ovenRightLocation = new Point(ovenRightLocationArr[0], ovenRightLocationArr[1]);
Size ovenRightSize = new Size(ovenRightSizeArr[0], ovenRightSizeArr[1]);
int ovenRightRow = Convert.ToUInt16(ConfigurationManager.AppSettings["ovenRightRow"]);
int ovenRightColumn = Convert.ToUInt16(ConfigurationManager.AppSettings["ovenRightColumn"]);
CreatOvenUI(gbOvenRight, ovenRightLocation, ovenRightSize, ovenRightRow, ovenRightColumn);
if (gbOvenLeft.Name == gbOvenRight.Name)
{
MessageBox.Show("配置文件中的ovenLeftName与ovenRightName值不可相同", "配置错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
Environment.Exit(0);
}
//lblRunningState
lblRunningState.Text = ConfigurationManager.AppSettings["runningStateText"];
lblRunningState.Location = new Point(gbOvenRight.Location.X, gbOvenRight.Location.Y + gbOvenRight.Height);
lblRunningState.Font = new Font(lblRunningState.Font.FontFamily.Name, Convert.ToSingle(ConfigurationManager.AppSettings["runningStateFontSize"]));
//导入没完成的数据
string sql = @"WITH alldata AS(
SELECT*,ROW_NUMBER() OVER (PARTITION BY jig_id,machine_id,location ORDER BY creat_time DESC) IS 1 AS last_operate
FROM operate_history
)
SELECT jig_id,machine_id,location,oven_time,creat_time
FROM alldata
WHERE last_operate is TRUE
AND operate_type='IN'";
DataSet ds = SQLiteHelper.ExecuteDataSet(jigDB, sql, null);
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
Jig jig = new Jig(dr);
GroupBox oven = (GroupBox)Controls[jig.Machine];
Label label = (Label)oven.Controls[jig.Location];
label.Tag = jig;
label.Text = string.Format("{0}\r\n{1}", jig.ID, jig.StartTime.ToString("yy/MM/dd\r\nHH:mm:ss"));
label.BackColor = jig.TimeOut ? Color.Red : Control.DefaultBackColor;
}
#endregion
}
private void sfButtonNextPage_Click(object sender, EventArgs e)
{
calculateSa();
calculateSo();
if (MessageBox.Show("Are you sure From Data You Entered ?", "", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
// sh.Execute("DELETE FROM Site_and_Location");
var dic = new Dictionary <string, object>();
dic["id"] = lastId;
dic["sa"] = TotalSafity;
dic["so"] = TotalSocial;
sh.Insert("safetyandsocialside", dic);
conn.Close();
}
getData();
BAC = projectdata[0];
Console.WriteLine("BAC=" + BAC);
percentOfCompleate = projectdata[4];
Console.WriteLine("percentOfCompleate=" + (percentOfCompleate));
EV = BAC * (percentOfCompleate);
Console.WriteLine("EV=" + EV);
AC = projectdata[1];
Console.WriteLine("AC=" + AC);
CPI = EV / AC;
Console.WriteLine("CPI=" + CPI);
contarctDuration = projectdata[2];
Console.WriteLine("contarctDuration=" + contarctDuration);
PV = projectdata[5];
Console.WriteLine("PV=" + PV);
SPI = (EV / PV);
Console.WriteLine("SPI=" + (EV / PV));
#region Befor
ETC = (BAC - EV) / CPI;
EAC = ETC + AC;
VAC = BAC - EAC;
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
// sh.Execute("DELETE FROM Site_and_Location");
var dic = new Dictionary <string, object>();
dic["id"] = lastId;
dic["contractvalue"] = BAC;
dic["actualcost"] = AC;
dic["contractduration"] = contarctDuration;
dic["actualduration"] = projectdata[3];
dic["percentageofcompleate"] = projectdata[4];
dic["PV"] = PV;
dic["EV"] = EV;
dic["CPI"] = CPI;
dic["SPI"] = SPI;
dic["ETC"] = ETC;
dic["EAC"] = EAC;
dic["VAC"] = VAC;
sh.Insert("report1", dic);
}
#endregion
#region After
ETC = (BAC - EV) / (CPI + SPI + costandtime[0] + costandtime[1] + qualityandrisk[0] + qualityandrisk[1] + safetyandsocialside[0] + safetyandsocialside[1]);
EAC = ETC + AC;
VAC = BAC - EAC;
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
// sh.Execute("DELETE FROM Site_and_Location");
var dic2 = new Dictionary <string, object>();
dic2["id"] = lastId;
dic2["CPI"] = CPI;
dic2["SPI"] = SPI;
dic2["CO"] = costandtime[0];
dic2["TI"] = costandtime[1];
dic2["QU"] = qualityandrisk[0];
dic2["RI"] = qualityandrisk[1];
dic2["SA"] = safetyandsocialside[0];
dic2["SO"] = safetyandsocialside[1];
dic2["ETC"] = ETC;
dic2["EAC"] = EAC;
dic2["VAC"] = VAC;
sh.Insert("report2", dic2);
#endregion
conn.Close();
}
Category.ReportProjectInformation_Form5 frm = new ReportProjectInformation_Form5();
frm.Show();
this.Hide();
}
}
private void button1_Click(object sender, EventArgs e)
{
#region 判断IP的合法性
foreach (Control c in this.Controls)
{
if (c is TextBox)
{
if (string.IsNullOrEmpty((c as TextBox).Text))
{
MessageBox.Show("有设置选项未填写。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
foreach (Control c in this.panel1.Controls)
{
if (c is TextBox)
{
if (string.IsNullOrEmpty((c as TextBox).Text))
{
MessageBox.Show("COS服务器IP未填写。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (Convert.ToInt32(c.Text) >= 256)
{
MessageBox.Show("请输入正确的IP地址。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
#endregion
#region 服务程序数据库操作
string ip = textBox1.Text + "." + textBox2.Text + "." + textBox3.Text + "." + textBox4.Text;
int interval = Convert.ToInt32(textBox5.Text);
int timeout = Convert.ToInt32(textBox6.Text);
using (SQLiteConnection conn = new SQLiteConnection(@"data source=.\CloudAgent.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
SQLiteTable tb = new SQLiteTable("server");
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("ip", ColType.Text));
sh.CreateTable(tb);
SQLiteTable tb2 = new SQLiteTable("setting");
tb2.Columns.Add(new SQLiteColumn("id", true));
tb2.Columns.Add(new SQLiteColumn("key", ColType.Text));
tb2.Columns.Add(new SQLiteColumn("value", ColType.Integer));
sh.CreateTable(tb2);
sh.BeginTransaction();
try
{
var dic = new Dictionary<string, object>();
dic["ip"] = ip;
sh.Insert("server", dic);
var dicData = new Dictionary<string, object>();
var dicData2 = new Dictionary<string, object>();
var dicData3 = new Dictionary<string, object>();
dicData["key"] = "interval";
dicData["value"] = interval;
dicData2["key"] = "timeout";
dicData2["value"] = timeout;
dicData3["key"] = "hostset";
dicData3["value"] = "";
DataTable dt = sh.Select("select * from setting where key='interval';");
if(dt.Rows.Count>0)
sh.Update("setting", dicData, "key", "interval");
else
sh.Insert("setting", dicData);
DataTable dt2 = sh.Select("select * from setting where key='timeout';");
if (dt2.Rows.Count > 0)
sh.Update("setting", dicData2, "key", "timeout");
else
sh.Insert("setting", dicData2);
DataTable dt3 = sh.Select("select * from setting where key='hostset';");
if (dt3.Rows.Count == 0)
sh.Insert("setting", dicData3);
sh.Commit();
}
catch
{
sh.Rollback();
}
conn.Close();
}
}
#endregion
#region 是否打开服务
if (radioButton1.Checked) {
Thread ce = new Thread(delegate ()
{
Process p = new Process();
string path = System.Environment.CurrentDirectory;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = path + @"\CloudAgent.exe";
//MessageBox.Show(p.StartInfo.FileName);
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.EnableRaisingEvents = true;
p.StartInfo.Arguments = "-install";
try
{
// MessageBox.Show(radioButton1.Checked.ToString());
p.Start();
p.WaitForExit();
}
catch (System.ComponentModel.Win32Exception err)
{
MessageBox.Show("系统找不到指定的程序文件。\r{2}");
p.Close();
return;
}
p.Close();
});
ce.IsBackground = false;
ce.Start();
Thread ce2 = new Thread(delegate ()
{
Process p = new Process();
string path = System.Environment.CurrentDirectory;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = path + @"\AutoOnOffLine.exe";
//MessageBox.Show(p.StartInfo.FileName);
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.EnableRaisingEvents = true;
p.StartInfo.Arguments = "-install";
try
{
// MessageBox.Show(radioButton1.Checked.ToString());
p.Start();
p.WaitForExit();
}
catch (System.ComponentModel.Win32Exception err)
{
MessageBox.Show("系统找不到指定的程序文件。\r{2}");
p.Close();
return;
}
p.Close();
});
ce2.IsBackground = false;
ce2.Start();
}
if (radioButton2.Checked)
{
Thread ce = new Thread(delegate ()
{
Process p = new Process();
string path = System.Environment.CurrentDirectory;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = path+@"\CloudAgent.exe";
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.EnableRaisingEvents = true;
p.StartInfo.Arguments = "-remove";
try
{
p.Start();
p.WaitForExit();
}
catch (System.ComponentModel.Win32Exception err)
{
MessageBox.Show("系统找不到指定的程序文件。\r{2}");
p.Close();
return;
}
p.Close();
});
ce.IsBackground = false;
ce.Start();
Thread ce2 = new Thread(delegate ()
{
Process p = new Process();
string path = System.Environment.CurrentDirectory;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = path + @"\AutoOnOffLine.exe";
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.EnableRaisingEvents = true;
p.StartInfo.Arguments = "-remove";
try
{
p.Start();
p.WaitForExit();
}
catch (System.ComponentModel.Win32Exception err)
{
MessageBox.Show("系统找不到指定的程序文件。\r{2}");
p.Close();
return;
}
p.Close();
});
ce2.IsBackground = false;
ce2.Start();
}
#endregion
/* Environment.Exit(1);*/
Application.Exit();
}
private void PublishDataToLocal(int taskindex, int threadindex)
{
try {
string str = string.Empty;
string LocalSQLiteName = "Data\\Collection\\" + ModelTask.TaskName + "\\SpiderResult.db";
DataTable dtData = SQLiteHelper.Query1(LocalSQLiteName, "Select * From Content").Tables[0];
if (!Directory.Exists(ModelTask.SaveDirectory2))
{
Directory.CreateDirectory(ModelTask.SaveDirectory2);
}
if (ModelTask.SaveFileFormat2.ToLower() == ".html")
{
foreach (DataRow dr in dtData.Rows)
{
string fileName = dr["标题"].ToString();
str = dr["内容"].ToString();
try {
fileName = fileName.Replace(".", "");
fileName = fileName.Replace(",", "");
fileName = fileName.Replace("、", "");
fileName = fileName.Replace(" ", "");
fileName = fileName.Replace("*", "_");
fileName = fileName.Replace("?", "_");
fileName = fileName.Replace("/", "_");
fileName = fileName.Replace("\\", "_");
fileName = fileName.Replace(":", "_");
fileName = fileName.Replace("|", "_");
fileName += ".html";
using (StreamWriter sw = new StreamWriter(ModelTask.SaveDirectory2 + "\\" + fileName, false, Encoding.UTF8)) {
sw.Write(str);
sw.Flush();
sw.Close();
}
}
catch {
continue;
}
}
}
else if (ModelTask.SaveFileFormat2.ToLower() == ".txt")
{
foreach (DataRow dr in dtData.Rows)
{
foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel)
{
str += mTaskLabel.LabelName + ":" + dr[mTaskLabel.LabelName] + "\r\n";
}
str += "\r\n\r\n";
}
using (StreamWriter sw = new StreamWriter(ModelTask.SaveDirectory2 + "\\采集结果文本保存.txt", false, Encoding.UTF8)) {
sw.Write(str);
sw.Flush();
sw.Close();
}
}
else if (ModelTask.SaveFileFormat2.ToLower() == ".sql")
{
try {
string strTemplateContent = File.ReadAllText(ModelTask.SaveHtmlTemplate2, Encoding.UTF8);
StringBuilder sbContent = new StringBuilder();
foreach (DataRow dr in dtData.Rows)
{
string sql = strTemplateContent;
foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel)
{
string content = dr[mTaskLabel.LabelName].ToString().Replace("'", "''");
sql = sql.Replace("[" + mTaskLabel.LabelName + "]", content);
}
sbContent.AppendLine(sql);
}
using (StreamWriter sw = new StreamWriter(ModelTask.SaveDirectory2 + "\\sql.sql", false, Encoding.UTF8)) {
sw.Write(sbContent.ToString());
sw.Flush();
sw.Close();
}
}
catch (Exception ex) {
gatherEv.Message = "错误!" + ex.Message;
PublishCompalteDelegate(this, gatherEv);
}
}
}
catch (Exception ex) {
Log4Helper.Write(V5_Utility.Utility.LogLevel.Error, ex);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(ModelTask model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.ID != null)
{
strSql1.Append("ID,");
strSql2.Append("" + model.ID + ",");
}
if (model.Status != null)
{
strSql1.Append("Status,");
strSql2.Append("" + model.Status + ",");
}
if (model.TaskClassID != null)
{
strSql1.Append("TaskClassID,");
strSql2.Append("" + model.TaskClassID + ",");
}
if (model.TaskName != null)
{
strSql1.Append("TaskName,");
strSql2.Append("'" + model.TaskName + "',");
}
if (model.IsSpiderUrl != null)
{
strSql1.Append("IsSpiderUrl,");
strSql2.Append("" + model.IsSpiderUrl + ",");
}
if (model.IsSpiderContent != null)
{
strSql1.Append("IsSpiderContent,");
strSql2.Append("" + model.IsSpiderContent + ",");
}
if (model.IsPublishContent != null)
{
strSql1.Append("IsPublishContent,");
strSql2.Append("" + model.IsPublishContent + ",");
}
if (model.PageEncode != null)
{
strSql1.Append("PageEncode,");
strSql2.Append("'" + model.PageEncode + "',");
}
if (model.CollectionType != null)
{
strSql1.Append("CollectionType,");
strSql2.Append("" + model.CollectionType + ",");
}
if (model.CollectionContent != null)
{
strSql1.Append("CollectionContent,");
strSql2.Append("'" + model.CollectionContent + "',");
}
if (model.LinkUrlMustIncludeStr != null)
{
strSql1.Append("LinkUrlMustIncludeStr,");
strSql2.Append("'" + model.LinkUrlMustIncludeStr + "',");
}
if (model.LinkUrlNoMustIncludeStr != null)
{
strSql1.Append("LinkUrlNoMustIncludeStr,");
strSql2.Append("'" + model.LinkUrlNoMustIncludeStr + "',");
}
if (model.LinkSpliceUrlStr != null)
{
strSql1.Append("LinkSpliceUrlStr,");
strSql2.Append("'" + model.LinkSpliceUrlStr + "',");
}
if (model.LinkUrlCutAreaStart != null)
{
strSql1.Append("LinkUrlCutAreaStart,");
strSql2.Append("'" + model.LinkUrlCutAreaStart + "',");
}
if (model.LinkUrlCutAreaEnd != null)
{
strSql1.Append("LinkUrlCutAreaEnd,");
strSql2.Append("'" + model.LinkUrlCutAreaEnd + "',");
}
if (model.TestViewUrl != null)
{
strSql1.Append("TestViewUrl,");
strSql2.Append("'" + model.TestViewUrl + "',");
}
if (model.IsWebOnlinePublish1 != null)
{
strSql1.Append("IsWebOnlinePublish1,");
strSql2.Append("" + model.IsWebOnlinePublish1 + ",");
}
if (model.IsSaveLocal2 != null)
{
strSql1.Append("IsSaveLocal2,");
strSql2.Append("" + model.IsSaveLocal2 + ",");
}
if (model.SaveFileFormat2 != null)
{
strSql1.Append("SaveFileFormat2,");
strSql2.Append("'" + model.SaveFileFormat2 + "',");
}
if (model.SaveDirectory2 != null)
{
strSql1.Append("SaveDirectory2,");
strSql2.Append("'" + model.SaveDirectory2 + "',");
}
if (model.SaveHtmlTemplate2 != null)
{
strSql1.Append("SaveHtmlTemplate2,");
strSql2.Append("'" + model.SaveHtmlTemplate2 + "',");
}
if (model.SaveIsCreateIndex2 != null)
{
strSql1.Append("SaveIsCreateIndex2,");
strSql2.Append("" + model.SaveIsCreateIndex2 + ",");
}
if (model.IsSaveDataBase3 != null)
{
strSql1.Append("IsSaveDataBase3,");
strSql2.Append("" + model.IsSaveDataBase3 + ",");
}
if (model.SaveDataType3 != null)
{
strSql1.Append("SaveDataType3,");
strSql2.Append("" + model.SaveDataType3 + ",");
}
if (model.SaveDataUrl3 != null)
{
strSql1.Append("SaveDataUrl3,");
strSql2.Append("'" + model.SaveDataUrl3 + "',");
}
if (model.SaveDataSQL3 != null)
{
strSql1.Append("SaveDataSQL3,");
strSql2.Append("'" + model.SaveDataSQL3 + "',");
}
if (model.IsSaveSQL4 != null)
{
strSql1.Append("IsSaveSQL4,");
strSql2.Append("" + model.IsSaveSQL4 + ",");
}
if (model.SaveSQLContent4 != null)
{
strSql1.Append("SaveSQLContent4,");
strSql2.Append("'" + model.SaveSQLContent4 + "',");
}
if (model.SaveSQLDirectory4 != null)
{
strSql1.Append("SaveSQLDirectory4,");
strSql2.Append("'" + model.SaveSQLDirectory4 + "',");
}
if (model.SaveSQLDirectory4 != null)
{
strSql1.Append("Guid,");
strSql2.Append("'" + model.Guid + "',");
}
//
if (model.PluginSpiderUrl != null)
{
strSql1.Append("PluginSpiderUrl,");
strSql2.Append("'" + model.PluginSpiderUrl + "',");
}
if (model.PluginSpiderContent != null)
{
strSql1.Append("PluginSpiderContent,");
strSql2.Append("'" + model.PluginSpiderContent + "',");
}
if (model.PluginSaveContent != null)
{
strSql1.Append("PluginSaveContent,");
strSql2.Append("'" + model.PluginSaveContent + "',");
}
if (model.PluginPublishContent != null)
{
strSql1.Append("PluginPublishContent,");
strSql2.Append("'" + model.PluginPublishContent + "',");
}
//======================================2012 2-6
if (model.CollectionContentThreadCount != null)
{
strSql1.Append("CollectionContentThreadCount,");
strSql2.Append("" + model.CollectionContentThreadCount + ",");
}
if (model.CollectionContentStepTime != null)
{
strSql1.Append("CollectionContentStepTime,");
strSql2.Append("" + model.CollectionContentStepTime + ",");
}
if (model.PublishContentThreadCount != null)
{
strSql1.Append("PublishContentThreadCount,");
strSql2.Append("" + model.PublishContentThreadCount + ",");
}
if (model.PublishContentStepTimeMin != null)
{
strSql1.Append("PublishContentStepTimeMin,");
strSql2.Append("" + model.PublishContentStepTimeMin + ",");
}
if (model.PublishContentStepTimeMax != null)
{
strSql1.Append("PublishContentStepTimeMax,");
strSql2.Append("" + model.PublishContentStepTimeMax + ",");
}
if (model.IsHandGetUrl != null)
{
strSql1.Append("IsHandGetUrl,");
strSql2.Append("" + model.IsHandGetUrl + ",");
}
if (model.HandCollectionUrlRegex != null)
{
strSql1.Append("HandCollectionUrlRegex,");
strSql2.Append("'" + model.HandCollectionUrlRegex + "',");
}
if (model.DemoListUrl != null)
{
strSql1.Append("DemoListUrl,");
strSql2.Append("'" + model.DemoListUrl + "',");
}
//
if (model.IsPlan != null)
{
strSql1.Append("IsPlan,");
strSql2.Append("" + model.IsPlan + ",");
}
if (model.PlanFormat != null)
{
strSql1.Append("PlanFormat,");
strSql2.Append("'" + model.PlanFormat + "',");
}
strSql.Append("insert into S_Task(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
SQLiteHelper.Execute(dbStr, strSql.ToString());
}
private void SendChanges(string subscriberId)
{
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable tables = sh.Select("select tbl_Name from sqlite_master where type='table' and sql like '%RowId%';");
StringBuilder sqlitesync_SyncDataToSend = new StringBuilder();
sqlitesync_SyncDataToSend.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?><SyncData xmlns=\"urn:sync-schema\">");
foreach (DataRow table in tables.Rows)
{
string tableName = table["tbl_Name"].ToString();
if (tableName.ToLower() != "MergeDelete".ToLower())
{
try
{
sqlitesync_SyncDataToSend.Append("<tab n=\"" + tableName + "\">");
#region new records
DataTable newRecords = sh.Select("select * from " + tableName + " where RowId is null;");
sqlitesync_SyncDataToSend.Append("<ins>");
foreach (DataRow record in newRecords.Rows)
{
sqlitesync_SyncDataToSend.Append("<r>");
foreach (DataColumn column in newRecords.Columns)
{
if (column.ColumnName != "MergeUpdate")
{
sqlitesync_SyncDataToSend.Append("<" + column.ColumnName + ">");
sqlitesync_SyncDataToSend.Append("<![CDATA[" + record[column.ColumnName].ToString() + "]]>");
sqlitesync_SyncDataToSend.Append("</" + column.ColumnName + ">");
}
}
sqlitesync_SyncDataToSend.Append("</r>");
}
sqlitesync_SyncDataToSend.Append("</ins>");
#endregion
#region updated records
DataTable updRecords = sh.Select("select * from " + tableName + " where MergeUpdate > 0 and RowId is not null;");
sqlitesync_SyncDataToSend.Append("<upd>");
foreach (DataRow record in updRecords.Rows)
{
sqlitesync_SyncDataToSend.Append("<r>");
foreach (DataColumn column in updRecords.Columns)
{
if (column.ColumnName != "MergeUpdate")
{
sqlitesync_SyncDataToSend.Append("<" + column.ColumnName + ">");
if (record[column.ColumnName].GetType().Name == "Byte[]")
{
sqlitesync_SyncDataToSend.Append("<![CDATA[" + Convert.ToBase64String((byte[])record[column.ColumnName]) + "]]>");
}
else
{
sqlitesync_SyncDataToSend.Append("<![CDATA[" + record[column.ColumnName].ToString() + "]]>");
}
sqlitesync_SyncDataToSend.Append("</" + column.ColumnName + ">");
}
}
sqlitesync_SyncDataToSend.Append("</r>");
}
sqlitesync_SyncDataToSend.Append("</upd>");
#endregion
sqlitesync_SyncDataToSend.Append("</tab>");
}
catch (Exception ex)
{
throw ex;
}
}
}
#region deleted records
DataTable delRecords = sh.Select("select * from MergeDelete;");
sqlitesync_SyncDataToSend.Append("<delete>");
foreach (DataRow record in delRecords.Rows)
{
sqlitesync_SyncDataToSend.Append("<r>");
sqlitesync_SyncDataToSend.Append("<tb>" + record["TableId"].ToString() + "</tb>");
sqlitesync_SyncDataToSend.Append("<id>" + record["RowId"].ToString() + "</id>");
sqlitesync_SyncDataToSend.Append("</r>");
}
sqlitesync_SyncDataToSend.Append("</delete>");
#endregion
sqlitesync_SyncDataToSend.Append("</SyncData>");
#region send changes to server
JsonObject inputObject = new JsonObject();
inputObject.Add("subscriber", subscriberId);
inputObject.Add("content", sqlitesync_SyncDataToSend.ToString());
inputObject.Add("version", "3");
System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
byte[] bytes = encoding.GetBytes(inputObject.ToString());
var request = new RestRequest("Send", Method.POST);
request.AddHeader("Content-Type", "application/json");
request.AddHeader("Accept", "*/*");
request.AddHeader("charset", "utf-8");
request.AddHeader("Content-Length", bytes.Length.ToString());
request.AddParameter("application/json; charset=utf-8", inputObject.ToString(), ParameterType.RequestBody);
request.RequestFormat = DataFormat.Json;
IRestResponse response = wsClient.Execute(request);
#endregion
#region clear update marker
foreach (DataRow table in tables.Rows)
{
string tableName = table["tbl_Name"].ToString().ToLower();
if (tableName != "MergeDelete".ToLower() && tableName != "MergeIdentity".ToLower())
{
string updTriggerSQL = (string)sh.ExecuteScalar("select sql from sqlite_master where type='trigger' and name like 'trMergeUpdate_" + tableName + "'");
sh.Execute("drop trigger trMergeUpdate_" + tableName + ";");
sh.Execute("update " + tableName + " set MergeUpdate=0 where MergeUpdate > 0;");
sh.Execute(updTriggerSQL);
}
if (tableName == "MergeIdentity".ToLower())
{
sh.Execute("update MergeIdentity set MergeUpdate=0 where MergeUpdate > 0;");
}
}
#endregion
#region clear delete marker
sh.Execute("delete from MergeDelete");
#endregion
conn.Close();
}
}
}
/// <summary>
/// 某个市的某个台站的某个节目的某年某月的播出时长
/// 从数据库读取信息；从界面上的复选框(chkBoxLastTuesday)读取最后周二是否停机检修
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="chkBoxLastTuesday"></param>
/// <param name="nostop2"></param>
/// <param name="nostop3"></param>
/// <param name="city"></param>
/// <param name="station"></param>
/// <param name="frequency"></param>
/// <returns></returns>
public string GetFrequencyHours(SQLiteHelper sqliteHelper,
int year, int month,
CheckBox chkBoxLastTuesday, int nostop2, int nostop3,
string city, string station, string frequency)
{
try {
double off_time_hour, off_time_min, on_time_hour, on_time_min;
bool stop3, stop2, stopLast2;
double off_time_2_hour, off_time_2_min, on_time_2_hour, on_time_2_min;
double off_time_3_hour, off_time_3_min, on_time_3_hour, on_time_3_min;
// 从数据库读取信息
// 停止播出
string off_time = sqliteHelper.GetTimeValue(city, station,
frequency, BCstatsHelper.STR_OFF_TIME);
off_time_hour = Convert.ToDouble(off_time.Substring(0, 2));
off_time_min = Convert.ToDouble(off_time.Substring(2, 2));
// 开始播出
string on_time = sqliteHelper.GetTimeValue(city, station,
frequency, BCstatsHelper.STR_ON_TIME);
on_time_hour = Convert.ToDouble(on_time.Substring(0, 2));
on_time_min = Convert.ToDouble(on_time.Substring(2, 2));
// 周三是否停机检修
int stop_3 = sqliteHelper.GetIntValue(city, station,
frequency, BCstatsHelper.STR_STOP_3);
if (stop_3 == 1)
{
stop3 = true;
}
else
{
stop3 = false;
}
// 周三停播
string off_time_3 = sqliteHelper.GetTimeValue(city, station,
frequency, BCstatsHelper.STR_OFF_TIME_3);
off_time_3_hour = Convert.ToDouble(off_time_3.Substring(0, 2));
off_time_3_min = Convert.ToDouble(off_time_3.Substring(2, 2));
// 周三播出
string on_time_3 = sqliteHelper.GetTimeValue(city, station,
frequency, BCstatsHelper.STR_ON_TIME_3);
on_time_3_hour = Convert.ToDouble(on_time_3.Substring(0, 2));
on_time_3_min = Convert.ToDouble(on_time_3.Substring(2, 2));
// 周二是否停机检修
int stop_2 = sqliteHelper.GetIntValue(city, station,
frequency, BCstatsHelper.STR_STOP_2);
if (stop_2 == 1)
{
stop2 = true;
}
else
{
stop2 = false;
}
// 周二停播
string off_time_2 = sqliteHelper.GetTimeValue(city, station,
frequency, BCstatsHelper.STR_OFF_TIME_2);
off_time_2_hour = Convert.ToDouble(off_time_2.Substring(0, 2));
off_time_2_min = Convert.ToDouble(off_time_2.Substring(2, 2));
// 周二播出
string on_time_2 = sqliteHelper.GetTimeValue(city, station,
frequency, BCstatsHelper.STR_ON_TIME_2);
on_time_2_hour = Convert.ToDouble(on_time_2.Substring(0, 2));
on_time_2_min = Convert.ToDouble(on_time_2.Substring(2, 2));
if (chkBoxLastTuesday == null)
{
//从数据库读取信息：最后一个周二是否停机检修
int stop_last_2 = sqliteHelper.GetIntValue(city, station,
frequency, BCstatsHelper.STR_STOP_LAST_2);
if (stop_last_2 == 1)
{
stopLast2 = true;
// 最后一个周二停机检修
}
else
{
stopLast2 = false;
// 最后一个周二不停机检修
}
}
else
{
// 从界面上的复选框读取信息：最后一个周二，是否停机检修？
if (chkBoxLastTuesday.IsChecked == true)
{
stopLast2 = true;
}
else
{
stopLast2 = false;
}
}
Tuple <double, string> t = BCstatsHelper.TotalHoursOfTheMonth(
year, month,
nostop2, nostop3,
off_time_hour, off_time_min,
on_time_hour, on_time_min,
stop3,
off_time_3_hour, off_time_3_min,
on_time_3_hour, on_time_3_min,
stop2,
off_time_2_hour, off_time_2_min,
on_time_2_hour, on_time_2_min,
stopLast2);
// 当月播出总时长
return(t.Item1.ToString());
} catch {
return("0");
}
}
private void ClearDapperTestEntity()
{
SQLiteHelper.ExecuteSQL(@"delete from ""dapper_all_test""", command => command.ExecuteNonQuery());
}
void Start()
{
//创建名为sqlite4unity的数据库
//sql = new SQLiteHelper("data source= data/sqlite4unity.db");
if (true)
{
return;
}
sql = SQLiteHelper.GetIns();
sql.OpenConnection("data source= data/sqlite4unity.db");
//创建名为table1的数据表
sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER PRIMARY KEY", "TEXT", "INTEGER", "TEXT" });
//插入两条数据
sql.InsertValues("table1", new string[] { "null", "'张三'", "'22'", "'*****@*****.**'" });
sql.InsertValues("table1", new string[] { "null", "'李四'", "'25'", "'*****@*****.**'" });
//更新数据，将Name="张三"的记录中的Name改为"Zhang3"
//sql.UpdateValues("table1", new string[]{"Name"}, new string[]{"'Zhang3'"}, "Name", "=", "'张三'");
//插入3条数据
sql.InsertValues("table1", new string[] { "null", "'王五1'", "25", "'*****@*****.**'" });
sql.InsertValues("table1", new string[] { "null", "'王五2'", "26", "'*****@*****.**'" });
sql.InsertValues("table1", new string[] { "null", "'王五3'", "27", "'*****@*****.**'" });
//sql.InsertIntoSpecific("table1",new string[]{"Name","Age","Email"},new string[]{"'王八'","25","'WangBa'"});
//删除Name="王五"且Age=26的记录,DeleteValuesOR方法类似
sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "=", "=" }, new string[] { "'王五2'", "'26'" });
sql.InsertValues("table1", new string[] { "null", "'999'", "21", "'*****@*****.**'" });
string s = "select last_insert_rowid() from table1";
var r = sql.ExecuteQuery(s);
while (r.Read())
{
Debug.Log("nafio--->" + r.GetInt32(0));
//Debug.Log (r.GetOrdinal("ID"));
//Debug.Log(r.GetInt32(r.GetOrdinal("ID")));
}
//读取整张表
SqliteDataReader reader = sql.ReadFullTable("table1");
while (reader.Read())
{
//读取ID
Debug.Log(reader.GetInt32(reader.GetOrdinal("ID")));
//读取Name
Debug.Log(reader.GetString(reader.GetOrdinal("Name")));
//读取Age
Debug.Log(reader.GetInt32(reader.GetOrdinal("Age")));
//读取Email
Debug.Log(reader.GetString(reader.GetOrdinal("Email")));
}
//读取数据表中Age>=25的所有记录的ID和Name
reader = sql.ReadTable("table1", new string[] { "ID", "Name" }, new string[] { "Age" }, new string[] { ">=" }, new string[] { "'25'" });
while (reader.Read())
{
//读取ID
Debug.Log(reader.GetInt32(reader.GetOrdinal("ID")));
//读取Name
Debug.Log(reader.GetString(reader.GetOrdinal("Name")));
}
//自定义SQL,删除数据表中所有Name="王五"的记录
sql.ExecuteQuery("DELETE FROM table1 WHERE NAME='王五'");
//关闭数据库连接
sql.CloseConnection();
}
/// <summary>
/// 验证单个属性输入是否合法
/// </summary>
/// <param name="propertyName"></param>
/// <returns>Single Property IsValid</returns>
private string IsValid(string propertyName)
{
switch (propertyName)
{
case "QuestionContent":
if (string.IsNullOrWhiteSpace(QuestionContent))
{
return "内容不能为空";
}
break;
case "QuestionNumber":
if (string.IsNullOrWhiteSpace(QuestionNumber) || !isValidNumber(QuestionNumber))
{
return "只能输入大于0的数字";
}
break;
case "QuestionTypeID":
if (QuestionTypeID == -1)
{
return "请选择";
}
break;
case "QuestionField":
{
if (!string.IsNullOrEmpty(QuestionField))
{
if (Regex.IsMatch(QuestionField.Substring(0, 1), @"^[\u4e00-\u9fa5a-zA-Z]") && !QuestionField.EndsWith(".") && !QuestionField.EndsWith("_"))
{
char[] variableName = QuestionField.ToCharArray();
if (!variableName.Contains('!') && !variableName.Contains('?') && !variableName.Contains('*'))
{
string[] reservedKeywords = new string[] { "ALL", "NE", "EQ", "TO", "LE", "LT", "BY", "OR", "GT", "AND", "NOT", "GE", "WITH" };
for (int i = 0; i < reservedKeywords.Length; i++)
{
if (string.Compare(QuestionField, reservedKeywords[i], true) != 0)
{
int length = System.Text.Encoding.Default.GetByteCount(QuestionField);
if (length > 65)
{
return "长度不能超过64字节";
}
else
{
//string DataBaseFile = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), "MyData.db");
//string DataSource = string.Format("data source={0}", DataBaseFile);
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
int count = 0;
string sqlString = String.Format("select count(*) from QuestionInfo where Q_Field='{0}' COLLATE NOCASE", QuestionField);//判断当前字段是否存在,注意字段值一定要加单引号
count = sh.ExecuteScalar<int>(sqlString);
conn.Close();
if (count > 0)
{
if (count == 1)
{
if (!IsQuestionFieldExist.Instance.IsExist)
{
return "字段已存在";
}
}
else
{
return "字段已存在";
}
}
}
}
}
}
else
{
return "不能使用以下保留关键字（不区分大小写）：ALL, NE, EQ, TO, LE, LT, BY, OR, GT, AND, NOT, GE, WITH";
}
}
}
else
{
return "不能包含特殊字符（!?*）";
}
}
else
{
return "只能以字母或汉字开头，不能以“.”或“_”结尾";
}
}
}
break;
case "QuestionLable":
{
if (QuestionTypeID == 1)
{
if (!string.IsNullOrEmpty(QuestionLable))
{
string[] lables = Regex.Split(QuestionLable, "\\s*,\\s*");
if (lables.Length.ToString() == OptionsCount)
{
foreach (var lable in lables)
{
if (string.IsNullOrWhiteSpace(lable))
{
return "标签个数必须与选项个数相等";
}
}
}
else
{
return "标签个数必须与选项个数相等";
}
}
}
}
break;
case "OptionsCount":
if ((string.IsNullOrWhiteSpace(OptionsCount) || !isValidNumber(OptionsCount)) && (QuestionTypeID == 0 || QuestionTypeID == 1))
{
return "只能输入大于0的数字";
}
break;
case "ValueLable":
{
if (!string.IsNullOrEmpty(ValueLable) && QuestionTypeID == 0)
{
string[] lables = Regex.Split(ValueLable, "\\s*,\\s*");
if (lables.Length.ToString() == OptionsCount)
{
foreach (var lable in lables)
{
if (string.IsNullOrWhiteSpace(lable))
{
return "值标签个数必须与选项个数相等";
}
}
}
else
{
return "值标签个数必须与选项个数相等";
}
}
}
break;
case "DataTypeID":
if ((DataTypeID == -1) && QuestionTypeID == 3)
{
return "请选择";
}
break;
case "JumpTarget":
if (((string.IsNullOrWhiteSpace(JumpTarget) || !isValidNumber(JumpTarget))) && (IsJump == true))
{
return "只能输入大于0的数字";
}
break;
case "JumpConditions":
{
if (QuestionTypeID != 3)
{
if (QuestionTypeID != 2)
{
string jumpConditionsPattern = @"^\d+(?:-\d+)?(?:,\d+(?:-\d+)?)*$"; //@"^\d+(\d+)?$|^\d+(\,\d+)+(\d+)?$";
if (!(Regex.IsMatch(JumpConditions, jumpConditionsPattern)) && (IsJump == true))
{
return "只能输入以英文逗号或横杆分隔的数字";
}
}
else
{
if ((JumpConditions == "对" || JumpConditions == "错"))
{
}
else if (IsJump == true)
{
return "只能输入“对”或“错”";
}
}
}
}
break;
case "ValueRange":
{
if (!string.IsNullOrEmpty(valueRange) && QuestionTypeID == 3 && DataTypeID != 1)
{
if (DataTypeID == 0)
{
string valueRangePattern = @"^-?\d+(\.\d+)?(?:--?\d+(\.\d+)?)?(?:,-?\d+(\.\d+)?(?:--?\d+(\.\d+)?)?)*$";
if (!Regex.IsMatch(ValueRange, valueRangePattern) && !string.IsNullOrEmpty(ValueRange))
{
return "逗号匹配单个值，横杠匹配区间，例如：1,2,3-10";
}
}
if (DataTypeID == 2)
{
string datePattern = @"^(?:(?!0000)[0-9]{4}/(?:(?:0[1-9]|1[0-2])/(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])/(?:29|30)|(?:0[13578]|1[02])/31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)/02/29)$";
string errorMessage = "按以下格式输入：2000/02/29-2001/02/28";
Dictionary<char, int> dic = new Dictionary<char, int>();
char[] charData = ValueRange.ToCharArray();
foreach (char c in charData)
{
if (!dic.ContainsKey(c))
{
dic.Add(c, 1);
}
else
{
dic[c] = dic[c] + 1;
}
}
if (!string.IsNullOrEmpty(ValueRange))
{
if (dic.ContainsKey('-'))
{
if (dic['-'] == 1)
{
int count = 0;
string[] dates = valueRange.Split('-');
foreach (var item in dates)
{
if (item != "")
{
if (Regex.IsMatch(item, datePattern))
{
}
else
{
return errorMessage;
}
}
else
{
count++;
}
}
if (count == 2)
{
return errorMessage;
}
else
{
}
}
else
{
return errorMessage;
}
}
else
{
return errorMessage;
}
}
}
}
}
break;
case "Pattern":
{
if ((Pattern != null) && (Pattern.Trim().Length > 0))
{
try
{
Regex.Match("", Pattern);
}
catch (ArgumentException)
{
// BAD PATTERN: Syntax error
return "无效的正则表达式";
}
}
}
break;
default:
break;
}
return null;
}
void Update()
{
// if (Input.GetKeyUp (KeyCode.B)) {
// sql.OpenConnection("data source= data/sqlite4unity.db");
// sql.BeTableExist ("table1");
// sql.CloseConnection();
// }
if (Input.GetKeyUp(KeyCode.S))
{
Debug.Log("Start");
sql = SQLiteHelper.GetIns();
sql.OpenConnection("data source= data/test.db");
}
if (Input.GetKeyUp(KeyCode.T))
{
//创建名为table1的数据表
sql.CreateTable("table1", new string[] { "ID", "Name", "Email" }, new string[] { "INTEGER PRIMARY KEY", "TEXT", "TEXT" });
}
if (Input.GetKeyUp(KeyCode.N))
{
Debug.Log("End");
sql.CloseConnection();
}
//测试新增一条数据
if (Input.GetKeyUp(KeyCode.C))
{
Debug.Log("Create");
string s = "name3333333";
GEditorDataMgr.CreateKVSqlData("table1", new string[] { "null", s, "*****@*****.**" });
}
//测试查询
if (Input.GetKeyUp(KeyCode.Q))
{
Debug.Log("Query");
//GEditorDataMgr.QueryKVSqlData("table1",new string[]{"ID","Name","Email"},1);
}
//测试修改
if (Input.GetKeyUp(KeyCode.M))
{
Debug.Log("Motify");
GEditorDataMgr.ModifyKVSqlData("table1", new string[] { "Name" }, new string[] { "'f**k'" }, 1);
}
//测试删除
if (Input.GetKeyUp(KeyCode.D))
{
Debug.Log("Del");
GEditorDataMgr.DelKVSqlData("table1", 1);
}
//测试copy
// if(Input.GetKeyUp(KeyCode.P))
// {
// Debug.Log ("Copy");
// string s = "INSERT INTO table1 SELECT * FROM table1";
// SQLiteHelper.GetIns ().ExecuteQuery (s);
// }
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(tdps.Model.SBXX model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SBXX(");
strSql.Append("DLZH,NSRSBH,SZ,SBZLCODE,SBZLMC,SSQQ,SSQZ,SBBWJ,BBZT,TBSJ,SBJSZT,SLSJ,CLSJ,JSSJ,SBQNF,SBQYF,FileName,SBCS,SSQLX,SBSE,SheetZT,BBTXCS,CWBBFSJG,CWBB_FileName)");
strSql.Append(" values (");
strSql.Append("@DLZH,@NSRSBH,@SZ,@SBZLCODE,@SBZLMC,@SSQQ,@SSQZ,@SBBWJ,@BBZT,@TBSJ,@SBJSZT,@SLSJ,@CLSJ,@JSSJ,@SBQNF,@SBQYF,@FileName,@SBCS,@SSQLX,@SBSE,@SheetZT,@BBTXCS,@CWBBFSJG,@CWBB_FileName)");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@DLZH", DbType.String, 20),
new SQLiteParameter("@NSRSBH", DbType.String, 20),
new SQLiteParameter("@SZ", DbType.String, 20),
new SQLiteParameter("@SBZLCODE", DbType.String, 10),
new SQLiteParameter("@SBZLMC", DbType.String, 80),
new SQLiteParameter("@SSQQ", DbType.String, 20),
new SQLiteParameter("@SSQZ", DbType.String, 20),
new SQLiteParameter("@SBBWJ", DbType.String),
new SQLiteParameter("@BBZT", DbType.String, 4),
new SQLiteParameter("@TBSJ", DbType.String, 20),
new SQLiteParameter("@SBJSZT", DbType.String, 4),
new SQLiteParameter("@SLSJ", DbType.String, 20),
new SQLiteParameter("@CLSJ", DbType.String, 20),
new SQLiteParameter("@JSSJ", DbType.String, 20),
new SQLiteParameter("@SBQNF", DbType.String, 4),
new SQLiteParameter("@SBQYF", DbType.String, 2),
new SQLiteParameter("@FileName", DbType.String, 200),
new SQLiteParameter("@SBCS", DbType.Int32, 4),
new SQLiteParameter("@SSQLX", DbType.Int32, 4),
new SQLiteParameter("@SBSE", DbType.Decimal, 9),
new SQLiteParameter("@SheetZT", DbType.String, 50),
new SQLiteParameter("@BBTXCS", DbType.String, 200),
new SQLiteParameter("@CWBBFSJG", DbType.String, 1),
new SQLiteParameter("@CWBB_FileName", DbType.String, 200)
};
parameters[0].Value = model.DLZH;
parameters[1].Value = model.NSRSBH;
parameters[2].Value = model.SZ;
parameters[3].Value = model.SBZLCODE;
parameters[4].Value = model.SBZLMC;
parameters[5].Value = model.SSQQ;
parameters[6].Value = model.SSQZ;
parameters[7].Value = model.SBBWJ;
parameters[8].Value = model.BBZT;
parameters[9].Value = model.TBSJ;
parameters[10].Value = model.SBJSZT;
parameters[11].Value = model.SLSJ;
parameters[12].Value = model.CLSJ;
parameters[13].Value = model.JSSJ;
parameters[14].Value = model.SBQNF;
parameters[15].Value = model.SBQYF;
parameters[16].Value = model.FileName;
parameters[17].Value = model.SBCS;
parameters[18].Value = model.SSQLX;
parameters[19].Value = model.SBSE;
parameters[20].Value = model.SheetZT;
parameters[21].Value = model.BBTXCS;
parameters[22].Value = model.CWBBFSJG;
parameters[23].Value = model.CWBBFileName;
SQLiteHelper.ExecuteNonQuery(strSql.ToString(), parameters);
}
internal List <ImageLogoInfo> FindAll(bool haslogo, bool hasnologo, bool hasconfusedimages, bool hasError)
{
List <ImageLogoInfo> items = new List <ImageLogoInfo>();
if (!haslogo && !hasnologo & !hasError)
{
return(items);
}
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
_OpenDBConn();
SQLiteHelper sh = new SQLiteHelper(cmd);
var sql = "select * from " + tb.TableName + " where ";
if (hasError)
{
sql += " Error is not null";
}
else
{
sql += " Error is null";
}
if (!haslogo || !hasnologo)
{
if (hasError && haslogo)
{
sql += " or HasLogo=1 ";
}
else if (!hasError && hasnologo)
{
sql += " and HasLogo=0 ";
}
if (hasError && hasnologo)
{
sql += " or HasLogo=0 ";
}
else if (!hasError && hasnologo)
{
sql += " and HasLogo=0 ";
}
}
else if (hasError && haslogo && hasnologo)
{
sql += " or HasLogo=0 or haslogo=1 ";
}
else if (haslogo && hasnologo)
{
sql += " and (HasLogo=0 or haslogo=1 )";
}
var dt = sh.Select(sql);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
var info = new ImageLogoInfo
{
Confidence = int.Parse(row["Confidence"] + ""),
ImagePath = row["ImagePath"] + "",
ProcessingTime = long.Parse(row["ProcessingTime"] + ""),
HasLogo = row["HasLogo"].ToString() == "1" ? true : false
};
if (info.ConfusedImage && !hasconfusedimages)
{
continue;
}
if (!string.IsNullOrWhiteSpace(row["Error"] + ""))
{
info.Error = new Exception(row["Error"] + "");
}
items.Add(info);
}
}
return(items);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(tdps.Model.SBXX model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update SBXX set ");
strSql.Append("DLZH=@DLZH,");
strSql.Append("NSRSBH=@NSRSBH,");
strSql.Append("SZ=@SZ,");
strSql.Append("SBZLCODE=@SBZLCODE,");
strSql.Append("SBZLMC=@SBZLMC,");
strSql.Append("SSQQ=@SSQQ,");
strSql.Append("SSQZ=@SSQZ,");
strSql.Append("SBBWJ=@SBBWJ,");
strSql.Append("BBZT=@BBZT,");
strSql.Append("TBSJ=@TBSJ,");
strSql.Append("SBJSZT=@SBJSZT,");
strSql.Append("SLSJ=@SLSJ,");
strSql.Append("CLSJ=@CLSJ,");
strSql.Append("JSSJ=@JSSJ,");
strSql.Append("SBQNF=@SBQNF,");
strSql.Append("SBQYF=@SBQYF,");
strSql.Append("FileName=@FileName,");
strSql.Append("SBCS=@SBCS,");
strSql.Append("SSQLX=@SSQLX,");
strSql.Append("SBSE=@SBSE,");
strSql.Append("DYMM=@DYMM,");
strSql.Append("SBLSH=@SBLSH,");
strSql.Append("SBFSSJ=@SBFSSJ,");
strSql.Append("CELLVERSION=@CELLVERSION,");
strSql.Append("DKFPRQQ=@DKFPRQQ,");
strSql.Append("DKFPRQZ=@DKFPRQZ,");
strSql.Append("SJBS=@SJBS,");
strSql.Append("BBTXCS = @BBTXCS,");
strSql.Append("CWBBFSJG = @CWBBFSJG,");
strSql.Append("CWBB_FileName = @CWBB_FileName");
strSql.Append(" where SBXXID=@SBXXID ");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@SBXXID", DbType.Int32, 4),
new SQLiteParameter("@DLZH", DbType.String, 20),
new SQLiteParameter("@NSRSBH", DbType.String, 20),
new SQLiteParameter("@SZ", DbType.String, 20),
new SQLiteParameter("@SBZLCODE", DbType.String, 10),
new SQLiteParameter("@SBZLMC", DbType.String, 80),
new SQLiteParameter("@SSQQ", DbType.String, 20),
new SQLiteParameter("@SSQZ", DbType.String, 20),
new SQLiteParameter("@SBBWJ", DbType.String),
new SQLiteParameter("@BBZT", DbType.String, 4),
new SQLiteParameter("@TBSJ", DbType.String, 20),
new SQLiteParameter("@SBJSZT", DbType.String, 4),
new SQLiteParameter("@SLSJ", DbType.String, 20),
new SQLiteParameter("@CLSJ", DbType.String, 20),
new SQLiteParameter("@JSSJ", DbType.String, 20),
new SQLiteParameter("@SBQNF", DbType.String, 4),
new SQLiteParameter("@SBQYF", DbType.String, 2),
new SQLiteParameter("@FileName", DbType.String, 200),
new SQLiteParameter("@SBCS", DbType.Int32, 4),
new SQLiteParameter("@SSQLX", DbType.Int32, 4),
new SQLiteParameter("@SBSE", DbType.Decimal, 9),
new SQLiteParameter("@SheetZT", DbType.String, 50),
new SQLiteParameter("@SBLSH", DbType.String, 100),
new SQLiteParameter("@DYMM", DbType.String, 50),
new SQLiteParameter("@SBFSSJ", DbType.String, 20),
new SQLiteParameter("@CELLVERSION", DbType.String, 50),
new SQLiteParameter("@DKFPRQQ", DbType.String, 50),
new SQLiteParameter("@DKFPRQZ", DbType.String, 50),
new SQLiteParameter("@SJBS", DbType.String, 16),
new SQLiteParameter("@BBTXCS", DbType.String, 200),
new SQLiteParameter("@CWBBFSJG", DbType.String, 1),
new SQLiteParameter("@CWBB_FileName", DbType.String, 200)
};
parameters[0].Value = model.SBXXID;
parameters[1].Value = model.DLZH;
parameters[2].Value = model.NSRSBH;
parameters[3].Value = model.SZ;
parameters[4].Value = model.SBZLCODE;
parameters[5].Value = model.SBZLMC;
parameters[6].Value = model.SSQQ;
parameters[7].Value = model.SSQZ;
parameters[8].Value = model.SBBWJ;
parameters[9].Value = model.BBZT;
parameters[10].Value = model.TBSJ;
parameters[11].Value = model.SBJSZT;
parameters[12].Value = model.SLSJ;
parameters[13].Value = model.CLSJ;
parameters[14].Value = model.JSSJ;
parameters[15].Value = model.SBQNF;
parameters[16].Value = model.SBQYF;
parameters[17].Value = model.FileName;
parameters[18].Value = model.SBCS;
parameters[19].Value = model.SSQLX;
parameters[20].Value = model.SBSE;
parameters[21].Value = model.SheetZT;
parameters[22].Value = model.SBLSH;
parameters[23].Value = model.DYMM;
parameters[24].Value = model.SBFSSJ;
parameters[25].Value = model.CELLVERSION;
parameters[26].Value = model.DKFPRQQ;
parameters[27].Value = model.DKFPRQZ;
parameters[28].Value = model.SJBS;
parameters[29].Value = model.BBTXCS;
parameters[30].Value = model.CWBBFSJG;
parameters[31].Value = model.CWBBFileName;
SQLiteHelper.ExecuteNonQuery(strSql.ToString(), parameters);
}
private void PublishDataToSQL(int taskindex, int threadindex)
{
string LocalSQLiteName = "Data\\Collection\\" + ModelTask.TaskName + "\\SpiderResult.db";
DataTable dtData = SQLiteHelper.Query1(LocalSQLiteName, "Select * From Content").Tables[0];
var listDiyUrl = DALDiyWebUrlHelper.GetList(" And SelfId=" + ModelTask.ID, "", 0);
HttpHelper4 http = new HttpHelper4();
int taskId = ModelTask.ID;
foreach (DataRow dr in dtData.Rows)
{
int resultId = int.Parse(dr["Id"].ToString());
foreach (var m in listDiyUrl)
{
try {
string getUrl = m.Url;
string postParams = m.UrlParams;
StringBuilder sbContent = new StringBuilder();
foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel)
{
string pageEncodeContent = dr[mTaskLabel.LabelName].ToString().Replace("'", "''");
//可能需要编码实际测试才知道
getUrl = getUrl.Replace("[" + mTaskLabel.LabelName + "]", pageEncodeContent);
postParams = postParams.Replace("[" + mTaskLabel.LabelName + "]", pageEncodeContent);
sbContent.Append(pageEncodeContent);
}
string md5key = StringHelper.Instance.MD5(taskId.ToString() + resultId.ToString() + sbContent.ToString(), 32).ToLower();
//判断该条记录这个weburl是否发过
if (!DALDataPublishLogHelper.ChkRecord(
ModelTask.ID, resultId, md5key))
{
//记录日志
DALDataPublishLogHelper.Insert(new ModelDataPublishLog()
{
TaskId = taskId,
ResultId = resultId,
DesKey = md5key,
CreateTime = DateTime.Now.ToString()
});
}
else
{
continue;
}
//开始发布网站
var result = http.GetHtml(new HttpItem()
{
URL = getUrl,
Postdata = postParams,
ContentType = "application/x-www-form-urlencoded"
});
var html = result.Html;
}
catch (Exception ex) {
continue;
}
}
}
if (PublishCompalteDelegate != null)
{
gatherEv.Message = "发布到自定义Web网站完成!";
PublishCompalteDelegate(this, gatherEv);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public ModelTask GetModel(int ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" * ");
strSql.Append(" from S_Task ");
strSql.Append(" where ID=" + ID + " ");
ModelTask model = new ModelTask();
DataSet ds = SQLiteHelper.Query1(dbStr, strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
{
model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
}
if (ds.Tables[0].Rows[0]["TaskClassID"] != null && ds.Tables[0].Rows[0]["TaskClassID"].ToString() != "")
{
model.TaskClassID = int.Parse(ds.Tables[0].Rows[0]["TaskClassID"].ToString());
}
if (ds.Tables[0].Rows[0]["TaskName"] != null && ds.Tables[0].Rows[0]["TaskName"].ToString() != "")
{
model.TaskName = ds.Tables[0].Rows[0]["TaskName"].ToString();
}
if (ds.Tables[0].Rows[0]["IsSpiderUrl"] != null && ds.Tables[0].Rows[0]["IsSpiderUrl"].ToString() != "")
{
model.IsSpiderUrl = int.Parse(ds.Tables[0].Rows[0]["IsSpiderUrl"].ToString());
}
if (ds.Tables[0].Rows[0]["IsSpiderContent"] != null && ds.Tables[0].Rows[0]["IsSpiderContent"].ToString() != "")
{
model.IsSpiderContent = int.Parse(ds.Tables[0].Rows[0]["IsSpiderContent"].ToString());
}
if (ds.Tables[0].Rows[0]["IsPublishContent"] != null && ds.Tables[0].Rows[0]["IsPublishContent"].ToString() != "")
{
model.IsPublishContent = int.Parse(ds.Tables[0].Rows[0]["IsPublishContent"].ToString());
}
if (ds.Tables[0].Rows[0]["PageEncode"] != null && ds.Tables[0].Rows[0]["PageEncode"].ToString() != "")
{
model.PageEncode = ds.Tables[0].Rows[0]["PageEncode"].ToString();
}
if (ds.Tables[0].Rows[0]["CollectionType"] != null && ds.Tables[0].Rows[0]["CollectionType"].ToString() != "")
{
model.CollectionType = int.Parse(ds.Tables[0].Rows[0]["CollectionType"].ToString());
}
if (ds.Tables[0].Rows[0]["CollectionContent"] != null && ds.Tables[0].Rows[0]["CollectionContent"].ToString() != "")
{
model.CollectionContent = ds.Tables[0].Rows[0]["CollectionContent"].ToString();
}
if (ds.Tables[0].Rows[0]["LinkUrlMustIncludeStr"] != null && ds.Tables[0].Rows[0]["LinkUrlMustIncludeStr"].ToString() != "")
{
model.LinkUrlMustIncludeStr = ds.Tables[0].Rows[0]["LinkUrlMustIncludeStr"].ToString();
}
if (ds.Tables[0].Rows[0]["LinkUrlNoMustIncludeStr"] != null && ds.Tables[0].Rows[0]["LinkUrlNoMustIncludeStr"].ToString() != "")
{
model.LinkUrlNoMustIncludeStr = ds.Tables[0].Rows[0]["LinkUrlNoMustIncludeStr"].ToString();
}
if (ds.Tables[0].Rows[0]["LinkSpliceUrlStr"] != null && ds.Tables[0].Rows[0]["LinkSpliceUrlStr"].ToString() != "")
{
model.LinkSpliceUrlStr = ds.Tables[0].Rows[0]["LinkSpliceUrlStr"].ToString();
}
if (ds.Tables[0].Rows[0]["LinkUrlCutAreaStart"] != null && ds.Tables[0].Rows[0]["LinkUrlCutAreaStart"].ToString() != "")
{
model.LinkUrlCutAreaStart = ds.Tables[0].Rows[0]["LinkUrlCutAreaStart"].ToString();
}
if (ds.Tables[0].Rows[0]["LinkUrlCutAreaEnd"] != null && ds.Tables[0].Rows[0]["LinkUrlCutAreaEnd"].ToString() != "")
{
model.LinkUrlCutAreaEnd = ds.Tables[0].Rows[0]["LinkUrlCutAreaEnd"].ToString();
}
if (ds.Tables[0].Rows[0]["TestViewUrl"] != null && ds.Tables[0].Rows[0]["TestViewUrl"].ToString() != "")
{
model.TestViewUrl = ds.Tables[0].Rows[0]["TestViewUrl"].ToString();
}
if (ds.Tables[0].Rows[0]["IsWebOnlinePublish1"] != null && ds.Tables[0].Rows[0]["IsWebOnlinePublish1"].ToString() != "")
{
model.IsWebOnlinePublish1 = int.Parse(ds.Tables[0].Rows[0]["IsWebOnlinePublish1"].ToString());
}
if (ds.Tables[0].Rows[0]["IsSaveLocal2"] != null && ds.Tables[0].Rows[0]["IsSaveLocal2"].ToString() != "")
{
model.IsSaveLocal2 = int.Parse(ds.Tables[0].Rows[0]["IsSaveLocal2"].ToString());
}
if (ds.Tables[0].Rows[0]["SaveFileFormat2"] != null && ds.Tables[0].Rows[0]["SaveFileFormat2"].ToString() != "")
{
model.SaveFileFormat2 = ds.Tables[0].Rows[0]["SaveFileFormat2"].ToString();
}
if (ds.Tables[0].Rows[0]["SaveDirectory2"] != null && ds.Tables[0].Rows[0]["SaveDirectory2"].ToString() != "")
{
model.SaveDirectory2 = ds.Tables[0].Rows[0]["SaveDirectory2"].ToString();
}
if (ds.Tables[0].Rows[0]["SaveHtmlTemplate2"] != null && ds.Tables[0].Rows[0]["SaveHtmlTemplate2"].ToString() != "")
{
model.SaveHtmlTemplate2 = ds.Tables[0].Rows[0]["SaveHtmlTemplate2"].ToString();
}
if (ds.Tables[0].Rows[0]["SaveIsCreateIndex2"] != null && ds.Tables[0].Rows[0]["SaveIsCreateIndex2"].ToString() != "")
{
model.SaveIsCreateIndex2 = int.Parse(ds.Tables[0].Rows[0]["SaveIsCreateIndex2"].ToString());
}
if (ds.Tables[0].Rows[0]["IsSaveDataBase3"] != null && ds.Tables[0].Rows[0]["IsSaveDataBase3"].ToString() != "")
{
model.IsSaveDataBase3 = int.Parse(ds.Tables[0].Rows[0]["IsSaveDataBase3"].ToString());
}
if (ds.Tables[0].Rows[0]["SaveDataType3"] != null && ds.Tables[0].Rows[0]["SaveDataType3"].ToString() != "")
{
model.SaveDataType3 = int.Parse(ds.Tables[0].Rows[0]["SaveDataType3"].ToString());
}
if (ds.Tables[0].Rows[0]["SaveDataUrl3"] != null && ds.Tables[0].Rows[0]["SaveDataUrl3"].ToString() != "")
{
model.SaveDataUrl3 = ds.Tables[0].Rows[0]["SaveDataUrl3"].ToString();
}
if (ds.Tables[0].Rows[0]["SaveDataSQL3"] != null && ds.Tables[0].Rows[0]["SaveDataSQL3"].ToString() != "")
{
model.SaveDataSQL3 = ds.Tables[0].Rows[0]["SaveDataSQL3"].ToString();
}
if (ds.Tables[0].Rows[0]["IsSaveSQL4"] != null && ds.Tables[0].Rows[0]["IsSaveSQL4"].ToString() != "")
{
model.IsSaveSQL4 = int.Parse(ds.Tables[0].Rows[0]["IsSaveSQL4"].ToString());
}
if (ds.Tables[0].Rows[0]["SaveSQLContent4"] != null && ds.Tables[0].Rows[0]["SaveSQLContent4"].ToString() != "")
{
model.SaveSQLContent4 = ds.Tables[0].Rows[0]["SaveSQLContent4"].ToString();
}
if (ds.Tables[0].Rows[0]["SaveSQLDirectory4"] != null && ds.Tables[0].Rows[0]["SaveSQLDirectory4"].ToString() != "")
{
model.SaveSQLDirectory4 = ds.Tables[0].Rows[0]["SaveSQLDirectory4"].ToString();
}
if (ds.Tables[0].Rows[0]["Guid"] != null && ds.Tables[0].Rows[0]["Guid"].ToString() != "")
{
model.Guid = ds.Tables[0].Rows[0]["Guid"].ToString();
}
//
if (ds.Tables[0].Rows[0]["PluginSpiderUrl"] != null && ds.Tables[0].Rows[0]["PluginSpiderUrl"].ToString() != "")
{
model.PluginSpiderUrl = ds.Tables[0].Rows[0]["PluginSpiderUrl"].ToString();
}
if (ds.Tables[0].Rows[0]["PluginSpiderContent"] != null && ds.Tables[0].Rows[0]["PluginSpiderContent"].ToString() != "")
{
model.PluginSpiderContent = ds.Tables[0].Rows[0]["PluginSpiderContent"].ToString();
}
if (ds.Tables[0].Rows[0]["PluginSaveContent"] != null && ds.Tables[0].Rows[0]["PluginSaveContent"].ToString() != "")
{
model.PluginSaveContent = ds.Tables[0].Rows[0]["PluginSaveContent"].ToString();
}
if (ds.Tables[0].Rows[0]["PluginPublishContent"] != null && ds.Tables[0].Rows[0]["PluginPublishContent"].ToString() != "")
{
model.PluginPublishContent = ds.Tables[0].Rows[0]["PluginPublishContent"].ToString();
}
//===============================2012 2-16
if (ds.Tables[0].Rows[0]["CollectionContentThreadCount"] != null && ds.Tables[0].Rows[0]["CollectionContentThreadCount"].ToString() != "")
{
model.CollectionContentThreadCount = int.Parse(ds.Tables[0].Rows[0]["CollectionContentThreadCount"].ToString());
}
if (ds.Tables[0].Rows[0]["CollectionContentStepTime"] != null && ds.Tables[0].Rows[0]["CollectionContentStepTime"].ToString() != "")
{
model.CollectionContentStepTime = int.Parse(ds.Tables[0].Rows[0]["CollectionContentStepTime"].ToString());
}
if (ds.Tables[0].Rows[0]["PublishContentThreadCount"] != null && ds.Tables[0].Rows[0]["PublishContentThreadCount"].ToString() != "")
{
model.PublishContentThreadCount = int.Parse(ds.Tables[0].Rows[0]["PublishContentThreadCount"].ToString());
}
if (ds.Tables[0].Rows[0]["PublishContentStepTimeMin"] != null && ds.Tables[0].Rows[0]["PublishContentStepTimeMin"].ToString() != "")
{
model.PublishContentStepTimeMin = int.Parse(ds.Tables[0].Rows[0]["PublishContentStepTimeMin"].ToString());
}
if (ds.Tables[0].Rows[0]["PublishContentStepTimeMax"] != null && ds.Tables[0].Rows[0]["PublishContentStepTimeMax"].ToString() != "")
{
model.PublishContentStepTimeMax = int.Parse(ds.Tables[0].Rows[0]["PublishContentStepTimeMax"].ToString());
}
if (ds.Tables[0].Rows[0]["IsHandGetUrl"] != null && ds.Tables[0].Rows[0]["IsHandGetUrl"].ToString() != "")
{
model.IsHandGetUrl = int.Parse(ds.Tables[0].Rows[0]["IsHandGetUrl"].ToString());
}
if (ds.Tables[0].Rows[0]["HandCollectionUrlRegex"] != null)
{
model.HandCollectionUrlRegex = ds.Tables[0].Rows[0]["HandCollectionUrlRegex"].ToString();
}
if (ds.Tables[0].Rows[0]["DemoListUrl"] != null)
{
model.DemoListUrl = ds.Tables[0].Rows[0]["DemoListUrl"].ToString();
}
//
if (ds.Tables[0].Rows[0]["IsPlan"] != null && ds.Tables[0].Rows[0]["IsPlan"].ToString() != "")
{
model.IsPlan = int.Parse(ds.Tables[0].Rows[0]["IsPlan"].ToString());
}
if (ds.Tables[0].Rows[0]["PlanFormat"] != null)
{
model.PlanFormat = ds.Tables[0].Rows[0]["PlanFormat"].ToString();
}
return(model);
}
else
{
return(null);
}
}
private void PublishDataToDB(int taskindex, int threadindex)
{
string LocalSQLiteName = "Data\\Collection\\" + ModelTask.TaskName + "\\SpiderResult.db";
DataTable dtData = SQLiteHelper.Query1(LocalSQLiteName, "Select * From Content").Tables[0];
int saveDateType = ModelTask.SaveDataType3.Value;
string connectionString = ModelTask.SaveDataUrl3;
string exeSQL = ModelTask.SaveDataSQL3;
string sql = string.Empty;
switch (saveDateType)
{
case 1: //ACCESS
break;
case 2: //MSSQL
foreach (DataRow dr in dtData.Rows)
{
try {
sql = exeSQL;
foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel)
{
sql = sql.Replace("[" + mTaskLabel.LabelName + "]", dr[mTaskLabel.LabelName].ToString().Replace("'", "''"));
}
sql = sql.Replace("[Guid]", Guid.NewGuid().ToString());
sql = sql.Replace("[Url]", dr["HrefSource"].ToString());
DbHelperSQL.connectionString = ModelTask.SaveDataUrl3;
DbHelperSQL.ExecuteSql(sql);
gatherEv.Message = dr["HrefSource"].ToString() + ":保存数据库成功!";
PublishCompalteDelegate(this, gatherEv);
}
catch (Exception ex) {
Log4Helper.Write(V5_Utility.Utility.LogLevel.Error, dr["HrefSource"].ToString() + ":保存数据库失败!", ex);
continue;
}
}
break;
case 3: //SQLITE
break;
case 4: //MYSQL
MySqlConnection conn = new MySqlConnection(ModelTask.SaveDataUrl3);
try
{
conn.Open();
MySqlScript script = new MySqlScript(conn);
foreach (DataRow dr in dtData.Rows)
{
try
{
sql = exeSQL;
foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel)
{
sql = sql.Replace("[" + mTaskLabel.LabelName + "]", dr[mTaskLabel.LabelName].ToString().Replace("'", "''"));
}
sql = sql.Replace("[Guid]", Guid.NewGuid().ToString());
sql = sql.Replace("[Url]", dr["HrefSource"].ToString());
script.Query = sql;
script.Execute();
gatherEv.Message = dr["HrefSource"].ToString() + ":保存数据库成功!";
PublishCompalteDelegate(this, gatherEv);
/*
* DbHelperSQL.connectionString = ModelTask.SaveDataUrl3;
* DbHelperSQL.ExecuteSql(sql);
* gatherEv.Message = dr["HrefSource"].ToString() + ":保存数据库成功!";
* PublishCompalteDelegate(this, gatherEv);
*/
}
catch (Exception ex)
{
Log4Helper.Write(V5_Utility.Utility.LogLevel.Error, dr["HrefSource"].ToString() + ":保存数据库失败!", ex);
continue;
}
}
}
catch (Exception ex)
{
}
conn.Close();
break;
case 5: //Oracle
break;
}
}
public int DeleteRecord(string id)
{
return(SQLiteHelper.ExecuteNonQuery(CommandType.Text, "delete from source where id=" + id));
}
// 创建所有的表
public static void CreateTables()
{
// name: 比赛名称， time:比赛时间，project：比赛项目（男子团体，女子团体...）,group_name:比赛组别（A组，B组...）
// PRE_ROUNDS: 预赛轮次， FINAL_ROUNDS： 决赛轮次，NUM_ONE_ROUND： 一轮球道数, MAX_EVERY_FAIRWAY:每球道最高杆数
// FINAL_NUM：录取决赛人数， SCORING_METHOD:团体成绩计分方法
var createProjectTableSQL = @"
create table MATCH(
ID varchar(255),
NAME varchar(255),
TIME varchar(255),
PROJECT_NAME varchar(255),
GROUP_NAME varchar(255),
PRE_ROUNDS varchar(255),
FINAL_ROUNDS varchar(255),
NUM_ONE_ROUND varchar(255),
MAX_EVERY_FAIRWAY varchar(255),
FINAL_NUM varchar(255),
SCORING_METHOD varchar(255),
CREATE_TIME varchar(255)
);
";
var createOthersTableSQL = @"
CREATE TABLE OTHERS(
ID VARCHAR(255),
MATCH_ID VARCHAR(255),
TEAM_NAME VARCHAR(255),
TEAM_SHORT_NAME VARCHAR(255),
GROUP_NAME VARCHAR(255),
TITLE VARCHAR(255),
NAME VARCHAR(255),
SEX VARCHAR(255),
PHONE VARCHAR(255),
ACCOMMODATION_REQUIREMENTS VARCHAR(255),
IS_VEGETARIAN VARCHAR(255),
CLOTHING_SIZE VARCHAR(255),
IDCARD VARCHAR(255))";
var createAthletesTableSQL = @"
create table ATHLETES(
ID VARCHAR(255),
MATCH_ID VARCHAR(255),
TEAM_NAME VARCHAR(255),
TEAM_SHORT_NAME VARCHAR(255),
GROUP_NAME VARCHAR(255),
NAME varchar(255),
SEX varchar(255),
BAR_GROUP_GAMES_STATUS varchar(255),
BAR_INDIVIDUAL_GAMES_STATUS varchar(255),
WAY_GROUP_GAMES_STATUS varchar(255),
WAY_INDIVIDUAL_GAMES_STATUS varchar(255),
IDCARD varchar(255),
PHONE VARCHAR(255),
ACCOMMODATION_REQUIREMENTS varchar(255),
IS_VEGETARIAN VARCHAR(255),
CLOTHING_SIZE VARCHAR(255),
PRELIMINARIES_RESULTS_ONE varchar(255),
PRELIMINARIES_RESULTS_TWO varchar(255),
PRELIMINARIES_RESULTS_THREE varchar(255),
PRELIMINARIES_RESULTS_FOUR varchar(255),
PRELIMINARIES_RESULTS_FIVE varchar(255),
FINAL_RESULTS_ONE varchar(255),
FINAL_RESULTS_TWO varchar(255),
FINAL_RESULTS_THREE varchar(255),
FINAL_RESULTS_FOUR varchar(255),
FINAL_RESULTS_FIVE varchar(255)
);";
var createMatchGroupTableSQL = @"
create table MATCH_GROUP(
ID varchar(255),
MATCH_ID varchar(255),
GROUP_NAME varchar(255),
SEX_NAME varchar(255),
TEAM_NAME varchar(255),
TEAM_SHORT_NAME VARCHAR(255),
NAME varchar(255),
ROW varchar(255),
COLUMN varchar(255),
ISINDIVIDUAL varchar(255),
MEN_WOMEN_COLUMN varchar(255),
DOUBLE_COLUMN varchar(255),
IS_FINAL varchar(255)
);
";
if (!TableExist("MATCH")) // 比赛表
{
// create PROJECT table
SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString, createProjectTableSQL, CommandType.Text);
}
if (!TableExist("OTHERS")) // 其他人员信息表
{
// create OTHERS table
SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString, createOthersTableSQL, CommandType.Text);
}
if (!TableExist("ATHLETES")) // 运动员信息表
{
// create ATHLETES table
SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString, createAthletesTableSQL, CommandType.Text);
}
if (!TableExist("MATCH_GROUP")) // 竞赛分组表
{
// create MATCH_GROUP table
SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString, createMatchGroupTableSQL, CommandType.Text);
}
}
//*********** GENERIC METHODS *****************
private void insertIntoDB(String table, Dictionary<string, object> dic)
{
using (SQLiteConnection conn = new SQLiteConnection(CONNECT)) {
using (SQLiteCommand cmd = new SQLiteCommand()) {
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.Insert(table, dic);
conn.Close();
}
}
}
void Start()
{
/*
* //各平台下数据库存储的绝对路径(通用)
* //PC：sql = new SQLiteHelper("data source=" + Application.dataPath + "/sqlite4unity.db");
* //Mac：sql = new SQLiteHelper("data source=" + Application.dataPath + "/sqlite4unity.db");
* //Android：sql = new SQLiteHelper("URI=file:" + Application.persistentDataPath + "/sqlite4unity.db");
* //iOS：sql = new SQLiteHelper("data source=" + Application.persistentDataPath + "/sqlite4unity.db");
*/
//创建名为sqlite4unity的数据库
sql = new SQLiteHelper("data source=sqlite4unity.db");
//创建名为table1的数据表
sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" });
//插入两条数据
sql.InsertValues("table1", new string[] { "'1'", "'张三'", "'22'", "'*****@*****.**'" });
sql.InsertValues("table1", new string[] { "'2'", "'李四'", "'25'", "'*****@*****.**'" });
//更新数据，将Name="张三"的记录中的Name改为"Zhang3"
sql.UpdateValues("table1", new string[] { "Name" }, new string[] { "'Zhang3'" }, "Name", "=", "'张三'");
//插入3条数据
sql.InsertValues("table1", new string[] { "3", "'王五'", "25", "'*****@*****.**'" });
sql.InsertValues("table1", new string[] { "4", "'王五'", "26", "'*****@*****.**'" });
sql.InsertValues("table1", new string[] { "5", "'王五'", "27", "'*****@*****.**'" });
//删除Name="王五"且Age=26的记录,DeleteValuesOR方法类似
sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "=", "=" }, new string[] { "'王五'", "'26'" });
//读取整张表
SqliteDataReader reader = sql.ReadFullTable("table1");
while (reader.Read())
{
//读取ID
Debug.Log(reader.GetInt32(reader.GetOrdinal("ID")));
//读取Name
Debug.Log(reader.GetString(reader.GetOrdinal("Name")));
//读取Age
Debug.Log(reader.GetInt32(reader.GetOrdinal("Age")));
//读取Email
Debug.Log(reader.GetString(reader.GetOrdinal("Email")));
}
//读取数据表中Age>=25的所有记录的ID和Name
reader = sql.ReadTable("table1", new string[] { "ID", "Name" }, new string[] { "Age" }, new string[] { ">=" }, new string[] { "'25'" });
while (reader.Read())
{
//读取ID
Debug.Log(reader.GetInt32(reader.GetOrdinal("ID")));
//读取Name
Debug.Log(reader.GetString(reader.GetOrdinal("Name")));
}
//自定义SQL,删除数据表中所有Name="王五"的记录
sql.ExecuteQuery("DELETE FROM table1 WHERE NAME='王五'");
//关闭数据库连接
sql.CloseConnection();
}
private void Form1_Load(object sender, EventArgs e)
{
using (SQLiteConnection conn = new SQLiteConnection(@"data source=.\CloudAgent.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try {
DataTable dt = sh.Select("select * from setting where key='interval';");
if (dt.Rows.Count > 0)
{
textBox5.Text = dt.Rows[0]["value"].ToString();
}
DataTable dt2 = sh.Select("select * from setting where key='timeout';");
if (dt2.Rows.Count > 0)
{
textBox6.Text = dt2.Rows[0]["value"].ToString();
}
DataTable dt3 = sh.Select("select * from server ;");
if (dt3.Rows.Count > 0)
{
string cosip = dt3.Rows[0]["ip"].ToString();
string[] s = cosip.Split(new char[] { '.' });
textBox1.Text = s[0];
textBox2.Text = s[1];
textBox3.Text = s[2];
textBox4.Text = s[3];
for (int i=0;i< dt3.Rows.Count;i++)
{
comboBox1.Items.Add(dt3.Rows[i]["ip"].ToString());
}
}
sh.Commit();
}
catch
{
sh.Rollback();
}
conn.Close();
}
}
}
public Workspaces(string filename)
{
sqlite = new SQLiteHelper(filename);
CreateTable(defaultTable);
}
private void RepostTaskCoreFrm_Load(object sender, EventArgs e)
{
selectCommitType.SelectedIndex = 0;
using (SQLiteConnection conn =
new SQLiteConnection(DBConfig.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
int count = sh.ExecuteScalar<int>("select count(*) from WeiboTaskInfo;") + 1;
this.TaskInfoDataView.DataSource =
sh.Select("select * from WeiboTaskInfo order by task_id desc;"); //加入后更新DataGridView
conn.Close();
}
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(ModelTask model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update S_Task set ");
if (model.Status != null)
{
strSql.Append("Status=" + model.Status + ",");
}
if (model.TaskClassID != null)
{
strSql.Append("TaskClassID=" + model.TaskClassID + ",");
}
if (model.TaskName != null)
{
strSql.Append("TaskName='" + model.TaskName + "',");
}
if (model.IsSpiderUrl != null)
{
strSql.Append("IsSpiderUrl=" + model.IsSpiderUrl + ",");
}
if (model.IsSpiderContent != null)
{
strSql.Append("IsSpiderContent=" + model.IsSpiderContent + ",");
}
if (model.IsPublishContent != null)
{
strSql.Append("IsPublishContent=" + model.IsPublishContent + ",");
}
if (model.PageEncode != null)
{
strSql.Append("PageEncode='" + model.PageEncode + "',");
}
if (model.CollectionType != null)
{
strSql.Append("CollectionType=" + model.CollectionType + ",");
}
if (model.CollectionContent != null)
{
strSql.Append("CollectionContent='" + model.CollectionContent + "',");
}
if (model.LinkUrlMustIncludeStr != null)
{
strSql.Append("LinkUrlMustIncludeStr='" + model.LinkUrlMustIncludeStr + "',");
}
if (model.LinkUrlNoMustIncludeStr != null)
{
strSql.Append("LinkUrlNoMustIncludeStr='" + model.LinkUrlNoMustIncludeStr + "',");
}
if (model.LinkSpliceUrlStr != null)
{
strSql.Append("LinkSpliceUrlStr='" + model.LinkSpliceUrlStr + "',");
}
if (model.LinkUrlCutAreaStart != null)
{
strSql.Append("LinkUrlCutAreaStart='" + model.LinkUrlCutAreaStart + "',");
}
if (model.LinkUrlCutAreaEnd != null)
{
strSql.Append("LinkUrlCutAreaEnd='" + model.LinkUrlCutAreaEnd + "',");
}
if (model.TestViewUrl != null)
{
strSql.Append("TestViewUrl='" + model.TestViewUrl + "',");
}
if (model.IsWebOnlinePublish1 != null)
{
strSql.Append("IsWebOnlinePublish1=" + model.IsWebOnlinePublish1 + ",");
}
if (model.IsSaveLocal2 != null)
{
strSql.Append("IsSaveLocal2=" + model.IsSaveLocal2 + ",");
}
if (model.SaveFileFormat2 != null)
{
strSql.Append("SaveFileFormat2='" + model.SaveFileFormat2 + "',");
}
if (model.SaveDirectory2 != null)
{
strSql.Append("SaveDirectory2='" + model.SaveDirectory2 + "',");
}
if (model.SaveHtmlTemplate2 != null)
{
strSql.Append("SaveHtmlTemplate2='" + model.SaveHtmlTemplate2 + "',");
}
if (model.SaveIsCreateIndex2 != null)
{
strSql.Append("SaveIsCreateIndex2=" + model.SaveIsCreateIndex2 + ",");
}
if (model.IsSaveDataBase3 != null)
{
strSql.Append("IsSaveDataBase3=" + model.IsSaveDataBase3 + ",");
}
if (model.SaveDataType3 != null)
{
strSql.Append("SaveDataType3=" + model.SaveDataType3 + ",");
}
if (model.SaveDataUrl3 != null)
{
strSql.Append("SaveDataUrl3='" + model.SaveDataUrl3 + "',");
}
if (model.SaveDataSQL3 != null)
{
strSql.Append("SaveDataSQL3='" + model.SaveDataSQL3 + "',");
}
if (model.IsSaveSQL4 != null)
{
strSql.Append("IsSaveSQL4=" + model.IsSaveSQL4 + ",");
}
if (model.SaveSQLContent4 != null)
{
strSql.Append("SaveSQLContent4='" + model.SaveSQLContent4 + "',");
}
if (model.SaveSQLDirectory4 != null)
{
strSql.Append("SaveSQLDirectory4='" + model.SaveSQLDirectory4 + "',");
}
if (model.Guid != null)
{
strSql.Append("Guid='" + model.Guid + "',");
}
//
if (model.PluginSpiderUrl != null)
{
strSql.Append("PluginSpiderUrl='" + model.PluginSpiderUrl + "',");
}
if (model.PluginSpiderContent != null)
{
strSql.Append("PluginSpiderContent='" + model.PluginSpiderContent + "',");
}
if (model.PluginSaveContent != null)
{
strSql.Append("PluginSaveContent='" + model.PluginSaveContent + "',");
}
if (model.PluginPublishContent != null)
{
strSql.Append("PluginPublishContent='" + model.PluginPublishContent + "',");
}
//============================================2012 2-6
if (model.CollectionContentThreadCount != null)
{
strSql.Append("CollectionContentThreadCount=" + model.CollectionContentThreadCount + ",");
}
if (model.CollectionContentStepTime != null)
{
strSql.Append("CollectionContentStepTime=" + model.CollectionContentStepTime + ",");
}
if (model.PublishContentThreadCount != null)
{
strSql.Append("PublishContentThreadCount=" + model.PublishContentThreadCount + ",");
}
if (model.PublishContentStepTimeMin != null)
{
strSql.Append("PublishContentStepTimeMin=" + model.PublishContentStepTimeMin + ",");
}
if (model.PublishContentStepTimeMax != null)
{
strSql.Append("PublishContentStepTimeMax=" + model.PublishContentStepTimeMax + ",");
}
if (model.IsHandGetUrl != null)
{
strSql.Append("IsHandGetUrl=" + model.IsHandGetUrl + ",");
}
if (model.HandCollectionUrlRegex != null)
{
strSql.Append("HandCollectionUrlRegex='" + model.HandCollectionUrlRegex + "',");
}
if (model.DemoListUrl != null)
{
strSql.Append("DemoListUrl='" + model.DemoListUrl + "',");
}
//
if (model.IsPlan != null)
{
strSql.Append("IsPlan=" + model.IsPlan + ",");
}
if (model.PlanFormat != null)
{
strSql.Append("PlanFormat='" + model.PlanFormat + "',");
}
int n = strSql.ToString().LastIndexOf(",");
strSql.Remove(n, 1);
strSql.Append(" where ID=" + model.ID + " ");
int rowsAffected = SQLiteHelper.Execute(dbStr, strSql.ToString());
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public void createTable()
{
using (SQLiteConnection conn = new SQLiteConnection(dataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.DropTable(tableName);
SQLiteTable tb = new SQLiteTable(tableName);
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("url", ColType.Text));
tb.Columns.Add(new SQLiteColumn("referer", ColType.Text));
tb.Columns.Add(new SQLiteColumn("status", ColType.Integer));
tb.Columns.Add(new SQLiteColumn("duration", ColType.Decimal));
sh.CreateTable(tb);
conn.Close();
}
}
}
public void Remove(int id)
{
string sql = "update Comment set enable = 0 where Commentid = ?";
SQLiteHelper.ExecuteNonQuery(sql, id);
}
