/// <inheritdoc />
/// <summary>
/// Перечень конструкций для данной ноды
/// </summary>
/// <param name="cIsso"></param>
/// <returns></returns>
public override Ais7ConstrItem[] ConstrList(int cIsso)
{
// Если у нас нет номеров конструкций
if (!IsForConstr)
{
return new Ais7ConstrItem[] { }
}
;
var ttName = TableName;
var cc = new List <Ais7ConstrItem>();
var forceToMake = false;
var constrName = NGrConstr;
if (allAvailableTables.ToList().IndexOf(TableName) != -1)
{
ttName = "I_PS";
constrName = "Пролетное строение";
forceToMake = true;
}
if (!forceToMake && (ParentNode == null || ParentNode.ParentNode != null))
{
return ParentNode != null?ParentNode.ConstrList(cIsso) : new Ais7ConstrItem[]
{
}
}
;
var driver = Ais7DataTableDriver.Create(ttName);
SqliteConnection conn = null;
SqliteDataReader dataReader = null;
try
{
dataReader = SqliteReader.SelectQueryReader(driver.GetSql($"c_isso={cIsso}"), out conn);
if (!dataReader.HasRows)
{
return(cc.ToArray());
}
while (dataReader.Read())
{
cc.Add(Ais7ConstrItem.Create(constrName, dataReader.GetInt16(0)));
}
}
catch (SQLiteException ex)
{
Debug.WriteLine($"Ошибка при получении списка конструктивов: {ex.Message}, StackTrace: {ex.StackTrace}");
}
finally
{
dataReader?.Close();
dataReader?.Dispose();
conn?.Close();
conn?.Dispose();
}
return(cc.ToArray());
}
/// <summary>
/// 中斷與資料庫的連結，當物件被GC釋放時會自動執行。
/// </summary>
public void Close()
{
filePath = null;
command?.Dispose();
command = null;
reader?.Close();
reader = null;
connection?.Close();
connection?.Dispose();
connection = null;
}
protected void parseReader(SqliteDataReader reader, out SQLiteData data)
{
data = null;
if (reader != null && reader.Read())
{
data = createInstance <SQLiteData>(mDataClassType);
data.mTable = this;
data.parse(reader);
}
reader?.Close();
}
private int DbOpen <T>(string command, Func <SqliteDataReader, T> queryHandler, out T result)
{
result = default(T);
SqliteConnection db = null;
try
{
db = new SqliteConnection("Filename=Devices.db");
db.Open();
SqliteDataReader query = null;
try
{
query = ExecutCommand(command, db);
try
{
result = queryHandler(query);
}
catch (Exception e)
{
Logger.Error($"Parsing has error: \r\n Message: {e.Message}");
return((int)DbErrors.QueryError);
}
}
catch (SqliteException e)
{
Logger.Error($"Execute has error: \r\n Code: {e.SqliteErrorCode}\r\n Message: {e.Message}");
return((int)DbErrors.ExecuteError);
}
finally
{
query?.Close();
query?.Dispose();
}
}
catch (SqliteException e)
{
Logger.Error($"Connection has error: \r\n Code: {e.SqliteErrorCode}\r\n Message: {e.Message}");
return((int)DbErrors.ConnectionError);
}
catch (Exception e)
{
Logger.Error($"Connection has unknow error: \r\n Message: {e.Message}");
return((int)DbErrors.UnknowError);
}
finally
{
db?.Close();
db?.Dispose();
}
return((int)DbErrors.Successful);
}
//---------------------------------------------------------------------------------------------------------------------------
protected void parseReader <T>(SqliteDataReader reader, out T data) where T : TableData, new()
{
data = null;
if (typeof(T) != mTableClassType)
{
logError("sqlite table type error, this type:" + mTableClassType.ToString() + ", param type:" + typeof(T).ToString());
return;
}
if (reader != null && reader.Read())
{
data = new T();
data.mTable = this;
data.parse(reader);
}
reader?.Close();
}
protected void parseReader(Type type, SqliteDataReader reader, out SQLiteData data)
{
data = null;
if (type != mDataClassType)
{
logError("sqlite table type error, this type:" + mDataClassType + ", param type:" + type);
return;
}
if (reader == null)
{
return;
}
if (reader.Read())
{
data = createInstance <SQLiteData>(type);
data.mTable = this;
data.parse(reader);
}
reader?.Close();
}
public bool CheckExistTable(string tableName)
{
SqliteConnection conn = (SqliteConnection)GetConnect();
SqliteDataReader dr = null;
try {
using (SqliteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT name,sql FROM sqlite_master WHERE type='table' ORDER BY name;";
dr = cmd.ExecuteReader();
}
if (dr.HasRows)
{
int cnt = -1;
while (dr.Read())
{
string name = dr[0].ToString();
if (name == tableName)
{
return(true);
}
cnt++;
}
}
} catch { //(Exception ex) {
//MessageDialogs ms = new MessageDialogs(MessageDialogs.DialogButtonType.Ok, "Error", ex.Message, MessageType.Error,null);
//ms.ShowDialog();
} finally {
if (dr != null)
{
dr.Close();
}
dr = null;
conn.Close();
conn = null;
}
return(false);
}
// Get all internal Flights from the data base.
public List <Flights> GetFlights(DateTime time)
{
List <Flights> flights = new List <Flights>();
OpenConnection();
// Get all Flights's initialLocation.
SqliteDataReader query = GetAllInitialLocationsQuery();
// For each location.
while (query.Read())
{
// Checks if the Flight of the current location is currently flying.
Flights flight1 = LocationIterationCurrentFlights(time, query);
if (flight1 != null)
{
flights.Add(flight1);
}
}
query.Close();
CloseConncetion();
return(flights);
}
// -------------------------------------------------------------------------------
// CloseDatabase
// -------------------------------------------------------------------------------
protected void CloseDatabase()
{
if (_reader != null && !_reader.IsClosed)
{
_reader.Close();
}
_reader = null;
if (_command != null)
{
_command.Dispose();
}
_command = null;
if (_connection != null && _connection.State != ConnectionState.Closed)
{
_connection.Close();
}
_connection = null;
Tools.SetChecksum(_dbPath);
}
public void TypeOfNullInResultTest()
{
_conn.ConnectionString = _connectionString;
SqliteDataReader reader = null;
using (_conn) {
_conn.Open();
SqliteCommand cmd = (SqliteCommand)_conn.CreateCommand();
cmd.CommandText = "select null from test";
reader = cmd.ExecuteReader();
try {
Assert.IsTrue(reader.Read());
Assert.IsNotNull(reader.GetFieldType(0));
} finally {
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
_conn.Close();
}
}
}
/// <summary>
/// Lekérdezi a toplista megjelenítéséhez szükséges adatokat.
/// </summary>
private void GetScores()
{
highScores.Clear();
string sqlQuery = "SELECT player.id, name, score FROM player INNER JOIN scores ON player.id = scores.playerid ORDER BY score DESC LIMIT 15";
using (SqliteConnection connection = new SqliteConnection(connectionString))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(sqlQuery, connection))
{
using (SqliteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
highScores.Add(new HighScore(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2)));
}
reader.Close();
}
}
connection.Close();
}
}
//关闭数据库
public void CloseDB()
{
if (command != null)
{
command.Cancel();
}
command = null;
if (reader != null)
{
reader.Close();
}
reader = null;
if (connection != null)
{
//connection.Close();
}
connection = null;
Debug.Log("关闭数据库");
}
/*
* 关闭数据库
*/
public void CloseDB()
{
if (cmd != null)
{
cmd.Dispose();
}
cmd = null;
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
reader = null;
if (conn != null)
{
conn.Close();
}
conn = null;
Debug.Log("Close success!");
}
public bool isRecordExists(string tableName, string col1, string col1Entry, string col2, string col2Entry)
{
bool result = false;
string query = "SELECT * FROM " + tableName + " WHERE " + col1 + "='" + col1Entry + "' AND " + col2 + "='" + col2Entry + "'";
using (SqliteConnection dbcon = new SqliteConnection(connection))
{
dbcon.Open();
using (SqliteCommand dbcmd = new SqliteCommand(query, dbcon))
{
using (SqliteDataReader reader = dbcmd.ExecuteReader())
{
if (reader.Read())
{
result = true;
}
reader.Close();
}
}
}
return(result);
}
public List <district_data> GetDistrict()
{
List <district_data> fdList = new List <district_data>();
try
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "select * from villages where country_code = 2 group by district";
r = cmd.ExecuteReader();
district_data fd = new district_data();
fd.district_code = "";
fd.district = "";
fdList.Add(fd);
while (r.Read())
{
district_data f = new district_data();
f.district_code = r["district_code"].ToString();
f.district = r["district"].ToString();
//
fdList.Add(f);
}
r.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(fdList);
}
public IActionResult Index()
{
string sql = "select * from leaveRecord order by ID DESC ";
SqliteConnection conn = new SqliteConnection(connString);
conn.Open();
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.ExecuteNonQuery();
cmd.CommandText = sql;
SqliteDataReader dr = cmd.ExecuteReader();
StringBuilder strGrid = new StringBuilder();
if (!dr.HasRows)
{
strGrid.Append("<tr class='XYDataGrid1-table-data-tr'>\r\n");
strGrid.Append("<td colspan=4 width='100%' height='100' class='XYDataGrid1-data-cell' align='center'>对不起，暂时没有可以操作的文档。\r\n");
strGrid.Append("</td></tr>\r\n");
}
else
{
while (dr.Read())
{
strGrid.Append("<tr onmouseover='onColor(this)' onmouseout='offColor(this)' class='XYDataGrid1-table-data-tr'>\r\n");
strGrid.Append("<td width='7%' height='16' bgcolor='' class='XYDataGrid1-data-cell'><div align='center'><image src='images/word.gif' border='0'></image></div></td>\r\n");
strGrid.Append("<td width='28%' height='16' bgcolor='' class='XYDataGrid1-data-cell'><div align='left'>" + dr["Subject"].ToString() + "</div></td>\r\n");
strGrid.Append("<td width='20%' height='16' bgcolor='' class='XYDataGrid1-data-cell'><div align='center'><font face='宋体'>" + dr["SubmitTime"].ToString() + "</font></div></td>\r\n");
strGrid.Append("<td width='45%' height='16' bgcolor='' class='XYDataGrid1-data-cell'><div align='center'>\r\n");
strGrid.Append("<a class=OPLink href='Word/datalist?ID=" + dr["ID"].ToString() + "' target='_blank' >数据库中字段内容</a> \r\n");
strGrid.Append("<a class=OPLink href=\"javascript:POBrowser.openWindowModeless('Word/SubmitDataOfDoc?ID=" + dr["ID"].ToString() + "', 'width=1200px;height=800px;');\">用户填写请假条</a> \r\n");
strGrid.Append("<a class=OPLink href=\"javascript:POBrowser.openWindowModeless('Word/GenDoc?ID=" + dr["ID"].ToString() + "', 'width=1200px;height=800px;');\">动态生成格式文档</a> \r\n");
strGrid.Append("</div></td></tr>\r\n");
}
}
dr.Close();
conn.Close();
ViewBag.strGrid = strGrid;
return(View());
}
private static async Task <IDataReader> ExecuteCommandReaderAsync(SqliteConnection connection, bool isOwnedConnection, SqliteCommand command)
{
SqliteDataReader reader = null;
try
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.Connection = connection;
if (isOwnedConnection)
{
reader = (SqliteDataReader)await command.ExecuteReaderAsync(CommandBehavior.CloseConnection).ConfigureAwait(false);
}
else
{
reader = (SqliteDataReader)await command.ExecuteReaderAsync().ConfigureAwait(false);
}
return(reader);
}
catch
{
if (isOwnedConnection)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
throw;
}
}
private void GetAllData()
{
SqliteDataReader reader = Database.Query("SELECT photo_tags.photo_id, tag_id, version_id, name "
+ "FROM photo_tags, photo_versions "
+ "WHERE photo_tags.photo_id = photo_versions.photo_id");
while (reader.Read())
{
uint id = Convert.ToUInt32(reader [0]);
Photo photo = LookupInCache(id) as Photo;
if (photo == null)
{
//Console.WriteLine ("Photo {0} not found", id);
continue;
}
if (photo.Loaded)
{
//Console.WriteLine ("Photo {0} already Loaded", photo);
continue;
}
if (reader [1] != null)
{
uint tag_id = Convert.ToUInt32(reader [1]);
Tag tag = Core.Database.Tags.Get(tag_id) as Tag;
photo.AddTagUnsafely(tag);
}
if (reader [2] != null)
{
uint version_id = Convert.ToUInt32(reader [2]);
string name = reader[3].ToString();
photo.AddVersionUnsafely(version_id, name);
}
}
reader.Close();
}
public void update_status(int id)
{
DateTime datetime = DateTime.Now;
try
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "Update forms set synced='" + 1 + "',synced_date='" + datetime.ToString("yyyy-MM-dd HH:mm:ss.fff") + "' where _id=" + id;
r = cmd.ExecuteReader();
r.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
while (mQueueSqliteDataReader.Count > 0)
{
SqliteDataReader reader = mQueueSqliteDataReader.Dequeue();
if (reader != null && !reader.IsClosed)
{
reader.Close();
reader = null;
}
}
if (mDbConnection != null && mDbConnection.State != System.Data.ConnectionState.Closed)
{
mDbConnection.Close();
mDbConnection.Dispose();
mDbConnection = null;
}
SqliteConnection.ClearAllPools();
GC.Collect();
GC.WaitForPendingFinalizers();
}
/// <summary>
/// Método que faz o load da classe
/// </summary>
public override void Load()
{
string sentenca = "SELECT DSCPF, DSNOME, DSSENH, DSMAIL, DSLOG, DTBRIT FROM " + base.table.Table_Name + " WHERE DSCPF = '" + dscpf + "'";
SqliteDataReader reader = Util.Conection.Select(sentenca);
if (reader.Read())
{
dscpf = reader.GetString(0);
dsnome = reader.GetString(1);
dssenh = reader.GetString(2);
dsmail = reader.GetString(3);
dslogin = reader.GetString(4);
dtbirt = Util.Conection.Int_to_Date(reader.GetInt32(5));
reader.Close();
Empty = false;
}
else
{
Empty = true;
}
}
public static T ReadValue <T> (string tableName, string colName, int idTable, System.Object[] columns)
{
using (var conn = new SqliteConnection(GlobalController.path))
{
conn.Open();
var sqlQuery = "SELECT * " + string.Format("FROM \"{0}\" WHERE \"{1}\" = \"{2}\";", tableName,
colName,
idTable);
using (var cmd = new SqliteCommand(sqlQuery, conn))
{
using (SqliteDataReader reader = cmd.ExecuteReader())
{
T classInstance;
reader.Read();
var columnsCopy = ObjectArray(columns, reader);
Type classType = typeof(T);
ConstructorInfo classConstructor = classType.GetConstructor(new [] { columnsCopy.GetType() });
classInstance = (T)classConstructor.Invoke(new object[] { columnsCopy });
reader.Dispose();
reader.Close();
cmd.Dispose();
conn.Dispose();
conn.Close();
SqliteConnection.ClearAllPools();
GC.Collect();
GC.WaitForPendingFinalizers();
return(classInstance);
}
}
}
}
private Int64 GetLastSendNews(SqliteConnection aSqlCnnctn)
{
Int64 iLastSendNews = 0;
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = aSqlCnnctn;
//
sqlCmmnd.CommandText = "SELECT news_setting.prm_value_int "
+ " FROM news_setting "
+ " WHERE news_setting.prm_name = 'last_send_news' ";
using (SqliteDataReader sqlDReader = sqlCmmnd.ExecuteReader())
{
if (sqlDReader.HasRows && sqlDReader.Read())
{
iLastSendNews = Convert.ToInt64(sqlDReader["prm_value_int"]);
}
sqlDReader.Close();
}
}
return(iLastSendNews);
}
public bool DeleteForm(int id, out string errormessage)
{
errormessage = "";
try
{
this.AddParameter("@id", id);
SqliteDataReader reader = this.ExecuteReader("SELECT id FROM [Report] WHERE fk_form=@id");
if (reader.HasRows)
{
errormessage = "We cannot delete this form as one or more reports are using it.";
return(false);
}
reader.Close();
this.ExecuteNonQuery("DELETE FROM [ReportingForm] WHERE id = @id");
return(true);
}
finally
{
this.CleanUp();
}
}
}// end Query
// metodo che restituisce un attributo scelto dalla tabella
public string Query(ref string m, string str)
{
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
// apro il database
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// query per cercare gli elementi
// le viene passata la stringa contente la query da eseguire
insertCommand.CommandText = str.ToString();
SqliteDataReader reader = insertCommand.ExecuteReader();
reader.Read();
m = reader.GetString(0);
reader.Close();
// chiudo il database
db.Close();
}
return(str);
}// end query
private void GetAllVersions()
{
SqliteDataReader reader = Database.Query("SELECT photo_id, version_id, name FROM photo_versions");
while (reader.Read())
{
uint id = Convert.ToUInt32(reader [0]);
Photo photo = LookupInCache(id) as Photo;
if (photo == null)
{
//Console.WriteLine ("Photo {0} not found", id);
continue;
}
if (photo.Loaded)
{
//Console.WriteLine ("Photo {0} already Loaded", photo);
continue;
}
if (reader [1] != null)
{
uint version_id = Convert.ToUInt32(reader [1]);
string name = reader[2].ToString();
photo.AddVersionUnsafely(version_id, name);
}
/*
* string directory_path = null;
* if (reader [3] != null)
* directory_path = reader [3].ToString ();
* System.Console.WriteLine ("directory_path = {0}", directory_path);
*/
}
reader.Close();
}
/// <summary>
/// realiza la lectura de la base de datos y la carga en la lista
/// </summary>
/// <param name="consulta"></param>
/// <param name="elementosLibroDiario"></param>
private void leerLibroDiario(string consulta, List <ElementoLibroDiario> elementosLibroDiario)
{
SqliteCommand command = new SqliteCommand(consulta, Connection);
try
{
SqliteDataReader lector = command.ExecuteReader();
while (lector.Read())
{
//dia,codigo,fecha,monto,transaccion
int dia = lector.GetInt32(0);
string codigo = lector.GetString(1);
DateTime fecha = DateTime.Parse(lector.GetString(2));
string nombre = lector.GetString(3);
string monto = lector.GetString(4);
string transaccion = lector.GetString(5);
string documentacionRespaldatoria = lector.GetString(6);
int index = lector.GetInt32(7);
ElementoLibroDiario elementoLibroDiario = new ElementoLibroDiario(dia, codigo, fecha, nombre, monto, transaccion, documentacionRespaldatoria, index);
elementosLibroDiario.Add(elementoLibroDiario);
}
lector.Close();
command.Connection.Close();
} catch (Exception e)
{
command.Connection.Close();
throw new Exception("no se pudo realizar la coneccion: " + e);
}
finally
{
command.Connection.Close();
}
}
void getDbValues()
{
Debug.Log("Get Database");
int val = 0;
try {
String sql = "SELECT Level, Completed, Time, Stars, Key, Unlocked, Deaths, Score FROM Levelmanager";
SqliteCommand cmd = new SqliteCommand(sql, dbcon);
SqliteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read() && val < arrayLength)
{
string Level = reader.GetString(0);
bool Completed = reader.GetBoolean(1);
float floatTime = reader.GetFloat(2);
String time = FormatTime(floatTime);
int rating = reader.GetInt16(3);
bool Key = reader.GetBoolean(4);
bool locked = reader.GetBoolean(5);
int deaths = reader.GetInt16(6);
int score = reader.GetInt32(7);
Debug.Log(Level + ", " + val + ", " + Completed + ", " + time + ", " + rating + ", " + Key + ", " + locked + ", " + deaths);
setValues(Level, val, Completed, time, floatTime, rating, Key, locked, deaths, score);
val++;
}
}
else
{
Debug.Log("No rows left");
}
reader.Close();
reader = null;
} catch (Exception e) {
Debug.Log("Error Getting Values from DB " + e);
}
}
public T GetSetting <T>(string name)
{
object setting = default(T);
using (SqliteConnection connection = new SqliteConnection(ConnectionString))
{
connection.Open();
using (SqliteCommand command = connection.CreateCommand())
{
command.CommandText = SELECT_SETTING_SCRIPT;
command.Parameters.AddWithValue("name", name);
using (SqliteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
string value = reader.GetString(0);
if (typeof(T) == typeof(int))
{
setting = int.Parse(value);
}
else if (typeof(T) == typeof(bool))
{
setting = bool.Parse(value);
}
else if (typeof(T) == typeof(DateTime))
{
setting = DateTime.Parse(value);
}
}
reader.Close();
}
}
}
return((T)setting);
}
public static T SingleSpecificSelect <T> (string tableName, System.Object[] columns, string query)
{
using (var conn = new SqliteConnection(GlobalController.path))
{
conn.Open();
var sqlQuery = query;
using (var cmd = new SqliteCommand(sqlQuery, conn))
{
using (SqliteDataReader reader = cmd.ExecuteReader())
{
T classInstance;
reader.Read();
var columnsCopy = ObjectArray(columns, reader);
Type classType = typeof(T);
ConstructorInfo classConstructor = classType.GetConstructor(new [] { columnsCopy.GetType() });
classInstance = (T)classConstructor.Invoke(new object[] { columnsCopy });
reader.Dispose();
reader.Close();
cmd.Dispose();
conn.Dispose();
conn.Close();
SqliteConnection.ClearAllPools();
GC.Collect();
GC.WaitForPendingFinalizers();
return(classInstance);
}
}
}
}
