/// <summary> /// /// </summary> /// <param name="attributeColumn"></param> /// <param name="whereClause"> forget the "WHERE", e.g. coulumn01 = someValue</param> /// <returns></returns> public override List <object> GetAttributes(string attributeColumn, string whereClause) { //SqlCeConnection connection = new SqlCeConnection(_connectionString); SqlCeCommand command = new SqlCeCommand(string.Format(System.Globalization.CultureInfo.InvariantCulture, "SELECT {0} FROM {1} {2}", attributeColumn, _tableName, MakeWhereClause(whereClause)), _connection); command.CommandType = System.Data.CommandType.Text; //connection.Open(); List <object> result = new List <object>(); SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable); if (!resultSet.HasRows) { return(new List <object>()); } int columnIndex = resultSet.GetOrdinal(attributeColumn); while (resultSet.Read()) { result.Add(resultSet.GetValue(columnIndex)); } //connection.Close(); return(result.Cast <object>().ToList()); }
public static TValue SafeGet <TValue>(this SqlCeResultSet self, string columnName, TValue defaultValue = default(TValue)) { var t = typeof(TValue); var ordinal = self.GetOrdinal(columnName); if (self.IsDBNull(ordinal)) { return(defaultValue); } dynamic value; if (t == typeof(int)) { value = self.GetInt32(ordinal); } else if (t == typeof(long)) { value = self.GetInt64(ordinal); } else if (t == typeof(bool)) { value = self.GetBoolean(ordinal); } else if (t == typeof(object)) { value = self.GetValue(ordinal); } else if (t == typeof(string)) { value = self.GetString(ordinal); } else if (t == typeof(int?) || t == typeof(long?) || t == typeof(bool?)) { value = self.GetValue(ordinal); } else { throw new ApplicationException($"{nameof(SafeGet)} does not support type '{t.Name}'!"); } return(value == null ? defaultValue : (TValue)Convert.ChangeType(value, Nullable.GetUnderlyingType(typeof(TValue)) ?? typeof(TValue))); }
/// <summary> /// s /// </summary> /// <param name="whereClause"> forget the "WHERE", e.g. coulumn01 = someValue</param> /// <returns></returns> public override List <SqlGeometry> GetGeometries(string whereClause) { //SqlCeConnection connection = new SqlCeConnection(_connectionString); //_connection.Open(); List <Microsoft.SqlServer.Types.SqlGeometry> geometries = new List <Microsoft.SqlServer.Types.SqlGeometry>(); SqlCeCommand command = new SqlCeCommand( string.Format(System.Globalization.CultureInfo.InvariantCulture, "SELECT {0} FROM {1} {2} ", _spatialColumnName, _tableName, MakeWhereClause(whereClause)), _connection); command.CommandType = System.Data.CommandType.Text; SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable); if (resultSet.HasRows) { int columnIndex = resultSet.GetOrdinal(_spatialColumnName); if (_wktMode) { while (resultSet.Read()) { geometries.Add(SqlGeometry.Parse(resultSet.GetString(columnIndex)).MakeValid()); } } else { while (resultSet.Read()) { geometries.Add(SqlGeometry.STGeomFromWKB( new System.Data.SqlTypes.SqlBytes((byte[])resultSet.GetValue(columnIndex)), 0).MakeValid()); } } } //connection.Close(); return(geometries); }
/// <summary> /// Checks if an index exists for the property of T that is not null. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="TableItem"></param> /// <returns>the name of the index</returns> public string CheckIndex <T>(T TableItem) where T : class, new() { if (!AccessPointReady()) { return(null); } SetPropertyInfosAndUniqueIdentifier(TableItem); string sqlStatement = ConstructSQLStatementCheckIndex(); SqlCeConnection connection = new SqlCeConnection(ConnectionString()); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand command = GetSqlCeCommand(connection, sqlStatement); SqlCeResultSet ResultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable); if (ResultSet.HasRows) { while (ResultSet.Read()) { object result = ResultSet.GetValue(5); if ((string)result == GetIndexName(TableItem)) { return((string)result); } } } return(""); } catch (Exception exception) { Console.WriteLine(exception.Message + exception.StackTrace); return(null); } finally { connection.Close(); } }
/// <summary> /// Returns a column result set as a list of the specified type (via cast, omitting NULLs) /// </summary> /// <typeparam name="T">Cast to this return type</typeparam> /// <param name="query">Query to execute</param> /// <param name="columnName">Column (name) to return</param> /// <returns></returns> public List <T> ExecuteListQuery <T>(string query, string columnName) { using (SqlCeCommand cmd = this.GetConnection().CreateCommand()) { List <T> result = new List <T>(); cmd.CommandText = query; SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.None); int ordinal = rs.GetOrdinal(columnName); while (rs.Read()) { if (!rs.IsDBNull(ordinal)) { result.Add((T)rs.GetValue(ordinal)); } } return(result); } }
public bool CheckTable <T>(T TableItem, ref string errMsg) where T : class, new() { SetPropertyInfosAndUniqueIdentifier(TableItem); string sqlStatement = ConstructSQLStatementCheckTable(); try { PrepareAndOpenConnection(); SqlCeCommand command = GetSqlCeCommand(Connection, sqlStatement); SqlCeResultSet ResultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable); if (ResultSet.HasRows) { while (ResultSet.Read()) { object result = ResultSet.GetValue(2); if ((string)result == typeof(T).Name) { return(true); } } } errMsg = "无法创建数据表!"; return(false); } catch (SqlCeException sqlexception) { throw new Exception(sqlexception.Message + sqlexception.StackTrace); } catch (Exception exception) { throw new Exception(exception.Message + exception.StackTrace); } finally { ReturnToPreviousConnectionState(); } }
protected override object InternalGetValue(int index) { return(StoreToNativeValue(_resultSet.GetValue(index))); }
public frmMain() { InitializeComponent(); if (!File.Exists(string.Format("{0}\\MyPersonalIndex\\MPI.sqlite", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)))) { MessageBox.Show("Error updating! Please run MyPersonalIndex version 3.0 (and then close it) before using this upgrade program.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); throw new Exception(); } if (!File.Exists(string.Format("{0}\\MyPersonalIndex\\MPI.sdf", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)))) { MessageBox.Show("Error updating! It does not appear you have MyPersonalIndex version 2.0 or later installed.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); throw new Exception(); } try { cnLite = new SQLiteConnection(string.Format("Data Source={0}\\MyPersonalIndex\\MPI.sqlite", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData))); cnCe = new SqlCeConnection(string.Format("Data Source={0}\\MyPersonalIndex\\MPI.sdf", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData))); if (cnCe.State == ConnectionState.Closed) { cnCe.Open(); } if (cnLite.State == ConnectionState.Closed) { cnLite.Open(); } using (SQLiteCommand c = new SQLiteCommand("BEGIN", cnLite)) c.ExecuteNonQuery(); // Portfolios if (Convert.ToDouble(ExecuteScalar("SELECT Version FROM Settings")) < 2.01) { MessageBox.Show("Error updating! Upgrade to version 2.0.1 before running this installer.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); throw new Exception(); } Dictionary <int, int> portfolioMapping = new Dictionary <int, int>(); using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Portfolios")) { foreach (SqlCeUpdatableRecord rec in rs) { int ID = rec.GetInt32(rec.GetOrdinal("ID")); string Name = rec.GetString(rec.GetOrdinal("Name")); bool Dividends = rec.GetBoolean(rec.GetOrdinal("Dividends")); bool HoldingsShowHidden = rec.GetBoolean(rec.GetOrdinal("HoldingsShowHidden")); int CostCalc = rec.GetInt32(rec.GetOrdinal("CostCalc")); bool NAVSort = rec.GetBoolean(rec.GetOrdinal("NAVSort")); decimal NAVStartValue = rec.GetDecimal(rec.GetOrdinal("NAVStartValue")); int AAThreshold = rec.GetInt32(rec.GetOrdinal("AAThreshold")); bool AAShowBlank = rec.GetBoolean(rec.GetOrdinal("AAShowBlank")); bool AcctShowBlank = rec.GetBoolean(rec.GetOrdinal("AcctShowBlank")); bool CorrelationShowHidden = rec.GetBoolean(rec.GetOrdinal("CorrelationShowHidden")); DateTime StartDate = rec.GetDateTime(rec.GetOrdinal("StartDate")); using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Portfolios (Description, StartValue, AAThreshold, ThresholdMethod, " + " CostBasis, StartDate, Dividends, HoldingsShowHidden, AAShowBlank, CorrelationShowHidden, AcctShowBlank, " + " NAVSortDesc) VALUES (@Description, @StartValue, @AAThreshold, @ThresholdMethod, " + " @CostBasis, @StartDate, @Dividends, @HoldingsShowHidden, @AAShowBlank, @CorrelationShowHidden, @AcctShowBlank, " + " @NAVSortDesc)", cnLite)) { c.Parameters.AddWithValue("@Description", Name); c.Parameters.AddWithValue("@StartValue", NAVStartValue); c.Parameters.AddWithValue("@AAThreshold", AAThreshold); c.Parameters.AddWithValue("@ThresholdMethod", 0); c.Parameters.AddWithValue("@CostBasis", CostCalc + 1); c.Parameters.AddWithValue("@StartDate", ConvertDateToJulian(StartDate)); c.Parameters.AddWithValue("@Dividends", Dividends ? 1 : 0); c.Parameters.AddWithValue("@HoldingsShowHidden", HoldingsShowHidden ? 1 : 0); c.Parameters.AddWithValue("@AAShowBlank", AAShowBlank ? 1 : 0); c.Parameters.AddWithValue("@CorrelationShowHidden", CorrelationShowHidden ? 1 : 0); c.Parameters.AddWithValue("@AcctShowBlank", AcctShowBlank ? 1 : 0); c.Parameters.AddWithValue("@NAVSortDesc", NAVSort ? 1 : 0); c.ExecuteNonQuery(); } portfolioMapping.Add(ID, getIdentity()); } } // Asset Allocation Dictionary <int, int> aaMapping = new Dictionary <int, int>(); using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM AA")) { foreach (SqlCeUpdatableRecord rec in rs) { int ID = rec.GetInt32(rec.GetOrdinal("ID")); int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio")); string Description = rec.GetString(rec.GetOrdinal("AA")); decimal?Target = null; if (rec.GetValue(rec.GetOrdinal("Target")) != System.DBNull.Value) { Target = rec.GetDecimal(rec.GetOrdinal("Target")) / 100; } using (SQLiteCommand c = new SQLiteCommand("INSERT INTO AA (PortfolioID, Description, Target) " + " VALUES (@PortfolioID, @Description, @Target)", cnLite)) { c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]); c.Parameters.AddWithValue("@Description", Description); c.Parameters.AddWithValue("@Target", Target.HasValue ? Target.Value : (object)System.DBNull.Value); c.ExecuteNonQuery(); } aaMapping.Add(ID, getIdentity()); } } // Accounts Dictionary <int, int> acctMapping = new Dictionary <int, int>(); using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Accounts")) { foreach (SqlCeUpdatableRecord rec in rs) { int ID = rec.GetInt32(rec.GetOrdinal("ID")); int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio")); string Description = rec.GetString(rec.GetOrdinal("Name")); bool OnlyGain = rec.GetBoolean(rec.GetOrdinal("OnlyGain")); decimal?TaxRate = null; if (rec.GetValue(rec.GetOrdinal("TaxRate")) != System.DBNull.Value) { TaxRate = rec.GetDecimal(rec.GetOrdinal("TaxRate")) / 100; } using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Acct (PortfolioID, Description, TaxRate, TaxDeferred, CostBasis) " + " VALUES (@PortfolioID, @Description, @TaxRate, @TaxDeferred, @CostBasis)", cnLite)) { c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]); c.Parameters.AddWithValue("@Description", Description); c.Parameters.AddWithValue("@TaxDeferred", !OnlyGain); c.Parameters.AddWithValue("@CostBasis", 0); c.Parameters.AddWithValue("@TaxRate", TaxRate.HasValue ? TaxRate.Value : (object)System.DBNull.Value); c.ExecuteNonQuery(); } acctMapping.Add(ID, getIdentity()); } } // Securities Dictionary <int, int> securityMapping = new Dictionary <int, int>(); using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Tickers")) { foreach (SqlCeUpdatableRecord rec in rs) { int ID = rec.GetInt32(rec.GetOrdinal("ID")); int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio")); string Ticker = rec.GetString(rec.GetOrdinal("Ticker")); bool Active = rec.GetBoolean(rec.GetOrdinal("Active")); int AA = rec.GetInt32(rec.GetOrdinal("AA")); bool Hide = rec.GetBoolean(rec.GetOrdinal("Hide")); int Account = rec.GetInt32(rec.GetOrdinal("Acct")); using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Security (PortfolioID, Symbol, Account, DivReinvest, CashAccount, IncludeInCalc, Hide) " + " VALUES (@PortfolioID, @Symbol, @Account, @DivReinvest, @CashAccount, @IncludeInCalc, @Hide)", cnLite)) { c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]); c.Parameters.AddWithValue("@Symbol", Ticker); c.Parameters.AddWithValue("@Account", Account == -1 ? (object)System.DBNull.Value : acctMapping[Account]); c.Parameters.AddWithValue("@DivReinvest", 0); c.Parameters.AddWithValue("@CashAccount", Ticker == "$" ? 1 : 0); c.Parameters.AddWithValue("@IncludeInCalc", Active ? 1 : 0); c.Parameters.AddWithValue("@Hide", Hide ? 1 : 0); c.ExecuteNonQuery(); } securityMapping.Add(ID, getIdentity()); if (AA == -1 || !aaMapping.ContainsKey(AA)) { continue; } using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityAA (SecurityID, AAID, Percent) " + " VALUES (@SecurityID, @AAID, @Percent)", cnLite)) { c.Parameters.AddWithValue("@SecurityID", securityMapping[ID]); c.Parameters.AddWithValue("@AAID", aaMapping[AA]); c.Parameters.AddWithValue("@Percent", 1); c.ExecuteNonQuery(); } } } using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Trades WHERE Custom IS NULL")) { foreach (SqlCeUpdatableRecord rec in rs) { DateTime TradeDate = rec.GetDateTime(rec.GetOrdinal("Date")); int TickerID = rec.GetInt32(rec.GetOrdinal("TickerID")); decimal Shares = rec.GetDecimal(rec.GetOrdinal("Shares")); decimal Price = rec.GetDecimal(rec.GetOrdinal("Price")); using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityTrades (SecurityID, Type, Value, Price, Frequency, Date) " + " VALUES (@SecurityID, @Type, @Value, @Price, @Frequency, @Date)", cnLite)) { c.Parameters.AddWithValue("@SecurityID", securityMapping[TickerID]); c.Parameters.AddWithValue("@Type", Shares < 0 ? 1 : 0); c.Parameters.AddWithValue("@Value", Math.Abs(Shares)); c.Parameters.AddWithValue("@Price", Price); c.Parameters.AddWithValue("@Frequency", 0); c.Parameters.AddWithValue("@Date", ConvertDateToJulian(TradeDate)); c.ExecuteNonQuery(); } } } using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM CustomTrades")) { foreach (SqlCeUpdatableRecord rec in rs) { string Dates = rec.GetString(rec.GetOrdinal("Dates")); int TickerID = rec.GetInt32(rec.GetOrdinal("TickerID")); int TradeType = rec.GetInt32(rec.GetOrdinal("TradeType")); int Frequency = rec.GetInt32(rec.GetOrdinal("Frequency")); decimal Value = rec.GetDecimal(rec.GetOrdinal("Value")); tradeType newTradeType = tradeType.tradeType_Purchase; tradeFreq newFrequency = tradeFreq.tradeFreq_Once; List <DateTime> ConvertedDates = new List <DateTime>(); switch ((DynamicTradeFreq)Frequency) { case DynamicTradeFreq.Daily: ConvertedDates.Add(DateTime.MinValue); newFrequency = tradeFreq.tradeFreq_Daily; break; case DynamicTradeFreq.Monthly: ConvertedDates.Add(new DateTime(2009, 1, Convert.ToInt32(Dates))); newFrequency = tradeFreq.tradeFreq_Monthly; break; case DynamicTradeFreq.Weekly: ConvertedDates.Add(new DateTime(2009, 1, 4 + Convert.ToInt32(Dates))); newFrequency = tradeFreq.tradeFreq_Weekly; break; case DynamicTradeFreq.Yearly: ConvertedDates.Add(new DateTime(2009, 1, 1).AddDays(Convert.ToInt32(Dates) - 1)); newFrequency = tradeFreq.tradeFreq_Yearly; break; case DynamicTradeFreq.Once: foreach (string s in Dates.Split('|')) { ConvertedDates.Add(new DateTime(Convert.ToInt32(s.Substring(0, 4)), Convert.ToInt32(s.Substring(4, 2)), Convert.ToInt32(s.Substring(6, 2)))); } newFrequency = tradeFreq.tradeFreq_Once; break; } switch ((DynamicTradeType)TradeType) { case DynamicTradeType.AA: newTradeType = tradeType.tradeType_AA; break; case DynamicTradeType.Fixed: newTradeType = Value < 0 ? tradeType.tradeType_FixedSale : tradeType.tradeType_FixedPurchase; break; case DynamicTradeType.Shares: newTradeType = Value < 0 ? tradeType.tradeType_Sale : tradeType.tradeType_Purchase; break; case DynamicTradeType.TotalValue: newTradeType = tradeType.tradeType_TotalValue; break; } foreach (DateTime d in ConvertedDates) { using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityTrades (SecurityID, Type, Value, Frequency, Date) " + " VALUES (@SecurityID, @Type, @Value, @Frequency, @Date)", cnLite)) { c.Parameters.AddWithValue("@SecurityID", securityMapping[TickerID]); c.Parameters.AddWithValue("@Type", (int)newTradeType); c.Parameters.AddWithValue("@Value", Math.Abs(Value)); c.Parameters.AddWithValue("@Frequency", (int)newFrequency); c.Parameters.AddWithValue("@Date", d == DateTime.MinValue ? (object)System.DBNull.Value : ConvertDateToJulian(d)); c.ExecuteNonQuery(); } } } } // Settings using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Settings")) { rs.ReadFirst(); DateTime DataStartDate = rs.GetDateTime(rs.GetOrdinal("DataStartDate")); int? LastPortfolio = null; if (rs.GetValue(rs.GetOrdinal("LastPortfolio")) != System.DBNull.Value) { LastPortfolio = rs.GetInt32(rs.GetOrdinal("LastPortfolio")); } int WindowX = rs.GetInt32(rs.GetOrdinal("WindowX")); int WindowY = rs.GetInt32(rs.GetOrdinal("WindowY")); int WindowHeight = rs.GetInt32(rs.GetOrdinal("WindowHeight")); int WindowWidth = rs.GetInt32(rs.GetOrdinal("WindowWidth")); int WindowState = rs.GetInt32(rs.GetOrdinal("WindowState")); bool Splits = rs.GetBoolean(rs.GetOrdinal("Splits")); bool TickerDiv = rs.GetBoolean(rs.GetOrdinal("TickerDiv")); using (SQLiteCommand c = new SQLiteCommand("UPDATE Settings SET DataStartDate = @DataStartDate, LastPortfolio = @LastPortfolio, " + " WindowX = @WindowX, WindowY = @WindowY, WindowHeight = @WindowHeight, WindowWidth = @WindowWidth, " + " WindowState = @WindowState, Splits = @Splits, CompareIncludeDividends = @CompareIncludeDividends", cnLite)) { c.Parameters.AddWithValue("@DataStartDate", ConvertDateToJulian(DataStartDate)); c.Parameters.AddWithValue("@LastPortfolio", LastPortfolio.HasValue ? portfolioMapping[LastPortfolio.Value] : (object)System.DBNull.Value); c.Parameters.AddWithValue("@WindowX", WindowX); c.Parameters.AddWithValue("@WindowY", WindowY); c.Parameters.AddWithValue("@WindowHeight", WindowHeight); c.Parameters.AddWithValue("@WindowWidth", WindowWidth); c.Parameters.AddWithValue("@WindowState", WindowState); c.Parameters.AddWithValue("@Splits", Splits ? 1 : 0); c.Parameters.AddWithValue("@CompareIncludeDividends", TickerDiv ? 1 : 0); c.ExecuteNonQuery(); } } using (SQLiteCommand c = new SQLiteCommand("COMMIT", cnLite)) c.ExecuteNonQuery(); MessageBox.Show("Upgrade successful!", "Success"); } catch (Exception e) { if (cnLite.State == ConnectionState.Open) { using (SQLiteCommand c = new SQLiteCommand("ROLLBACK", cnLite)) c.ExecuteNonQuery(); } MessageBox.Show("Error updating! You can run this program again from the installation folder.\nError:\n" + e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } finally { cnCe.Close(); cnLite.Close(); throw new Exception(); } }
private void LoadData(ThreadExecuteTask threadExecute) { try { this.symbolList.Clear(); for (int i = 0; i < countPerPage; i++) { //If an abort has been requested, we should quit if (threadExecute != null && threadExecute.State == ThreadExecuteTask.ProcessingState.requestAbort) { threadExecute.setProcessingState(ThreadExecuteTask.ProcessingState.aborted); System.Windows.Forms.MessageBox.Show("aborted"); return; } bool ok; if (i == 0) { ok = this.resultSet.ReadAbsolute(this.firstRecordindex); Debug.Assert(ok, "Failed to seek to position: " + this.firstRecordindex); } else { ok = resultSet.Read(); } if (ok) { SymbolInfo info = new SymbolInfo(); info.Id = resultSet.GetInt32(0); object image = resultSet.GetValue(1); using (MemoryStream ms = new MemoryStream(image as byte[])) { info.Image = ResizeImage(new Bitmap(ms), this.imageSize); } info.Sound = resultSet.GetValue(2) as byte[]; info.Text = resultSet.GetString(3); symbolList.Add(info); } else { break; } } } catch (Exception e) { Debug.WriteLine(e); } if (this.DataLoaded != null) { this.DataLoaded(); } }