protected void Button1_Click(object sender, EventArgs e) { con = new SQLiteConnection("Data Source=e:\\db\\project.s3db"); con.Open(); SQLiteCommand command = new SQLiteCommand("Select * from train", con); reader = command.ExecuteReader(); //SQLiteDataAdapter ad=new SQLiteDataAdapter(,con); //GridView gv = new GridView(); String str = "<table><tr><th>train no</th><th>train name</th><th>train timing</th><th>avaliable tickets</th><th>total tickets</th><th>cost</th></tr>"; while (reader.Read()) { str += "</tr><td>" + reader.GetValue(0) + "</td>"; str += "<td>" + reader.GetString(1) + "</td>"; str += "<td>" + reader.GetString(2) + "</td>"; str += "<td>" + reader.GetValue(3) + "</td>"; str += "<td>" + reader.GetValue(4) + "</td>"; str += "<td>" + reader.GetValue(5) + "</td></tr>"; } str += "</table>"; traindiv.InnerHtml = str; con.Close(); }
protected void Button2_Click(object sender, EventArgs e) { con = new SQLiteConnection("Data Source=e:\\db\\project.s3db"); con.Open(); SQLiteCommand command = new SQLiteCommand("Select * from train", con); reader = command.ExecuteReader(); //SQLiteDataAdapter ad=new SQLiteDataAdapter(,con); //GridView gv = new GridView(); while (reader.Read()) { trainno_list.Items.Add(reader.GetValue(0).ToString()); } bookdiv.Style.Value = "visibility:visible"; con.Close(); }
public static object ReadBoolean(SQLiteDataReader reader, int idx) { try { object val = reader.GetValue(idx); if (val == null) return false; if (val is int) return (int)val == 1; return val.ToString() == "1"; } catch (Exception) { return false; } }
public static object ReadFloat(SQLiteDataReader reader, int idx) { try { object val = reader.GetValue(idx); if (val == null) return 0; string sval = val.ToString(); if (String.IsNullOrEmpty(sval)) return 0; return Single.Parse(sval, System.Globalization.CultureInfo.InvariantCulture); } catch (Exception) { return 0; } }
public static object ReadDateTime(SQLiteDataReader reader, int idx) { try { object val = reader.GetValue(idx); if (val == null) return new DateTime(1970, 1, 1); if (val is DateTime) return val; string sval = val.ToString(); if (String.IsNullOrWhiteSpace(sval)) return new DateTime(1970, 1, 1); return DateTime.Parse(sval); } catch (Exception) { return new DateTime(1970, 1, 1); } }
void ReadHeader() { SQLiteCommand command = _connection.CreateCommand(); command.CommandText = "SELECT key, value FROM header;"; _reader = command.ExecuteReader(); while (_reader.Read()) { var key = _reader.GetString(0); var value = _reader.GetValue(1); if (key.ToLower() == "clientbuild") { int build; if (int.TryParse(value.ToString(), out build)) SetBuild(build); break; } } _reader.Close(); }
public List <T> TableAsList <T>() where T : new() { var map = GetMapping(typeof(T)); var str = typeof(T).Name; var query = "select * from " + str; #if !RUNINSERVER var connection = _connection; connection.CommandText = query; connection.Prepare(); var n = connection.GetColumnCont(); var list = new List <T>(n); try { var cols = new TableMapping.Column[n]; for (var i = 0; i < n; i++) { var name = connection.ColumnName16(i); var col = map.FindColumn(name); cols[i] = col; if (col == null) { Debug.LogError("table:" + str + " ,col:" + name + " is Null"); throw new Exception(); } } var t = map.MappedType; while (connection.Step() == SQLite3.Result.Row) { var obj = Activator.CreateInstance(t); for (var i = 0; i < n; i++) { var col = cols[i]; var val = connection.ReadCol(i, col.ColumnType); col.FieldInfo.SetValue(obj, val); } list.Add((T)obj); } } catch (Exception e) { Debug.LogErrorFormat("{0}\n : {1}", str, e); } finally { connection.FinalizeStmt(); } return(list); #else System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.Connection = _connection; cmd.CommandText = query; //cmd.Prepare(); Debug.Log("query:" + query); System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); int n = reader.FieldCount; Debug.Log("FieldCount:" + n + " reader.StepCount:" + reader.StepCount); //cmd. List <T> list = new List <T>(n); try { var cols = new TableMapping.Column[n]; for (int i = 0; i < cols.Length; i++) { var name = reader.GetName(i); cols[i] = map.FindColumn(name); //if (cols[i] == null) Debug.LogError("table:" + typeof(T).Name + " ,col:" + name + " " + cols[i].Name); } //for (int j=0;j<reader.StepCount;j++) while (reader.Read()) { //Debug.Log("int read:"+ cols.Length); var obj = Activator.CreateInstance(map.MappedType); for (int i = 0; i < cols.Length; i++) { //if (cols[i] == null) // continue; var colType = reader.GetFieldType(i); object val = reader.GetValue(i);; // _connection.ReadCol(i, colType, cols[i].ColumnType); //Debug.Log("val:" + i + " " + val); if (colType == typeof(double)) { //val = 2.5f;//reader.GetFloat(i); float vv = (float)((double)val); cols[i].FieldInfo.SetValue(obj, vv); } else { cols[i].FieldInfo.SetValue(obj, val); } } list.Add((T)obj); } } catch (Exception e) { Debug.LogErrorFormat("{0}\n : {1}", typeof(T).Name, e); } finally { //_connection.Close(); } return(list); #endif }
private static ServerData ResultToServerData(SQLiteDataReader result) { ServerData serverToReturn = null; Guid id = result.GetGuid(0); string name = result.GetString(1); string description = result.GetString(2); string location = result.GetString(3); byte[] imageBytes = (byte[])result.GetValue(4); SensorDescriptionsData sensorData = new SensorDescriptionsData(); SensorDefinition[] sensor = new SensorDefinition[4] { new SensorDefinition(), new SensorDefinition(), new SensorDefinition(), new SensorDefinition()}; for (int s = 0; s < 4; s++) { // Do 4 sensor descriptions and 4 fields for each sensor[s].Name = (string)SQLHelper.ValueIfNull(result, s + 5, ""); sensor[s].Unit = (string)SQLHelper.ValueIfNull(result, s + 9, ""); sensor[s].ID = Convert.ToInt16(SQLHelper.ValueIfNull(result, s + 13, 255)); sensor[s].Range = Convert.ToInt16(SQLHelper.ValueIfNull(result, s + 17, 255)); sensorData.Add(sensor[s]); } serverToReturn = new ServerData(id, name, location, description, sensorData, imageBytes); return serverToReturn; }
// // GetUserFromReader // A helper function that takes the current row from the SQLiteDataReader // and hydrates a MembershipUser from the values. Called by the // MembershipUser.GetUser implementation. // private MembershipUser GetUserFromReader(SQLiteDataReader reader) { if (reader.GetString(1)=="") return null; object providerUserKey=null; string strGooid=Guid.NewGuid().ToString(); if (reader.GetValue(0).ToString().Length > 0) providerUserKey = new Guid(reader.GetValue(0).ToString()); else providerUserKey = new Guid(strGooid); string username = reader.GetString(1); string email = reader.GetString(2); string passwordQuestion = ""; if (reader.GetValue(3) != DBNull.Value) passwordQuestion = reader.GetString(3); string comment = ""; if (reader.GetValue(4) != DBNull.Value) comment = reader.GetString(4); bool tmpApproved = (reader.GetValue(5) == null); bool isApproved=false; if(tmpApproved) isApproved = reader.GetBoolean(5); bool tmpLockedOut = (reader.GetValue(6) == null); bool isLockedOut = false; if(tmpLockedOut) isLockedOut = reader.GetBoolean(6); DateTime creationDate = DateTime.Now; try { if (reader.GetValue(6) != DBNull.Value) creationDate = reader.GetDateTime(7); } catch { } DateTime lastLoginDate = DateTime.Now; try { if (reader.GetValue(8) != DBNull.Value) lastLoginDate = reader.GetDateTime(8); } catch { } DateTime lastActivityDate = DateTime.Now; try { if (reader.GetValue(9) != DBNull.Value) lastActivityDate = reader.GetDateTime(9); } catch { } DateTime lastPasswordChangedDate = DateTime.Now; try { if (reader.GetValue(10) != DBNull.Value) lastPasswordChangedDate = reader.GetDateTime(10); } catch { } DateTime lastLockedOutDate = DateTime.Now; try { if (reader.GetValue(11) != DBNull.Value) lastLockedOutDate = reader.GetDateTime(11); } catch { } MembershipUser u = new MembershipUser(this.Name, username, providerUserKey, email, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate); return u; }
public static void ReadValue(SQLiteDataReader reader, int index, TypeStorage type, ICdlValueWriter writer) { switch (type) { case TypeStorage.Boolean: writer.SetBoolean(reader.GetInt32(index) != 0); break; case TypeStorage.Byte: writer.SetByte((byte) reader.GetInt32(index)); break; case TypeStorage.Int16: writer.SetInt16((short) reader.GetInt32(index)); break; case TypeStorage.Int32: writer.SetInt32((int) reader.GetInt32(index)); break; case TypeStorage.Int64: writer.SetInt64((long) reader.GetInt64(index)); break; case TypeStorage.SByte: writer.SetSByte((sbyte) reader.GetInt32(index)); break; case TypeStorage.UInt16: writer.SetUInt16((ushort) reader.GetInt32(index)); break; case TypeStorage.UInt32: writer.SetUInt32((uint) reader.GetInt32(index)); break; case TypeStorage.UInt64: writer.SetUInt64((ulong) reader.GetInt64(index)); break; case TypeStorage.DateTime: writer.SetDateTime(DateTime.Parse(reader.GetString(index), CultureInfo.InvariantCulture)); //writer.SetDateTime(DateTime.ParseExact(reader.GetString(index), "s", CultureInfo.InvariantCulture)); break; case TypeStorage.DateTimeEx: writer.SetDateTimeEx(DateTimeEx.ParseNormalized(reader.GetString(index))); break; case TypeStorage.DateEx: writer.SetDateEx(DateEx.ParseNormalized(reader.GetString(index))); break; case TypeStorage.TimeEx: writer.SetTimeEx(TimeEx.ParseNormalized(reader.GetString(index))); break; case TypeStorage.Decimal: { var dtype = reader.GetFieldType(index); decimal value; if (dtype == typeof (string)) { value = Decimal.Parse(reader.GetString(index), CultureInfo.InvariantCulture); } else { value = (decimal) reader.GetDouble(index); } writer.SetDecimal(value); } break; case TypeStorage.Float: writer.SetFloat((float) reader.GetDouble(index)); break; case TypeStorage.Double: writer.SetDouble((double) reader.GetDouble(index)); break; case TypeStorage.String: writer.SetString(reader.GetString(index)); break; case TypeStorage.Guid: writer.SetGuid(new Guid(reader.GetString(index))); break; case TypeStorage.ByteArray: writer.SetByteArray((byte[]) reader.GetValue(index)); break; case TypeStorage.Null: writer.SetNull(); break; default: throw new Exception("DBSH-00167 Unsupported field type:" + type.ToString()); } }
//read data from database and add to drawParameterList public List<Coordinates.Coordinate> readTrackData(int trackId) { StringBuilder query; dbCoordinatesList = new List<Coordinates.Coordinate>(); try { query = new StringBuilder(); query.Append("SELECT DISTINCT longitude, latitude, altitude, fix FROM gpsData WHERE trackId = " + Int64.Parse(trackId.ToString()) + " order by ID ASC ;"); createSqliteConn(); dataReader = readData(query); while(dataReader.Read()) { dbCoordinatesList.Add(new Coordinates.Coordinate(new Geometry.Point( float.Parse(dataReader.GetString(1).ToString()), float.Parse(dataReader.GetString(0).ToString()), float.Parse(dataReader.GetValue(2).ToString().Contains(",") ? dataReader.GetValue(2).ToString() : dataReader.GetValue(2).ToString() + ",0")), true ));//(latitude, longitude, altitude) } closeSqliteConn(); } catch (SQLiteException sqliteEx) { MessageBox.Show("m2" + sqliteEx.Message); } return dbCoordinatesList; }
//Reader数据封装成list private List<DealListEntity> Package(SQLiteDataReader reader) { List<DealListEntity> DLEL; DLEL = new List<DealListEntity>(); DealListEntity DLE; if (reader.HasRows) { while (reader.Read()) { DLE.deal = Convert.ToInt32(reader.GetValue(0)); DLE.name = reader.GetValue(1).ToString(); DLE.id = reader.GetValue(2).ToString(); DLE.date = Convert.ToDateTime(reader.GetValue(3)); DLE.type = reader.GetValue(4).ToString(); DLE.money = Convert.ToDouble(reader.GetValue(5)); DLE.number = Convert.ToInt32(reader.GetValue(6)); DLE.taxrate = Convert.ToDouble(reader.GetValue(7)); DLE.commission = Convert.ToDouble(reader.GetValue(8)); DLE.explain = reader.GetValue(9).ToString(); DLE.remark = reader.GetValue(10).ToString(); DLEL.Add(DLE); } } return DLEL; }
//Reader数据封装成list private List<HistoryStockHoldEntity> Package(SQLiteDataReader reader) { List<HistoryStockHoldEntity> HSHEL = new List<HistoryStockHoldEntity>(); HistoryStockHoldEntity HSHE; if (reader.HasRows) { while (reader.Read()) { HSHE.id = reader.GetValue(0).ToString(); HSHE.date = Convert.ToDateTime(reader.GetValue(1)); HSHE.number = Convert.ToInt32(reader.GetValue(2)); HSHE.change = Convert.ToInt32(reader.GetValue(3)); HSHE.money = Convert.ToDouble(reader.GetValue(4)); HSHEL.Add(HSHE); } } return HSHEL; }
// 输出 RML 格式的表格 // 本函数负责写入 <table> 元素 // parameters: // nTopLines 顶部预留多少行 public void OutputRmlTable( Report report, SQLiteDataReader table, XmlTextWriter writer, int nMaxLines = -1) { // StringBuilder strResult = new StringBuilder(4096); int i, j; #if NO if (nMaxLines == -1) nMaxLines = table.Count; #endif writer.WriteStartElement("table"); writer.WriteAttributeString("class", "table"); writer.WriteStartElement("thead"); writer.WriteStartElement("tr"); int nEvalCount = 0; // 具有 eval 的栏目个数 for (j = 0; j < report.Count; j++) { PrintColumn column = (PrintColumn)report[j]; if (column.Colspan == 0) continue; if (string.IsNullOrEmpty(column.Eval) == false) nEvalCount++; writer.WriteStartElement("th"); if (string.IsNullOrEmpty(column.CssClass) == false) writer.WriteAttributeString("class", column.CssClass); if (column.Colspan > 1) writer.WriteAttributeString("colspan", column.Colspan.ToString()); writer.WriteString(column.Title); writer.WriteEndElement(); // </th> } writer.WriteEndElement(); // </tr> writer.WriteEndElement(); // </thead> // 合计数组 object[] sums = null; // 2008/12/1 new changed if (report.SumLine) { sums = new object[report.Count]; for (i = 0; i < sums.Length; i++) { sums[i] = null; } } NumberFormatInfo nfi = new CultureInfo("zh-CN", false).NumberFormat; nfi.NumberDecimalDigits = 2; writer.WriteStartElement("tbody"); // Jurassic.ScriptEngine engine = null; if (nEvalCount > 0 && engine == null) { engine = new Jurassic.ScriptEngine(); engine.EnableExposedClrTypes = true; } // 内容行循环 for (i = 0; ; i++) // i < Math.Min(nMaxLines, table.Count) { if (table.HasRows == false) break; // Line line = table[i]; if (engine != null) engine.SetGlobalValue("reader", table); string strLineCssClass = "content"; #if NO if (report.OutputLine != null) { OutputLineEventArgs e = new OutputLineEventArgs(); e.Line = line; e.Index = i; e.LineCssClass = strLineCssClass; report.OutputLine(this, e); if (e.Output == false) continue; strLineCssClass = e.LineCssClass; } #endif // strResult.Append("<tr class='" + strLineCssClass + "'>\r\n"); writer.WriteStartElement("tr"); writer.WriteAttributeString("class", strLineCssClass); // 列循环 for (j = 0; j < report.Count; j++) { PrintColumn column = (PrintColumn)report[j]; if (column.ColumnNumber < -1) { throw (new Exception("PrintColumn对象ColumnNumber列尚未初始化,位置" + Convert.ToString(j))); } string strText = ""; if (column.ColumnNumber != -1) { if (string.IsNullOrEmpty(column.Eval) == false) { // engine.SetGlobalValue("cell", line.GetObject(column.ColumnNumber)); strText = engine.Evaluate(column.Eval).ToString(); } else if (column.DataType == DataType.PriceDouble) { if (table.IsDBNull(column.ColumnNumber /**/) == true) strText = column.DefaultValue; else { double v = table.GetDouble(column.ColumnNumber); /* NumberFormatInfo provider = new NumberFormatInfo(); provider.NumberDecimalDigits = 2; provider.NumberGroupSeparator = "."; provider.NumberGroupSizes = new int[] { 3 }; strText = Convert.ToString(v, provider); * */ strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.PriceDecimal) { if (table.IsDBNull(column.ColumnNumber) == true) strText = column.DefaultValue; else { decimal v = table.GetDecimal(column.ColumnNumber); strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.PriceDecimal) { if (table.IsDBNull(column.ColumnNumber) == true) strText = column.DefaultValue; else { decimal v = table.GetDecimal(column.ColumnNumber); strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.Price) { // Debug.Assert(false, ""); if (table.IsDBNull(column.ColumnNumber) == true) strText = column.DefaultValue; // 2005/5/26 else strText = table.GetString(column.ColumnNumber); // } else strText = table.GetString(column.ColumnNumber/*, column.DefaultValue*/); } else { strText = table.GetString(0); // line.Entry; } writer.WriteStartElement(j == 0 ? "th" : "td"); if (string.IsNullOrEmpty(column.CssClass) == false) writer.WriteAttributeString("class", column.CssClass); writer.WriteString(strText); writer.WriteEndElement(); // </td> if (report.SumLine == true && column.Sum == true && column.ColumnNumber != -1) { try { // if (column.DataType != DataType.Currency) { object v = table.GetValue(column.ColumnNumber); #if NO if (report.SumCell != null) { SumCellEventArgs e = new SumCellEventArgs(); e.DataType = column.DataType; e.ColumnNumber = column.ColumnNumber; e.LineIndex = i; e.Line = line; e.Value = v; report.SumCell(this, e); if (e.Value == null) continue; v = e.Value; } #endif if (sums[j] == null) sums[j] = v; else { sums[j] = AddValue(column.DataType, sums[j], v); // sums[j] = ((decimal)sums[j]) + v; } } } catch (Exception ex) // 俘获可能因字符串转换为整数抛出的异常 { throw new Exception("在累加 行 " + i.ToString() + " 列 " + column.ColumnNumber.ToString() + " 值的时候,抛出异常: " + ex.Message); } } } // strResult.Append("</tr>\r\n"); writer.WriteEndElement(); // </tr> }
private static Tag Parse(SQLiteDataReader dr) { Tag tag = new Tag { Id = dr.GetInt32(0), Description = dr.GetString(1) }; object idTagTypeObj = dr.GetValue(2); if (idTagTypeObj != DBNull.Value) { tag.TagType = new TagType { Id = dr.GetInt32(2), Description = dr.GetString(3) }; } return tag; }
public static object ReadString(SQLiteDataReader reader, int idx) { try { object val = reader.GetValue(idx); if (val == null) return ""; string sval = val.ToString(); return String.IsNullOrWhiteSpace(sval) ? String.Empty : sval; } catch (Exception) { return ""; } }
public DataSet DataReader2DataSet ( SQLiteDataReader reader ) { DataSet dataSet = new DataSet ( ); do { // Create new data table DataTable schemaTable = reader.GetSchemaTable ( ); DataTable dataTable = new DataTable ( ); if ( schemaTable != null ) { // A query returning records was executed for ( int i = 0 ; i < schemaTable.Rows.Count ; i++ ) { DataRow dataRow = schemaTable.Rows [ i ]; // Create a column name that is unique in the data table string columnName = ( string ) dataRow [ "ColumnName" ]; // Add the column definition to the data table DataColumn column = new DataColumn ( columnName , ( Type ) dataRow [ "DataType" ] ); dataTable.Columns.Add ( column ); } dataSet.Tables.Add ( dataTable ); // Fill the data table we just created while ( reader.Read ( ) ) { DataRow dataRow = dataTable.NewRow ( ); for ( int i = 0 ; i < reader.FieldCount ; i++ ) dataRow [ i ] = reader.GetValue ( i ); dataTable.Rows.Add ( dataRow ); } } else { // No records were returned DataColumn column = new DataColumn ( "RowsAffected" ); dataTable.Columns.Add ( column ); dataSet.Tables.Add ( dataTable ); DataRow dataRow = dataTable.NewRow ( ); dataRow [ 0 ] = reader.RecordsAffected; dataTable.Rows.Add ( dataRow ); } } while ( reader.NextResult ( ) ); return dataSet; }
private static File ParseWithReferences(SQLiteDataReader dr) { if (!dr.Read()) return null; File file = Parse(dr); List<Tag> tags = new List<Tag>(); dr.NextResult(); while (dr.Read()) { Tag tag; tags.Add(tag = new Tag { Id = dr.GetInt32(0), Description = dr.GetString(1) }); object idTagTypeObj = dr.GetValue(2); if (idTagTypeObj != DBNull.Value) { tag.TagType = new TagType { Id = dr.GetInt32(2), Description = dr.GetString(3) }; } } file.Tags = tags; return file; }
/// <summary> /// Analyses the SQLiteDataReader passed to it, filling the /// Rows collection using the column name and it's respective value /// for each row returned. /// </summary> /// <param name="reader">The SQLiteDataReader object containing the results of a query.</param> public void Analyse(SQLiteDataReader reader) { int currentRow = 0; while (reader.Read()) { if (reader.HasRows) { Dictionary<string, object> columns = new Dictionary<string, object>(); for (int i = 0; i < reader.FieldCount; i++) { columns.Add(reader.GetName(i), reader.GetValue(i)); } this.Rows.Add(currentRow, columns); } currentRow++; } }
/// <summary> /// Reads the specified reader. /// </summary> /// <param name="reader">The reader.</param> /// <returns></returns> private IEnumerable<IEnumerable<object>> Read(SQLiteDataReader reader) { while (reader.Read()) { var row = new object[reader.FieldCount]; for (var i = 0; i < reader.FieldCount; i++) { row[i] = reader.GetValue(i); if (reader.IsDBNull(i)) row[i] = ""; } yield return row; } }
private CrashReport MakeCrashReport(SQLiteDataReader reader) { Guid g = reader.GetGuid(0); //Guid.Parse(reader.GetString(0))); long l = reader.GetInt64(1); string s1 = reader.GetString(2); string s2 = reader.GetString(3); string s3 = reader.GetString(4); string s4 = reader.GetString(5); Version v = Version.Parse(reader.GetString(6)); string s6 = reader.GetValue(7) as string; //Not GetString as GetString throws an invalidCast string s7 = reader.GetValue(8) as string; //(or another exception, cannot rmbr) string userstory = reader.GetValue(9) as string; //If the value is null, and this and following fields string trace = reader.GetValue(10) as string; //can be null as they aren't set before zip is downloaded return new CrashReport(innerApi, g, l, s1, s2, s3, s4, v, s6, s7, trace, userstory); }
public static QueryRow Read(SQLiteDataReader reader) { var result = new QueryRow(); reader.GetValues(); for (var i = 0; i < reader.FieldCount; i++) { result.queryDefinition.Fields.Add( reader.GetName(i), new FieldDefinition( reader.GetName(i), reader.GetFieldType(i).ToSqlDbType(), false, false ) ); result.values.Add( reader.GetName(i), reader.GetValue(i) ); } return result; }