public void UpdateEndTime(Database db) { db.ExecNonQuery( string.Format("update {0} set {1}=?time where {2}=?id", Mapper.TableName, Mapper.EndTime, Mapper.PickId) , new string[]{ "time", "id" } , new object[]{ this.EndTime, this.PickId }); }
public void Create(Database db) { db.ExecNonQuery( string.Format( "insert into {0} ({1},{2},{3}) values(?id, ?code, ?name)" , Mapper.TableName, Mapper.StockId, Mapper.StockCode, Mapper.StockName ) , new string[] {"id","code","name"} , new object[] {this.StockId, this.StockCode, this.StockName}); }
public void Create(Database db) { db.ExecNonQuery( string.Format( "insert into {0} ({1},{2},{3},{4},{5}) values(?strategy, ?stime, ?etime, ?mode, ?param)" , Mapper.TableName, Mapper.Strategy, Mapper.StartTime, Mapper.EndTime, Mapper.PickingMode, Mapper.Params ) , new string[] {"strategy","stime","etime", "mode", "param"} , new object[] {this.Strategy, this.StartTime, this.EndTime, this.PickingMode, this.Params}); this.PickId = Convert.ToInt32(db.ExecScalar("select last_insert_id()", null, null)); }
private bool UpdateMA(Database db) { return db.ExecNonQuery( string.Format("update {0} set {2}=?macs, {3}=?macl, {4}=?vmacs, {5}=?vmacl where {1}=?id" , Mapper.TableName, Mapper.Id, Mapper.MAShort, Mapper.MALong, Mapper.VMAShort, Mapper.VMALong), new string[] { "id", "macs", "macl", "vmacs", "vmacl" }, new object[] { this.Id, this.MAShort, this.MALong, this.VMAShort, this.VMALong } ) > 0; }
private bool Remove(Database db) { return db.ExecNonQuery( string.Format("delete from {0} where {1}=?id", Mapper.TableName, Mapper.Id), new string[] { "id" }, new object[] { this.Id } ) > 0; }
/// <summary> /// 创建股东数数据。前提条件:<br /> /// 1. 每次调用,<paramref name="entities"/>只能包含同一只股票的股东数数据;<br /> /// 2. 调用时,必须确保StockId, PublishDate, HolderCount, AverageStockNumber, Source属性值有效; /// </summary> /// <param name="db"></param> /// <param name="entities"></param> public static int Create(Database db, IList<ShareholdersNumEntity> entities) { if(entities==null || entities.Count<=0) return 0; DateTime minDate = DateTime.MaxValue, effectiveDate = new DateTime(1990, 1, 1); int stockId = 0, exists=0, insertedRows = 0; try{ db.BeginTransaction(); //添加数据 foreach(ShareholdersNumEntity entity in entities){ //数据校验 if(entity.StockId<=0 || entity.ReportDate<=effectiveDate // || entity.HolderCount<=0 || entity.AverageStockNumber<=0 || (entity.Source==null || entity.Source.Trim().Length<=0)) throw new EntityException("[holder-num] [create] 属性无效,无法更新数据库,[id:" + entity.StockId + ", date:" + entity.ReportDate.ToString("yyyyMMdd")); if(stockId==0) stockId = entity.StockId; if(stockId!=entity.StockId) throw new EntityException("[holder-num] [create] entities中包含了多只股票的股东数数据"); entity.CreateTime = DateTime.Now; entity.VarNum = 0; //插入数据 exists = Convert.ToInt32(db.ExecScalar( "select count(*) from sto_holder_num where sto_id=?id and report_date=?date", new string[] {"id", "date"}, new object[] { entity.StockId, entity.ReportDate} )); if(exists<=0){ insertedRows += db.ExecNonQuery(INSERT_SQL, new string[]{"id", "date", "holdersNum", "varNum", "avgShares", "totalShares", "transShares", "time", "source"}, new object[]{entity.StockId, entity.ReportDate, entity.HoldersNum, entity.VarNum , entity.AvgShares, entity.TotalShares, entity.TransShares , entity.CreateTime, entity.Source}); if(minDate>entity.ReportDate) minDate = entity.ReportDate; } } //更新股东数增长量 int prevCount = 0; exists = Convert.ToInt32(db.ExecScalar( "select count(*) from sto_holder_num where sto_id=?id and report_date<?date", new string[] {"id", "date"}, new object[] { stockId, minDate} )); if(exists>0){ prevCount = Convert.ToInt32(db.ExecScalar( "select holders_num from sto_holder_num where sto_id=?id and report_date<?date order by report_date desc limit 1", new string[] {"id", "date"}, new object[] { stockId, minDate} )); } DataSet ds = db.ExecDataSet( "select * from sto_holder_num where sto_id=?id and report_date>=?date order by report_date asc", new string[] { "id", "date" }, new object[] { stockId, minDate } ); foreach(DataRow row in ds.Tables[0].Rows){ if(prevCount==0){ prevCount = Convert.ToInt32(row["holders_num"]); continue; } int curCount = Convert.ToInt32(row["holders_num"]); db.ExecNonQuery( "update sto_holder_num set var_num=?varNum where sto_id=?id and report_date=?date", new string[] { "id", "date", "varNum" }, new object[] { stockId, row["report_date"], curCount-prevCount} ); prevCount = curCount; } db.CommitTransaction(); }catch(Exception ex){ db.RollbackTransaction(); throw ex; } return insertedRows; }
void BtnImpPlateClick(object sender, EventArgs e) { if(this.txtPlateFile.Text.Trim().Length<=0) return; if(!File.Exists(this.txtPlateFile.Text)) return; string plate = (string)this.comboPlate.SelectedItem; if(String.IsNullOrEmpty(plate)) return; int plateId = 0; switch(plate){ case "上证50": plateId = 3; break; case "沪深300": plateId = 1; break; case "融资融券": plateId = 2; break; } if(plateId<=0) return; Database db = new Database(this.txtDatabase.Text); db.Open(); db.ExecNonQuery("delete from bas_plate_stocks where plate_id=?id" , new string[] { "id" }, new object[] { plateId }); using(StreamReader sr = new StreamReader(this.txtPlateFile.Text, Encoding.Default)){ string line = ""; while((line = sr.ReadLine()) != null){ if(string.IsNullOrEmpty(line)) continue; string[] fields = line.Trim().Split('\t'); if(fields.Length<2 || fields[0].Trim().Length!=6 || (fields[0].Trim()[0]!= '6' && fields[0].Trim()[0]!= '3' && fields[0].Trim()[0]!= '0')) continue; string code = fields[0].Trim(); int stockId = int.Parse(code); if(stockId<=0) continue; db.ExecNonQuery("insert into bas_plate_stocks(plate_id, sto_id) values(?pid, ?sid)" , new string[] {"pid", "sid"}, new object[] {plateId, stockId} ); } } db.Close(); MessageBox.Show("导入成功", "导入成功"); }
public void UpdateInfo(Database db) { db.ExecNonQuery( string.Format( "update {0} set {1}=?date,{2}=?tCap,{3}=?cCap,{4}=?eps,{5}=?navps,{6}=?growth,{7}=?loc,{8}=?plat, {9}=?industry where {10}=?id", Mapper.TableName, Mapper.ListDate, Mapper.TotalCapital, Mapper.CirculatingCapital, Mapper.EarningsPerShare, Mapper.NetAssetValuePerShare, Mapper.NetProfitGrowth, Mapper.CompanyLocation, Mapper.Plate, Mapper.Industry, Mapper.StockId) , new string[]{"date","tCap","cCap","eps","navps","growth","loc","plat","industry","id"} , new object[]{ this.ListDate, this.TotalCapital, this.CirculatingCapital, this.EarningsPerShare, this.NetAssetValuePerShare, this.NetProfitGrowth, this.CompanyLocation, this.Plate, this.Industry, this.StockId }); }
protected static int BatchImport(Database db, string tableName, IList entities) { if(entities == null || entities.Count<=0) return 0; int stockId = (entities[0] as KTrend).StockId; db.ExecNonQuery(string.Format("delete from {0} where {1}=?stoId", tableName, Mapper.StockId), new string[] { "stoId" }, new object[] { stockId }); BulkInserter<KTrend> bi = CreateBulkInserter(db, tableName, 200); //初步验证,一批次插入200条性能最好 for(int i=0; i<entities.Count; i++){ bi.Push(entities[i] as KTrend); } bi.Flush(); return entities.Count; }