/// <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();
}
