/// <summary>
/// Removes the old entries (entries that have been in the database more than the specified time) from the database.
/// </summary>
/// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param>
public static void CleanNewsOlderThan(int olderThan)
{
try
{
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
{
sqLiteConnection.Open();
DateTime olderDate = DateTime.Now;
TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0);
olderDate = olderDate.Subtract(daySpan);
SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
{
CommandText = "DELETE " +
"FROM NEWS_STORAGE " +
"WHERE NEWSITEM_AQUISITION_DATE <= ?"
};
sqLiteCommand.Parameters.AddWithValue(null, olderDate);
sqLiteCommand.ExecuteNonQuery();
sqLiteConnection.Close();
}
}
catch (Exception ex)
{
ErrorMessageBox.Show(ex.Message, ex.ToString());
Logger.ErrorLogger("error.txt", ex.ToString());
}
}
/// <summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
/// </summary>
/// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText, params object[] paramList)
{
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
using (SQLiteCommand cmd = new SQLiteCommand(commandText, conn))
{
try
{
conn.Open();
if (paramList != null)
{
AttachParameters(cmd, commandText, paramList);
}
DataSet ds = new DataSet();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
conn.Close();
return ds;
}
catch (Exception ex)
{
cmd.Dispose();
conn.Close();
throw new Exception(ex.Message);
}
}
}
}
/// <summary>
/// Authenticate User with giver login data. Returns null if user does not exist
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public static UserDatabaseError Authenticate(string username, string password, out User user)
{
user = null;
SQLiteConnection connection = new SQLiteConnection(DatabaseManager.CONNECTION_STRING);
SQLiteCommand command = new SQLiteCommand("SELECT salt FROM users WHERE username=@username and active = 1", connection);
//SQLiteCommand command = new SQLiteCommand("SELECT count(*) FROM users", connection);
command.Parameters.AddWithValue("@username", username);
try
{
connection.Open();
}
catch
{
return UserDatabaseError.ERR_DATABASE_CONNECTION;
}
String salt = (string)command.ExecuteScalar();
if (salt == null || salt == string.Empty)
{
connection.Close();
return UserDatabaseError.ERR_USER_DOES_NOT_EXIST;
}
string hash = DatabaseManager.GetSha256(password + salt);
command.CommandText = "SELECT name, privilege FROM users WHERE username=@username AND sha256p =@password";
command.Parameters.AddWithValue("@password", hash);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
user = new User(username, (string)reader["name"], (User.UserPrivilege)(Int64)reader["privilege"]);
if (user == null)
return UserDatabaseError.ERR_AUTH;
connection.Close();
return UserDatabaseError.ERR_SUCCESS;
}
public bool AddQuote()
{
var myDirectory = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
string parentDirectory = myDirectory.Parent.FullName;
using (
var cn =
new SQLiteConnection(string.Format(@"Data Source={0}{1} Version=3;", parentDirectory,
ConfigurationManager.ConnectionStrings["connectionstring"]
.ConnectionString)))
{
try
{
string _sql = string.Format("Insert into quotes values('{0}','{1}','{2}',{3}','');",
ProjectName, QuoteBy, Quotestring, User);
SQLiteCommand myCommand = cn.CreateCommand();
myCommand.CommandText = _sql;
cn.Open();
myCommand.ExecuteNonQuery();
cn.Close();
return true;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
cn.Close();
return false;
}
}
}
public static bool Login(int accountId, string password)
{
using (SQLiteConnection con = new SQLiteConnection(Settings.Default.AccountingConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = con;
cmd.CommandText = "SELECT COUNT(accountId) FROM vAccountPassword WHERE accountId = @account AND password = @password";
SQLiteParameter pAccount = new SQLiteParameter("account",accountId);
pAccount.Direction = ParameterDirection.Input;
SQLiteParameter pPassword = new SQLiteParameter("password", password);
pPassword.Direction = ParameterDirection.Input;
cmd.Parameters.AddRange(new SQLiteParameter[] { pAccount, pPassword });
con.Open();
if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
{
con.Close();
return true;
}
con.Close();
return false;
}
}
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
/// <summary>
/// Creates an example table containing 'DVD' records.
/// </summary>
/// <param name="conn">A SQLiteConnection object.</param>
public void CreateDVDTable(SQLiteConnection conn)
{
//This sql query creates a dvds table with an auto incrementing id field as the primary key and some other columns.
string sql = "CREATE TABLE dvds (id INTEGER PRIMARY KEY ASC, title TEXT, genre TEXT, date_added DATETIME)";
//SQLiteCommand objects are where we exectute sql statements.
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch (SQLiteException ex)
{
//if anything is wrong with the sql statement or the database,
//a SQLiteException will show information about it.
Debug.Print(ex.Message);
//always make sure the database connectio is closed.
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
private bool CreateDatabase()
{
SQLiteConnection tempConnection = null;
try
{
SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]);
tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;");
tempConnection.Open();
SQLiteCommand command = tempConnection.CreateCommand();
command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)";
command.ExecuteNonQuery();
tempConnection.Close();
}
catch(Exception e)
{
errors.Add("Tietokannan luominen epäonnistui");
return false;
}
finally
{
if(tempConnection != null) tempConnection.Close();
}
return true;
}
public static void Try(string[] parameters)
{
Player.PlayerStruct p = Player.GetPlayer(parameters[1]);
if (!string.IsNullOrEmpty(p.Squad) && !string.IsNullOrEmpty(parameters[3]) && p.name.ToLower() != parameters[3].ToLower())
{
/* Begin Database Connection */
DataTable dt = new DataTable();
SQLiteConnection SConnection = new SQLiteConnection();
SConnection.ConnectionString = SQLite.ConnectionString;
SConnection.Open();
SQLiteCommand cmd = new SQLiteCommand(SConnection);
cmd.CommandText = @"SELECT owner FROM squads WHERE name = @nsquad";
SQLiteParameter nsquad = new SQLiteParameter("@nsquad");
SQLiteParameter pname = new SQLiteParameter("@pname");
cmd.Parameters.Add(nsquad);
cmd.Parameters.Add(pname);
nsquad.Value = p.Squad;
pname.Value = p.name;
SQLiteDataReader Reader = cmd.ExecuteReader();
dt.Load(Reader);
Reader.Close();
SConnection.Close();
/* End Database Connection */
if (dt.Rows.Count > 0)
{
if (dt.Rows[0][0].ToString().ToLower() == p.name.ToLower())
{
/* Begin Database Connection */
SConnection.Open();
cmd.CommandText = @"UPDATE players SET squad = '' WHERE name = @kname AND squad = @nsquad";
SQLiteParameter kname = new SQLiteParameter("@kname");
cmd.Parameters.Add(kname);
kname.Value = parameters[3];
cmd.ExecuteNonQuery();
SConnection.Close();
/* End Database Connection */
//TODO: do we send the kicked player a message?
Message.Send = "MSG:" + parameters[1] + ":0:If said player was a member of your squad, he or she has been kicked.";
SiusLog.Log(SiusLog.INFORMATION, "?squadkick", p.name + " kicked " + parameters[3] + " from squad <" + p.Squad + ">.");
}
else
{
Message.Send = "MSG:" + parameters[1] + ":0:You do not own this squad.";
SiusLog.Log(SiusLog.INFORMATION, "?squadkick", p.name + " attempted to kick " + parameters[3] + " from squad <"
+ p.Squad + ">, but is not owner.");
return;
}
}
}
}
/// <summary>
/// 检查用户密码问题
/// </summary>
/// <param name="uname"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public static string CheckLogin(string UUser, string PPwd)
{
string Querystring = "select username from online_user_tab where username='******' and password='******'";
SQLiteConnection conn = new SQLiteConnection();
string connectstr = DataAccess.PDAConnStr;
conn.ConnectionString = connectstr;
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(Querystring, conn);
SQLiteDataReader Datareader = cmd.ExecuteReader();
Datareader.Read();
//Method 1
//OracleNumber oraclenumber = reader.GetOracleNumber(0);
//Response.Write("OracleNum " + oraclenumber.ToString());
//string aa = oraclenumber.ToString();
//if (aa=="1")
//{
// Response.Redirect("mainform.aspx");
// reader.Close();
//}
//else
//{
// Response.Write("Username and password do not match!Please try it again!");
// reader.Close();
// TextBox1.Text = "";
// TextBox2.Text = "";
// TextBox1.Focus();
//}
//Method 2;
if (Datareader.HasRows)
{
//Response.Write(cmd.ExecuteScalar().ToString());
//reader.Close();
//conn.Close();
//Response.Redirect("mainform.aspx");
Datareader.Close();
conn.Close();
return "1";
}
else
{
//Response.Write("Invalid Password or Username,Please Check!!");
//reader.Close();
//conn.Close();
Datareader.Close();
conn.Close();
return "2";
}
}
public Block_Edit(int id_)
{
InitializeComponent();
id = id_;
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
SQLiteCommand command = new SQLiteCommand(conn);
command.CommandText = "SELECT * FROM Block WHERE id=@id";
command.Parameters.Add(new SQLiteParameter("@id", id));
using (command)
{
using (SQLiteDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
labelName.Text = rdr.GetValue(1).ToString();
txtImieNazw.Text = rdr.GetValue(1).ToString();
}
}
}
conn.Close();
}
}
public List<AttachmentDetail> getWitAttachments()
{
List<AttachmentDetail> attachments;
try
{
sql_con = new SQLiteConnection(Common.localDatabasePath, true);
sql_cmd = new SQLiteCommand("select * from wit_attachments", sql_con);
sql_con.Open();
SQLiteDataReader reader = sql_cmd.ExecuteReader();
attachments = new List<AttachmentDetail>();
while (reader.Read())
{
AttachmentDetail attachment = new AttachmentDetail();
attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
attachment.fileName = StringUtils.ConvertFromDBVal<string>(reader["file_name"]);
attachment.witId = StringUtils.ConvertFromDBVal<string>(reader["wit_id"]);
attachment.fileMimeType = StringUtils.ConvertFromDBVal<string>(reader["file_mime_type"]);
attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
attachment.seqNumber = StringUtils.ConvertFromDBVal<string>(reader["seq_number"]);
attachment.extention = StringUtils.ConvertFromDBVal<string>(reader["extention"]);
attachments.Add(attachment);
}
}
catch (SQLiteException e) { throw e; }
finally { sql_con.Close(); }
return attachments;
}
private void Consulta_Load(object sender, EventArgs e)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;";
DataSet DS = new DataSet();
SQLiteConnection con = new SQLiteConnection(connString);
con.Open();
SQLiteDataAdapter DA = new SQLiteDataAdapter("select * from Expediente", con);
DA.Fill(DS, "Expediente");
dataGridView1.DataSource = DS.Tables["Expediente"];
con.Close();
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
int i = 0;
foreach (DataGridViewColumn c in dataGridView1.Columns)
{
i += c.Width;
}
if ((i + dataGridView1.RowHeadersWidth + 2) > 616)
{
dataGridView1.Width = 616;
}
else
{
dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
}
}
private void DeleteCardBase()
{
if (dataGridView1.SelectedRows.Count > 0)
{
id = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
if (dataGridView1.RowCount != 0) index = dataGridView1.SelectedRows[0].Index;
DialogResult result = MessageBox.Show("Czy na pewno chcesz usunąć bazę karty numer " + id + "? \n\nOperacji nie można cofnąć.", "Ważne", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
SQLiteCommand command = new SQLiteCommand(conn);
command.CommandText = "DELETE FROM [CardBase] WHERE id = @id";
command.Parameters.Add(new SQLiteParameter("@id", id));
command.ExecuteNonQuery();
conn.Close();
Odswierz();
if (dataGridView1.RowCount != 0)
{
if (index == dataGridView1.RowCount) dataGridView1.CurrentCell = dataGridView1.Rows[index - 1].Cells[0];
else dataGridView1.CurrentCell = dataGridView1.Rows[index].Cells[0];
}
}
}
}
/// <summary>
/// Takes a GIS model and a file and writes the model to that file.
/// </summary>
/// <param name="model">
/// The GisModel which is to be persisted.
/// </param>
/// <param name="fileName">
/// The name of the file in which the model is to be persisted.
/// </param>
public void Persist(GisModel model, string fileName)
{
Initialize(model);
PatternedPredicate[] predicates = GetPredicates();
if ( File.Exists(fileName))
{
File.Delete(fileName);
}
using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3"))
{
mDataConnection.Open();
mDataCommand = mDataConnection.CreateCommand();
CreateDataStructures();
using (mDataTransaction = mDataConnection.BeginTransaction())
{
mDataCommand.Transaction = mDataTransaction;
CreateModel(model.CorrectionConstant, model.CorrectionParameter);
InsertOutcomes(model.GetOutcomeNames());
InsertPredicates(predicates);
InsertPredicateParameters(model.GetOutcomePatterns(), predicates);
mDataTransaction.Commit();
}
mDataConnection.Close();
}
}
//public static int Insert(string dbFile, string sql,)
public static ObservableCollection<Jewelry> GetAll()
{
ObservableCollection<Jewelry> ob = new ObservableCollection<Jewelry>();
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:/xhz/ms.db;"))
//using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=DB/ms.db;"))
{
conn.Open();
string sql = string.Format("select * from detail");
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
using (SQLiteDataReader dr1 = cmd.ExecuteReader())
{
while (dr1.Read())
{
var d = dr1;
var dd = dr1.GetValue(0);
var data = dr1.GetValue(1);
var insert = dr1.GetValue(2);
var update = dr1.GetValue(3);
}
}
conn.Close();
}
ob.Add(new Jewelry());
return ob;
}
public static DataTable ExecuteNonQueryDt(string cmdText, SQLiteConnection con)
{
DataTable dt = new DataTable("Table");
try
{
using (con)
{
SQLiteDataAdapter da = new SQLiteDataAdapter(cmdText, con);
con.Open();
da.Fill(dt);
con.Close();
}
return dt;
}
catch (Exception ex)
{
using (FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "\\" + System.DateTime.Now.Date.ToString("dd-MMM-yyyy") + "_Log.txt", FileMode.Append, FileAccess.Write))
{
StreamWriter streamWriter = new StreamWriter(file);
streamWriter.WriteLine(System.DateTime.Now + " - " + "ExecuteNonQueryDt" + " - " + ex.Message.ToString());
streamWriter.Close();
}
return dt;
}
}
public static void ChapterFinished(string mangaTitle)
{
try
{
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
{
sqLiteConnection.Open();
SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
{
CommandText = "UPDATE READING_LIST " +
"SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " +
"WHERE MANGA_ID = ?"
};
sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now);
sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle));
sqLiteCommand.ExecuteNonQuery();
sqLiteConnection.Close();
}
}
catch (Exception ex)
{
ErrorMessageBox.Show(ex.Message, ex.ToString());
Logger.ErrorLogger("error.txt", ex.ToString());
}
}
static void Main(string[] args)
{
// Create sqlite connection
SQLiteConnection connection = new SQLiteConnection(string.Format(@"Data Source={0}\SimpleDatabase.s3db", Environment.CurrentDirectory));
// Open sqlite connection
connection.Open();
// Get all rows from example_table
SQLiteDataAdapter db = new SQLiteDataAdapter("SELECT * FROM Names", connection);
// Create a dataset
DataSet ds = new DataSet();
// Fill dataset
db.Fill(ds);
// Create a datatable
DataTable dt = new DataTable("Names");
dt = ds.Tables[0];
// Close connection
connection.Close();
// Print table
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(string.Format("{0} {1}", row["Firstname"], row["Surname"]));
}
Console.ReadLine();
}
/// <summary>
/// Constructor creates a database if it doesn't exist and creates the database's tables
/// </summary>
public DataBaseRepository()
{
// "using " keywords ensures the object is properly disposed.
using (var conn = new SQLiteConnection(Connectionstring))
{
try
{
if (!File.Exists(Startup.dbSource))
{
SQLiteConnection.CreateFile(Startup.dbSource);
}
conn.Open();
//Create a Table
string query = $"create table IF NOT EXISTS {TableName} (Id INTEGER PRIMARY KEY, Date VARCHAR NOT NULL DEFAULT CURRENT_DATE, Title nvarchar(255) not null, Content nvarchar(1000) Not NULL) ";
SQLiteCommand command = new SQLiteCommand(query, conn);
command.ExecuteNonQuery();
}
//TODO: Handle try catch better cath a more specific error type.
catch (SQLiteException ex )
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
}
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
int countCurrent = 0;
string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在
try
{
Int32.Parse((string)value);
countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent);
conn.Close();
if (countCurrent > 0)
{
return "更新";
}
else
{
return "保存";
}
}
catch (Exception)
{
return "保存";
}
}
}
}
public static DataTable buscaDatosSing4()
{
DataTable tabla = new dataCredito.datosSing4DataTable();
using (SQLiteConnection con = new SQLiteConnection(Datos.conexion))
{
using (SQLiteCommand comando = new SQLiteCommand())
{
comando.CommandText = "select * from datosSing4";
comando.Connection = con;
con.Open();
SQLiteDataReader lector = comando.ExecuteReader();
while (lector.Read())
{
DataRow fila = tabla.NewRow();
fila["nombre"] = lector.GetString(0);
fila["direccion"] = lector.GetString(1);
fila["direccion2"] = lector.GetString(2);
fila["ciudad"] = lector.GetString(3);
fila["id"] = lector.GetString(4);
fila["idCliente"] = lector.GetInt32(5);
fila["asesor"] = lector.GetString(6);
fila["encabezado"] = lector.GetString(7);
fila["fecha"] = lector.GetDateTime(8);
fila["pie"] = lector.GetString(9);
fila["saludo"] = lector.GetString(10);
tabla.Rows.Add(fila);
}
}
con.Close();
}
return (tabla);
}
public void create(string file)
{
db_file = file;
conn = new SQLiteConnection("Data Source=" + db_file);
if (!File.Exists(db_file))
{
conn.Open();
using (SQLiteCommand command = conn.CreateCommand())
{
command.CommandText = @"CREATE TABLE `erogamescape` (
`id` INTEGER NOT NULL,
`title` TEXT,
`saleday` TEXT,
`brand` TEXT,
PRIMARY KEY(id));
CREATE TABLE `tableinfo` (
`tablename` TEXT NOT NULL,
`version` INTEGER,
PRIMARY KEY(tablename))";
command.ExecuteNonQuery();
command.CommandText = @"INSERT INTO tableinfo VALUES('erogamescape',0)";
command.ExecuteNonQuery();
}
conn.Close();
}
}
/// <summary>
/// Получить данные из таблицы
/// </summary>
/// <param name="databasename">Имя таблицы</param>
/// <param name="where">Условия</param>
/// <param name="etc">Остальные параметры: сортировка, группировка и т.д.</param>
/// <returns>Таблица с данными</returns>
public DataTable FetchAll(string databasename, string where, string etc)
{
DataTable dt = new DataTable();
string sql = string.Format("SELECT * FROM {0} {1} {2}", databasename, where, etc);
ConnectionState previousConnectionState = ConnectionState.Closed;
using (SQLiteConnection connect = new SQLiteConnection(ConnectionString))
{
try
{
previousConnectionState = connect.State;
if (connect.State == ConnectionState.Closed)
{
connect.Open();
}
SQLiteCommand command = new SQLiteCommand(sql, connect);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(dt);
}
catch (Exception error)
{
System.Windows.Forms.MessageBox.Show(error.Message, "Ошибка при получении данных из базы", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
finally
{
if (previousConnectionState == ConnectionState.Closed)
{
connect.Close();
}
}
}
return dt;
}
public static bool IsExistingClientByHostname(string hostname)
{
bool clientExists = false;
string connStr = ConfigurationManager.ConnectionStrings["uWiMPConnString"].ConnectionString;
SQLiteConnection conn = new SQLiteConnection(connStr);
SQLiteDataReader reader;
SQLiteCommand cmd = new SQLiteCommand("SELECT Hostname FROM `MPClients` WHERE Hostname = $Hostname", conn);
cmd.Parameters.Add("$Hostname", DbType.String, 255).Value = hostname.ToLower();
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.Default);
if (reader.HasRows)
clientExists = true;
}
catch (SQLiteException ex)
{
return false;
}
finally
{
conn.Close();
}
return clientExists;
}
/// <summary>Initialization ctor</summary>
/// <param name="file">Name of database file, will be created if not existing</param>
public WebCache(string file = "Cache.dat")
{
_File = file;
if (!System.IO.File.Exists(_File))
{
SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File);
try
{
connection.Open();
string[] inits = new string[]{
"CREATE TABLE [InetCache] ([Id] INTEGER PRIMARY KEY, [Timestamp] INTEGER, [Keyword] TEXT NOT NULL, [Content] TEXT NOT NULL)",
"CREATE INDEX [IDX_InetCache] ON [InetCache] ([Keyword])"
};
SQLiteCommand command = connection.CreateCommand();
foreach (string cmd in inits)
{
command.CommandText = cmd;
command.ExecuteNonQuery();
}
}
finally
{
connection.Close();
}
}
}
public DataTable FetchAll(string databasename, string where, string etc)
{
DataTable dataTable = new DataTable();
string commandText = string.Format("SELECT * FROM {0} {1} {2}", databasename, where, etc);
ConnectionState closed = ConnectionState.Closed;
using (SQLiteConnection connection = new SQLiteConnection(this.ConnectionString))
{
try
{
closed = connection.State;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SQLiteCommand cmd = new SQLiteCommand(commandText, connection);
new SQLiteDataAdapter(cmd).Fill(dataTable);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка при получении данных из базы");
return null;
}
finally
{
if (closed == ConnectionState.Closed)
{
connection.Close();
}
}
}
return dataTable;
}
public static AttendanceReport[] GetAttendaceReports(string data_path)
{
SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + data_path);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select * from AttendanceReports";
SQLiteDataReader reader = cmd.ExecuteReader();
List<AttendanceReport> attendanceReports = new List<AttendanceReport>();
while (reader.Read())
{
int ID = reader.GetInt32(0);
string timeIN = reader.GetString(1);
string timeOUT = reader.GetString(2);
string deltaTime = reader.GetString(3);
string activity = reader.GetString(4);
string mentor = reader.GetString(5);
int index = reader.GetInt32(6);
attendanceReports.Add(new AttendanceReport(ID, timeIN, timeOUT, deltaTime, activity, mentor, index));
}
reader.Close();
conn.Close();
return attendanceReports.ToArray();
}
public void addElement(string value)
{
string[] values = value.Split('|');
string moduleCode = values[0];
string moduleName = "";
SQLiteConnection con = new SQLiteConnection(Form1.dbConnectionString);
try
{
con.Open();
string query = "Select ModuleName from ModuleInformation where ModuleCode = '" + moduleCode + "'";
SQLiteCommand command = new SQLiteCommand(query, con);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
moduleName = reader.GetString(0);
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dataGridView1.Rows.Add(values[0], moduleName, values[1], values[2]);
}
public FrmEdytujKlienta(int id_)
{
InitializeComponent();
id = id_;
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
SQLiteCommand command = new SQLiteCommand(conn);
command.CommandText = "SELECT * FROM Klienci WHERE id=@id";
command.Parameters.Add(new SQLiteParameter("@id", id));
using (command)
{
using (SQLiteDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
txt_id.Text = rdr.GetValue(0).ToString();
txt_imie.Text = rdr.GetValue(1).ToString();
txt_nazwisko.Text = rdr.GetValue(2).ToString();
txt_pesel.Text = rdr.GetValue(3).ToString();
txt_nr_dowodu.Text = rdr.GetValue(4).ToString();
txt_nr_telefonu.Text = rdr.GetValue(5).ToString();
txt_email.Text = rdr.GetValue(6).ToString();
txt_miejscowosc.Text = rdr.GetValue(7).ToString();
txt_kod_pocztowy.Text = rdr.GetValue(8).ToString();
txt_ulica.Text = rdr.GetValue(9).ToString();
txt_nr_domu.Text = rdr.GetValue(10).ToString();
}
}
}
conn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
//class created to store athletes info
Athlete AddAthlete = new Athlete();
//objects used to store the athletes info
AddAthlete.firstName = dataGridView1.SelectedCells[1].Value.ToString();
AddAthlete.lastName = dataGridView1.SelectedCells[2].Value.ToString();
//stores country name
string country = dataGridView1.SelectedCells[0].Value.ToString();
//select puts the selected event into a string
string selected = listBox1.GetItemText(listBox1.SelectedItem);
if (selected == "Mens 500m Speed Skating, Monday, 12:00pm, Rink 1")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Mens 500m Speed Skating', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Monday', 'Rink 1');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
MessageBox.Show("Athlete has been entered into Event!");
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"ss500m Times Men\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
}
if (selected == "Womens 500m Speed Skating, Monday, 12:00pm Rink 2")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Womens 500m Speed Skating', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Monday', 'Rink 2');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"ss500m Times Women\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Mens 1000m Speed Skating, Tueday, 12:00pm, Rink 1")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Mens 1000m Speed Skating', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Tuesday', 'Rink 1');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"ss1000m Times Men\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Womens 1000m Speed Skating, Tuesday, 12:00pm, Rink 2")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Womens 1000m Speed Skating', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Tuesday', 'Rink 2');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"ss1000m Times Women\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Mens 1500m Speed Skating, Wednesday, 12:00pm, Rink 1")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Mens 1500m Speed Skating', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Wednesday', 'Rink 1');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"ss1500m Times Men\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Womens 1500m Speed Skating, Wednesday, 12:00pm, Rink 2")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Womens 1500m Speed Skating', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Wednesday', 'Rink 2');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"ss1500m Times Women\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Single Skating Mens, Monday, 11:00am, Rink 3")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Single Skating Mens', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '11:00am', 'Monday', 'Rink 3');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"Single Skating Men Scores\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Single Skating Womens, Monday, 11:00am, Rink 4")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Single Skating Womens', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '11:00am', 'Monday', 'Rink 4');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"Single Skating Womens Scores\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Ice Dance Mens, Tuesday, 2:00pm, Rink 3")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Ice Dance Mens', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '2:00pm', 'Tuesday', 'Rink 3');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"Ice Dance Mens Scores\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Ice Dance Womens, Tuesday, 2:00pm, Rink 4")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Ice Dance Womens', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '2:00pm', 'Tuesday', 'Rink 4');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"Ice Dance Womens Scores\" ('Athlete First Name', 'Athlete Last Name', " +
"'TeamName') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
if (selected == "Couple Skating, Monday, 5:00pm, Rink 3")
{
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," +
"'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Couple Skating', '" +
country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '5:00pm', 'Tuesday', 'Rink 3');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Athlete has been entered into Event!");
con.Close();
}
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); //opens DB
//checks connection and fills grid view with Race table
if (conn.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO \"Pair Skating Scores\" ('Athlete First Name', 'Athlete Last Name', " +
"'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
}
//this will repopulate Event with the added entry
string View = "SELECT * FROM 'Events'"; //gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View, con);
da.Fill(ds);
dataGridView2.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
}
static void Main(string[] args)
{
Database databaseObject = new Database();
/*
* // * INSERT INTO DATABASE
* //
*/
string createQuery = @"CREATE TABLE IF NOT EXISTS
[Mytable] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] NVARCHAR(2048) NULL,
[Gender] NVARCHAR(2048) NULL)";
System.Data.SQLite.SQLiteConnection.CreateFile("sample.db3");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=sample.db3"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Mytable (Name,Gender) values('Kenneth', 'Male')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Mytable (Name,Gender) values('Monique', 'Female')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Mytable (Name,Gender) values('Jimmy-Crack-Corn', 'Bird')";
cmd.ExecuteNonQuery();
/*
* // * SELECT FROM DATABASE
* //
*/
cmd.CommandText = "SELECT * from Mytable";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["Name"] + " : " + reader["Gender"]);
}
conn.Close();
}
}
/*
* DELETE FROM DATABASE
*
*/
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
SQLiteTransaction trans;
conn.ConnectionString = @"data source = C:\Users\Kenneth D Clark\Documents\Visual Studio 2017\Projects\Ch27Lab\Ch27Lab\bin\Debug\sample.db3";
conn.Open();
trans = conn.BeginTransaction();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM Mytable WHERE name = @Name";
cmd.Parameters.AddWithValue("@Name", "Jimmy-Crack-Corn");
cmd.ExecuteNonQuery();
trans.Commit();
}
}
Console.ReadLine();
}
private void InsertDBTransaction()
{
///
///INSERT TRANSACTION INTO LOCAL SQLite DATABASE
///
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
cmd.CommandText = @"INSERT INTO RES_KEY
(
OUT_DATETIME,
KEY_BOX_OUT_NO,
RESV_RESERV_NO,
TRANSACTION_OUT_NO,
KIOSK_ID
)
VALUES
(
@KioskDateOut,
@BoxNum,
@ReservNum,
@IdentificationType,
@KioskId
)";
//parameterized insert - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{// SQLite date format is: yyyy-MM-dd HH:mm:ss
ParameterName = "@KioskDateOut",
Value = this.SQLiteDateAndTime,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@BoxNum",
Value = this.BoxNumber,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@ReservNum",
Value = this.AccessCode,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KioskId",
Value = Program.KIOSK_ID,
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Transaction Inserted Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}
CookieJar ICookieRetriever.GetCookies(string url)
{
if (url == null)
{
throw new ArgumentNullException("url");
}
CookieJar cookieJar = new CookieJar();
string domain = new Uri(url).Host.Trim().Replace("www.", "").Trim();
var dbPath = ConfigurationManager.AppSettings["HttpCookieDirectory"];
if (!System.IO.File.Exists(dbPath))
{
return(new CookieJar());
}
var connectionString = "Data Source=" + dbPath + ";";
string commandText = "SELECT name,encrypted_value, host_key, expires_utc, path, secure, httponly FROM cookies WHERE host_key LIKE %@domain";
SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString);
SQLiteCommand cmd = new SQLiteCommand(commandText, conn);
cmd.Parameters.AddWithValue("@domain", domain);
SQLiteDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
var encryptedData = (byte[])reader[1];
var decodedData = System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser);
var plainText = Encoding.ASCII.GetString(decodedData);
HttpCookie c = new HttpCookie();
c.Name = reader["name"].ToString();
c.Domain = reader["host_key"].ToString();
c.Expiry = new DateTime((long)reader["expires_utc"]).ToString();
c.Path = reader["path"].ToString();
c.Type = "http";
c.IsSecure = (reader["secure"].ToString() == "1");
c.IsHttpOnly = (reader["httponly"].ToString() == "1");
c.DateCreated = DateTime.Now.Ticks;
c.Content = plainText;
cookieJar.Add(c);
}
}
catch (Exception err)
{
Debug.WriteLine(err.ToString());
}
finally
{
if (reader != null)
{
reader.Close();
}
conn.Close();
}
return(cookieJar);
}
private void button1_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count != 0)
{
DialogResult resultado = MessageBox.Show("Esta seguro que desea eliminar?", "Seguro?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (resultado == DialogResult.Yes)
{
Int64 articuloID = Int64.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "Delete From Almacen Where [ArticuloID] = " + articuloID + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
appPath = Path.GetDirectoryName(Application.ExecutablePath);
sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBstk.s3db");
cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "Delete From Existencia Where [ArticuloID] = " + articuloID + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
//create the database query
string query = "Select * From Almacen";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
}
else
{
MessageBox.Show("Tiene que elegir un articulo para borrarlo");
}
}
void LoadData()
{
SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("datasource=Northwind_small.sqlite");
conn.Open();
SQLiteCommand cmd = new SQLiteCommand("select * from Customer", conn);
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataTable dataTable = new DataTable();
da.Fill(dataTable);
customers = new List <Customer>();
foreach (DataRow row in dataTable.Rows)
{
customers.Add(
new Models.Customer()
{
Id = (string)row["Id"],
CompanyName = (string)row["CompanyName"],
ContactName = (string)row["ContactName"],
ContactTitle = (string)row["ContactTitle"],
Address = (string)row["Address"],
City = (string)row["City"],
Country = (string)row["Country"],
Fax = row.IsNull("Fax") ? "" : (string)row["Fax"],
Phone = (string)row["Phone"],
PostalCode = row.IsNull("PostalCode") ? "" : (string)row["PostalCode"],
Region = (string)row["Region"]
}
);
}
dataTable = new DataTable();
da.SelectCommand.CommandText = "select * from [order]";
da.Fill(dataTable);
orders = new List <Order>();
foreach (DataRow row in dataTable.Rows)
{
orders.Add(new Order()
{
OrderID = getField <long>(row, "ID"),
CustomerID = getField <string>(row, "CustomerID"),
EmployeeID = getField <long>(row, "EmployeeID"),
OrderDate = Convert.ToDateTime(getField <string>(row, "OrderDate")),
RequiredDate = Convert.ToDateTime(getField <string>(row, "RequiredDate")),
ShipAddress = getField <string>(row, "ShipAddress"),
ShipVia = getField <long>(row, "ShipVia"),
Freight = getField <decimal>(row, "Freight"),
ShipName = getField <string>(row, "ShipName"),
ShipCity = getField <string>(row, "ShipCity"),
ShipPostalCode = getField <string>(row, "ShipPostalCode"),
ShipRegion = getField <string>(row, "ShipRegion"),
ShipCountry = getField <string>(row, "ShipCountry"),
ShippedDate = Convert.ToDateTime(getField <string>(row, "ShippedDate")),
Comment = ""
});
}
orders[0].Comment = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt.";
orders[1].Comment = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.";
orders[2].Comment = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation.";
dataTable = new DataTable();
da.SelectCommand.CommandText = "select * from orderDetail";
da.Fill(dataTable);
orderDetails = new List <OrderDetail>();
foreach (DataRow row in dataTable.Rows)
{
orderDetails.Add(new OrderDetail()
{
OrderID = getField <long>(row, "OrderID"),
ProductID = getField <long>(row, "ProductID"),
Discount = getField <double>(row, "Discount"),
Quantity = getField <long>(row, "Quantity"),
UnitPrice = getField <decimal>(row, "UnitPrice")
});
}
dataTable = new DataTable();
da.SelectCommand.CommandText = "select * from product";
da.Fill(dataTable);
products = new List <Product>();
foreach (DataRow row in dataTable.Rows)
{
products.Add(new Product()
{
ProductID = getField <long>(row, "Id"),
ProductName = getField <string>(row, "ProductName"),
SupplierID = getField <long>(row, "SupplierID"),
CategoryID = getField <long>(row, "CategoryID"),
//QuantityPerUnit= Convert.ToInt32(getField<string>(row, "QuantityPerUnit")),
UnitPrice = getField <decimal>(row, "UnitPrice"),
UnitsInStock = getField <long>(row, "UnitsInStock"),
UnitsOnOrder = getField <long>(row, "UnitsOnOrder"),
ReorderLevel = getField <long>(row, "ReorderLevel"),
Discontinued = Convert.ToBoolean(getField <long>(row, "Discontinued"))
});
}
conn.Close();
foreach (OrderDetail orderDetail in orderDetails)
{
orderDetail.Order = orders.FirstOrDefault(o => o.OrderID == orderDetail.OrderID);
orderDetail.Product = products.FirstOrDefault(p => p.ProductID == orderDetail.ProductID);
}
}
private void button2_Click(object sender, EventArgs e)
{
string numeroencuesta = "";
try
{
numeroencuesta = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para borrar
cmd.CommandText = "DELETE FROM DatosGenerales WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM CuadroFamiliar WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM DatosVivienda WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM EgresosMensuales WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM Observaciones WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM ServicioMedico WHERE [Noencuesta] = " + numeroencuesta;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Encuesta eliminada exitosamente");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;";
//create the database query
string query = "select Noencuesta AS [Numero de Encuesta], Programa, Nombre, Lugardeorigen as [Lugar de Origen], Domicilio, Tiempopermanencia as [Tiempo de Permanencia], NoIntegrantesFam as [Numero de Integrantes Familiares], Parroquia from DatosGenerales";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void firstRunOperations()
{
try //if i get error in this line its only cause the not existance of the db, so we could consider that this is the first run!
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = "SELECT * FROM ISCRITTO";
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
catch (SQLiteException e)
{
string command = @"CREATE TABLE `Iscritto` (
`CodIscritto` INTEGER PRIMARY KEY AUTOINCREMENT,
`Nome` TEXT NOT NULL,
`Cognome` TEXT NOT NULL,
`DataN` TEXT,
`Residenza` TEXT,
`Via` TEXT,
`Recapito` TEXT,
`DataIn` TEXT NOT NULL,
`DataFine` TEXT NOT NULL,
`NIngressi` INTEGER,
`TipoAbb` INTEGER NOT NULL,
`Costo` REAL NOT NULL,
`Email` TEXT,
`CodFisc` TEXT,
`Ntessera` TEXT NOT NULL
);
CREATE TABLE `Certificato` (
`CodIscritto` INTEGER,
`Presente` TEXT NOT NULL,
`DataScadenza` TEXT,
FOREIGN KEY(`CodIscritto`) REFERENCES `Iscritto`(`CodIscritto`));
CREATE TABLE `Rata` (
`CodIscritto` INTEGER,
`Costo` REAL NOT NULL,
`PagamentoAvv` TEXT NOT NULL DEFAULT -1,
`DataPagam` TEXT,
FOREIGN KEY(`CodIscritto`) REFERENCES `Iscritto`(`CodIscritto`));
CREATE TABLE `Corso` (
`Id` INTEGER PRIMARY KEY AUTOINCREMENT,
`Nome` TEXT NOT NULL,
`DataIn` TEXT NOT NULL,
`DataFin` TEXT NOT NULL);
CREATE TABLE `Frequenta` (
`CodIscritto` INTEGER,
`Id` INTEGER,
FOREIGN KEY(`Id`) REFERENCES `Corso`(`Id`),
FOREIGN KEY(`CodIscritto`) REFERENCES `Iscritto`(`CodIscritto`));
CREATE TABLE `User` (
`Id` INTEGER PRIMARY KEY AUTOINCREMENT,
`Username` TEXT NOT NULL UNIQUE,
`Password` TEXT NOT NULL
);
CREATE TABLE `Messaggio` (
`Cod` INTEGER PRIMARY KEY AUTOINCREMENT,
`Id` INTEGER,
`Oggetto` TEXT NOT NULL,
`Text` TEXT,
FOREIGN KEY(`Id`) REFERENCES `User`(`Id`)
);
INSERT INTO User(Username,Password) VALUES ('Admin', 'Password');
INSERT INTO Corso(Nome,DataIn,Datafin) VALUES('generale',date('now'),'2030-03-14');
";
utils.createDB(command);
utils.createFolderBackupDB(@"C:\backupDBgymSoftware");
utils.BackupDB(dbName + ".db", @"C:\backupDBgymSoftware");
createConfigFile();
}
}
private void button1_Click(object sender, EventArgs e)
{
//string used to place cell contents into
string country;
//gets selected row
int rowindex = dataGridView2.CurrentCell.RowIndex;
//enters selected row into a string variable
country = dataGridView2.CurrentCell.Value.ToString();
//string to store selected gender
string selected = listBox1.GetItemText(listBox1.SelectedItem);
//object used to store athletes info
Athlete AddAthlete = new Athlete();
//add athletes info to object
AddAthlete.firstName = textBox1.Text;
AddAthlete.lastName = textBox2.Text;
AddAthlete.gender = selected;
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();//opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry to athlete information
string sql = String.Format("INSERT INTO 'Athlete Information' ('TeamName' , 'AthleteFirstName', 'AthleteLastName', 'Gender') " +
"VALUES ('" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + selected + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Entry has been entered into Database!");
con.Close();
}
string ViewTable = "SELECT * FROM 'Athlete Information'";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
}
private void Form6_Load(object sender, EventArgs e)
{
//stores countries to compare medal winners
string first, second, third;
string[] countries = { "Team America", "Team Great Britian", "Team Norway", "Team Germany", "Team Canada", "Team South Korea", "Team China", "Team Japan", "Team Switzerland", "Team Sweden", "Team Azerbaijan", "Team Bosnia" };
//count stores the medal count for each country as they get added incrimently
int[] count = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
//this is used to populate an event table
string ViewTable = "SELECT * FROM \"ss500m Times Men\" ORDER BY Times DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//stores country name of first second and third places
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View1 = "SELECT * FROM \"ss500m Times Women\" ORDER BY Times DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View1, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View2 = "SELECT * FROM \"ss1000m Times Men\" ORDER BY Times DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View2, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View3 = "SELECT * FROM \"ss1000m Times Women\" ORDER BY Times DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View3, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View4 = "SELECT * FROM \"ss1500m Times Men\" ORDER BY Times DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View4, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View5 = "SELECT * FROM \"ss1500m Times Women\" ORDER BY Times DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View5, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View6 = "SELECT * FROM \"Ice Dance Mens Scores\" ORDER BY Score DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View6, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View7 = "SELECT * FROM \"Ice Dance Womens Scores\" ORDER BY Score DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View7, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View8 = "SELECT * FROM \"Pair Skating Scores\" ORDER BY Score DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View8, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View9 = "SELECT * FROM \"Single Skating Men Scores\" ORDER BY Score DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View9, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
string View10 = "SELECT * FROM \"Single Skating Womens Scores\" ORDER BY Score DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View10, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
first = dataGridView1.Rows[0].Cells[3].Value.ToString();
second = dataGridView1.Rows[1].Cells[3].Value.ToString();
third = dataGridView1.Rows[2].Cells[3].Value.ToString();
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for first if so add to count
if (first == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for second if so add to count
if (second == countries[i])
{
count[i]++;
}
}
//for loop loops through all possible countries to check
for (int i = 0; i < 12; i++)
{
//if statement checks each country if they match for third if so add to count
if (third == countries[i])
{
count[i]++;
}
}
//this will display county names in the datagridview
string ViewT = "SELECT * FROM 'Team Information'";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewT, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//for loop used to populate the medal column
for (int i = 0; i < 12; i++)
{
dataGridView1.Rows[i].Cells[0].Value = count[i];
}
}
public List <T> ReadFromJson <T>([NotNull] ResultTableDefinition rtd, [NotNull] HouseholdKey key,
ExpectedResultCount expectedResult)
{
if (!_isFileNameDictLoaded)
{
LoadFileNameDict();
}
string sql = "SELECT json FROM " + rtd.TableName;
if (!FilenameByHouseholdKey.ContainsKey(key))
{
throw new LPGException("Missing sql file for household key " + key);
}
string constr = "Data Source=" + FilenameByHouseholdKey[key].Filename + ";Version=3";
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(constr)) {
//;Synchronous=OFF;Journal Mode=WAL;
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand()) {
cmd.Connection = conn;
/*
* List<string> tables = new List<string>();
* cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table';";
* using (var reader2 = cmd.ExecuteReader()) {
* while (reader2.Read())
* {
* tables.Add(reader2[0].ToString());
* }
* }*/
cmd.CommandText = sql;
List <string> results = new List <string>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(reader[0].ToString());
}
switch (expectedResult)
{
case ExpectedResultCount.One:
if (results.Count != 1)
{
throw new DataIntegrityException("Not exactly one result");
}
break;
case ExpectedResultCount.Many:
if (results.Count < 2)
{
throw new DataIntegrityException("Not many results");
}
break;
case ExpectedResultCount.OneOrMore:
if (results.Count < 1)
{
throw new DataIntegrityException("Not one or more results");
}
break;
default:
throw new ArgumentOutOfRangeException(nameof(expectedResult), expectedResult, null);
}
List <T> resultsObjects = new List <T>();
foreach (string s in results)
{
T re = JsonConvert.DeserializeObject <T>(s);
resultsObjects.Add(re);
}
conn.Close();
return(resultsObjects);
}
}
}
public static void close()
{
Connected = false;
connection.Close();
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "")
{
bool edadesnumero = true;
try
{
Int32.Parse(textBox2.Text);
}
catch
{
edadesnumero = false;
}
if (edadesnumero == true)
{
try
{
fecha = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
nombre = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
edad = Int32.Parse(dataGridView1.SelectedRows[0].Cells[2].Value.ToString());
apoyo = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "UPDATE Donaciones SET Nombre = '" + textBox1.Text + "', Edad = '" + textBox2.Text + "', Apoyo ='" + textBox3.Text + "', Comunidad = '" + textBox4.Text + "' WHERE Nombre='" + nombre + "' AND Edad='" + edad + "' AND Apoyo = '" + apoyo + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Cambios guardados con exito");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;";
//create the database query
string query = "select * from Donaciones";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No se ha seleccionado ninguna donacion para modificar");
}
}
else
{
MessageBox.Show("Solo se aceptan numeros en el campo de edad");
}
}
else
{
MessageBox.Show("No se pueden guardar los cambios ya que hay campos en blanco");
}
}
/// <summary>
/// Функция получения данных для MSSQL, Postgre, SQLite. Результат в DataSet. Возвращает несколько таблиц.
/// </summary>
/// <param name="sql">Запрос любой, select, update, delete, ddl</param>
/// <param name="ds">DataSet - набор таблиц</param>
/// <param name="errorMes">Сообщение об ошибке</param>
/// <param name="errorShow">Показывать или нет ошибки</param>
/// <returns>Если успешно, то true</returns>
public bool SelectDS(string sql, out DataSet ds, out string errorMes, bool errorShow)
{
ds = new DataSet("DS");
errorMes = "";
if ((sys.IsEmpty(sql)) || (sql == "Empty"))
{
return(false);
}
try
{
if (ConnectionDirect)
{
if (serverType == ServerType.Postgre)
{
Npgsql.NpgsqlConnection connection = null; //Postgres
connection = new Npgsql.NpgsqlConnection(ConnectStringSQLite);
connection.Open();
var da = new NpgsqlDataAdapter(sql, connection);
da.Fill(ds);
connection.Close();
}
if (serverType == ServerType.MSSQL)
{
var connection = new System.Data.SqlClient.SqlConnection(ConnectStringMSSQL);
connection.Open();
var da = new SqlDataAdapter(sql, connection);
da.Fill(ds);
connection.Close();
}
}
if (serverType == ServerType.SQLite)
{
var connection = new System.Data.SQLite.SQLiteConnection(ConnectStringSQLite);
connection.Open();
var da = new SQLiteDataAdapter(sql, connection);
da.Fill(ds);
connection.Close();
//connection.BindFunction(CloseToSQLiteFunction.GetAttribute(), new CloseToSQLiteFunction());
}
if (!ConnectionDirect)
{
if (!AppSelectDS(sql, out ds))
{
return(false);
}
}
LastQueryDateTime = DateTime.Now;
return(true);
}
catch (Exception e)
{
errorMes = "703. Ошибка выполнения запроса: " + errorMes + Var.CR + e.Message + Var.CR + sql;
if (errorShow)
{
sys.SM(errorMes);
}
ds = null;
return(false);
}
}
static void Main(string[] args)
{
//To store the index and column name from the file
Dictionary <int, string> Columns = new Dictionary <int, string>();
//To store datatypes
Dictionary <string, string> ColumnDataTypes = new Dictionary <string, string>();
ColumnDataTypes["id"] = "integer";
ColumnDataTypes["dt"] = "datetime";
ColumnDataTypes["product_id"] = "integer";
ColumnDataTypes["amount"] = "double";
// Define delimiter characters
char[] delimiterChars = { '\t' };
//Create Sample data
string createQuery = @" create table if not exists products(id integer not null primary key, name text);
insert into products (name) values ('A');
insert into products (name) values ('B');
insert into products (name) values ('C');
insert into products (name) values ('D');
insert into products (name) values ('E');
insert into products (name) values ('F');
insert into products (name) values ('G');
create table if not exists orders(
id integer not null CHECK(TYPEOF(id) = 'integer'),
dt datetime CHECK(JULIANDAY(dt) IS NOT NULL),
product_id integer CHECK(TYPEOF(product_id) = 'integer'),
amount real CHECK(amount > 0 AND TYPEOF(amount) = 'real'),
foreign key(product_id) references products(id)
);";
System.Data.SQLite.SQLiteConnection.CreateFile("myDB.db3");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=myDB.db3;foreign keys=true;"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
// Read file from App_Data folder
string[] lines = System.IO.File.ReadAllLines(Directory.GetCurrentDirectory() + @"../../../App_Data/import.txt");
cmd.CommandText = "INSERT INTO orders (";
// Identify the column order from first row of the import file
string[] elements = lines[0].Split(delimiterChars);
for (int i = 0; i < elements.Length; i++)
{
Columns[i] = elements[i];
cmd.CommandText = cmd.CommandText + elements[i] + ", ";
}
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 2);
cmd.CommandText = cmd.CommandText + ") VALUES (";
string temp = cmd.CommandText;
//Create Insert Statements
//As insert itself is slow, it is better to use transaction
using (SQLiteTransaction tr = conn.BeginTransaction())
{
int j = 0;
cmd.Transaction = tr;
for (int i = 1; i < lines.Length; i++)
{
try
{
cmd.CommandText = temp;
elements = lines[i].Split(delimiterChars);
for (j = 0; j < elements.Length; j++)
{
switch (ColumnDataTypes[Columns[j]])
{
case "double":
double columnDouble = Convert.ToDouble(elements[j]);
cmd.CommandText = cmd.CommandText + "@VALUE" + j + ", ";
cmd.Parameters.AddWithValue("@VALUE" + j, columnDouble);
break;
case "integer":
int columnInt = Int32.Parse(elements[j]);
cmd.CommandText = cmd.CommandText + "@VALUE" + j + ", ";
cmd.Parameters.AddWithValue("@VALUE" + j, columnInt);
break;
default:
cmd.CommandText = cmd.CommandText + "@VALUE" + j + ", ";
cmd.Parameters.AddWithValue("@VALUE" + j, elements[j]);
break;
}
}
// Insert values, try catch exceptions
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 2);
cmd.CommandText = cmd.CommandText + ")";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("On line " + (i + 1) + " caught exception: " + ex.Message + "\nThe Insert Statement: " + cmd.CommandText + "\nValues: " + string.Join(", ", elements) + "\n");
}
}
catch (Exception ex)
{
Console.WriteLine("On line " + (i + 1) + " caught exception: " + ex.Message + "\nColumn: " + Columns[j] + ", value: " + elements[j]);
break;
}
}
tr.Commit();
}
cmd.CommandText = "Select * from orders";
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("ID | Datetime |Product_ID|Amount|");
while (reader.Read())
{
Console.WriteLine(reader["ID"] + " | " + reader["dt"] + " | " + reader["product_id"] + " | " + reader["amount"]);
}
}
// SQL query 1
cmd.CommandText = @"select
a.product_id,
count(*) as cnt,
sum(amount) as sm
from orders a
where strftime('%m', dt) = '10' and strftime('%Y', dt) = '2017'
group by product_id;";
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("\n1.Query");
Console.WriteLine(" product_id | count | Sum ");
while (reader.Read())
{
Console.WriteLine(reader["product_id"] + " | " + reader["cnt"] + " | " + " | " + reader["sm"]);
}
}
// SQL query 2.a
cmd.CommandText = @"select
a.product_id
from orders a
where strftime('%m', dt) = strftime('%m', date('now')) and strftime('%Y', dt) = strftime('%Y', date('now')) and
product_id not in (select product_id from orders where strftime('%m', dt) = strftime('%m', date('now', '-1 month')) and strftime('%Y', dt) = strftime('%Y', date('now')) )
group by product_id
;";
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("2.a query\nproduct_id ");
while (reader.Read())
{
Console.WriteLine(reader["product_id"]);
}
}
// SQL query 2.b
cmd.CommandText = @"select
a.product_id
from orders a
where strftime('%m', dt) = strftime('%m', date('now', '-1 month')) and strftime('%Y', dt) = strftime('%Y', date('now')) and
product_id not in (select product_id from orders where strftime('%m', dt) = strftime('%m', date('now')) and strftime('%Y', dt) = strftime('%Y', date('now')) )
group by product_id
;";
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("2.b query\nproduct_id ");
while (reader.Read())
{
Console.WriteLine(reader["product_id"]);
}
}
//SQL query 3
cmd.CommandText = @"select
strftime('%m', a.dt) || '/' || strftime('%Y', a.dt) as Period,
a.product_id, max(sm) as Mx,
max(sm)*100 /b.total as Percentage
from
(select
dt,
product_id,
sum(amount) as sm
from orders
group by
strftime('%Y', dt),
strftime('%m', dt),
product_id) a
left join
(select
strftime('%m', dt) as month,
strftime('%Y', dt) as year,
sum(amount) as total
from orders
group by
strftime('%Y', dt),
strftime('%m', dt)) b
on strftime('%m',a.dt) = b.month and strftime('%Y',a.dt) = b.year
group by
strftime('%m', a.dt),
strftime('%Y', a.dt);" ;
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("\n3. ");
Console.WriteLine(" Period | Product_id | Sum | Percentage |");
while (reader.Read())
{
Console.WriteLine(reader["Period"] + " | " + reader["product_id"] + " | " + reader["Mx"] + " | " + reader["Percentage"]);
}
}
conn.Close();
}
}
Console.WriteLine("Press any key to exit.");
Console.ReadLine();
}
private void UpdateDBTransaction()
{
///
///UPDATE TRANSACTION INTO REMOTE SQL SERVER DATABASE
///
#region SQL SERVER
int intReturnValue = 0;
int intOutput1 = 0;
SqlCommand comm = new SqlCommand(); //This is needed because there is no public contructor for SqlParameterCollection
SqlParameterCollection Parameters = comm.Parameters;
SqlParameterCollection ReturnParameters;
try
{
//Input Parameters
Parameters.Add(Program.SqlManager.CreateParameter("ResvNum", ParameterDirection.Input, this.AccessCode, SqlDbType.VarChar));
Parameters.Add(Program.SqlManager.CreateParameter("OperNum", ParameterDirection.Input, this.OperatorNumber, SqlDbType.VarChar));
Parameters.Add(Program.SqlManager.CreateParameter("KioskDateIn", ParameterDirection.Input, this.DateAndTime, SqlDbType.VarChar));
Parameters.Add(Program.SqlManager.CreateParameter("KioskNumIn", ParameterDirection.Input, Program.KIOSK_ID.PadLeft(4, '0'), SqlDbType.VarChar));
Parameters.Add(Program.SqlManager.CreateParameter("MileageEnd", ParameterDirection.Input, this.Odometer, SqlDbType.Int));
Parameters.Add(Program.SqlManager.CreateParameter("KeyNumMax", ParameterDirection.Input, Program.NUMBER_RELAYS, SqlDbType.Int));
//Return Values
Parameters.Add(Program.SqlManager.CreateParameter("KeyNumIn", ParameterDirection.Output, 0, SqlDbType.Int));
Parameters.Add(Program.SqlManager.CreateParameter("Return_Val", ParameterDirection.ReturnValue, 0, SqlDbType.Int));
ReturnParameters = Program.SqlManager.SqlStoredProcedure("SPA_ReturnKey", Parameters);
intOutput1 = (int)ReturnParameters["KeyNumIn"].Value;
//set the return code (of the stored procedure) to the results sent back.
intReturnValue = (int)ReturnParameters["Return_Val"].Value;
switch (intReturnValue)
{
case 1:
//Success
Program.logEvent("Transaction Data Successfully Updated into Database");
break;
case 12:
//Failure
Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000);
Program.logEvent("Failure - can't save data - perhaps server is down.");
Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - can't save data - perhaps server is down.", "", Odometer);
break;
case 13:
//Failure
Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000);
Program.logEvent("Failure - RES_KEY table INSERT failed.");
Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - RES_KEY table INSERT failed.", "", Odometer);
break;
case 14:
//Failure
Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000);
Program.logEvent("Failure - Key# not in range (CAST(em.PARKING_STALL as int)).");
Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - Key# not in range (CAST(em.PARKING_STALL as int)).", "", Odometer);
break;
}
}
catch (Exception ex)
{
//An application exception was thrown - process/display exception.
Program.logEvent("GENERAL EXCEPTION:" + "Transaction Update: " + ex.Message);
Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "GENERAL EXCEPTION:" + "Transaction Update: " + ex.Message, "", Odometer);
Program.ShowErrorMessage("Database Error\r\nTransaction Data Saved\r\nPlease Call\r\n" + Program.SERVICE_MANAGER_NUMBER, 5000);
}
#endregion
///
///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE
///
#region SQLite
if (!Program.ENABLE_SQLITE)
{
return;
}
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
// '56650' , '501889889' , 38318 , '2012-12-20' , 'RETURNED' 3 '2012-12-20' , 'COTTAGE GROVE','OR' '011563'
//RESV_RESERV_NO OPER_OPER_NO METER_1_READING_OUT REQUIRED_DATETIME STATUS KeyBoxNum DUE_DATETIME_ORIG DESTINATION_CITY EQ_EQUIP_NO
cmd.CommandText = @"UPDATE RES_KEY
SET
KIOSK_IN_NO = @KioskNumIn,
KEY_BOX_IN_NO = @KeyNumIn,
RETURN_DATETIME = @ReturnDate,
METER_1_READING_OUT = @MileageBegin,
METER_1_READING_IN = @MileageEnd
WHERE
RESV_RESERV_NO = @ResvNum
";
//parameterized update - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KioskNumIn",
Value = Program.KIOSK_ID.PadLeft(4, '0'),
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KeyNumIn",
Value = this.BoxNumber,
});
// SQLite date format is: yyyy-MM-dd HH:mm:ss
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@ReturnDate",
Value = this.SQLiteDateAndTime,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@MileageBegin",
Value = " ",
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@MileageEnd",
Value = this.Odometer,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@ResvNum",
Value = this.AccessCode
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Transaction Updated Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
#endregion
}
private void button1_Click_1(object sender, EventArgs e)
{
ArticiculoID = new Int64[listBox1.Items.Count];
cantidad = new int[listBox1.Items.Count];
total = new float[listBox1.Items.Count];
cantidadexistencia = new int[listBox1.Items.Count];
nombre = new string[listBox1.Items.Count];
for (int cont = 0; cont < listBox1.Items.Count; cont++)
{
ArticiculoID[cont] = Int64.Parse(listBox1.Items[cont].ToString());
cantidad[cont] = Int32.Parse(listBox2.Items[cont].ToString());
total[cont] = float.Parse(listBox3.Items[cont].ToString());
cantidadexistencia[cont] = cantidadexistenciatotal1[cont] - cantidadexistenciatotal2[cont];
nombre[cont] = listBox4.Items[cont].ToString();
}
if (listBox1.Items.Count != 0)
{
float cambio = 0.0F;
try
{
cambio = float.Parse(textBox4.Text) - float.Parse(label18.Text);
}
catch
{
cambio = -1;
}
if (cambio >= 0)
{
float vtotal = 0.0F;
for (int i = 0; i < total.Length; i++)
{
vtotal += total[i];
}
try
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBPInc.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "INSERT INTO Ventas Values ('" + Int32.Parse(textBox1.Text) + "', '" + vtotal + "', '" + DateTime.Now.ToShortDateString() + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//ventas hechas
appPath = Path.GetDirectoryName(Application.ExecutablePath);
sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBPInc.s3db ;Version=3;");
cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
for (int i = 0; i < ArticiculoID.Length; i++)
{
//comando sql para insercion
cmd.CommandText = "INSERT INTO Ventashechas Values ('" + Int32.Parse(textBox1.Text) + "', '" + ArticiculoID[i] + "', '" + nombre[i] + "', '" + cantidad[i] + "', '" + total[i] + "', '" + DateTime.Now.ToShortDateString() + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para atualizar
cmd.CommandText = "UPDATE Almacen Set Cantidadexistencia = '" + cantidadexistencia[i] + "' Where ArticuloID = '" + ArticiculoID[i] + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
DialogResult result = MessageBox.Show("Venta registrada exitosamente, su cambio es: $" + cambio.ToString() + " pesos , desea ver el documento de impresion?", "Impresion", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
Reporte reporte = new Reporte(textBox1.Text);
reporte.MdiParent = Sistema_Caritas.Bienvenida.ActiveForm;
reporte.Show();
}
textBox1.Text = (Int64.Parse(textBox1.Text) + 1).ToString();
textBox2.Text = "";
label6.Text = "";
label5.Text = "";
textBox3.Text = "";
label10.Text = "";
listBox1.Items.Clear();
listBox2.Items.Clear();
listBox3.Items.Clear();
listBox4.Items.Clear();
textBox4.Text = "";
label18.Text = "0";
ct = 0;
for (int i = 0; i < cantidadexistenciatotal1.Length; i++)
{
cantidadexistenciatotal1[i] = 0;
cantidadexistenciatotal2[i] = 0;
}
}
catch
{
int result;
bool accept = Int32.TryParse(textBox1.Text, out result);
if (accept == false)
{
MessageBox.Show("No ha introducido algun numero de venta o no es valido");
}
else
{
MessageBox.Show("El numero de venta ya ha sido tomado");
}
}
}
else
{
MessageBox.Show("Cantidad a pagar insuficiente");
}
}
else
{
MessageBox.Show("No hay ventas que registrar");
}
}
private void button1_Click(object sender, EventArgs e)
{
string idformatosilla = "";
try
{
idformatosilla = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para borrar
cmd.CommandText = "DELETE FROM SRDatosGenerales WHERE [IDFormatoSillas] = " + idformatosilla;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para borrar
cmd.CommandText = "DELETE FROM SRTamanoTipo WHERE [IDFormatoSillas] = " + idformatosilla;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Expediente eliminado exitosamente");
appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;";
//create the database query
string query = "select * from SRDatosGenerales";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
catch
{
MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Botón_Cargar_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
if (!string.IsNullOrEmpty(ComboBox_Ruta.Text) && File.Exists(ComboBox_Ruta.Text))
{
ComboBox_Fila.Items.Clear();
string Texto_Conexión = "Data Source=" + ComboBox_Ruta.Text + ";New=False;Version=3";
System.Data.SQLite.SQLiteConnection Conexión_SQL = new System.Data.SQLite.SQLiteConnection(Texto_Conexión);
Conexión_SQL.Open();
string Texto_Origen = "sqlite_master";
string Texto_Comando = "Select name from sqlite_master;"; // Get the main names.
System.Data.SQLite.SQLiteDataAdapter Adaptador_SQL = new System.Data.SQLite.SQLiteDataAdapter(Texto_Comando, Conexión_SQL);
DataSet Set_Datos = new DataSet();
Set_Datos.RemotingFormat = SerializationFormat.Binary;
Adaptador_SQL.Fill(Set_Datos, Texto_Origen);
if (Set_Datos.Tables != null && Set_Datos.Tables.Count > 0)
{
foreach (DataTable Tabla in Set_Datos.Tables)
{
try
{
if (Tabla != null &&
Tabla.Columns != null &&
Tabla.Columns.Count > 0 &&
Tabla.Columns[0] != null &&
Tabla.Rows != null &&
Tabla.Rows.Count > 0)
{
foreach (DataRow Fila in Tabla.Rows)
{
try
{
if (Fila != null && !string.IsNullOrEmpty(Fila[0] as string))
{
ComboBox_Fila.Items.Add(Fila[0] as string);
}
}
catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; continue; }
}
}
}
catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; continue; }
}
}
Set_Datos.Dispose();
Set_Datos = null;
Adaptador_SQL.Dispose();
Adaptador_SQL = null;
Conexión_SQL.Close();
Conexión_SQL.Dispose();
Conexión_SQL = null;
/*string Texto_Conexión = "Data Source=" + ComboBox_Ruta.Text + ";New=False;Version=3";
* SQLiteConnection Conexión_SQL = new SQLiteConnection(Texto_Conexión);
* Conexión_SQL.Open();
* string Texto_Origen = "sqlite_master";
* string Texto_Comando = "Select name from sqlite_master;"; // Get the main names.
* SQLiteDataAdapter Adaptador_SQL = new SQLiteDataAdapter(Texto_Comando, Conexión_SQL);
* DataSet Set_Datos = new DataSet();
* Set_Datos.RemotingFormat = SerializationFormat.Binary;
* Adaptador_SQL.Fill(Set_Datos, Texto_Origen);
* if (Set_Datos.Tables != null && Set_Datos.Tables.Count > 0)
* {
* foreach (DataTable Tabla in Set_Datos.Tables)
* {
* try
* {
* if (Tabla != null &&
* Tabla.Columns != null &&
* Tabla.Columns.Count > 0 &&
* Tabla.Columns[0] != null &&
* Tabla.Rows != null &&
* Tabla.Rows.Count > 0)
* {
* foreach (DataRow Fila in Tabla.Rows)
* {
* try
* {
* if (Fila != null && !string.IsNullOrEmpty(Fila[0] as string))
* {
* ComboBox_Fila.Items.Add(Fila[0] as string);
* }
* }
* catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; continue; }
* }
* }
* }
* catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; continue; }
* }
* }
* Set_Datos.Dispose();
* Set_Datos = null;
* Adaptador_SQL.Dispose();
* Adaptador_SQL = null;
* Conexión_SQL.Close();
* Conexión_SQL.Dispose();
* Conexión_SQL = null;*/
if (ComboBox_Fila.Items.Count > 0)
{
ComboBox_Fila.SelectedIndex = 0;
}
}
}
catch (Exception Excepción) { Depurador.Escribir_Excepción(Excepción != null ? Excepción.ToString() : null); Variable_Excepción_Total++; Variable_Excepción = true; }
finally { this.Cursor = Cursors.Default; }
}
private void button3_Click(object sender, EventArgs e)
{
double total;
double[] num = new double[7];
num[0] = Convert.ToDouble(textBox2.Text);
num[1] = Convert.ToDouble(textBox3.Text);
num[2] = Convert.ToDouble(textBox4.Text);
num[3] = Convert.ToDouble(textBox5.Text);
num[4] = Convert.ToDouble(textBox6.Text);
num[5] = Convert.ToDouble(textBox7.Text);
num[6] = Convert.ToDouble(textBox8.Text);
total = num[0] + num[1] + num[2] + num[3] + num[4] + num[5] + num[6];
total = total - (num.Min() + num.Max());
total = total / 5;
//class created to store athletes info
Athlete AthleteScore = new Athlete();
//stores name of athlete
AthleteScore.firstName = dataGridView1.SelectedCells[1].Value.ToString();
AthleteScore.lastName = dataGridView1.SelectedCells[2].Value.ToString();
//creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();//opens DB
//checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//adds entry from event to the specific event
string sql = String.Format("UPDATE " + SelectedEvent + " SET Score = " + total +
" WHERE \"Athlete First Name\" = '" + AthleteScore.firstName + "' AND \"Athlete Last Name\" = '" + AthleteScore.lastName + "';");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
MessageBox.Show("Score has been entered into " + AthleteScore.firstName + " " + AthleteScore.lastName + "'s score");
//this will update the score board to reflect the changes
string View = "SELECT * FROM " + SelectedEvent + " ORDER BY Score DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(View, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
string selected = listBox1.GetItemText(listBox1.SelectedItem);
if (selected == "ss500m Times Men")
{
//global string used to place event name into it
SelectedEvent = "\"ss500m Times Men\"";
label1.Text = "Mens 500m Speed Skating"; label1.Refresh();
string ViewTable = "SELECT * FROM \"ss500m Times Men\" ORDER BY Times DESC;";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
//make buttons visible that will work with this table to enter times in
button2.Visible = true;
label4.Visible = true;
textBox1.Visible = true;
}
if (selected == "ss500m Times Women")
{
//global string used to place event name into it
SelectedEvent = "\"ss500m Times Women\"";
label1.Text = "Womens 500m Speed Skating"; label1.Refresh();
string ViewTable = "SELECT * FROM \"ss500m Times Women\" ORDER BY Times DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
//make buttons visible that will work with this table to enter times in
button2.Visible = true;
label4.Visible = true;
textBox1.Visible = true;
//makes sure unused components are disabled
label6.Visible = false;
label7.Visible = false;
button3.Visible = false;
textBox2.Visible = false;
textBox3.Visible = false;
textBox4.Visible = false;
textBox5.Visible = false;
textBox6.Visible = false;
textBox7.Visible = false;
textBox8.Visible = false;
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "ss1000m Times Men")
{
SelectedEvent = "\"ss1000m Times Men\"";
label1.Text = "Mens 1000m Speed Skating"; label1.Refresh();
string ViewTable = "SELECT * FROM \"ss1000m Times Men\" ORDER BY Times DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
//make buttons visible that will work with this table to enter times in
button2.Visible = true;
label4.Visible = true;
textBox1.Visible = true;
//makes sure unused components are disabled
label6.Visible = false;
label7.Visible = false;
button3.Visible = false;
textBox2.Visible = false;
textBox3.Visible = false;
textBox4.Visible = false;
textBox5.Visible = false;
textBox6.Visible = false;
textBox7.Visible = false;
textBox8.Visible = false;
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "ss1000m Times Women")
{
SelectedEvent = "\"ss1000m Times Women\"";
label1.Text = "Womens 1000m Speed Skating"; label1.Refresh();
string ViewTable = "SELECT * FROM \"ss1000m Times Women\" ORDER BY Times DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
//make buttons visible that will work with this table to enter times in
button2.Visible = true;
label4.Visible = true;
textBox1.Visible = true;
//makes sure unused components are disabled
label6.Visible = false;
label7.Visible = false;
button3.Visible = false;
textBox2.Visible = false;
textBox3.Visible = false;
textBox4.Visible = false;
textBox5.Visible = false;
textBox6.Visible = false;
textBox7.Visible = false;
textBox8.Visible = false;
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "ss1500m Times Men")
{
SelectedEvent = "\"ss1500m Times Men\"";
label1.Text = "Mens 1500m Speed Skating"; label1.Refresh();
string ViewTable = "SELECT * FROM \"ss1500m Times Men\" ORDER BY Times DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//make buttons visible that will work with this table to enter times in
button2.Visible = true;
label4.Visible = true;
textBox1.Visible = true;
//makes sure unused components are disabled
label6.Visible = false;
label7.Visible = false;
button3.Visible = false;
textBox2.Visible = false;
textBox3.Visible = false;
textBox4.Visible = false;
textBox5.Visible = false;
textBox6.Visible = false;
textBox7.Visible = false;
textBox8.Visible = false;
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "ss1500m Times Women")
{
SelectedEvent = "\"ss1500m Times Women\"";
label1.Text = "Womens 1500m Speed Skating"; label1.Refresh();
string ViewTable = "SELECT * FROM \"ss1500m Times Women\" ORDER BY Times DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
//make buttons visible that will work with this table to enter times in
button2.Visible = true;
label4.Visible = true;
textBox1.Visible = true;
//makes sure unused components are disabled
label6.Visible = false;
label7.Visible = false;
button3.Visible = false;
textBox2.Visible = false;
textBox3.Visible = false;
textBox4.Visible = false;
textBox5.Visible = false;
textBox6.Visible = false;
textBox7.Visible = false;
textBox8.Visible = false;
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "Ice Dance Mens Scores")
{
SelectedEvent = "\"Ice Dance Mens Scores\"";
label1.Text = "Ice Dance Mens"; label1.Refresh();
string ViewTable = "SELECT * FROM \"Ice Dance Mens Scores\" ORDER BY Score DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
//makes the correct components are visible to enter scores
label6.Visible = true;
label7.Visible = true;
button3.Visible = true;
textBox2.Visible = true;
textBox3.Visible = true;
textBox4.Visible = true;
textBox5.Visible = true;
textBox6.Visible = true;
textBox7.Visible = true;
textBox8.Visible = true;
//makes sure the other components are disabled
button2.Visible = false;
label4.Visible = false;
textBox1.Visible = false;
}
if (selected == "Ice Dance Womens Scores")
{
SelectedEvent = "\"Ice Dance Womens Scores\"";
label1.Text = "Ice Dance Womens"; label1.Refresh();
string ViewTable = "SELECT * FROM \"Ice Dance Womens Scores\" ORDER BY Score DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
//makes the correct components are visible to enter scores
label6.Visible = true;
label7.Visible = true;
button3.Visible = true;
textBox2.Visible = true;
textBox3.Visible = true;
textBox4.Visible = true;
textBox5.Visible = true;
textBox6.Visible = true;
textBox7.Visible = true;
textBox8.Visible = true;
//makes sure the other components are disabled
button2.Visible = false;
label4.Visible = false;
textBox1.Visible = false;
}
if (selected == "Single Skating Men Scores")
{
SelectedEvent = "\"Single Skating Men Scores\"";
label1.Text = "Single Skating Mens"; label1.Refresh();
string ViewTable = "SELECT * FROM \"Single Skating Men Scores\" ORDER BY Score DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
//makes the correct components are visible to enter scores
label6.Visible = true;
label7.Visible = true;
button3.Visible = true;
textBox2.Visible = true;
textBox3.Visible = true;
textBox4.Visible = true;
textBox5.Visible = true;
textBox6.Visible = true;
textBox7.Visible = true;
textBox8.Visible = true;
//makes sure the other components are disabled
button2.Visible = false;
label4.Visible = false;
textBox1.Visible = false;
}
if (selected == "Single Skating Womens Scores")
{
SelectedEvent = "\"Single Skating Womens Scores\"";
label1.Text = "Single Skating Womens"; label1.Refresh();
string ViewTable = "SELECT * FROM \"Single Skating Womens Scores\" ORDER BY Score DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
//makes the correct components are visible to enter scores
label6.Visible = true;
label7.Visible = true;
button3.Visible = true;
textBox2.Visible = true;
textBox3.Visible = true;
textBox4.Visible = true;
textBox5.Visible = true;
textBox6.Visible = true;
textBox7.Visible = true;
textBox8.Visible = true;
//makes sure the other components are disabled
button2.Visible = false;
label4.Visible = false;
textBox1.Visible = false;
}
if (selected == "Pair Skating Scores")
{
SelectedEvent = "\"Pair Skating Scores\"";
label1.Text = "Pair Skating Scores"; label1.Refresh();
string ViewTable = "SELECT * FROM \"Pair Skating Scores\" ORDER BY Score DESC";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
//this gets the number of entries the event has
int GridCount = dataGridView1.Rows.Count;
//for loop used to populate the scores column with places
for (int i = 0; i < GridCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
//makes the correct components are visible to enter scores
label6.Visible = true;
label7.Visible = true;
button3.Visible = true;
textBox2.Visible = true;
textBox3.Visible = true;
textBox4.Visible = true;
textBox5.Visible = true;
textBox6.Visible = true;
textBox7.Visible = true;
textBox8.Visible = true;
//makes sure the other components are disabled
button2.Visible = false;
label4.Visible = false;
textBox1.Visible = false;
}
}
private void UpdateDBTransaction()
{
///
///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE
///
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
cmd.CommandText = @"UPDATE RES_KEY
SET
IN_DATETIME = @ReturnDate,
KEY_BOX_IN_NO = @KeyBoxNumIn,
TRANSACTION_IN_NO = @TransactionNum,
CAR_DAMAGED = @CarDamaged,
CAR_CLEANED = @CarCleaned,
CAR_REFUELED = @CarRefueled
WHERE
RESV_RESERV_NO = @ResvNum
";
//parameterized update - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{// SQLite date format is: yyyy-MM-dd HH:mm:ss
ParameterName = "@ReturnDate",
Value = this.SQLiteDateAndTime,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KeyBoxNumIn",
Value = this.BoxNumber,
});
//cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
//{
// ParameterName = "@CarDamaged",
// Value = this.CarDamaged,
//});
//cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
//{
// ParameterName = "@CarCleaned",
// Value = this.CarCleaned,
//});
//cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
//{
// ParameterName = "@CarRefueled",
// Value = this.CarRefueled,
//});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@ResvNum",
Value = this.AccessCode
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Transaction Updated Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}
private void button3_Click_1(object sender, EventArgs e)
{
int cant = 0;
bool cantidad = Int32.TryParse(textBox6.Text, out cant);
float vent = 0.0F;
bool venta = float.TryParse(textBox7.Text, out vent);
float comp = 0.0F;
bool compra = float.TryParse(textBox8.Text, out comp);
if (textBox1.Text != "" && cantidad == true && compra == true && venta == true)
{
try
{
bool existeprov = false;
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Proveedor Where Nombreproveedor = '" + textBox5.Text + "'";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
if (Row["Nombreproveedor"].ToString() == textBox5.Text)
{
existeprov = true;
}
}
if (existeprov == true)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBPInc.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "INSERT INTO Almacen Values ('" + Int64.Parse(textBox4.Text) + "', '" + textBox1.Text + "', '" + float.Parse(textBox7.Text) + "', '" + float.Parse(textBox8.Text) + "', '" + Int32.Parse(textBox6.Text) + "' ,'" + textBox5.Text + "', '" + DateTime.Now.ToShortDateString() + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBstk.s3db ;Version=3;");
cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO Existencia Values('" + Int64.Parse(textBox4.Text) + "', '" + Int64.Parse(textBox13.Text) + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox7.Text = "";
textBox8.Text = "";
textBox1.Text = "";
textBox13.Text = "";
}
else
{
MessageBox.Show("El Proveedor Especificado no existe");
}
}
catch
{
MessageBox.Show("Ya existe un articulo con esa ID");
}
}
else
{
if (textBox1.Text != "")
{
MessageBox.Show("No ha desginado un nombre para el articulo");
}
else if (cantidad == false)
{
MessageBox.Show("La cantidad puesta no es valida");
}
else if (compra == false)
{
MessageBox.Show("La cantidad puesta no es valida en el campo de compra");
}
else if (venta == false)
{
MessageBox.Show("La cantidad puesta no es valida en el campo de venta");
}
}
}
private void button16_Click(object sender, EventArgs e)
{
//comando sql para insercion en datos generales
cmd.CommandText = "INSERT INTO DatosGenerales VALUES ( '" + label2.Text + "', '" + textBox1.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox45.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox2.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
for (int i = 0; i <= contadordefamilia; i++)
{
if (i == 1)
{
//comando sql para insercion del cuadro familiar
cmd.CommandText = "INSERT INTO CuadroFamiliar VALUES ( '" + label2.Text + "', '" + textBox7.Text + "', '" + textBox8.Text + "', '" + comboBox1.Text + "', '" + comboBox2.Text + "', '" + comboBox3.Text + "', '" + textBox9.Text + "', '" + textBox10.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else if (i == 2)
{
//comando sql para insercion del cuadro familiar
cmd.CommandText = "INSERT INTO CuadroFamiliar VALUES ( '" + label2.Text + "', '" + textBox14.Text + "', '" + textBox13.Text + "', '" + comboBox6.Text + "', '" + comboBox5.Text + "', '" + comboBox4.Text + "', '" + textBox12.Text + "', '" + textBox11.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else if (i == 3)
{
//comando sql para insercion del cuadro familiar
cmd.CommandText = "INSERT INTO CuadroFamiliar VALUES ( '" + label2.Text + "', '" + textBox18.Text + "', '" + textBox17.Text + "', '" + comboBox9.Text + "', '" + comboBox8.Text + "', '" + comboBox7.Text + "', '" + textBox16.Text + "', '" + textBox15.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else if (i == 4)
{
//comando sql para insercion del cuadro familiar
cmd.CommandText = "INSERT INTO CuadroFamiliar VALUES ( '" + label2.Text + "', '" + textBox22.Text + "', '" + textBox21.Text + "', '" + comboBox12.Text + "', '" + comboBox11.Text + "', '" + comboBox10.Text + "', '" + textBox20.Text + "', '" + textBox19.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else if (i == 5)
{
//comando sql para insercion del cuadro familiar
cmd.CommandText = "INSERT INTO CuadroFamiliar VALUES ( '" + label2.Text + "', '" + textBox26.Text + "', '" + textBox25.Text + "', '" + comboBox15.Text + "', '" + comboBox14.Text + "', '" + comboBox13.Text + "', '" + textBox24.Text + "', '" + textBox23.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else if (i == 6)
{
//comando sql para insercion del cuadro familiar
cmd.CommandText = "INSERT INTO CuadroFamiliar VALUES ( '" + label2.Text + "', '" + textBox30.Text + "', '" + textBox29.Text + "', '" + comboBox18.Text + "', '" + comboBox17.Text + "', '" + comboBox16.Text + "', '" + textBox28.Text + "', '" + textBox27.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else if (i == 7)
{
//comando sql para insercion del cuadro familiar
cmd.CommandText = "INSERT INTO CuadroFamiliar VALUES ( '" + label2.Text + "', '" + textBox34.Text + "', '" + textBox33.Text + "', '" + comboBox21.Text + "', '" + comboBox20.Text + "', '" + comboBox19.Text + "', '" + textBox32.Text + "', '" + textBox31.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else if (i == 8)
{
//comando sql para insercion del cuadro familiar
cmd.CommandText = "INSERT INTO CuadroFamiliar VALUES ( '" + label2.Text + "', '" + textBox38.Text + "', '" + textBox37.Text + "', '" + comboBox24.Text + "', '" + comboBox23.Text + "', '" + comboBox22.Text + "', '" + textBox36.Text + "', '" + textBox35.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
}
string situacionvivienda = "";
if (comboBox25.SelectedIndex == 0)
{
situacionvivienda = comboBox25.Text;
}
else if (comboBox25.SelectedIndex == 1)
{
situacionvivienda = comboBox25.Text;
}
else if (comboBox25.SelectedIndex == 2)
{
situacionvivienda = comboBox25.Text + " " + textBox39.Text;
}
else if (comboBox25.SelectedIndex == 3)
{
situacionvivienda = comboBox25.Text + " " + textBox39.Text;
}
string ServiciosPublicos = "";
if (checkedListBox1.GetItemChecked(0))
{
ServiciosPublicos = checkedListBox1.Items[0].ToString();
}
if (checkedListBox1.GetItemChecked(1))
{
if (ServiciosPublicos != "")
{
ServiciosPublicos += ", ";
}
ServiciosPublicos += checkedListBox1.Items[1].ToString();
}
if (checkedListBox1.GetItemChecked(2))
{
if (ServiciosPublicos != "")
{
ServiciosPublicos += ", ";
}
ServiciosPublicos += checkedListBox1.Items[2].ToString();
}
string paredes = "";
if (checkedListBox2.GetItemChecked(0))
{
paredes = checkedListBox2.Items[0].ToString();
}
if (checkedListBox2.GetItemChecked(1))
{
if (paredes != "")
{
paredes += ", ";
}
paredes += checkedListBox2.Items[1].ToString();
}
if (checkedListBox2.GetItemChecked(2))
{
if (paredes != "")
{
paredes += ", ";
}
paredes += checkedListBox2.Items[2].ToString();
}
if (checkedListBox2.GetItemChecked(3))
{
if (paredes != "")
{
paredes += ", ";
}
paredes += checkedListBox2.Items[3].ToString();
}
if (checkedListBox2.GetItemChecked(4))
{
if (paredes != "")
{
paredes += ", ";
}
paredes += checkedListBox2.Items[4].ToString();
}
string piso = "";
if (checkedListBox3.GetItemChecked(0))
{
piso = checkedListBox3.Items[0].ToString();
}
if (checkedListBox3.GetItemChecked(1))
{
if (piso != "")
{
piso += ", ";
}
piso += checkedListBox3.Items[1].ToString();
}
if (checkedListBox3.GetItemChecked(2))
{
if (piso != "")
{
piso += ", ";
}
piso += checkedListBox3.Items[2].ToString();
}
string techo = "";
if (checkedListBox4.GetItemChecked(0))
{
techo = checkedListBox4.Items[0].ToString();
}
if (checkedListBox4.GetItemChecked(1))
{
if (techo != "")
{
techo += ", ";
}
techo += checkedListBox4.Items[1].ToString();
}
if (checkedListBox4.GetItemChecked(2))
{
if (techo != "")
{
techo += ", ";
}
techo += checkedListBox4.Items[2].ToString();
}
string cuartos = "";
cuartos = label25.Text + " " + textBox41.Text + ", " + label26.Text + " " + textBox42.Text + ", " + label27.Text + " " + textBox43.Text + ", " + label28.Text + " " + textBox44.Text;
//comando sql para insertar datos de la vivienda
cmd.CommandText = "INSERT INTO DatosVivienda VALUES ( '" + label2.Text + "', '" + situacionvivienda + "', '" + ServiciosPublicos + "', '" + paredes + "', '" + piso + "', '" + techo + "', '" + textBox40.Text + "', '" + cuartos + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
string enfermedadesfrecuentes = "";
if (checkedListBox5.GetItemChecked(0))
{
enfermedadesfrecuentes = checkedListBox5.Items[0].ToString();
}
if (checkedListBox5.GetItemChecked(1))
{
if (enfermedadesfrecuentes != "")
{
enfermedadesfrecuentes += ", ";
}
enfermedadesfrecuentes += checkedListBox5.Items[1].ToString();
}
if (checkedListBox5.GetItemChecked(2))
{
if (enfermedadesfrecuentes != "")
{
enfermedadesfrecuentes += ", ";
}
enfermedadesfrecuentes += checkedListBox5.Items[2].ToString();
}
if (checkedListBox5.GetItemChecked(3))
{
if (enfermedadesfrecuentes != "")
{
enfermedadesfrecuentes += ", ";
}
enfermedadesfrecuentes += checkedListBox5.Items[3].ToString();
}
if (checkedListBox5.GetItemChecked(4))
{
if (enfermedadesfrecuentes != "")
{
enfermedadesfrecuentes += ", ";
}
enfermedadesfrecuentes += checkedListBox5.Items[4].ToString();
}
//comando sql para insertar datos al servicio medico
cmd.CommandText = "INSERT INTO ServicioMedico VALUES ( '" + label2.Text + "', '" + comboBox26.Text + "', '" + enfermedadesfrecuentes + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
string gastosfamiliares = "";
gastosfamiliares = label34.Text + " " + textBox46.Text + ", " + label35.Text + " " + textBox47.Text + ", " + label36.Text + " " + textBox48.Text + ", " + label37.Text + " " + textBox49.Text + ", " + label38.Text + " " + textBox50.Text + ", " + label40.Text + " " + textBox51.Text + ", " + label41.Text + " " + textBox52.Text;
//comando sql para insertar datos a los egresos mensuales
cmd.CommandText = "INSERT INTO EgresosMensuales VALUES ( '" + label2.Text + "', '" + gastosfamiliares + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
//comando sql para insertar datos a las observaciones
cmd.CommandText = "INSERT INTO Observaciones VALUES ( '" + label2.Text + "', '" + richTextBox1.Text + "', '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "', '" + textBox53.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Datos Guardados con exito");
panel1.BringToFront();
this.Controls.Clear();
this.InitializeComponent();
appPath = Path.GetDirectoryName(Application.ExecutablePath);
sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBESIL.s3db ;Version=3;");
cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
////
appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
connString = @"Data Source= " + appPath2 + @"\DBESIL.s3db ;Version=3;";
//create the database query
query = "SELECT * FROM DatosGenerales";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["Noencuesta"].ToString();
int autonum = Int32.Parse(num);
label2.Text = (autonum + 1).ToString();
}
else
{
label2.Text = "1";
}
}
public bool ErrorDatabaseEntry(string Reservation, string Operator, string SQLFailTimeStamp, string KioskNum, int FailErrorFlag, string Generic1, string Generic2, int Generic3)
{
if (!Program.ENABLE_SQLITE)
{
return(true);
}
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO RES_KEY_FAIL
(
RESV_RESERV_NO,
OPERATOR_NO,
FAIL_DATETIME,
SQL_FAIL_TIME_STAMP,
KIOSK_FAIL_NO,
FAIL_ERROR_FLAG,
COL_GENERIC_1,
COL_GENERIC_2,
COL_GENERIC_3
)
VALUES
(
@Reservation,
@Operator,
@FailDateTime,
@SQLFailDateTime,
@KioskID,
@FailErrFlag,
@Generic1,
@Generic2,
@Generic3
)";
//parameterized update - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Reservation",
Value = Reservation
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Operator",
Value = Operator
});
// SQLite date format is: yyyy-MM-dd HH:mm:ss
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@FailDateTime",
Value = String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now)
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@SQLFailDateTime",
Value = SQLFailTimeStamp
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KioskID",
Value = Program.KIOSK_ID
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@FailErrFlag",
Value = FailErrorFlag
});
string gen1;
if (Generic1.Length > 254)
{
gen1 = Generic1.Substring(0, 254);
}
else
{
gen1 = Generic1;
}
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Generic1",
Value = gen1
});
string gen2;
if (Generic2.Length > 254)
{
gen2 = Generic2.Substring(0, 254);
}
else
{
gen2 = Generic2;
}
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Generic2",
Value = gen2
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Generic3",
Value = Generic3
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Error Inserted Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
return(true);
}
public async void EquipItem(object sender, RoutedEventArgs e)
{
String errorMessage = "Unable to equip item, insufficient values:";
Boolean isOpen = false;
if (ViewModel.Db.CurrentCharacter.Strength < ViewModel.SelectedItem.Strength)
{
int value = ViewModel.SelectedItem.Strength - ViewModel.Db.CurrentCharacter.Strength;
errorMessage += "\n Strength must be increased " + value + " levels.";
isOpen = true;
}
if (ViewModel.Db.CurrentCharacter.Stamina < ViewModel.SelectedItem.Stamina)
{
int value = ViewModel.SelectedItem.Stamina - ViewModel.Db.CurrentCharacter.Stamina;
errorMessage += "\n Stamina must be increased " + value + " levels.";
isOpen = true;
}
if (ViewModel.Db.CurrentCharacter.Constitution < ViewModel.SelectedItem.Constitution)
{
int value = ViewModel.SelectedItem.Constitution - ViewModel.Db.CurrentCharacter.Constitution;
errorMessage += "\n Constitution must be increased " + value + " levels.";
isOpen = true;
}
if (ViewModel.Db.CurrentCharacter.Dexterity < ViewModel.SelectedItem.Dexterity)
{
int value = ViewModel.SelectedItem.Dexterity - ViewModel.Db.CurrentCharacter.Dexterity;
errorMessage += "\n Dexterity must be increased " + value + " levels.";
isOpen = true;
}
if (ViewModel.Db.CurrentCharacter.Wisdom < ViewModel.SelectedItem.Wisdom)
{
int value = ViewModel.SelectedItem.Wisdom - ViewModel.Db.CurrentCharacter.Wisdom;
errorMessage += "\n Wisdom must be increased " + value + " levels.";
isOpen = true;
}
if (ViewModel.Db.CurrentCharacter.Intelligence < ViewModel.SelectedItem.Intelligence)
{
int value = ViewModel.SelectedItem.Intelligence - ViewModel.Db.CurrentCharacter.Intelligence;
errorMessage += "\n Intelligence must be increased " + value + " levels.";
isOpen = true;
}
if (ViewModel.Db.CurrentCharacter.Charisma < ViewModel.SelectedItem.Charisma)
{
int value = ViewModel.SelectedItem.Charisma - ViewModel.Db.CurrentCharacter.Charisma;
errorMessage += "\n Charisma must be increased " + value + " levels.";
isOpen = true;
}
if (isOpen)
{
ContentDialog errorDialog = new ContentDialog()
{
Title = "Insufficient Stats",
Content = errorMessage,
CloseButtonText = "Ok"
};
await errorDialog.ShowAsync();
}
else
{
Item newItem = (Item)(sender as FrameworkElement).DataContext;
App.Database.CurrentCharacter.EquipItem(newItem);
App.Database.SaveCharacterAsync(App.Database.CurrentCharacter);
string sqlQuery = "";
if (ViewModel.SelectedItem.Type == Models.ItemType.WEAPON)
{
sqlQuery = "SELECT Id, Title FROM Item WHERE Equipped = true AND Type = 0";
}
if (ViewModel.SelectedItem.Type == Models.ItemType.ARMOR)
{
sqlQuery = "SELECT Id, Title FROM Item WHERE Equipped = true AND Type = 2";
}
if (ViewModel.SelectedItem.Type == Models.ItemType.POTION)
{
sqlQuery = "SELECT Id, Title FROM Item WHERE Equipped = true AND Type = 1";
}
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "QuestArc.db3");
using (var dbConnection = new System.Data.SQLite.SQLiteConnection("Data Source = " + path))
{
using (var dbcmd = new System.Data.SQLite.SQLiteCommand())
{
dbConnection.Open();
dbcmd.Connection = dbConnection;
dbcmd.CommandText = sqlQuery;
IDataReader reader = dbcmd.ExecuteReader();
string equippedId = null;
string title = "";
while (reader.Read())
{
equippedId = reader["Id"].ToString();
title = reader["Title"].ToString();
}
reader.Close();
if (!newItem.Title.Equals(title))
{
if (equippedId != null)
{
dbcmd.Parameters.AddWithValue("@id", equippedId);
int end = title.IndexOf("-");
dbcmd.Parameters.AddWithValue("@title", title.Substring(0, end));
dbcmd.CommandText = @"UPDATE Item SET Equipped = false WHERE Id = @id";
dbcmd.ExecuteNonQuery();
dbcmd.CommandText = @"UPDATE Item SET Title = @title WHERE Id = @Id";
dbcmd.ExecuteNonQuery();
}
title = newItem.Title + "- Equipped";
dbcmd.Parameters.AddWithValue("@description", newItem.Description);
dbcmd.Parameters.AddWithValue("@title", title);
dbcmd.CommandText = @"UPDATE Item SET Equipped = true WHERE Description = @description";
dbcmd.ExecuteNonQuery();
dbcmd.CommandText = @"UPDATE Item SET Title = @title WHERE Description = @description";
dbcmd.ExecuteNonQuery();
}
dbConnection.Close();
}
ViewModel.UpdateView();
this.Bindings.Update();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
///create the connection string
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Usuarios";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
bool usuarioexistente = false;
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
if (Row["Usuario"].ToString() == textBox1.Text && Row["Contrasena"].ToString() == textBox2.Text)
{
MessageBox.Show("Bienvenido al Sistema Caritas");
((Form)this.MdiParent).Controls["menuStrip1"].Enabled = true;
usuarioexistente = true;
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "Update Usuarios Set FechaUltimaSesion = '" + DateTime.Now.ToShortDateString() + "' Where ID = '" + Row["ID"].ToString() + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
string prueba = Row["TipoDeUsuario"].ToString();
if (Row["TipoDeUsuario"].ToString() == "Administrador")
{
Form frm = (Form)this.MdiParent;
MenuStrip ms = (MenuStrip)frm.Controls["menuStrip1"];
ToolStripMenuItem ti = (ToolStripMenuItem)ms.Items["controlDeUsuariosToolStripMenuItem"];
ti.Visible = true;
}
Bienvenida.tipouser = Row["TipoDeUsuario"].ToString();
this.Close();
//--------------
appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
connString = @"Data Source= " + appPath2 + @"\DBpinc.s3db ;Version=3;";
//create the database query
query = "SELECT * FROM Almacen";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
if (dTable.Rows.Count > 0)
{
for (int k = 0; k < dTable.Rows.Count; k++)
{
Row = dTable.Rows[k];
Int32 cantidadexistencia = Int32.Parse(Row["Cantidadexistencia"].ToString());
string nombre = Row["Nombrearticulo"].ToString();
appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
///create the connection string
connString = @"Data Source= " + appPath2 + @"\DBstk.s3db ;Version=3;";
//create the database query
query = "SELECT * FROM Existencia Where ArticuloID = '" + Row["ArticuloID"].ToString() + "'";
//create an OleDbDataAdapter to execute the query
dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable2 = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable2);
dAdapter.Update(dTable2);
if (dTable2.Rows.Count > 0)
{
Row = dTable2.Rows[0];
Int64 cantlimite = Int64.Parse(Row["Limite"].ToString());
if (cantlimite >= cantidadexistencia)
{
MessageBox.Show("Quedan " + cantlimite.ToString() + " o menos en existencia del articulo " + nombre + "");
}
}
}
}
//--------------
break;
}
}
if (usuarioexistente == false)
{
MessageBox.Show("Usuario o contraseña incorrecta");
}
}
public List <CookieItem> ReadCookies(string hostName)
{
if (hostName == null)
{
throw new ArgumentNullException("hostName");
}
List <CookieItem> ret = new List <CookieItem>();
var oriCookieFile = $@"C:\Users\{localUser}\AppData\Local\115Chrome\User Data\Default\Cookies";
var copyCookieFile = @"c:\setting\Cookies";
if (File.Exists(oriCookieFile))
{
File.Copy(oriCookieFile, copyCookieFile, true);
}
else
{
throw new System.IO.FileNotFoundException("Cant find cookie store", oriCookieFile);
}
var dbPath = copyCookieFile;
if (!System.IO.File.Exists(dbPath))
{
throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath);
}
var connectionString = "Data Source=" + dbPath + ";pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
var prm = cmd.CreateParameter();
prm.ParameterName = "hostName";
prm.Value = hostName;
cmd.Parameters.Add(prm);
cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key = '" + hostName + "'";
//cmd.CommandText = "SELECT * FROM cookies";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = reader[0].ToString();
var encryptedData = (byte[])reader[1];
string encKey = File.ReadAllText($@"C:\Users\{localUser}\AppData\Local\115Chrome\User Data\Local State");
encKey = JObject.Parse(encKey)["os_crypt"]["encrypted_key"].ToString();
var decodedKey = System.Security.Cryptography.ProtectedData.Unprotect(Convert.FromBase64String(encKey).Skip(5).ToArray(), null, System.Security.Cryptography.DataProtectionScope.LocalMachine);
var _cookie = _decryptWithKey(encryptedData, decodedKey, 3);
if (string.IsNullOrEmpty(_cookie))
{
_cookie = Encoding.ASCII.GetString(System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser));
}
if (ret.Find(x => x.Name == name) == null)
{
ret.Add(new CookieItem
{
Name = name,
Value = _cookie
});
}
}
}
conn.Close();
}
return(ret);
}
private void button1_Click_1(object sender, EventArgs e)
{
bool foliop = textBox12.Text.All(Char.IsNumber);
bool folionon = false;
string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Data Source=" + appPath2 + @"\EXCL.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Expediente";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
dAdapter.Update(dTable);
for (int i = 0; i < dTable.Rows.Count; i++)
{
DataRow Row = dTable.Rows[i];
if (textBox12.Text == Row["Folio"].ToString())
{
folionon = true;
break;
}
}
bool edadp = textBox2.Text.All(Char.IsNumber);
float pesov;
bool pesop = float.TryParse(textBox6.Text, out pesov);
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && textBox6.Text != "" && textBox7.Text != "" && textBox8.Text != "" && textBox9.Text != "" && textBox10.Text != "" && textBox11.Text != "" && textBox12.Text != "" && textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "" && textBox17.Text != "" && textBox18.Text != "" && textBox19.Text != "" && textBox20.Text != "" && textBox21.Text != "" && textBox22.Text != "")
{
if (edadp == true)
{
if (pesop == true)
{
if (folionon == false)
{
if (foliop == true)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "INSERT INTO Expediente (Folio, Nombre, Sexo, Edad, Ocupacion, Estadocivil, Religion, TA, Peso, Tema,FC, FR, EnfermedadesFamiliares, AreaAfectada, Antecedentes, Habitos,GPAC,FUMFUP,Motivo, CuadroClinico, ID, EstudiosSolicitados, TX, PX, Doctor, CP, SSA) VALUES ('" + textBox12.Text + "','" + textBox1.Text + "', '" + comboBox1.Text + "', '" + textBox2.Text + "', '" + textBox4.Text + "', '" + comboBox2.Text + "', '" + textBox3.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + textBox8.Text + "', '" + textBox9.Text + "', '" + textBox10.Text + "', '" + comboBox3.Text + "', '" + textBox11.Text + "', '" + textBox13.Text + "', '" + comboBox4.Text + "', '" + comboBox5.Text + "', '" + textBox14.Text + "', '" + textBox15.Text + "', '" + textBox16.Text + "', '" + textBox17.Text + "', '" + textBox18.Text + "', '" + textBox19.Text + "', '" + textBox20.Text + "', '" + textBox21.Text + "', '" + textBox22.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
this.Close();
}
else
{
MessageBox.Show("Solo introduzca numeros en el folio", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Ya existe un expediente con el mismo folio", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Solo introduzca numeros en el peso", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Solo introduzca numeros en la edad", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Ha dejado campos en blanco", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void UpdateDBTransaction()
{
///
///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE
///
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
cmd.CommandText = @"UPDATE RES_KEY
SET
IN_DATETIME = @ReturnDate,
ID_CARD_NO_IN = @CardNoIn,
RFID_TAG_IN_DETECTED = @TagDetectIn
WHERE
ID_CARD_NO_OUT = @CardNoIn
AND
OUT_DATETIME = (SELECT max(OUT_DATETIME) FROM RES_KEY WHERE ID_CARD_NO_OUT = @CardNoIn)
"; //This query should update the newest transaction for CardNoIn
//////Alternatively perhaps we should select the oldest transaction for CardNoIn which hasn't been finished
// ID_CARD_NO_OUT = @CardNoIn
// AND
// OUT_DATETIME = (SELECT min(OUT_DATETIME) FROM RES_KEY WHERE ID_CARD_NO_IN = '')
//parameterized update - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{// SQLite date format is: yyyy-MM-dd HH:mm:ss
ParameterName = "@ReturnDate",
Value = this.SQLiteDateAndTime,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@CardNoIn",
Value = this.CardNumber,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@TagDetectIn",
Value = this.RFIDTagDetected,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KeyBoxNo",
Value = this.BoxNumber
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Transaction Updated Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}