/// <summary> /// Inicializa una nueva instancia de la clase <see cref="SelectionResult"/>. /// </summary> /// <param name="reader">El lector de datos de SQLite.</param> /// <param name="columnIndex">El indice de la columna a leer.</param> public SelectionResult(SQLiteDataReader reader, int columnIndex) { this.ColumnName = reader.GetName(columnIndex); this.Value = reader[columnIndex]; this.DotNetType = reader.GetFieldType(columnIndex); this.SQLiteType = reader.GetDataTypeName(columnIndex); }
/* * Parameterized SQL Statement * Change other methods to look like this */ static public List <object> GetTuple(string tableName, string returnColumn, string[] column, object[] values) { var returnObj = new List <object>(); string type; int numberOfColumns = column.Length; SQLiteConnection connection = new SQLiteConnection(Constants.connectionString); SQLiteCommand cmd = connection.CreateCommand(); var commandText = new System.Text.StringBuilder(); commandText.Append("SELECT " + returnColumn + " FROM " + tableName + " WHERE "); for (int i = 0; i < numberOfColumns; i++) { commandText.Append(column[i] + " = @" + column[i] + " AND "); cmd.Parameters.AddWithValue("@" + column[i], values[i]); } commandText.Remove(commandText.Length - 4, 4); cmd.CommandText = commandText.ToString() + " COLLATE NOCASE"; Common.DebugWriteLine(debug, "Db.GetTuple(" + cmd.CommandText + " )"); connection.Open(); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { type = Regex.Match(reader.GetDataTypeName(i), @"(\w+)").Groups[1].Value; Common.DebugWriteLine(debug, " Value " + i + "=" + reader.GetValue(i).ToString()); switch (type) { case "INTEGER": //Console.WriteLine("found int"); returnObj.Add(reader.GetValue(i).ToString()); //bool notNumeric = !int.TryParse(lockNumber, out int n); break; case "DATE": //Console.WriteLine("found date"); //returnString.Add(reader.GetValue(i).ToString()); break; case "VARCHAR": returnObj.Add(reader.GetValue(i).ToString()); //Console.WriteLine("type = " + type); break; } //returnString.Add(reader.GetValue(i)); //returnString.Add(reader.GetValue(i).ToString()); //Console.WriteLine("returnString = " + returnString[i]); } } } connection.Close(); return(returnObj); }
private void TableSelectedCommandWorker(String tableName) { String sql = "select * from " + tableName; SQLiteDataReader reader = dbHelper.ExecuteReader(sql); this.TableColumns.Clear(); int fieldCount = reader.FieldCount; for (int i = 0; i < fieldCount; i++) { var columnInfo = new ColumnInfo { HeaderText = reader.GetName(i), DisplayMemberPath = String.Format("[{0}]", i), }; this.TableColumns.Add(columnInfo); } this.TableDataItems.Clear(); while (reader.Read()) { List <Object> rowData = new List <Object>(); for (int i = 0; i < fieldCount; i++) { String data = null; if (!reader.IsDBNull(i)) { String typeName = reader.GetDataTypeName(i); if (typeName == "TEXT") { data = Decode(reader.GetString(i)); } else if (typeName == "BLOB") { byte[] bytes = new byte[reader.GetBytes(i, 0, null, 0, int.MaxValue)]; reader.GetBytes(i, 0, bytes, 0, bytes.Length); data = Decode(bytes); } else { data = reader.GetValue(i).ToString(); } } rowData.Add(data); } this.TableDataItems.Add(rowData); } reader.Close(); }
public string GetAllData(string sql) { SQLiteConnection conn = DBInitialize.GetDBConnection(); StringBuilder sb = new StringBuilder(); string result = ""; conn.Open(); try { SQLiteCommand cmd = new SQLiteCommand(); cmd.CommandText = sql; cmd.Connection = conn; SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { string dbType = reader.GetDataTypeName(i); switch (dbType) { case "DATE": sb = sb.Append(reader.GetDateTime(i).ToString("yyyy-MM-dd")).Append(","); break; default: sb = sb.Append(reader.GetValue(i)).Append(","); break; } } sb.Append("\t"); } result = sb.ToString().ToString(); } } catch (SQLiteException e) { Log.Write(e.Message); } finally { conn.Close(); conn.Dispose(); } return(result); }
private static Dictionary <string, object> LoadFromDataReader(SQLiteDataReader sdr) { if (!sdr.HasRows) { return(null); } Dictionary <string, object> result = new Dictionary <string, object>(); for (int index = 0; index < sdr.FieldCount; index++) { var name = sdr.GetName(index); var type = sdr.GetDataTypeName(index); var value = sdr.GetValue(index); result.Add(name, value); } return(result); }
/// <inheritdoc /> public string GetDataTypeName(int i) { ThrowIfNoReader(); return(_reader.GetDataTypeName(i)); }
/// <summary> /// /// </summary> /// <param name="column"></param> /// <returns></returns> public string GetDataTypeName(int column) { return(currentReader.GetDataTypeName(column)); }
public void fillGrid(SQLiteDataReader objReader, int ColStart, ref DataGridView Grid, Graphics objGraphics) { clsFormat objFormat = new clsFormat(); int[] colW = new int[objReader.FieldCount]; //Setting rows and columns Grid.Columns.Clear(); Grid.Rows.Clear(); int Row = 0; Grid.ColumnCount = objReader.FieldCount; //Adding Data while (objReader.Read()) { SizeF objSize; Grid.Rows.Add(); for (int Col = 0; Col < objReader.FieldCount; Col++) { if (Row == 0) { string strHeader = objReader.GetName(Col); Grid.Columns[Col].HeaderText = objReader.GetName(Col); objSize = objGraphics.MeasureString(objReader.GetName(Col), Grid.Font); if (Col >= ColStart) { colW[Col] = (int)objSize.Width; } } string strValue = ""; if (objReader.GetDataTypeName(Col) == "Time") { strValue = objFormat.convertTime_12hr(objReader.GetDateTime(Col)); } else { strValue = objReader[Col].ToString(); } objSize = objGraphics.MeasureString(strValue.Trim(), Grid.Font); if (objSize.Width > colW[Col] && Col > 0) { if (Col >= ColStart) { colW[Col] = (int)objSize.Width; } } Grid[Col, Row].Value = strValue; } Row++; } // grdActivity.AutoResizeColumns(); AdjustGrid(colW, Grid.Columns.Count - ColStart, ColStart, ref Grid); if (Grid.RowCount > 0) { Grid.Rows[0].Selected = false; } }
static public List <object> GetTupleNewOne(string sqlStatement) { var returnObj = new List <object>(); string type; SQLiteConnection connection = new SQLiteConnection(Constants.connectionString); SQLiteCommand cmd = connection.CreateCommand(); cmd.CommandText = sqlStatement; Common.DebugWriteLine(debug, "Db.GetTuple(" + cmd.CommandText + " )"); connection.Open(); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { type = Regex.Match(reader.GetDataTypeName(i), @"(\w+)").Groups[1].Value; switch (type) { case "INTEGER": //Console.WriteLine("found int"); returnObj.Add(reader.GetValue(i).ToString()); //bool notNumeric = !int.TryParse(lockNumber, out int n); break; case "DATE": //Console.WriteLine("found date"); //const string FMT = "O"; //DateTime? value = (DateTime?)resultSet["myDateColumn"]; //string strDate = now1.ToString(FMT); //var lmnop = reader.GetValue(i); //string mydate= Convert.ToDateTime(reader.GetDateTime(i)).ToShortDateString().ToString(); //string mydate2 = DateTime.ParseExact(reader.GetValue(i).ToShortDateString(), "M/d/yyyy", CultureInfo.InvariantCulture); //DateTime? jj = (DateTime?)reader.GetDateTime(i); /*returnObj.Add(Convert.ToDateTime(reader.GetValue(i).ToString())); //String was not recognized as a valid DateTime. See ImportScheule * StartTime = Convert.ToDateTime(tuple[i * 12 + 10].ToString()),*/ returnObj.Add("Date = *"); //returnString.Add(reader.GetValue(i).ToString()); break; case "VARCHAR": returnObj.Add(reader.GetValue(i).ToString()); //Console.WriteLine("type = " + type); break; default: returnObj.Add(reader.GetValue(i).ToString()); //Console.WriteLine("type = " + type); break; } //returnString.Add(reader.GetValue(i)); //returnString.Add(reader.GetValue(i).ToString()); //Console.WriteLine("returnString = " + returnString[i]); } } } connection.Close(); return(returnObj); }
public static DbTableModel GetModel(string connectionStr, string tableName) { using var con = new SQLiteConnection(connectionStr); con.Open(); using var cmd = con.CreateCommand(); cmd.CommandText = $"SELECT * FROM {tableName}"; cmd.VerifyOnly(); SQLiteDataReader reader = cmd.ExecuteReader(); DbTableModel model = new DbTableModel(); for (int i = 0; i < reader.FieldCount; i++) { model.FieldsInfo.Add(new DbTableModel.FieldInfo(reader.GetName(i), SqliteDbConverter.GetTypeFromString(reader.GetDataTypeName(i)), false)); } while (reader.Read()) { model.Table.Add(new List <string>()); for (int i = 0; i < reader.FieldCount; i++) { if (model.FieldsInfo[i].FieldType == DbTableModel.allowedDataType.floatNumber) { model.Table[model.Table.Count - 1].Add(reader.GetFloat(i).ToString(CultureInfo.InvariantCulture)); } else { model.Table[model.Table.Count - 1].Add(reader.GetValue(i).ToString()); } } } return(model); }
void SqliteTest1() { WriteLog("Test Begins"); WriteLog("Creating a db in memory..."); string cs = "Data Source=:memory:"; var con = new SQLiteConnection(cs); con.Open(); WriteLog("Requiring SQLite Version..."); string stm = "SELECT SQLITE_VERSION()"; var cmd = new SQLiteCommand(stm, con); string version = cmd.ExecuteScalar().ToString(); WriteLog("SQLite version: " + version); cmd = new SQLiteCommand(con); cmd.CommandText = "DROP TABLE IF EXISTS cars"; cmd.ExecuteNonQuery(); cmd.CommandText = @"CREATE TABLE cars( id INTEGER PRIMARY KEY, name TEXT, price INT, code TEXT, x REAL )"; cmd.ExecuteNonQuery(); /* * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Audi',52642,'A')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Mercedes',57127,'A')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Skoda',9000,'C')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Volvo',29000,'A')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Bentley',350000,'B')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Citroen',21000,'B')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Hummer',41400,'C')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Volkswagen',21600,'A')"; * cmd.ExecuteNonQuery(); */ cmd.CommandText = "INSERT INTO cars(name, price,code,x) VALUES(@name, @price,@code,@x)"; cmd.Parameters.AddWithValue("@name", "BMW"); cmd.Parameters.AddWithValue("@price", 36600); cmd.Parameters.AddWithValue("@code", "E"); cmd.Parameters.AddWithValue("@x", "1.2"); cmd.Prepare(); cmd.ExecuteNonQuery(); WriteLog("Renew the table"); //stm = "SELECT code, sum(price) as summary FROM cars group by code LIMIT 5"; stm = "select * from cars limit 5"; cmd = new SQLiteCommand(stm, con); SQLiteDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { //Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}"); //var name = rdr.GetString(rdr.GetOrdinal("code")); //var price = rdr.GetDouble(rdr.GetOrdinal("summary")); //WriteLog("Result: name=" + name + " price=" + price); for (int i = 0; i < reader.FieldCount; i++) { WriteLog("Field: " + reader.GetName(i)); WriteLog("Type: " + reader.GetDataTypeName(i)); WriteLog("C#T: " + reader.GetFieldType(i)); WriteLog("Value: " + reader.GetValue(i)); } } }
/// <summary> /// 显示命令效果 /// </summary> /// <param name="reader"></param> public static void ShowData(SQLiteDataReader reader) { if (reader == null) { return; } bool result = false; while (reader.Read()) { result = true; _strShowBuilder.Clear(); _strShowBuilder.Append("命令执行成功: "); for (int i = 0; i < reader.FieldCount - 1; i++) { _strShowBuilder.Append("第" + i + "列:" + "(列名:" + reader.GetName(i) + " 类型:" + reader.GetDataTypeName(i) + ") " + reader.GetValue(i) + " | "); } int temp = reader.FieldCount - 1; _strShowBuilder.Append("第 " + temp + " 列:" + "(列名:" + reader.GetName(temp) + " 类型:" + reader.GetDataTypeName(temp) + ") " + reader.GetValue(temp)); Console.WriteLine(_strShowBuilder.ToString()); } if (result == false) { Console.WriteLine("该命令,没有获取任何数据"); } reader.Close(); }