/// <summary>
/// Получение следующей записи, удовлетворяющей критерию, и удаление полученной записи из базы данных
/// </summary>
/// <param name="maskRecord"></param>
/// <param name="compare"></param>
/// <returns></returns>
public Record GetNext(Record maskRecord, string compare = "=")
{
var record = new Record();
Debug.WriteLine(ConnectionString);
try
{
Connection.Open();
using (SQLiteCommand command = Connection.CreateCommand())
{
string where = String.Join(" AND ",
maskRecord.GetType()
.GetProperties()
.Where(prop => !IsNullOrEmpty(prop.GetValue(maskRecord, null)))
.Select(prop => String.Format("{0}{1}@{0}", prop.Name, compare)));
command.CommandText = String.IsNullOrWhiteSpace(where)
? String.Format("SELECT * FROM {0} LIMIT 1", maskRecord.GetType().Name)
: String.Format("SELECT * FROM {0} WHERE {1} LIMIT 1", maskRecord.GetType().Name, where);
Debug.WriteLine(command.CommandText);
foreach (
PropertyInfo prop in
maskRecord.GetType()
.GetProperties()
.Where(prop => !IsNullOrEmpty(prop.GetValue(maskRecord, null)))
)
{
command.Parameters.Add(new SQLiteParameter(String.Format("@{0}", prop.Name),
prop.GetValue(maskRecord, null)));
}
SQLiteDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
{
throw new ObjectNotFoundException();
}
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
record.GetType()
.GetProperty(reader.GetName(i))
.GetValue(reader[i], null);
}
}
}
using (SQLiteCommand command = Connection.CreateCommand())
{
string where = String.Join(" AND ",
maskRecord.GetType()
.GetProperties()
.Where(prop => !IsNullOrEmpty(prop.GetValue(maskRecord, null)))
.Select(prop => String.Format("{0}{1}@{0}", prop.Name, compare)));
command.CommandText = String.IsNullOrWhiteSpace(where)
? String.Format("DELETE FROM {0}", maskRecord.GetType().Name)
: String.Format("DELETE FROM {0} WHERE {1}", maskRecord.GetType().Name, where);
Debug.WriteLine(command.CommandText);
foreach (
PropertyInfo prop in
maskRecord.GetType()
.GetProperties()
.Where(prop => !IsNullOrEmpty(prop.GetValue(maskRecord, null)))
)
{
command.Parameters.Add(new SQLiteParameter(String.Format("@{0}", prop.Name),
prop.GetValue(record, null)));
}
}
}
finally
{
Connection.Close();
}
return(record);
}
public static void Main()
{
/******************************************
* CREATE TABLE, CREATE DB CONNECTION
******************************************/
SQLiteConnection.CreateFile("MovieDatabase.sqlite");
SQLiteConnection MovieDatabaseConnection = new SQLiteConnection("Data Source=MovieDatabase.sqlite;Version=3;");
MovieDatabaseConnection.Open();
string sql = "CREATE TABLE MovieDatabase (title VARCHAR(20), movieType VARCHAR(20), numOfCopies INT)";
SQLiteCommand command = new SQLiteCommand(sql, MovieDatabaseConnection);
command.ExecuteNonQuery();
/******************************************
* CREATE INSTANCE OF CLASS
******************************************/
Movie newMovie = new Movie();
/******************************************
* ADD MOVIE TO DATABASE: NOT COMPLETE, 7/13/2019
******************************************/
/******************************************
* A = Add
* V = View
* S = Search
* E = Edit
* D = Delete
******************************************/
Console.WriteLine("Do you want to view your database or add a new movie? Press A for Add a Movie, V for View Movie Database, S for Search database, and D to Delete a database item.");
string userInput = Console.ReadLine();
using (SQLiteConnection conAdd = new SQLiteConnection(MovieDatabaseConnection))
if (userInput.ToUpper() == "A")
{
Console.WriteLine("What is the title of your movie?");
newMovie.Title = Console.ReadLine();
Console.WriteLine("What is your movie type? DVD, Bluray, or Digital?");
newMovie.MovieType = Console.ReadLine();
//have to convert int to string since NumOfCopies is an int in the Movie class
//convert movieCopies from string to int and return answer depending on number of copies owned
string movieCopies;
Console.WriteLine("How many copies do you have?");
movieCopies = Console.ReadLine();
//List<string> movieList = new List<string>();
int copies = newMovie.NumOfCopies;
if (!Int32.TryParse(movieCopies, out copies))
{
Console.WriteLine("Invalid data input. Only whole numbers accepted. Please try again.");
Main(); //is calling Main the best way?
//want to continue the loop but continue doesn't work here
}
else if (copies == 0)
{
Console.WriteLine("You have to enter a number greater than 1.");
Main(); //is calling Main the best way?
//want to continue the loop but continue doesn't work here
}
else if (copies == 1)
{
sql = "insert into MovieDatabase (title, movieType, numOfCopies) values ('" + newMovie.Title + "'" + ", '" + newMovie.MovieType + "'" + ", " + newMovie.NumOfCopies + ")";
command = new SQLiteCommand(sql, MovieDatabaseConnection);
command.ExecuteNonQuery();
Console.WriteLine(copies + " copy of " + newMovie.Title + " of type " + newMovie.MovieType + " has been added to your database.");
}
else
{
sql = "insert into MovieDatabase (title, movieType, numOfCopies) values ('" + newMovie.Title + "'" + ", '" + newMovie.MovieType + "'" + ", " + newMovie.NumOfCopies + ")";
command = new SQLiteCommand(sql, MovieDatabaseConnection);
command.ExecuteNonQuery();
Console.WriteLine(copies + " copies of " + newMovie.Title + " of type " + newMovie.MovieType + " has been added to your database.");
}
conAdd.Close();
Environment.Exit(0);
}
/******************************************
* SEARCH DATABASE
******************************************/
else if (userInput.ToUpper() == "S")
{
string cs = "Data Source=MovieDatabase.sqlite;Version=3;"; //TRYING TO FIND RELATIVE PATH
Console.WriteLine("Enter any movie title to see if its in your movie database: ");
newMovie.Title = Console.ReadLine();
//connection to MovieDatabase
using (SQLiteConnection con = new SQLiteConnection(cs))
{
con.Open();
//user can search database without having to type quotes for title (string value)
string stm = "SELECT * FROM MovieDatabase WHERE title = '" + newMovie.Title + "'";
// execute select statement
using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
//will write if statement showing this message only if movie is in database
//searching database for movie
for (int i = 0; i < rdr.FieldCount; i++)
{
//prints column name and result(s) to database
if (rdr.HasRows)
{
Console.WriteLine(newMovie.Title + " is in your database.");
Console.WriteLine(rdr.GetName(i) + ": " + rdr.GetValue(i));
}
else
{
Console.WriteLine(newMovie.Title + " is not in your database.");;
}
}
}
}
}
//close connection
con.Close();
}
}
else if (userInput.ToUpper() == "V")
{
string cs = "Data Source=MovieDatabase.sqlite;Version=3;"; //TRYING TO FIND RELATIVE PATH
/*Console.WriteLine("Enter any movie title to see if its in your movie database: ");
* newMovie.Title = Console.ReadLine();*/
//connection to MovieDatabase
using (SQLiteConnection con = new SQLiteConnection(cs))
{
con.Open();
//user can search database without having to type quotes for title (string value)
string stm = "SELECT * FROM MovieDatabase";
// execute select statement
using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
//will write if statement showing this message only if movie is in database
//searching database for movie
for (int i = 0; i < rdr.FieldCount; i++)
{
//prints column name and result(s) to database
Console.WriteLine("Database Results: ");
Console.WriteLine(rdr.GetName(i) + ": " + rdr.GetValue(i));
}
}
}
}
//close connection
con.Close();
}
}
else
{
Console.WriteLine("Exiting application.");
Console.ReadKey();
Environment.Exit(0);
}
/*Not sure if this is needed yet
* // Add items using Add method
* movieList.Add(newMovie.Title + ", " + newMovie.MovieType + ", " + copies.ToString());
* // Show items in list
* foreach (string movieItem in movieList)
* {
* Console.WriteLine(movieItem);
* }
*
*
*/
}
static Bet BetParser(SQLiteDataReader Reader)
{
string site = "";
Bet tmp = new Bet();
for (int i = 0; i < Reader.FieldCount; i++)
{
switch (Reader.GetName(i))
{
case "betid": tmp.Id = (long)Reader[i]; break;
case "date": tmp.date = (DateTime)Reader[i]; break;
case "stake": tmp.Amount = (decimal)Reader[i]; break;
case "profit": tmp.Profit = (decimal)Reader[i]; break;
case "chance": tmp.Chance = (decimal)Reader[i]; break;
case "high": tmp.high = (short)Reader[i] == 1; break;
case "lucky": tmp.Roll = (decimal)Reader[i]; break;
case "hash": tmp.serverhash = (string)Reader[i]; break;
case "nonce": tmp.nonce = (long)Reader[i]; break;
case "uid": tmp.uid = (int)Reader[i]; break;
case "Client": tmp.clientseed = (string)Reader[i]; break;
case "server": tmp.serverseed = (string)Reader[i]; break;
case "site": site = (string)Reader[i]; break;
}
}
if (!string.IsNullOrEmpty(tmp.serverseed) && !string.IsNullOrEmpty(tmp.clientseed) && tmp.Roll != -1 && site != "")
{
switch (site)
{
case "JustDice": tmp.Verified = tmp.Roll == (decimal)DiceSite.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "PrimeDice": tmp.Verified = tmp.Roll == (decimal)PD.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "999Dice": tmp.Verified = tmp.Roll == (decimal)dice999.sGetLucky(tmp.serverseed, (tmp.clientseed), (int)tmp.nonce, /*(long)(tmp.Roll*10000m),*/ tmp.serverhash); break;
case "SafeDice": tmp.Verified = tmp.Roll == (decimal)SafeDice.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "PRCDice": tmp.Verified = tmp.Roll == (decimal)PRC.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "RollinIO": tmp.Verified = tmp.Roll == (decimal)rollin.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "BitDice": tmp.Verified = tmp.Roll == (decimal)bitdice.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "BetterBets": tmp.Verified = tmp.Roll == (decimal)BB.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "MoneyPot": tmp.Verified = tmp.Roll == (decimal)moneypot.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "MoneroDice": tmp.Verified = tmp.Roll == (decimal)MoneroDice.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "FortuneJack": tmp.Verified = tmp.Roll == (decimal)FortuneJack.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "Coinichiwa": tmp.Verified = tmp.Roll == (decimal)Coinichiwa.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "CoinMillions": tmp.Verified = tmp.Roll == (decimal)CoinMillions.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "CryptoGames": tmp.Verified = tmp.Roll == (decimal)cryptogames.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "Bitsler": tmp.Verified = tmp.Roll == (decimal)Bitsler.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "Wealthydice": tmp.Verified = tmp.Roll == (decimal)WD.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
case "SatoshiDice": tmp.Verified = tmp.Roll == (decimal)SatoshiDice.sGetLucky(tmp.serverseed, tmp.clientseed, (int)tmp.nonce); break;
}
}
return(tmp);
}
/// <summary>
/// 通过sql语句返回导出CSV格式的string
/// </summary>
/// <param name="SQL">sql语句</param>
/// <param name="index">从第几列开始</param>
/// <param name="filePath">保存的路径</param>
/// <returns>错误信息</returns>
public static string ExportCSV(string SQL, int index, string filePath)
{
string errorMsg = "";
try
{
ConnString = string.Format(Config.ConnectionString, System.Windows.Forms.Application.StartupPath) + Config.DBName + ".db";
//采用边读边写的方式，防止内存溢出
int currentIndex = 0;
StreamWriter sw = new StreamWriter(filePath, false, Encoding.Default);
StringBuilder sb = new StringBuilder("");//要写入的数据
using (SQLiteConnection conn = new SQLiteConnection(ConnString))
{
SQLiteCommand cmd = new SQLiteCommand(SQL, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
SQLiteDataReader dr = cmd.ExecuteReader();
//表头
for (int count = index; count < dr.FieldCount; count++)
{
if (dr.GetName(count) != null)
{
sb.Append(dr.GetName(count));
}
if (count < dr.FieldCount - 1)
{
sb.Append(",");
}
}
sb.Append("\r\n");
//内容
string value = "";
while (dr.Read())
{
for (int col = index; col < dr.FieldCount; col++)
{
if (!dr.IsDBNull(col))
{
value = dr.GetValue(col).ToString().Trim().Replace("\"", "\"\"");
if (value.Contains(","))
{
sb.Append("\"" + value + "\"");
}
else
{
sb.Append(value);
}
}
//换行
if (col != dr.FieldCount - 1)
{
sb.Append(",");
}
}
sb.Append("\r\n");
currentIndex++;
if (currentIndex % 500 == 0)
{
sw.Write(sb.ToString());
sb = new StringBuilder("");
}
}
if (sb.ToString() != "")
{
sw.Write(sb.ToString());
sb = new StringBuilder("");
}
//释放变量
try
{
sw.Close();
dr.Dispose();
}
catch
{
}
}
}
catch (Exception e)
{
errorMsg = "导出失败，请再试一次或通过菜单中的【问题反馈】将问题报告给我们！\n" + e.Message;
}
return(errorMsg);
}
public IEnumerable <dynamic> ExecuteDynamic(string sql, List <string> parameters = null)
{
using (SQLiteCommand command = new SQLiteCommand(sql, Connection))
{
if (parameters != null || parameters.Count() > 0)
{
for (int index = 0; index < parameters.Count(); index++)
{
command.Parameters.Add(new SQLiteParameter(string.Format("p{0}", index), parameters[index]));
}
}
SQLiteDataReader reader = command.ExecuteReader();
int fieldCount = reader.VisibleFieldCount;
while (reader.Read())
{
dynamic expando = new ExpandoObject();
for (int idx = 0; idx < fieldCount; idx++)
{
((IDictionary <string, object>)expando).Add(new KeyValuePair <string, object>(reader.GetName(idx), reader[idx]));
}
yield return(expando);
}
reader.Dispose();
}
}
/// <summary>
/// Converts attributes from an <see cref="SQLiteDataReader"/> to an Entity
/// </summary>
/// <param name="table">The <see cref="TableMapping"/> for this Entity</param>
/// <param name="reader">The current, open DataReader object</param>
/// <returns></returns>
internal TEntity ConvertToEntity <TEntity>(TableMapping table, SQLiteDataReader reader)
{
// Use reflection to map the column name to the object Property
TEntity entity = (TEntity)Activator.CreateInstance(table.EntityType, new object[] { });
for (int i = 0; i < reader.FieldCount; ++i)
{
string attrName = reader.GetName(i);
PropertyInfo property = table.GetAttribute(attrName).Property;
if (property.PropertyType.IsEnum)
{
var value = Enum.Parse(property.PropertyType, reader.GetValue(i).ToString());
property.SetValue(entity, value);
}
else
{
// SQLite doesn't support nearly as many primitive types as
// C# does, so we must translate
switch (Type.GetTypeCode(property.PropertyType))
{
case TypeCode.Byte:
property.SetValue(entity, reader.GetByte(i));
break;
case TypeCode.Int16:
property.SetValue(entity, reader.GetInt16(i));
break;
case TypeCode.Int32:
property.SetValue(entity, reader.GetInt32(i));
break;
case TypeCode.Int64:
property.SetValue(entity, reader.GetInt64(i));
break;
case TypeCode.Boolean:
property.SetValue(entity, reader.GetBoolean(i));
break;
case TypeCode.Decimal:
property.SetValue(entity, reader.GetDecimal(i));
break;
case TypeCode.Double:
property.SetValue(entity, reader.GetDouble(i));
break;
case TypeCode.Char:
property.SetValue(entity, reader.GetChar(i));
break;
case TypeCode.DateTime:
if (!reader.IsDBNull(i))
{
property.SetValue(entity, reader.GetDateTime(i));
}
break;
default:
// Correct DBNull values
object val = reader.GetValue(i);
if (val is DBNull)
{
continue;
}
property.SetValue(entity, val);
break;
}
}
}
// Foreign keys!
table.CreateRelationships(entity, this);
// Add object
return(entity);
}
/// <summary>
/// Retrieve a list of objects for target type using reflection from the data table;
/// Type must have public properties; Property names are case insensitive;
/// Closes and disposed the reader
/// </summary>
public static List <Type> Unwrap <Type>(this SQLiteDataReader reader) where Type : new()
{
if (!reader.HasRows)
{
return(new List <Type>());
}
// Get column name mapping
Dictionary <string, string> columnNameMapping = new Dictionary <string, string>();
foreach (string column in Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetName(i)))
{
columnNameMapping.Add(column.Replace(" ", string.Empty), column); // Case-sensitive; Do notice though it cannot contain spaces
}
// Initialize objects from rows
List <Type> returnValues = new List <Type>();
while (reader.Read())
{
// Create a new instance of type
Type instance = new Type();
// Initialize properties of the instance
foreach (PropertyInfo prop in typeof(Type).GetProperties())
{
string propertyName = prop.Name;
if (columnNameMapping.ContainsKey(propertyName))
{
var value = reader[columnNameMapping[propertyName]];
if (value == DBNull.Value)
{
prop.SetValue(instance, null);
}
else
{
// Add handling for nullable type
System.Type t = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
object safeValue = (value == null) ? null : Convert.ChangeType(value, t);
prop.SetValue(instance, safeValue, null);
}
}
}
// Add to return
returnValues.Add(instance);
}
reader.Close();
return(returnValues);
}
/*
* Queries the entire database and populates the ListView
*/
private bool PopulateDatabaseList()
{
try
{
// Query database
SQLiteConnection SqlConn = new SQLiteConnection();
SqlConn.ConnectionString = String.Format("Data Source={0};New=False;Version=3", m_strDBFileName);
SqlConn.Open();
string strSQLQuery = "SELECT " +
"TAG_ACCESSION_NUMBER as [Accession Number]," +
"TAG_MODALITY as [Modality]," +
"TAG_INSTITUTION_NAME as [Institution Name]," +
"TAG_REFERRING_PHYSICIAN_NAME as [Referring Physician Name]," +
"TAG_PATIENT_NAME as [Patient Name]," +
"TAG_PATIENT_ID as [Patient ID]," +
"TAG_PATIENT_BIRTH_DATE as [Patient Birth Date]," +
"TAG_PATIENT_SEX as [Patient Sex]," +
"TAG_PATIENT_WEIGHT as [Patient Weight]," +
"TAG_STUDY_INSTANCE_UID AS [Study Instance UID]," +
"TAG_REQUESTING_PHYSICIAN AS [Requesting Physician]," +
"TAG_REQUESTED_PROCEDURE_DESCRIPTION AS [Requested Procedure Description]," +
"TAG_ADMISSION_ID AS [Admission ID]," +
"TAG_SCHEDULED_STATION_AE_TITLE AS [Scheduled Station AE Title]," +
"TAG_SCHEDULED_PROCEDURE_STEP_START_DATE AS [Scheduled Procedure Step Start Date]," +
"TAG_SCHEDULED_PROCEDURE_STEP_START_TIME AS [Scheduled Procedure Step Start Time]," +
"TAG_SCHEDULED_PERFORMING_PHYSICIAN_NAME AS [Scheduled Performing Physician Name]," +
"TAG_SCHEDULED_PROCEDURE_STEP_DESCRIPTION AS [Scheduled Procedure Step Description]," +
"TAG_SCHEDULED_PROCEDURE_STEP_ID AS [Scheduled Procedure Step ID]," +
"TAG_SCHEDULED_PROCEDURE_STEP_LOCATION AS [Scheduled Procedure Step Location]," +
"TAG_REQUESTED_PROCEDURE_ID AS [Requested Procedure ID]," +
"TAG_REASON_FOR_THE_REQUESTED_PROCEDURE AS [Reason for the Requested Procedure]," +
"TAG_REQUESTED_PROCEDURE_PRIORITY AS [Requested Procedure Priority]," +
"Item_ID " +
"FROM MwlSCPTbl ORDER BY Item_ID;";
SQLiteCommand cmd = SqlConn.CreateCommand();
cmd.CommandText = strSQLQuery;
SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
// Create the columns
ColumnHeader[] columnHeaders = new ColumnHeader[reader.FieldCount - 1]; //one less field because we don't care about displaying the Item_ID
Graphics g = lstDatabase.CreateGraphics();
for (int i = 0; i < columnHeaders.Length; i++)
{
columnHeaders[i] = new ColumnHeader();
columnHeaders[i].Text = reader.GetName(i);
columnHeaders[i].Width = Convert.ToInt32((g.MeasureString(columnHeaders[i].Text, lstDatabase.Font)).Width) + 10;
}
lstDatabase.Columns.AddRange(columnHeaders);
// Create the rows
while (reader.Read())
{
string[] items = new string[reader.FieldCount]; // We use all fields here, but Item_ID is hidden
for (int i = 0; i < reader.FieldCount; i++)
{
// SQLite stores dates as strings, so avoid the internal conversion
if (reader.GetFieldType(i).ToString() == "System.DateTime")
{
items[i] = reader.GetString(i);
}
else
{
items[i] = reader.GetValue(i).ToString();
}
}
lstDatabase.Items.Add(new ListViewItem(items));
}
SqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error populating listbox with database:\r\n\r\n" + ex.ToString());
return(false);
}
return(true);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dbLocation"></param>
/// <param name="dbName"></param>
/// <param name="selectSql"></param>
/// <returns></returns>
public static List <T> SelectBySql <T>(String dbLocation, String dbName, String selectSql)
{
var list = new List <T>();
SQLiteConnection dbConnection = null;
try
{
BindingFlags comm = BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static;
Type tbc = typeof(T);
FieldInfo[] fields = tbc.GetFields(comm);
// PropertyInfo[] propertyInfos = tbc.GetProperties(comm);
Dictionary <string, PropertyInfo> fieldDic = new Dictionary <string, PropertyInfo>();
object currentValue = null;
string currentField = null;
IEnumerable <TableFieldAttribute> enumerable = null;
for (int ind = 0; ind < fields.Length; ind++)
{
enumerable = fields[ind].GetCustomAttributes <TableFieldAttribute>();
foreach (TableFieldAttribute attr in enumerable)
{
if (attr != null && attr.ColumName != null && !"".Equals(attr.ColumName))
{
fieldDic.Add(attr.ColumName, tbc.GetProperty(UpperCaseFirst(fields[ind].Name)));
//table.Add(fieldInfos[i].Name, attr);
}
}
}
string dbPath = dbLocation + @"\" + dbName;
dbConnection = new SQLiteConnection("data source=" + dbPath);
if (dbConnection.State != System.Data.ConnectionState.Open)
{
dbConnection.Open();
}
SQLiteCommand sqlcmd = new SQLiteCommand(selectSql, dbConnection);//sql查询语句
sqlcmd.CommandTimeout = 120;
SQLiteDataReader reader = sqlcmd.ExecuteReader();
T entity;
object v = null;
while (reader.Read())
{
entity = Activator.CreateInstance <T>();
for (int j = 0; j < reader.FieldCount; j++)
{
currentValue = reader.GetValue(j);
currentField = reader.GetName(j);
//GetJAVAField(reader.GetName(j));
if (fieldDic.ContainsKey(currentField))
{
if (fieldDic[currentField].PropertyType.ToString().Contains("System.Nullable"))
{
v = Convert.ChangeType(currentValue, Nullable.GetUnderlyingType(fieldDic[currentField].PropertyType));
}
else
{
v = Convert.ChangeType(currentValue, fieldDic[currentField].PropertyType);
}
fieldDic[currentField].SetValue(entity, v, null);
}
}
list.Add(entity);
}
return(list);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (dbConnection != null)
{
dbConnection.Close();
}
}
return(list);
}
public void Handle()
{
bool flag;
List <Dictionary <string, object> > dictionaries = null;
int num = 0;
long lastInsertRowId = (long)0;
try
{
if (this.Connection == null)
{
throw new Exception("Connection is null");
}
this._connection = (SQLiteConnection)this.Connection.get_Con();
if (this._connection.State == ConnectionState.Closed)
{
this._connection.Open();
}
this._cmd = this._connection.CreateCommand();
this._cmd.CommandText = this.Sql.get_SQL();
Sql.AddParams(this._cmd, this.Sql.get_Arguments(), "@");
if (!this.NonQuery)
{
using (SQLiteDataReader sQLiteDataReader = this._cmd.ExecuteReader())
{
dictionaries = new List <Dictionary <string, object> >();
while (sQLiteDataReader.Read())
{
Dictionary <string, object> strs = new Dictionary <string, object>();
for (int i = 0; i < sQLiteDataReader.FieldCount; i++)
{
strs.Add(sQLiteDataReader.GetName(i), sQLiteDataReader.GetValue(i));
}
dictionaries.Add(strs);
}
}
}
else
{
num = this._cmd.ExecuteNonQuery();
}
lastInsertRowId = this._connection.LastInsertRowId;
this.Cleanup();
}
catch (Exception exception3)
{
Exception exception2 = exception3;
string str1 = "Sqlite handle raised an exception";
Connection connection3 = this.Connection;
if (connection3 != null)
{
flag = connection3.get_Plugin();
}
else
{
flag = false;
}
if (flag)
{
str1 = string.Concat(str1, string.Format(" in '{0} v{1}' plugin", this.Connection.get_Plugin().get_Name(), this.Connection.get_Plugin().get_Version()));
}
Interface.get_Oxide().LogException(str1, exception2);
this.Cleanup();
}
Interface.get_Oxide().NextTick(() => {
bool plugin;
Connection connection = this.Connection;
if (connection != null)
{
Plugin plugin1 = connection.get_Plugin();
if (plugin1 != null)
{
plugin1.TrackStart();
}
else
{
}
}
else
{
}
try
{
if (this.Connection != null)
{
this.Connection.set_LastInsertRowId(lastInsertRowId);
}
if (this.NonQuery)
{
Action <int> callbackNonQuery = this.CallbackNonQuery;
if (callbackNonQuery != null)
{
callbackNonQuery(num);
}
else
{
}
}
else
{
this.Callback(dictionaries);
}
}
catch (Exception exception1)
{
Exception exception = exception1;
string str = "Sqlite command callback raised an exception";
Connection connection1 = this.Connection;
if (connection1 != null)
{
plugin = connection1.get_Plugin();
}
else
{
plugin = false;
}
if (plugin)
{
str = string.Concat(str, string.Format(" in '{0} v{1}' plugin", this.Connection.get_Plugin().get_Name(), this.Connection.get_Plugin().get_Version()));
}
Interface.get_Oxide().LogException(str, exception);
}
Connection connection2 = this.Connection;
if (connection2 == null)
{
return;
}
Plugin plugin2 = connection2.get_Plugin();
if (plugin2 == null)
{
return;
}
plugin2.TrackEnd();
});
}
//creat new table in carrent profile
private void button_create_Click(object sender, EventArgs e)
{
SQLiteFactory factory = (SQLiteFactory)DbProviderFactories.GetFactory("System.Data.SQLite");
//connect to database
using (SQLiteConnection connection = (SQLiteConnection)factory.CreateConnection())
{
connection.ConnectionString = "Data Source = " + Work_Form.login + ".db3";
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "SELECT name FROM sqlite_master WHERE type='table'";
command.CommandType = CommandType.Text;
SQLiteDataReader reader = command.ExecuteReader();
Boolean flag_copy = false;
//searching table duplicate
while (reader.Read())
{
if (text_name.Text == $"{reader.GetString(0)}")
{
flag_copy = true;
break;
}
}
//if duplicate exist then show message and close window
//else create and show new table
if (flag_copy)
{
MessageBox.Show("Таблица с таким именем уже существует.");
reader.Close();
connection.Close();
Close();
Dispose();
}
else
{
reader.Close();
command.CommandText = "CREATE TABLE IF NOT EXISTS [" + text_name.Text + "] (" +
"[id] INTEGER PRIMARY KEY AUTOINCREMENT," +
"[name] TEXT," +
"[surname] TEXT," +
"[patronymic] TEXT," +
"[birthday] TEXT," +
"[address] TEXT," +
"[department] TEXT," +
"[about] TEXT" +
")";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
Work_Form.table = new DataTable();
DataColumn col;
command.CommandText = "SELECT * FROM [" + text_name.Text + "]";
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
for (int i = 1; i < reader.FieldCount; i++)
{
col = new DataColumn(reader.GetName(i));
Work_Form.table.Columns.Add(col);
}
reader.Close();
connection.Close();
MessageBox.Show("Таблица создана.");
Work_Form.table_name = text_name.Text; //current table = new table
Close();
Dispose();
}
}
}
}
// Sees to that only the needed number of textboxes show under "Add value" and that their labels are correct.
public void UpdateLables(string query)
{
using (SQLiteConnection connection = new SQLiteConnection(Db.connectionString))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(query, connection))
{
SQLiteDataReader reader = command.ExecuteReader();
mix.Clear();
// All the items added to a list.
mix.Add(new Mix(lblColumn1, tbxAddColumn1, new Point(20, 45)));
mix.Add(new Mix(lblColumn2, tbxAddColumn2, new Point(164, 45)));
mix.Add(new Mix(lblColumn3, tbxAddColumn3, new Point(307, 45)));
mix.Add(new Mix(lblColumn4, tbxAddColumn4, new Point(451, 45)));
mix.Add(new Mix(lblColumn5, tbxAddColumn5, new Point(594, 45)));
mix.Add(new Mix(lblTermFor, cbTermFor, new Point(20, 97)));
mix.Add(new Mix(lblGenus, cbGenus, new Point(164, 97)));
mix.Add(new Mix(lblRelation, cbRelation, new Point(307, 97)));
mix.Add(new Mix(lblCensur, cbCensur, new Point(451, 97)));
//Hide all
foreach (Mix element in mix)
{
element.Label.Visible = false;
try { element.TextBox.Visible = false; }
catch { element.ComboBox.Visible = false; }
}
location.Clear();
for (int i = 0; i < reader.FieldCount; i++)
{
// If just a textbox then add to list and give name
if (!RequiresComboBox(reader.GetName(i)))
{
location.Add(mix[i]);
location[i].TextBox.Visible = true;
location[i].Label.Visible = true;
location[i].Label.Text = reader.GetName(i);
location[i].TextBox.Size = GetRequiredSize(reader.GetName(i));
}
// If it's a combobox then go through the mixlist and compare with what the current column in the database is.
// WHen there is a match then add to locaiton list.
else if (RequiresComboBox(reader.GetName(i)))
{
for (int j = 0; j < mix.Count; j++)
{
if (reader.GetName(i) == mix[j].Label.Text)
{
location.Add(mix[j]);
location[i].ComboBox.Visible = true;
location[i].Label.Visible = true;
}
}
}
}
reader.Close();
}
connection.Close();
}
Reposition();
}
public void UpdateGridView(string query)
{
updateInProgress = true;
using (SQLiteConnection connection = new SQLiteConnection(Db.connectionString))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(query, connection))
{
SQLiteDataReader reader = command.ExecuteReader();
// Remove all columns
DbDisplay.Columns.Clear();
// Add the needed nr of columns. Use reader.FieldCount to figure out the needed nr.
for (int i = 0; i < reader.FieldCount; i++)
{
//I first need to create this object, whetever it is, to have something to add
// in Columns.Add.
DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();
column.Frozen = true;
column.Name = reader.GetName(i);
column.HeaderText = reader.GetName(i);
DbDisplay.Columns.Add(column);
}
// Add info to the GridView cells from the database.
// Creating an array because it opens for the loop solution below.
// Before i used: string[] row = new string[reader.FieldCount]; but then when I tried to sort
// the id in the gridview, it got sorted in a weird way. Because it was a string.
// This way I'm creating a string that isn't locked to one type.
var row = new object[reader.FieldCount];
while (reader.Read())
{
// While there are rows...
for (int i = 0; i < reader.FieldCount; i++)
{
// ...Go through all the columns in the current row... the add to the array.
// Var to prepare the variable for different types of content.
var addToColumn = reader.GetValue(i);
row[i] = addToColumn;
}
// Add the current row to the gridview. Had I not used the array then I would have had to
// write a lot more code. One reason is that I'd have to specify the GetSqlValue().
DbDisplay.Rows.Add(row);
}
DbDisplay.Columns[0].Visible = false;
DbDisplay.Columns[DbDisplay.Columns.Count - 1].Visible = false;
DbDisplay.Columns[DbDisplay.Columns.Count - 2].Visible = false;
reader.Close();
}
// Don't forget to close the connection. It created problems before.
connection.Close();
updateInProgress = false;
// So that you can't enter a value that is less or more than the actual Nr of rows/columns.
numDeleteRow.Maximum = DbDisplay.RowCount;
numChangeRow.Maximum = DbDisplay.RowCount;
// There are always three hidden columns
numChangeColumn.Maximum = DbDisplay.ColumnCount - 3;
numWriteColumn.Maximum = DbDisplay.ColumnCount - 3;
}
}
public DataTable ExecuteReader(String query, String resultName = "Result")
{
DataTable dtResult = new DataTable();
dtResult.TableName = resultName;
SQLiteCommand command = new SQLiteCommand(query, _connection);
try
{
SQLiteDataReader reader = command.ExecuteReader();
command.Dispose();
//dtResult.Load(reader); //This has issues when reading DateTime columns
Boolean success = false;
//The first read will populate the columns
success = reader.Read();
for (int col = 0; col < reader.FieldCount; col++)
{
String colname = reader.GetName(col);
Type colType = reader.GetFieldType(col);
if (colType == typeof(Double))
{
colType = typeof(Decimal); //prevents scientific notation ToString()
}
if (!dtResult.Columns.Contains(colname))
{
dtResult.Columns.Add(colname, colType);
}
}
if (success) //means there were rows
{
do
{
DataRow dr = dtResult.NewRow();
for (int col = 0; col < reader.FieldCount; col++)
{
String colname = reader.GetName(col);
Type colType = reader.GetFieldType(col);
if (!reader.IsDBNull(col))
{
switch (colType.GetTypeCode())
{
case TypeCode.DateTime:
if (DateTimeOffset.TryParse(reader.GetString(col), out DateTimeOffset dateTimeOffset))
{
dr[colname] = dateTimeOffset.DateTime;
}
break;
default:
dr[colname] = reader[col];
break;
}
}
}
dtResult.Rows.Add(dr);
} while (reader.Read());
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
return(dtResult);
}
public void ExportDatabase(string Separator, string path)
{
SQLiteConnection sqlite_conn;
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
StreamWriter outputFile = new StreamWriter(path);
sqlite_conn = new SQLiteConnection("Data Source=" + Program.DB_PATH + ";Version=3;New=False;Compress=True;");
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM receipts";
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
string headers = String.Empty;
for (int x = 0; x < sqlite_datareader.FieldCount; x++)
{
headers += sqlite_datareader.GetName(x).ToString();
if (x != sqlite_datareader.FieldCount - 1)
{
headers += Separator;
}
}
outputFile.WriteLine(headers);
string line = String.Empty;
for (int x = 0; x < sqlite_datareader.FieldCount; x++)
{
line += sqlite_datareader[x].ToString();
if (x != sqlite_datareader.FieldCount - 1)
{
line += Separator;
}
}
outputFile.WriteLine(line);
outputFile.WriteLine("Položky");
outputFile.WriteLine("---------");
SQLiteCommand sqlite_cmd2 = sqlite_conn.CreateCommand();
sqlite_cmd2.CommandText = "SELECT * FROM items WHERE receiptid='" + sqlite_datareader["id"].ToString() + "'";
SQLiteDataReader sqlite_datareader2 = sqlite_cmd2.ExecuteReader();
headers = String.Empty;
for (int x = 0; x < sqlite_datareader2.FieldCount; x++)
{
headers += sqlite_datareader2.GetName(x).ToString();
if (x != sqlite_datareader2.FieldCount - 1)
{
headers += Separator;
}
}
outputFile.WriteLine(headers);
while (sqlite_datareader2.Read())
{
line = String.Empty;
for (int x = 0; x < sqlite_datareader2.FieldCount; x++)
{
line += sqlite_datareader2[x].ToString();
if (x != sqlite_datareader2.FieldCount - 1)
{
line += Separator;
}
}
outputFile.WriteLine(line);
}
outputFile.WriteLine("---------");
outputFile.WriteLine(String.Empty);
outputFile.WriteLine(String.Empty);
}
sqlite_conn.Close();
outputFile.Close();
}
public static HSSFWorkbook exportXLS(this SQLiteDataReader reader, string FileTemplate = "", string PathSave = "", List <CellMerge> lsTieuDe = null, int RowIndex = 0, bool ShowHeader = true, bool addColumnAutoNumber = false, string formatDate = "dd/MM/yyyy HH:mm:ss")
{
/* Tạo mới */
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
if (FileTemplate != "")
{
var fs = new FileStream(FileTemplate, FileMode.Open, FileAccess.Read);
hssfworkbook = new HSSFWorkbook(fs);
fs.Close();
fs.Dispose();
}
/* Mặc định từ dòng thứ 2 đi */
int index = RowIndex <= 0 ? 1 : RowIndex;
int pointIndex = index - 1;
/* Tạo hoặc lấy Sheet */
var sheet = FileTemplate == "" ? hssfworkbook.CreateSheet() : hssfworkbook.GetSheetAt(0);
/* Tạo đường viền của ô */
var cell = hssfworkbook.CreateCellStyleThin();
/* tạo tiêu đề */
var cellb = hssfworkbook.CreateCellStyleTitle();
var fb = hssfworkbook.CreateFontTahomaBold();
if (ShowHeader)
{
var cr = sheet.CreateRow(index - 1);
int i = 0;
if (addColumnAutoNumber)
{
ICell stt = cr.CreateCell(0, CellType.String);
stt.SetCellValue("STT");
stt.CellStyle = cellb;
for (int j = 0; j < reader.FieldCount; j++)
{
var cc = cr.CreateCell(i + 1, CellType.String); cc.SetCellValue(reader.GetName(j)); cc.CellStyle = cellb; cc.CellStyle.SetFont(fb); i++;
}
}
else
{
for (int j = 0; j < reader.FieldCount; j++)
{
var cc = cr.CreateCell(i, CellType.String); cc.SetCellValue(reader.GetName(j)); cc.CellStyle = cellb; i++;
}
}
}
/* Kiểm tra và đặt tiêu đề */
if (lsTieuDe == null)
{
lsTieuDe = new List <CellMerge>();
}
foreach (var item in lsTieuDe)
{
/* Lấy vị trí RowIndex dòng tiêu đề */
IRow row = sheet.GetRow(item.RowIndex);
if (row == null)
{
row = sheet.CreateRow(item.RowIndex);
}
/* Lấy vị trí ColumnIndex và đặt giá trị */
var cTitle = row.GetCell(item.ColumnIndex);
if (cTitle == null)
{
var column = row.CreateCell(item.ColumnIndex);
column.SetCellValue(item.Value);
column.CellStyle = cellb;
}
else
{
cTitle.SetCellValue(item.Value);
cTitle.CellStyle = cellb;
}
if (item.MergeColumnCount > 0 || item.MergeRowCount > 0)
{
int LastRow = item.MergeRowCount > item.RowIndex ? item.MergeRowCount : item.RowIndex;
int LastColumn = item.MergeColumnCount > item.ColumnIndex ? item.MergeColumnCount : item.ColumnIndex;
var cellRange = new CellRangeAddress(item.RowIndex, LastRow, item.ColumnIndex, LastColumn);
sheet.AddMergedRegion(cellRange);
}
}
/* Xuất dữ liệu */
if (addColumnAutoNumber)
{
while (reader.Read())
{
var cr = sheet.CreateRow(index);
ICell stt = cr.CreateCell(0, CellType.String);
stt.SetCellValue((index - pointIndex).ToString());
stt.CellStyle = cell;
for (int i = 0; i < reader.FieldCount; i++)
{
Type t = reader[i].GetType();
if (XLS.typeNumber.Contains(t))
{
var cc = cr.CreateCell(i + 1, CellType.Numeric);
if (reader.GetValue(i) == DBNull.Value)
{
cc.SetCellValue(0);
}
else
{
cc.SetCellValue(double.Parse(reader.GetValue(i).ToString()));
}
cc.CellStyle = cell;
}
else
{
var cc = cr.CreateCell(i + 1, CellType.String);
if (XLS.typeDateTime.Contains(t))
{
if (reader.GetValue(i) == null)
{
cc.SetCellValue("");
}
else
{
cc.SetCellValue(((DateTime)reader.GetValue(i)).ToString(formatDate));
}
}
else
{
cc.SetCellValue(reader.GetValue(i).ToString());
}
cc.CellStyle = cell;
}
}
index++;
}
}
else
{
while (reader.Read())
{
var cr = sheet.CreateRow(index);
for (int i = 0; i < reader.FieldCount; i++)
{
Type t = reader[i].GetType();
if (XLS.typeNumber.Contains(t))
{
var cc = cr.CreateCell(i, CellType.Numeric);
if (reader.GetValue(i) == DBNull.Value)
{
cc.SetCellValue(0);
}
else
{
cc.SetCellValue(double.Parse(reader.GetValue(i).ToString()));
}
cc.CellStyle = cell;
}
else
{
var cc = cr.CreateCell(i, CellType.String);
if (XLS.typeDateTime.Contains(t))
{
if (reader.GetValue(i) == null)
{
cc.SetCellValue("");
}
else
{
cc.SetCellValue(((DateTime)reader.GetValue(i)).ToString(formatDate));
}
}
else
{
cc.SetCellValue(reader.GetValue(i).ToString());
}
cc.CellStyle = cell;
}
}
}
}
return(hssfworkbook);
}
//handles all instances where the cell is clicked. Mainly for handling when the export and import buttons are clicked
private void tDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
//for exporting
if (e.ColumnIndex == 2)
{
Type officeType = Type.GetTypeFromProgID("Excel.Application");
if (officeType == null)
{
MessageBox.Show("Microsoft Office and Excel needs to be installed to export testing reports.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Templates\", "testing sheet.xlsx");
Workbooks wbs = xlApp.Workbooks; //this is BEYOND the stupidest f*****g thing ever. You have to make a Workbooks object, otherwise when you close everything else out, this will still remain and excel will still be open.
Workbook wb = wbs.Add(path);
Worksheet ws = (Worksheet)wb.Worksheets[1];
using (SQLiteConnection conn = new SQLiteConnection("Data Source=ReportDB.sqlite;Version=3"))
{
try
{
conn.Open();
//gets the building and room number for later
string b = tDGV.Rows[e.RowIndex].Cells[0].Value.ToString();
string r = tDGV.Rows[e.RowIndex].Cells[1].Value.ToString();
ws.Name = b + " " + r;
//main, fills in basic information (building, room, name, date)
SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM testingmain WHERE Building=@b AND Room=@r", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
ws.Cells[36, 1] = "Building: " + b;
ws.Cells[36, 6] = r;
ws.Cells[38, 1] = "Agent Name: " + reader["Agent"].ToString();
string dt = reader["DateCol"].ToString();
dt = dt.Substring(0, dt.Length - 5);//removes time from date
ws.Cells[38, 6] = dt;
ws.Cells[67, 1] = reader["Notes"].ToString();
}
string[] data; //collects the column names in the database to make looping easier when needing to fetch a column's data from an SQL data reader
string[,] mark; //indicates where to mark in the excel spreadsheet
string[,] notes; //simply collects notes
int pos; //tracks position for reading data (usually info is stored at the beginning and notes are stored at the end in the database, excepting being video/audio).
//(cont.) Since two loops need to be used for two arrays, felt it was easier to track using a variable than figuring where the data needed to start in 2nd loop manually
//general
cmd = new SQLiteCommand("SELECT * FROM testinggeneral WHERE Building=@b AND Room=@r", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
data = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
data[i] = reader.GetName(i);
}
//much faster for interop to apply a data set from a range using an array than enter data in one cell at at time
mark = new string[16, 3];
for (int i = 0; i < 16; i++)
{
for (int k = 0; k < 3; k++)
{
mark[i, k] = "";
}
}
notes = new string[16, 1];
for (int i = 0; i < 16; i++)
{
notes[i, 0] = "";
}
pos = 2;
for (int y = 0; y < 16; y++)
{
mark[y, int.Parse(reader[data[pos]].ToString()) - 1] = "X";
pos++;
}
for (int y = 0; y < 16; y++)
{
notes[y, 0] = reader[data[pos]].ToString();
pos++;
}
ws.Range[ws.Cells[5, 2], ws.Cells[20, 4]].Value = mark;
ws.Range[ws.Cells[5, 5], ws.Cells[20, 5]].Value = notes;
ws.Range[ws.Cells[5, 2], ws.Cells[20, 4]].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Range[ws.Cells[5, 5], ws.Cells[20, 10]].HorizontalAlignment = XlHAlign.xlHAlignLeft;
}//everything follows the same general principal (except being vid/aud)
//if ever want to streamline, turn this into a function.
//vid/aud
cmd = new SQLiteCommand("SELECT * FROM testingvideoaudio WHERE Building=@b AND Room=@r", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
data = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
data[i] = reader.GetName(i);
}
mark = new string[6, 6];
for (int i = 0; i < 6; i++)
{
for (int k = 0; k < 6; k++)
{
mark[i, k] = "";
}
}
//no need for notes, everything is linear
pos = 2;
for (int y = 0; y < 6; y++)
{
for (int x = 0; x < 6; x++)
{
mark[y, x] = reader[data[pos]].ToString();
pos++;
}
}
ws.Range[ws.Cells[24, 2], ws.Cells[29, 7]].Value = mark;
ws.Range[ws.Cells[24, 2], ws.Cells[29, 6]].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Range[ws.Cells[24, 7], ws.Cells[29, 10]].HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
//mic
cmd = new SQLiteCommand("SELECT * FROM testingmic WHERE Building=@b AND Room=@r", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
data = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
data[i] = reader.GetName(i);
}
mark = new string[2, 3];
for (int i = 0; i < 2; i++)
{
for (int k = 0; k < 3; k++)
{
mark[i, k] = "";
}
}
notes = new string[2, 1];
for (int i = 0; i < 2; i++)
{
notes[i, 0] = "";
}
pos = 2;
for (int y = 0; y < 2; y++)
{
mark[y, int.Parse(reader[data[pos]].ToString()) - 1] = "X";
pos++;
}
for (int y = 0; y < 2; y++)
{
notes[y, 0] = reader[data[pos]].ToString();
pos++;
}
ws.Range[ws.Cells[33, 2], ws.Cells[34, 4]].Value = mark;
ws.Range[ws.Cells[33, 5], ws.Cells[34, 5]].Value = notes;
ws.Range[ws.Cells[33, 2], ws.Cells[34, 4]].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Range[ws.Cells[33, 5], ws.Cells[34, 10]].HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
//doc cam
cmd = new SQLiteCommand("SELECT * FROM testingdoccam WHERE Building=@b AND Room=@r", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
data = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
data[i] = reader.GetName(i);
}
mark = new string[4, 3];
for (int i = 0; i < 4; i++)
{
for (int k = 0; k < 3; k++)
{
mark[i, k] = "";
}
}
notes = new string[4, 1];
for (int i = 0; i < 4; i++)
{
notes[i, 0] = "";
}
pos = 2;
for (int y = 0; y < 4; y++)
{
mark[y, int.Parse(reader[data[pos]].ToString()) - 1] = "X";
pos++;
}
for (int y = 0; y < 4; y++)
{
notes[y, 0] = reader[data[pos]].ToString();
pos++;
}
ws.Range[ws.Cells[45, 2], ws.Cells[48, 4]].Value = mark;
ws.Range[ws.Cells[45, 5], ws.Cells[48, 5]].Value = notes;
ws.Range[ws.Cells[45, 2], ws.Cells[48, 4]].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Range[ws.Cells[45, 5], ws.Cells[48, 10]].HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
//dvd/blu
cmd = new SQLiteCommand("SELECT * FROM testingdvdblu WHERE Building=@b AND Room=@r", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
data = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
data[i] = reader.GetName(i);
}
mark = new string[4, 3];
for (int i = 0; i < 4; i++)
{
for (int k = 0; k < 3; k++)
{
mark[i, k] = "";
}
}
notes = new string[4, 1];
for (int i = 0; i < 4; i++)
{
notes[i, 0] = "";
}
pos = 2;
for (int y = 0; y < 4; y++)
{
mark[y, int.Parse(reader[data[pos]].ToString()) - 1] = "X";
pos++;
}
for (int y = 0; y < 4; y++)
{
notes[y, 0] = reader[data[pos]].ToString();
pos++;
}
ws.Range[ws.Cells[52, 2], ws.Cells[55, 4]].Value = mark;
ws.Range[ws.Cells[52, 5], ws.Cells[55, 5]].Value = notes;
ws.Range[ws.Cells[52, 2], ws.Cells[55, 4]].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Range[ws.Cells[52, 5], ws.Cells[55, 10]].HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
//iptv
cmd = new SQLiteCommand("SELECT * FROM testingiptv WHERE Building=@b AND Room=@r", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
data = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
data[i] = reader.GetName(i);
}
mark = new string[5, 3];
for (int i = 0; i < 5; i++)
{
for (int k = 0; k < 3; k++)
{
mark[i, k] = "";
}
}
notes = new string[5, 1];
for (int i = 0; i < 5; i++)
{
notes[i, 0] = "";
}
pos = 2;
for (int y = 0; y < 5; y++)
{
mark[y, int.Parse(reader[data[pos]].ToString()) - 1] = "X";
pos++;
}
for (int y = 0; y < 5; y++)
{
notes[y, 0] = reader[data[pos]].ToString();
pos++;
}
ws.Range[ws.Cells[59, 2], ws.Cells[63, 4]].Value = mark;
ws.Range[ws.Cells[59, 5], ws.Cells[63, 5]].Value = notes;
ws.Range[ws.Cells[59, 2], ws.Cells[63, 4]].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Range[ws.Cells[59, 5], ws.Cells[63, 10]].HorizontalAlignment = XlHAlign.xlHAlignLeft;
reader.Close();
}
//main part done, attempts to save completed file
xlApp.DisplayAlerts = false; //used because excel is stupid an will prompt again if you want to replace the file (even though s.f.d will already ask you that).
SaveFileDialog sfd = new SaveFileDialog();
sfd.FileName = tDGV.Rows[e.RowIndex].Cells[0].Value.ToString() + " " + tDGV.Rows[e.RowIndex].Cells[1].Value.ToString(); //set default filename which will consist of building and room #
sfd.Filter = "Excel Spreadsheet (*.xlsx)|*.xlsx"; //so it saves as an excel file only
sfd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); //defaults the directory to Documents
if (sfd.ShowDialog() == DialogResult.OK) //even occurs if the ok button is pressed
{
try
{
wb.Close(SaveChanges: true, Filename: sfd.FileName.ToString()); //Filename will included specified path
}
catch (Exception)
{
wb.Close(0);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
//release all excel related objects to close excel, otherwise process will remain in memory unless closed via task manager.
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(ws) > 0)
{
;
}
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(wb) > 0)
{
;
}
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs) > 0)
{
;
}
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) > 0)
{
;
}
ws = null;
wb = null;
wbs = null;
xlApp = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
}
//for editing
if (e.ColumnIndex == 3)
{
f1.resetTestingTables(); //completely resets the data in the testing tables before adding data in
DataGridView getDGVInfo = (DataGridView)sender; //converts the sender object into data datagridview variable
int i = e.RowIndex; //get's row index for next two variables
string b = getDGVInfo.Rows[i].Cells[0].Value.ToString(); //get's building from row clicked
string r = getDGVInfo.Rows[i].Cells[1].Value.ToString(); //get's room # from row clicked
getDGVInfo = null; //object not use again, set to null for garbage collection later
using (SQLiteConnection conn = new SQLiteConnection("Data Source=ReportDB.sqlite;Version=3;"))
{
try
{
//main info
conn.Open();
SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM testingmain WHERE Building = @b AND Room = @r;", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
f1.testBuilding.Text = reader["Building"].ToString();
f1.testRoom.Text = reader["Room"].ToString();
f1.testName.Text = reader["Agent"].ToString();
f1.testDate.Text = reader["DateCol"].ToString();
f1.testNotesTB.Text = reader["Notes"].ToString();
}
//general info
//only commenting the first one since every one (except vid/aud) works the same.
cmd = new SQLiteCommand("SELECT * FROM testinggeneral WHERE Building=@b AND Room=@r;", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
string[] data = new string[reader.FieldCount]; //column names extracted to string array (way easier than writing them out manually)
for (int t = 0; t < reader.FieldCount; t++) //fills in string with column names
{
data[t] = reader.GetName(t).ToString();
}
reader.Read();//reads the data (important, otherwise no data will be collected. also no need for a while or if since there's only one instance of data).
for (int x = 0; x < f1.testGeneralDGV.Rows.Count; x++)
{
f1.testGeneralDGV.Rows[x].Cells[int.Parse(reader[data[x + 2].ToString()].ToString())].Value = true; //sets value based on number recorded
}
for (int x = 0; x < f1.testGeneralDGV.Rows.Count; x++) //gets notes
{
f1.testGeneralDGV.Rows[x].Cells[4].Value = reader["Notes" + (x + 1)].ToString();
}
}
//vid/aud info
cmd = new SQLiteCommand("SELECT * FROM testingvideoaudio WHERE Building=@b AND Room=@r;", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
string[] data = new string[reader.FieldCount];
for (int t = 0; t < reader.FieldCount; t++)
{
data[t] = reader.GetName(t).ToString();
}
reader.Read();
int k = 2;
for (int y = 0; y < f1.testVidAudDGV.Rows.Count; y++)
{
for (int x = 1; x < f1.testVidAudDGV.Columns.Count; x++)
{
f1.testVidAudDGV.Rows[y].Cells[x].Value = reader[k].ToString();
k++;
}
}
}
//mic info
cmd = new SQLiteCommand("SELECT * FROM testingmic WHERE Building=@b AND Room=@r;", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
string[] data = new string[reader.FieldCount];
for (int t = 0; t < reader.FieldCount; t++)
{
data[t] = reader.GetName(t).ToString();
}
reader.Read();
for (int x = 0; x < f1.testMicDGV.Rows.Count; x++)
{
f1.testMicDGV.Rows[x].Cells[int.Parse(reader[data[x + 2].ToString()].ToString())].Value = true;
}
for (int x = 0; x < f1.testMicDGV.Rows.Count; x++)
{
f1.testMicDGV.Rows[x].Cells[4].Value = reader["Notes" + (x + 1)].ToString();
}
}
//doccam info
cmd = new SQLiteCommand("SELECT * FROM testingdoccam WHERE Building=@b AND Room=@r;", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
string[] data = new string[reader.FieldCount];
for (int t = 0; t < reader.FieldCount; t++)
{
data[t] = reader.GetName(t).ToString();
}
reader.Read();
for (int x = 0; x < f1.testDocDGV.Rows.Count; x++)
{
f1.testDocDGV.Rows[x].Cells[int.Parse(reader[data[x + 2].ToString()].ToString())].Value = true;
}
for (int x = 0; x < f1.testDocDGV.Rows.Count; x++)
{
f1.testDocDGV.Rows[x].Cells[4].Value = reader["Notes" + (x + 1)].ToString();
}
}
//bluray/dvd info
cmd = new SQLiteCommand("SELECT * FROM testingdvdblu WHERE Building=@b AND Room=@r;", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
string[] data = new string[reader.FieldCount];
for (int t = 0; t < reader.FieldCount; t++)
{
data[t] = reader.GetName(t).ToString();
}
reader.Read();
for (int x = 0; x < f1.testDVDDGV.Rows.Count; x++)
{
f1.testDVDDGV.Rows[x].Cells[int.Parse(reader[data[x + 2].ToString()].ToString())].Value = true;
}
for (int x = 0; x < f1.testDVDDGV.Rows.Count; x++)
{
f1.testDVDDGV.Rows[x].Cells[4].Value = reader["Notes" + (x + 1)].ToString();
}
}
//iptv info
cmd = new SQLiteCommand("SELECT * FROM testingiptv WHERE Building=@b AND Room=@r;", conn);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.AddWithValue("@r", r);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
string[] data = new string[reader.FieldCount];
for (int t = 0; t < reader.FieldCount; t++)
{
data[t] = reader.GetName(t).ToString();
}
reader.Read();
for (int x = 0; x < f1.testIPTVDGV.Rows.Count; x++)
{
f1.testIPTVDGV.Rows[x].Cells[int.Parse(reader[data[x + 2].ToString()].ToString())].Value = true;
}
for (int x = 0; x < f1.testIPTVDGV.Rows.Count; x++)
{
f1.testIPTVDGV.Rows[x].Cells[4].Value = reader["Notes" + (x + 1)].ToString();
}
}
f1.testFocus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
}
public List <object> ReadRows(string table, string[] columns, string[] values, object obj)
{
SQLiteConnection conn = new SQLiteConnection(db_file);
try
{
if (columns == null || columns.Length == 0 || values == null || values.Length == 0 || columns.Length != values.Length)
{
throw new Exception("Invalid row parameters");
}
if (columns.Where((t, idx) => string.IsNullOrEmpty(t) || string.IsNullOrEmpty(values[idx])).Any())
{
throw new Exception("Invalid row comparison parameters");
}
conn.Open();
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("SELECT * FROM {0} WHERE ", table));
for (int idx = 0; idx < columns.Length; idx++)
{
if (idx > 0)
{
sb.Append(" AND ");
}
sb.Append(string.Format("{0} = '{1}'", columns[idx], values[idx]));
}
SQLiteCommand cmd = new SQLiteCommand(conn)
{
CommandText = sb.ToString()
};
SQLiteDataReader rdr = cmd.ExecuteReader();
Type ObjType = obj.GetType();
bool isFound = false;
List <object> objList = new List <object>();
while (rdr.Read())
{
if (!rdr.HasRows || rdr.FieldCount <= 0)
{
throw new Exception("No Row Returned");
}
object instance = Activator.CreateInstance(ObjType);
foreach (FieldInfo item in ObjType.GetRuntimeFields().Where(x => x.IsStatic == false))
{
for (int i = 0; i < rdr.FieldCount; i++)
{
if (item.Name.Contains(string.Format("<{0}>", rdr.GetName(i))) &&
rdr.GetFieldType(i) == item.FieldType)
{
var v = rdr.GetValue(i);
item.SetValue(instance, v);
isFound = true;
break;
}
}
}
if (!isFound)
{
throw new Exception("Error Reading Fields");
}
objList.Add(instance);
}
rdr.Close();
CloseConnection(conn);
return(objList);
}
catch (Exception e)
{
Console.WriteLine(e);
CloseConnection(conn);
return(null);
}
}
/// <summary>
/// Method that verify if the table on the database is equal if the data table on the system
/// </summary>
/// <param name="database_directory">Disrectory of the database</param>
/// <returns>True - correct; False - error</returns>
public bool CheckDataBaseWithDataTable(string database_directory)
{
try
{
Util.DataBase.CloseConnection();
if (Util.DataBase.OpenConnection(database_directory))
{
bool were_error = false;
string command = CreateCommandSQLTable();
SQLiteDataReader reader = Util.DataBase.Select(command);
DataTable table = new DataTable();
// Verify if all colluns were created on the database
foreach (DataColumn collumn in data_table.Columns)
{
string field = collumn.ColumnName;
int i = 0;
bool match = false;
for (i = 0; i < fields_Table.Count && !match; i++)
{
if (reader.GetName(i).ToUpper().Equals(field.ToUpper()))
{
table.Columns.Add(field);
match = true;
}
}
if (!match)
{
were_error = true;
}
}
List <string> list = new List <string>();
while (reader.Read())
{
list = new List <string>();
foreach (DataColumn collumn in data_table.Columns)
{
list.Add(reader[collumn.ColumnName].ToString());
}
table.Rows.Add(list.ToArray());
}
reader.Close();
for (int i = 0; i < data_table.Rows.Count; i++)
{
List <object> l1 = data_table.Rows[i].ItemArray.ToList();
List <object> l2 = table.Rows[i].ItemArray.ToList();
if (l1.Count != l2.Count)
{
were_error = true;
break;
}
for (int j = 0; j < l1.Count; j++)
{
if (l1[j].ToString().Replace(',', '.') != l2[j].ToString().Replace(',', '.'))
{
were_error = true;
break;
}
}
if (were_error)
{
break;
}
}
if (were_error)
{
Util.CL_Files.WriteOnTheLog("[TESTE_ZFX_2]The new database don't mach!", Util.Global.TipoLog.SIMPLES);
return(false);
}
else
{
return(true);
}
}
return(true);
}
catch (Exception e)
{
Util.CL_Files.WriteOnTheLog("[TESTE_ZFX_2]Error comparing datatable.Error: " + e.Message, Util.Global.TipoLog.SIMPLES);
}
return(false);
}
/// <summary>
/// 将SqlDataReader转成T类型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
public static T To <T>(this SQLiteDataReader reader) where T : new()
{
if (reader == null || reader.HasRows == false)
{
return(default(T));
}
var res = new T();
var propInfos = GetFieldnameFromCache <T>();
for (int i = 0; i < reader.FieldCount; i++)
{
var n = reader.GetName(i).ToLower();
if (propInfos.ContainsKey(n))
{
PropertyInfo prop = propInfos[n];
object defaultValue = null;//引用类型或可空值类型的默认值
if (prop.PropertyType == typeof(string))
{
defaultValue = default(string);
var v = reader.GetString(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(int))
{
defaultValue = default(int);
var v = reader.GetInt32(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(decimal))
{
defaultValue = default(decimal);
var v = reader.GetDecimal(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(double))
{
defaultValue = default(double);
var v = reader.GetDouble(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(DateTime))
{
defaultValue = default(DateTime);
var v = reader.GetString(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(bool))
{
defaultValue = default(bool);
var v = reader.GetBoolean(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else
{
defaultValue = default(object);
var v = reader.GetValue(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
}
}
return(res);
}
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));
}
}
}
public string GetName(int i)
{
return(Reader.GetName(i));
}
/// <summary>Void function that will create tables and their colummn names</summary>
static void CreateTable(string cs)
{
// insert into a table the user specifies
using var con = new SQLiteConnection(cs);
con.Open();
using var cmd = new SQLiteCommand(con);
Print_New_Lines(2);
Console.Write("What would you like to name your table? (If it already exists, it will be removed) ");
string table = Console.ReadLine();
// display example db
// ReadSample(cs);
// get user input for column names, id's auto generated
Console.WriteLine(@"Column names are how data is identified. They have data types, which specify
what data is going to be stored. Some data types include
INTEGER - Whole numbers like 1, 2, 999, or even 234
REAL - A number that has a decimal point
TEXT - Any data that are words, phrases or characters
NULL - Basically, this stores a 'nothing' value, but something is stored");
Console.Write(@"What data is going to be in the table? (Write the column names and data type separated by commas,
like this: name TEXT) ");
string columns = Console.ReadLine();
string[] col = columns.Split(',');
string insert = @"CREATE TABLE " + table + "(ID INTEGER PRIMARY KEY, ";
// to populate the data
// string dynamics = "@";
// how to dynamically add data to create table
// add to a string and set @data+toSTring(i)
for (int i = 0; i < col.Length; i++)
{
if (i == col.Length - 1)
{
insert += col[i] + ")";
}
else
{
insert += col[i] + ", ";
}
}
Console.WriteLine(insert);
cmd.CommandText = "DROP TABLE IF EXISTS " + table;
cmd.ExecuteNonQuery();
cmd.CommandText = insert;
cmd.ExecuteNonQuery();
Console.WriteLine("Table was created!");
// sample db will be
// contact (table)
// include ID for all tables
// first_name - string
// city - string
// determine that data was input and is in table
Print_New_Lines(3);
string insertStatement = InsertUserTable(table, col);
Console.WriteLine(insertStatement);
// System.Environment.Exit(0);
cmd.CommandText = insertStatement;
cmd.ExecuteNonQuery();
// Console.WriteLine("Insert was successful!!!");
Print_New_Lines(2);
string stm = "SELECT * FROM " + table;
using var cmnd = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmnd.ExecuteReader();
// outputs
// + num is right align, - is left align
for (int i = 0; i <= col.Length; i++)
{
Console.Write($"{rdr.GetName(i),-12}");
}
Print_New_Lines(1);
// Console.WriteLine($"{rdr.GetName(0),-3} {rdr.GetName(1),-12} {rdr.GetName(2),8} {rdr.GetName(3),12}");
while (rdr.Read())
{
Console.Write($"{rdr.GetInt16(0),-12}");
for (int i = 1; i <= col.Length; i++)
{
Console.Write($"{rdr.GetString(i),-12}");
}
Print_New_Lines(1);
}
// System.Environment.Exit(0);
rdr.Close();
}
protected void SqliteConnection()
{
string Text1 = TextBox1.Text;
string inputPattern = @"^[\s\;]*\w+[^\;]*[\;\s]*$";
Regex input = new Regex(inputPattern);
Match inputmatch = input.Match(TextBox1.Text);
if (inputmatch.Success)
{
string s = @"Data Source=C:\TargetDatabase\SQLite\" + User.Identity.Name + DropDownList2.SelectedItem.Text + ".db;Version=3;";
SQLiteConnection myConnection = new SQLiteConnection(s);
SQLiteCommand cmd = new SQLiteCommand();
SQLiteDataReader r = null;
SQLiteDataAdapter adapter = new SQLiteDataAdapter(Text1, myConnection);
try
{
cmd.CommandText = TextBox1.Text;
cmd.Connection = myConnection;
myConnection.Open();
string firstWord = Regex.Match(TextBox1.Text, @"\w+\b").ToString().ToLower();
if (firstWord == "select")
{
DataTable dt = new DataTable();
adapter.Fill(dt);
GridView1.DataSource = dt;
r = cmd.ExecuteReader();
int rowCount = 0;
if (r.HasRows) //results>0
{
while (r.Read())
{
rowCount++;
}
msg.Text = "Result: " + rowCount + " row(s) found";
}
else
{
msg.Text = "Result: 0 row(s) found.<br /><span style='background-color:#339933; color:black; font-size: 15pt'>";
for (int i = 0; i < r.FieldCount; i++)
{
msg.Text += r.GetName(i) + " |";
}
msg.Text += "</span><br />";
}
r.Close();
}
else
{
int numberOfRecords = cmd.ExecuteNonQuery();
msg.Text = "Result: " + numberOfRecords + " row(s) affected.";
}
}
catch (Exception ex)
{
msg.Text = ex.Message;
}
finally
{
//myConnection.Close();
GridView1.DataBind();
FormatView();
}
}
else
{
//msg.Text = "Only one statement allowed";
Header.Controls.Add(new LiteralControl("<script type=\"text/javascript\">alert('Only one statement allowed');</script>"));
}
}
public static SQLiteBlob Create(SQLiteDataReader dataReader, int i, bool readOnly)
{
SQLiteConnection connection = SQLiteDataReader.GetConnection(dataReader);
if (connection == null)
{
throw new InvalidOperationException("Connection not available");
}
SQLite3 sQLite3 = connection._sql as SQLite3;
if (sQLite3 == null)
{
throw new InvalidOperationException("Connection has no wrapper");
}
SQLiteConnectionHandle sQLiteConnectionHandle = sQLite3._sql;
if (sQLiteConnectionHandle == null)
{
throw new InvalidOperationException("Connection has an invalid handle.");
}
long?rowId = dataReader.GetRowId(i);
if (!rowId.HasValue)
{
throw new InvalidOperationException("No RowId is available");
}
SQLiteBlobHandle sQLiteBlobHandle = null;
try
{
}
finally
{
IntPtr zero = IntPtr.Zero;
SQLiteErrorCode sQLiteErrorCode = UnsafeNativeMethods.sqlite3_blob_open(sQLiteConnectionHandle, SQLiteConvert.ToUTF8(dataReader.GetDatabaseName(i)), SQLiteConvert.ToUTF8(dataReader.GetTableName(i)), SQLiteConvert.ToUTF8(dataReader.GetName(i)), rowId.Value, (readOnly ? 0 : 1), ref zero);
if (sQLiteErrorCode != SQLiteErrorCode.Ok)
{
throw new SQLiteException(sQLiteErrorCode, null);
}
sQLiteBlobHandle = new SQLiteBlobHandle(sQLiteConnectionHandle, zero);
}
object[] objArray = new object[] { typeof(SQLiteBlob), dataReader, i, readOnly };
SQLiteConnection.OnChanged(null, new ConnectionEventArgs(SQLiteConnectionEventType.NewCriticalHandle, null, null, null, dataReader, sQLiteBlobHandle, null, objArray));
return(new SQLiteBlob(sQLite3, sQLiteBlobHandle));
}
static void Main(string[] args)
{
string cs = @"URI=file:C:\C#\SummerProject\SP_Project_v1.0\record.sqlite3";
using var con = new SQLiteConnection(cs);
con.Open();
using var cmd = new SQLiteCommand(con);
//cmd.CommandText = "DROP TABLE IF EXISTS Category";
//cmd.ExecuteNonQuery();
//cmd.CommandText = "DROP TABLE IF EXISTS Account";
//cmd.ExecuteNonQuery();
//cmd.CommandText = "DROP TABLE IF EXISTS Entry";
//cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS Category(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, MainCategory TEXT UNIQUE)";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS Account(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, SubCategory TEXT UNIQUE, category_id INTEGER)";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS Entry(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, description TEXT, year TEXT,
month TEXT, day TEXT, ymd INTEGER, price INTEGER, account_id INTEGER)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Category(MainCategory) VALUES('Food')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Category(MainCategory) VALUES('Shopping')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Category(MainCategory) VALUES('Entertainment')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Category(MainCategory) VALUES('Transportation')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Category(MainCategory) VALUES('Others')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Breakfast',1)"; // [group] food
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Lunch',1)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Dinner',1)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Food_Others',1)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Clothing',2)"; // [group] shopping
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Shop_Others',2)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Movie',3)"; // [group] entertainment
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Events',3)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Entertain_Others',3)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Public',4)"; // [group] transportation
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Private',4)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Trans_Others',4)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT OR IGNORE INTO Account(SubCategory,category_id) VALUES('Others_Others',5)"; // [group] Others
cmd.ExecuteNonQuery();
// 程式開始執行
int price;
string year;
string month;
string day;
string ymd;
string description;
while (true)
{
//大類別選擇
Console.WriteLine("請選擇記帳類別:");
Console.WriteLine("「飲食」請按a 「購物」請按b 「娛樂」請按c\n「交通」請按d 「其他」請按e 「分析資料」請按f");
Console.WriteLine("欲結束記帳請直接按Enter");
string catagory = Console.ReadLine();
while (true)
{
if (catagory == "a" || catagory == "b" || catagory == "c" || catagory == "d" || catagory == "e" || catagory == "f" || catagory == "")
{
break;
}
Console.WriteLine("輸入有誤，請重新選擇:");
Console.WriteLine("「飲食」請按a 「購物」請按b 「娛樂」請按c\n「交通」請按d 「其他」請按e 「分析資料」請按f");
Console.WriteLine("欲結束記帳程式請直接按Enter");
catagory = Console.ReadLine();
}
if (catagory == "")
{
break;
}
switch (catagory)
{
case "a": // food
while (true)
{
//細項選擇
Console.WriteLine("請選擇記帳細項:");
Console.WriteLine("「早餐」請按a 「午餐」請按b 「晚餐」請按c 「其他」請按d");
Console.WriteLine("欲返回上一層請直接按Enter");
string type = Console.ReadLine();
while (true)
{
if (type == "a" || type == "b" || type == "c" || type == "d" || type == "")
{
break;
}
Console.WriteLine("輸入有誤，請重新選擇:");
Console.WriteLine("「早餐」請按a 「午餐」請按b 「晚餐」請按c 「其他」請按d");
Console.WriteLine("欲返回上一層請直接按Enter");
type = Console.ReadLine();
}
if (type == "")
{
break;
}
switch (type)
{
case "a": // food/breakfast [1]
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,1)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "b": // food/lunch
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,2)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "c": // food/dinner
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,3)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "d": // food/others
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,4)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
}
}
break;
case "b": // shopping
while (true)
{
Console.WriteLine("請選擇記帳細項:");
Console.WriteLine("「衣物、飾品」請按a 「其他」請按b");
Console.WriteLine("欲返回上一層請直接按Enter");
string type = Console.ReadLine();
while (true)
{
if (type == "a" || type == "b" || type == "")
{
break;
}
Console.WriteLine("輸入有誤，請重新選擇:");
Console.WriteLine("「衣物、飾品」請按a 「其他」請按b");
Console.WriteLine("欲返回上一層請直接按Enter");
type = Console.ReadLine();
}
if (type == "")
{
break;
}
switch (type)
{
case "a": // shopping/clothing
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,5)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "b": // shopping/others
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,6)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
}
}
break;
case "c": // entertainment
while (true)
{
Console.WriteLine("請選擇記帳細項:");
Console.WriteLine("「電影」請按a 「活動」請按b 「其他」請按c");
Console.WriteLine("欲返回上一層請直接按Enter");
string type = Console.ReadLine();
while (true)
{
if (type == "a" || type == "b" || type == "c" || type == "")
{
break;
}
Console.WriteLine("輸入有誤，請重新選擇:");
Console.WriteLine("「電影」請按a 「活動」請按b 「其他」請按c");
Console.WriteLine("欲返回上一層請直接按Enter");
type = Console.ReadLine();
}
if (type == "")
{
break;
}
switch (type)
{
case "a": // entertainment/movie
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,7)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "b": // entertainment/events
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,8)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "c": // entertainment/others
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,9)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
}
}
break;
case "d": // transportation
while (true)
{
Console.WriteLine("請選擇記帳細項:");
Console.WriteLine("「大眾運輸」請按a 「自用車」請按b 「其他」請按c");
Console.WriteLine("欲返回上一層請直接按Enter");
string type = Console.ReadLine();
while (true)
{
if (type == "a" || type == "b" || type == "c" || type == "")
{
break;
}
Console.WriteLine("輸入有誤，請重新選擇:");
Console.WriteLine("「大眾運輸」請按a 「自用車」請按b 「其他」請按c");
Console.WriteLine("欲返回上一層請直接按Enter");
type = Console.ReadLine();
}
if (type == "")
{
break;
}
switch (type)
{
case "a": // transportation/public
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,10)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "b": // transportation/private
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,11)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "c": // transportation/others
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,12)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
}
}
break;
case "e": // C_others
while (true)
{
Console.Write("輸入金額: $ ");
price = int.Parse(Console.ReadLine());
Console.Write("輸入年份 (YYYY): ");
year = Console.ReadLine();
Console.Write("輸入月份 (MM): ");
month = Console.ReadLine();
Console.Write("輸入日期 (DD): ");
day = Console.ReadLine();
Console.Write("內容描述: ");
description = Console.ReadLine();
ymd = year + month + day;
Convert.ToUInt32(ymd);
cmd.CommandText = "INSERT INTO Entry(description,year,month,day,ymd,price,account_id) VALUES(@description,@year,@month,@day,@ymd,@price,13)";
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@ymd", ymd);
cmd.Parameters.AddWithValue("@price", price);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine("儲存成功。下列為您輸入的資料：");
Console.WriteLine("日期: {0}/{1}/{2} 金額: ${3}\n內容: {4}", year, month, day, price, description);
Console.WriteLine("欲繼續輸入下一筆資料請輸入任意文字，欲返回上一層請直接按Enter:");
if (Console.ReadLine() == "")
{
break;
}
}
break;
case "f": // output data ANALYSIS
string stm;
while (true)
{
Console.WriteLine("請選擇資料輸出方式:");
Console.WriteLine("「不排列」請按a 「依照花費排列」請按b\n「依照日期先後排列」請按c 「依照分類排列」請按d\n「依照帳目說明排列」請按e 「預算」請按f");
Console.WriteLine("欲返回上一層請直接按Enter");
string type = Console.ReadLine();
while (true)
{
if (type == "a" || type == "b" || type == "c" || type == "d" || type == "e" || type == "f" || type == "")
{
break;
}
Console.WriteLine("輸入有誤，請重新選擇:");
Console.WriteLine("「不排列」請按a 「依照花費排列」請按b\n「依照日期先後排列」請按c 「依照分類排列」請按d\n「依照帳目說明排列」請按e 「預算」請按f");
Console.WriteLine("欲返回上一層請直接按Enter");
type = Console.ReadLine();
}
if (type == "")
{
break;
}
switch (type)
{
case "a": // random output
stm = "SELECT Category.MainCategory,Account.SubCategory,Entry.description,Entry.year,Entry.month,Entry.day,Entry.price,Entry.ymd FROM Category,Entry JOIN Account ON Account.id = Entry.account_id AND Category.id=Account.category_id";
{
using var cmd_retrieve = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmd_retrieve.ExecuteReader();
Console.Write($"\n{rdr.GetName(0), -30} {rdr.GetName(1), -16} {rdr.GetName(2), -30} {rdr.GetName(6), 6}");
Console.WriteLine(" Date\n");
while (rdr.Read())
{
Console.Write($@"{rdr.GetString(0), -30} {rdr.GetString(1), -16} {rdr.GetString(2), -30} {rdr.GetInt32(6), 6}");
Console.WriteLine(" {0}/{1}/{2}", rdr.GetString(3), rdr.GetString(4), rdr.GetString(5));
}
Console.WriteLine("");
}
break;
case "b": // ordered by expenditure
stm = "SELECT Category.MainCategory,Account.SubCategory,Entry.description,Entry.year,Entry.month,Entry.day,Entry.price,Entry.ymd FROM Category,Entry JOIN Account ON Account.id = Entry.account_id AND Category.id=Account.category_id ORDER BY Entry.price DESC";
{
using var cmd_retrieve = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmd_retrieve.ExecuteReader();
Console.Write($"\n{rdr.GetName(0), -30} {rdr.GetName(1), -20} {rdr.GetName(2), -30} {rdr.GetName(6), 6}");
Console.WriteLine(" Date\n");
while (rdr.Read())
{
Console.Write($@"{rdr.GetString(0), -30} {rdr.GetString(1), -20} {rdr.GetString(2), -30} {rdr.GetInt32(6), 6}");
Console.WriteLine(" {0}/{1}/{2}", rdr.GetString(3), rdr.GetString(4), rdr.GetString(5));
}
Console.WriteLine("");
}
break;
case "c": // ordered by date
stm = "SELECT Category.MainCategory,Account.SubCategory,Entry.description,Entry.year,Entry.month,Entry.day,Entry.price,Entry.ymd FROM Category,Entry JOIN Account ON Account.id = Entry.account_id AND Category.id=Account.category_id ORDER BY Entry.ymd";
{
using var cmd_retrieve = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmd_retrieve.ExecuteReader();
Console.Write($"\n{rdr.GetName(0), -30} {rdr.GetName(1), -20} {rdr.GetName(2), -30} {rdr.GetName(6), 6}");
Console.WriteLine(" Date\n");
while (rdr.Read())
{
Console.Write($@"{rdr.GetString(0), -30} {rdr.GetString(1), -20} {rdr.GetString(2), -30} {rdr.GetInt32(6), 6}");
Console.WriteLine(" {0}/{1}/{2}", rdr.GetString(3), rdr.GetString(4), rdr.GetString(5));
}
Console.WriteLine("");
}
break;
case "d": // ordered by Category
stm = "SELECT Category.MainCategory,Account.SubCategory,Entry.description,Entry.year,Entry.month,Entry.day,Entry.price,Entry.ymd FROM Category,Entry JOIN Account ON Account.id = Entry.account_id AND Category.id=Account.category_id ORDER BY Category.MainCategory";
{
using var cmd_retrieve = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmd_retrieve.ExecuteReader();
Console.Write($"\n{rdr.GetName(0), -30} {rdr.GetName(1), -20} {rdr.GetName(2), -30} {rdr.GetName(6), 6}");
Console.WriteLine(" Date\n");
while (rdr.Read())
{
Console.Write($@"{rdr.GetString(0), -30} {rdr.GetString(1), -20} {rdr.GetString(2), -30} {rdr.GetInt32(6), 6}");
Console.WriteLine(" {0}/{1}/{2}", rdr.GetString(3), rdr.GetString(4), rdr.GetString(5));
}
Console.WriteLine("");
}
break;
case "e": // ordered by description naming
stm = "SELECT Category.MainCategory,Account.SubCategory,Entry.description,Entry.year,Entry.month,Entry.day,Entry.price,Entry.ymd FROM Category,Entry JOIN Account ON Account.id = Entry.account_id AND Category.id=Account.category_id ORDER BY Entry.description";
{
using var cmd_retrieve = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmd_retrieve.ExecuteReader();
Console.Write($"\n{rdr.GetName(0), -30} {rdr.GetName(1), -20} {rdr.GetName(2), -30} {rdr.GetName(6), 6}");
Console.WriteLine(" Date\n");
while (rdr.Read())
{
Console.Write($@"{rdr.GetString(0), -30} {rdr.GetString(1), -20} {rdr.GetString(2), -30} {rdr.GetInt32(6), 6}");
Console.WriteLine(" {0}/{1}/{2}", rdr.GetString(3), rdr.GetString(4), rdr.GetString(5));
}
Console.WriteLine("");
}
break;
case "f": // BUDGET constraint
uint expenditure = 0;
float budget = 0;
uint search_ymd_begin;
uint search_ymd_end;
Console.Write("輸入花費額度: $ ");
budget = float.Parse(Console.ReadLine());
Console.Write("輸入查詢開始日期 (YYYYMMDD) : ");
search_ymd_begin = uint.Parse(Console.ReadLine());
Console.Write("輸入查詢結束日期 (YYYYMMDD) : ");
search_ymd_end = uint.Parse(Console.ReadLine());
{
stm = @"SELECT price,description,year,month,day,ymd FROM Entry WHERE (ymd<=@end) AND (ymd>=@start) ORDER BY ymd";
using var cmd_retrieve = new SQLiteCommand(stm, con);
cmd_retrieve.Parameters.Add(new SQLiteParameter("@start", DbType.UInt32));
cmd_retrieve.Parameters.Add(new SQLiteParameter("@end", DbType.UInt32));
cmd_retrieve.Parameters["@start"].Value = search_ymd_begin;
cmd_retrieve.Parameters["@end"].Value = search_ymd_end;
cmd_retrieve.ExecuteNonQuery();
using SQLiteDataReader rdr = cmd_retrieve.ExecuteReader();
Console.Write($"\n{rdr.GetName(1), -30} {rdr.GetName(0), 6}");
Console.WriteLine(" Date\n");
while (rdr.Read())
{
expenditure += (uint)rdr.GetInt32(0);
Console.Write($@"{rdr.GetString(1), -30} {rdr.GetInt32(0), 6}");
Console.WriteLine(" {0}/{1}/{2}", rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
}
Console.WriteLine("");
}
Console.WriteLine("在 {0} ~ {1} 期間的消費總金額: $ {2}", search_ymd_begin, search_ymd_end, expenditure);
if (expenditure > budget)
{
Console.WriteLine("消費已超過額度");
}
else
{
float percentage = ((float)expenditure / budget) * 100;
if (percentage < 90)
{
Console.WriteLine("您已消費總額度之 {0}%", percentage);
}
else
{
Console.WriteLine("注意! 您已消費超過額度的90%");
}
}
break;
}
} // F while loop
break; // case f END
} // END OF ** big_switch **
} // END OF ** big_while **
} // END OF ** static void Main(string[] args) **
private List <StarSystemDatabaseResult> ReadStarSystems(List <StarSystem> starSystems)
{
if (!starSystems.Any())
{
return(null);
}
List <StarSystemDatabaseResult> results = new List <StarSystemDatabaseResult>();
using (var con = SimpleDbConnection())
{
con.Open();
using (var cmd = new SQLiteCommand(con))
{
using (con.BeginTransaction())
{
foreach (StarSystem starSystem in starSystems)
{
try
{
if (starSystem.systemAddress != null)
{
cmd.CommandText = SELECT_SQL + WHERE_SYSTEMADDRESS;
}
else if (starSystem.EDSMID != null)
{
cmd.CommandText = SELECT_SQL + WHERE_EDSMID;
}
else
{
cmd.CommandText = SELECT_SQL + WHERE_NAME;
}
cmd.Prepare();
cmd.Parameters.AddWithValue("@name", starSystem.systemname);
cmd.Parameters.AddWithValue("@systemaddress", starSystem.systemAddress);
cmd.Parameters.AddWithValue("@edsmid", starSystem.EDSMID);
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
string systemName = null;
long? systemAddress = null;
long? edsmId = null;
string starSystemJson = null;
for (int i = 0; i < rdr.FieldCount; i++)
{
if (SCHEMA_VERSION >= 2 && rdr.GetName(i) == "systemaddress")
{
systemAddress = rdr.IsDBNull(i) ? null : (long?)rdr.GetInt64(i);
}
if (SCHEMA_VERSION >= 2 && rdr.GetName(i) == "edsmid")
{
edsmId = rdr.IsDBNull(i) ? null : (long?)rdr.GetInt64(i);
}
if (rdr.GetName(i) == "name")
{
systemName = rdr.GetString(i);
}
if (rdr.GetName(i) == "starsystem")
{
starSystemJson = rdr.GetString(i);
}
}
if ((systemAddress != null || edsmId != null || systemName != null) && starSystemJson != null)
{
results.Add(new StarSystemDatabaseResult(systemName, systemAddress, edsmId, starSystemJson));
}
}
}
}
catch (SQLiteException)
{
Logging.Warn("Problem reading data for star system '" + starSystem.systemname + "' from database, refreshing database and re-obtaining from source.");
RecoverStarSystemDB();
Instance.GetStarSystem(starSystem.systemname);
}
}
}
}
}
return(results);
}
public bool ReadFile()
{
//Get all tables name
List <string> tables = new List <string>();
DataTable dt = con.GetSchema("Tables");
foreach (DataRow row in dt.Rows)
{
string tablename = (string)row[2];
tables.Add(tablename);
}
//select tables #2
string stm = "SELECT * FROM " + tables[1];
var cmd = new SQLiteCommand(stm, con);
SQLiteDataReader rdr = cmd.ExecuteReader();
int fieldtocount = this.fieldtoread;
if (rdr.FieldCount < fieldtocount)
{
fieldtocount = rdr.FieldCount;
}
string leggititolo = null;
//Read title try/catch
try
{
//Read all field in a row
for (int i = 0; i < fieldtocount; i++)
{
if (i == fieldtocount)
{
leggititolo += rdr.GetName(i);
}
else
{
leggititolo += rdr.GetName(i) + ",";
}
}
Csvwriter.Writecsv(leggititolo);
}
catch (InvalidOperationException e)
{
//LOG
Console.WriteLine(e);
return(false);
}
string leggi = null;
int countline = 0;//to remove
//set number of field to count
//Read lines try/catch
try
{
leggi = null;
while (rdr.Read() /*&& countline < 5000*/)
{
countline++;
//Read all field in a row
for (int i = 0; i < fieldtocount; i++)
{
if (i == fieldtocount - 1)
{
leggi += rdr.GetString(i) + " \n";
}
else
{
leggi += rdr.GetString(i) + ",";
}
}
var howManyBytes = leggi.Length * sizeof(Char);
if (howManyBytes > 5000000)
{
if (!Csvwriter.Writecsv(leggi))
{
return(false);
}
leggi = null;
}
}
if (!Csvwriter.Writecsv(leggi))
{
return(false);
}
}
catch (InvalidOperationException e)
{
//LOG
Console.WriteLine(e);
return(false);
}
return(true);
}
/// <summary>
/// 根据sql获得list
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private List <Gongshi> baseList(string sql)
{
List <Gongshi> list = new List <Gongshi>();
//准备读数据
using (SQLiteDataReader reader = session.ExecuteQuery(sql))
{
//判断是否有数据(有没有行)
if (reader.HasRows)
{
//读取每一行
while (reader.Read())
{
Gongshi gs = new Gongshi(); //创建餐桌对象
Type type = gs.GetType(); //获取类型
for (int i = 0; i < reader.FieldCount; i++)
{
System.Reflection.PropertyInfo propertyInfo = type.GetProperty(reader.GetName(i));
if (propertyInfo != null)
{
propertyInfo.SetValue(gs, reader.GetValue(i), null); //给对应属性赋值
}
}
list.Add(gs); //添加到集合中
} //end while
} //end if
} // end sqldatareader
return(list);
}
/// <summary>
/// Load all the songs from a pool, ordered and filtered if you want.
/// </summary>
/// <param name="poolname"></param>
/// <param name="order_by"></param>
/// <param name="filters"></param>
/// <returns></returns>
public List <Song> LoadSongs(string poolname, IEnumerable <string> order_by, IEnumerable <Filter> filters)
{
List <Song> result = new List <Song>();
using (SQLiteCommand sqc = new SQLiteCommand(connection))
{
// Process the filters to WHEREs
List <string> wheres = new List <string>();
foreach (Filter f in filters)
{
wheres.Add(((f.Not_Flag) ? "NOT " : "") + "LOWER(" + f.Key + ")" + f.Comparetype + "?");
sqc.Parameters.Add(new SQLiteParameter(f.Key, f.Value.ToLower()));
}
sqc.CommandText = "SELECT Songs.id AS SID, Songs.Artists, Songs.Title, Songs.Genres, Songs.TrackNr, Songs.Copyright, Songs.Comment, Songs.Composer, Songs.Conductor, Songs.Lyrics, Songs.BPM, Songs.Version, Songs.PlayCount, Songs.SkipCount, Songs.Rating, Albums.id AS AID, Albums.Name AS Album, Albums.AlbumArtists, Albums.TrackCount, Albums.Year FROM Poolsongs INNER JOIN Songs ON Poolsongs.idSong=Songs.id INNER JOIN Albums ON Albums.id=Songs.idAlbum WHERE Poolsongs.idPool=(SELECT id FROM Songpools WHERE Songpools.Name=:Poolname)" + String.Join(" AND ", wheres);
sqc.Parameters.Add(new SQLiteParameter("Poolname", poolname));
if (order_by.Count() > 0)
{
sqc.CommandText += " ORDER BY " + String.Join(", ", order_by);
}
// Process the result: Create song objects
using (SQLiteDataReader sqr = sqc.ExecuteReader())
{
while (sqr.Read())
{
Song tempsong = new Song();
for (int i = 0; i < sqr.FieldCount; i++)
{
if (!sqr.IsDBNull(i))
{
switch (sqr.GetName(i))
{
case "SID":
tempsong.id = Convert.ToUInt32(sqr.GetInt32(i));
break;
case "AID":
if (tempsong.Album == null)
{
tempsong.Album = new CAlbum();
}
tempsong.Album.id = Convert.ToUInt32(sqr.GetValue(i));
break;
case "Album":
if (tempsong.Album == null)
{
tempsong.Album = new CAlbum();
}
tempsong.Album.Name = (string)sqr.GetValue(i);
break;
case "AlbumArtists":
case "TrackCount":
case "Year":
if (tempsong.Album != null)
{
tempsong.Album[sqr.GetName(i)] = sqr.GetValue(i);
}
break;
default:
tempsong[sqr.GetName(i)] = sqr.GetValue(i);
break;
}
}
}
result.Add(tempsong);
}
}
return(result);
}
}
