/// <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());
}
}
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;
}
}
}
}
public static DataSet ExecuteDataSet(string SqlRequest, SQLiteConnection Connection)
{
DataSet dataSet = new DataSet();
dataSet.Reset();
SQLiteCommand cmd = new SQLiteCommand(SqlRequest, Connection);
try
{
Connection.Open();
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd);
dataAdapter.Fill(dataSet);
}
catch (SQLiteException ex)
{
Log.Write(ex);
//Debug.WriteLine(ex.Message);
throw; // пересылаем исключение на более высокий уровень
}
finally
{
Connection.Dispose();
}
return dataSet;
}
public DataManager(string dbFilePath)
{
if (string.IsNullOrEmpty(dbFilePath) == true)
{
throw new ArgumentNullException("dbFilePath");
}
_fileExists = File.Exists(CommonConst.DBFileName);
_dbFilePath = dbFilePath;
// DBとの接続を開始する。
_conn = new SQLiteConnection("Data Source=" + CommonConst.DBFileName);
// DBに接続する。
_conn.Open();
_command = _conn.CreateCommand();
// DBファイルが新規作成された場合
if (_fileExists == false)
{
// 空のテーブルを作成する。
this.CreateNewTables();
_fileExists = true;
}
// アプリケーション設定
_applicationSettings.amountSplitCharacter = this.GetAmountsSplitCharacter();
_applicationSettings.commentSplitCharacter = this.GetCommentSplitCharacter();
}
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 loadHeroDiary()
{
// Load the latest Hero Diary entries
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + dbPath + @"\gv.db;Version=3;New=False;Compress=True;"))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
string commandText = "select Diary_ID as ID, Updated, EntryTime, Entry from Diary where HeroName=@HeroName order by Diary_ID desc limit 1000";
cmd.CommandText = commandText;
cmd.Parameters.AddWithValue("@HeroName", this.HeroName);
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
da = new SQLiteDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = ds.Tables[0];
grdDiary.DataSource = bindingSource;
grdDiary.AutoGenerateColumns = true;
grdDiary.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
grdDiary.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
}
}
/// <summary>Write data with a given key to cache</summary>
/// <param name="key">Key of data to be stored</param>
/// <param name="data">Data to be stored</param>
public void Store(string key, string data)
{
SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File);
try
{
DateTime now = DateTime.Now;
connection.Open();
SQLiteCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM [InetCache] WHERE [Keyword]=?";
command.Parameters.Add("kw", DbType.String).Value = key;
command.ExecuteNonQuery(); // out with the old
command = connection.CreateCommand();
command.CommandText = "INSERT INTO [InetCache] ([Timestamp], [Keyword], [Content]) VALUES (?, ?, ?)";
command.Parameters.Add("ts", DbType.Int32).Value = now.Year * 10000 + now.Month * 100 + now.Day;
command.Parameters.Add("kw", DbType.String).Value = key;
command.Parameters.Add("dt", DbType.String).Value = data;
command.ExecuteNonQuery(); // in with the new
}
finally
{
connection.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 void addAccount(string name, string passwd, string url, string provider, string blogname)
{
string conn_str = "Data Source=" + path + pwd_str;
SQLiteConnection conn = new SQLiteConnection(conn_str);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
String sql = "Insert INTO BlogAccount(BlogUrl,BlogName,Provider,AccountName,Password) Values(@url,@blogname,@provider,@name,@password)";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(new SQLiteParameter("url", url));
cmd.Parameters.Add(new SQLiteParameter("blogname", blogname));
cmd.Parameters.Add(new SQLiteParameter("provider", provider));
cmd.Parameters.Add(new SQLiteParameter("name", name));
cmd.Parameters.Add(new SQLiteParameter("password", passwd));
cmd.ExecuteNonQuery();
conn.Close();
}
public static DataTable GetDataTable(string sql)
{
DataTable dt = new DataTable();
try
{
SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db");
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();
} catch {
// Catching exceptions is for communists
}
return dt;
}
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();
}
private List<Result> QuerySqllite(Doc doc, string key)
{
string dbPath = "Data Source =" + doc.DBPath;
SQLiteConnection conn = new SQLiteConnection(dbPath);
conn.Open();
string sql = GetQuerySqlByDocType(doc.DBType).Replace("{0}", key);
SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = cmdQ.ExecuteReader();
List<Result> results = new List<Result>();
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("name"));
string docPath = reader.GetString(reader.GetOrdinal("path"));
results.Add(new Result
{
Title = name,
SubTitle = doc.Name.Replace(".docset", ""),
IcoPath = doc.IconPath,
Action = (c) =>
{
string url = string.Format(@"{0}\{1}\Contents\Resources\Documents\{2}#{3}", docsetPath,
doc.Name+".docset", docPath, name);
string browser = GetDefaultBrowserPath();
Process.Start(browser, String.Format("\"file:///{0}\"", url));
return true;
}
});
}
conn.Close();
return results;
}
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 EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None)
{
_databaseFullPath = databaseFullPath;
_destructorType = destructorType;
_connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath);
if (!File.Exists(_databaseFullPath))
{
SQLiteConnection.CreateFile(_databaseFullPath);
Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath);
}
else
{
Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath);
}
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
Log.Verbose("About to create or check for the Entities table in the EntityStore database.");
cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database.");
}
}
}
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();
}
}
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;
}
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 AttendanceReport[] GetAttendaceSummaryReports(string data_path)
{
SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + data_path);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select * from AttendanceSummary";
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 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());
}
}
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();
}
}
/// <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();
}
}
}
/// <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 static DataTable SearchMusicDatabase(
FileInfo theDatabaseFileInfo,
string theCriteria)
{
using (DataSet theDataSet = new DataSet())
{
using (SQLiteConnection theConnection = new SQLiteConnection(
DatabaseLayer.ToConnectionString(theDatabaseFileInfo)))
{
try
{
theConnection.Open();
using (SQLiteCommand theCommand = theConnection.CreateCommand())
{
StringBuilder theCommandBuilder = new StringBuilder(
"SELECT [ID], [Path], [Details], [Extension], 0, " +
"[Path] || '\\' || [Details] || [Extension] AS [FullPath] ");
theCommandBuilder.Append("FROM [Tracks] ");
theCommandBuilder.Append("WHERE ");
bool IsFirstParameter = true;
foreach (string thisCriteria in theCriteria.Split(' '))
{
if (!IsFirstParameter)
{
theCommandBuilder.Append(" AND ");
}
theCommandBuilder.Append("([Details] LIKE ? OR [Path] LIKE ?)");
SQLiteParameter thisCriteriaParameter = theCommand.CreateParameter();
theCommand.Parameters.Add(thisCriteriaParameter);
theCommand.Parameters.Add(thisCriteriaParameter);
thisCriteriaParameter.Value = String.Format(
CultureInfo.CurrentCulture,
"%{0}%",
thisCriteria.ToString());
IsFirstParameter = false;
}
theCommandBuilder.Append(" ORDER BY [Path], [Details]");
theCommand.CommandText = theCommandBuilder.ToString();
using (SQLiteDataAdapter theAdapter = new SQLiteDataAdapter(theCommand))
{
theAdapter.Fill(theDataSet);
}
}
}
finally
{
theConnection.Close();
}
}
return theDataSet.Tables[0];
}
}
private DoorInfo Retrieve(string DoorID)
{
var ret = new DoorInfo();
var cs = ConfigurationManager.ConnectionStrings["DoorSource"].ConnectionString;
var conn = new SQLiteConnection(cs);
conn.Open();
var cmd = new SQLiteCommand(conn);
cmd.CommandText = "SELECT DoorID, Location, Description, EventID FROM Doors WHERE DoorID = @DoorID LIMIT 1";
cmd.Parameters.Add(new SQLiteParameter("@DoorID", DoorID));
SQLiteDataReader res = null;
try
{
res = cmd.ExecuteReader();
if (res.HasRows && res.Read())
{
ret.DoorID = DoorID;
ret.Location = res.GetString(1);
ret.Description = res.GetString(2);
ret.EventID = res.GetInt64(3);
}
return ret;
}
catch(Exception ex)
{
throw;
}
finally
{
if (null != res && !res.IsClosed)
res.Close();
}
}
public static DataTable buscaSing4()
{
DataTable tabla = new dataCredito.sing4DataTable();
using (SQLiteConnection con = new SQLiteConnection(Datos.conexion))
{
using (SQLiteCommand comando = new SQLiteCommand())
{
comando.CommandText = "select * from sing4";
comando.Connection = con;
con.Open();
SQLiteDataReader lector = comando.ExecuteReader();
while (lector.Read())
{
DataRow fila = tabla.NewRow();
fila["encabezado"] = lector.GetString(0);
fila["saludo"] = lector.GetString(1);
fila["pie"] = lector.GetString(2);
fila["asesor"] = lector.GetString(3);
fila["fecha"] = lector.GetDateTime(4);
fila["id"] = lector.GetString(5);
fila["idCliente"] = lector.GetInt32(6);
tabla.Rows.Add(fila);
}
}
con.Close();
}
return (tabla);
}
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;
}
}
private static SQLiteConnection ConnectToDatabase()
{
var dbConnection = new SQLiteConnection(Settings.Default.SQLiteConnectionString);
dbConnection.Open();
return dbConnection;
}
public void Clear(string name)
{
int procCount = Process.GetProcessesByName("firefox").Length;
if (procCount > 0)
throw new ApplicationException(string.Format("There are {0} instances of Firefox still running",
procCount));
try
{
using (var conn = new SQLiteConnection("Data Source=" + GetFirefoxCookiesFileName()))
{
conn.Open();
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "delete from moz_cookies where name='" + name + "'";
int count = command.ExecuteNonQuery();
}
}
catch (SQLiteException ex)
{
if (
!(ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Busy) ||
ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Locked)))
throw new ApplicationException("The Firefox cookies.sqlite file is locked");
}
}
public List <CookieItem> ReadChromeCookies(string hostName)
{
if (hostName == null)
{
throw new ArgumentNullException("hostName");
}
List <CookieItem> ret = new List <CookieItem>();
var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies";
if (!System.IO.File.Exists(dbPath))
{
throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it
}
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 like '%" + hostName + "%'";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = reader[0].ToString();
var encryptedData = (byte[])reader[1];
string encKey = File.ReadAllText(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\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 (ret.Find(x => x.Name == name) == null)
{
ret.Add(new CookieItem
{
Name = name,
Value = _cookie
});
}
}
}
conn.Close();
}
return(ret);
}
public List <CookieItem> ReadCookies(string hostName)
{
if (hostName == null)
{
throw new ArgumentNullException("hostName");
}
List <CookieItem> ret = new List <CookieItem>();
var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies";
if (!System.IO.File.Exists(dbPath))
{
throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it
}
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 like '%" + hostName + "%'";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = reader[0].ToString();
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); // Looks like ASCII
if (ret.Find(x => x.Name == name) == null)
{
ret.Add(new CookieItem
{
Name = name,
Value = plainText
});
}
}
}
conn.Close();
}
return(ret);
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "")
{
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 = "INSERT INTO Usuarios (Nombre, Usuario, Contrasena, FechaContrasena, TipoDeUsuario, Huella) VALUES ('" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + DateTime.Now.ToShortDateString() + "', '" + comboBox1.Text + "', @0)";
SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary);
cmd.Connection = sqlConnection1;
param.Value = fp_image;
cmd.Parameters.Add(param);
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Usuario Guardado con Exito");
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
label5.Text = "";
pictureBox1.Image = null;
progressBar1.Value = 0;
}
else
{
MessageBox.Show("Hay campos en blancos sin llenar");
}
}
/// <summary>
/// 更新表格中的一些项目
/// </summary>
/// <param name="collect">权限集合</param>
/// <returns>更新计数</returns>
public int UpdateControlAuthorityTale(ObservableCollection <ControlAuthority> collect)
{
try
{
int cn = 0;
using (var conn = new System.Data.SQLite.SQLiteConnection())
{
var connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
string sql = "";
foreach (var m in collect)
{
if (m.UpdateData)
{
sql = string.Format("update control_authority set MinLevel = {0} where ElementName = '{1}' ", m.MinLevel, m.ElementName);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
m.UpdateData = false;
cn++;
}
}
}
conn.Close();
return(cn);
}
}
catch (Exception ex)
{
throw ex;
}
}
static public void SQLiteLoadTransactionsFromDatabase()
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=MSBase.sqlite;Version=3;");
conn.Open();
//sql_command = "INSERT INTO MSTransactions (MSTransactionId, MSIO, MSValue, MSCurrencyCode, MSAccountId, MSCategoryId, MSNote, MSDateTime, MSMulticurrency) "
// + "VALUES ( 0, '+', 0.01, 'AZN', 1, 1, 'Test', '20.07.2018 00:40:00', 0);";
SQLiteCommand cmd = new SQLiteCommand("", conn);
cmd.CommandText = "SELECT MSTransactionId, MSIO, MSValue, " +
"MSCurrencyCode, MSAccountId, MSCategoryId, " +
"MSNote, MSDateTime, MSMulticurrency " +
"FROM MSTransactions";
try
{
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Program.transaction.MSTransactionId = int.Parse(dr["MSTransactionId"].ToString());
Program.transaction.MSIO = ((string)dr["MSIO"])[0];
Program.transaction.MSValue = float.Parse(dr["MSValue"].ToString());
Program.transaction.MSCurrencyCode = (string)dr["MSCurrencyCode"];
Program.transaction.MSAccountId = int.Parse(dr["MSAccountId"].ToString());
Program.transaction.MSCategoryId = int.Parse(dr["MSCategoryId"].ToString());
Program.transaction.MSNote = (string)dr["MSNote"];
Program.transaction.MSDateTime = DateTime.Parse((string)dr["MSDateTime"]);
Program.transaction.MSMulticurrency = (string)dr["MSDateTime"] == "1" ? true : false;
Program.transactions.Add(new MSTransaction(Program.transaction));
}
dr.Close();
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
}
}
public void GetAllClassesTest()
{
QuizardDatabase db = null;
Assert.DoesNotThrow(delegate
{
db = new QuizardDatabase();
db.Open();
if (!File.Exists("quizard.db"))
{
int x = db.buildDB();
Assert.AreEqual(x, 0);
}
});
Console.WriteLine("Database created");
List <Class> allClasses = db.GetAllClasses();
Console.WriteLine("Query successful");
Console.WriteLine("Class count = " + allClasses.Count);
Assert.DoesNotThrow(delegate
{
using (SQLiteConnection database = new System.Data.SQLite.SQLiteConnection("Data Source = quizard.db"))
{
database.Open();
using (SQLiteCommand command = database.CreateCommand())
{
Console.WriteLine("SELECT COUNT(*) FROM classes;");
command.CommandText = "SELECT COUNT(*) FROM classes;";
Assert.AreEqual(command.ExecuteScalar(), allClasses.Count);
Console.WriteLine("Count was equal");
}
}
});
}
public static void SetupDB()
{
// Check if Directory where we save db exists. If it doesn't. Create it.
if (!Directory.Exists("C:\\SQLite"))
{
Directory.CreateDirectory("C:\\SQLite");
}
// Check if db exists. If it doesnt, Create it, create tables and fill it with some data.
if (!System.IO.File.Exists("C:\\SQLite\\ProjectFodMap.db"))
{
Console.WriteLine("Trying to write database to: C:\\SQLite\\ProjectFodMap.db");
System.Data.SQLite.SQLiteConnection.CreateFile("C:\\SQLite\\ProjectFodMap.db");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(conString))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = @"CREATE TABLE Meals(
MealID INTEGER PRIMARY KEY AUTOINCREMENT,
MealName CHAR(40) NOT NULL,
MealIngredients VARCHAR(max) NOT NULL,
MealInstructions VARCHAR(max),
MealNotes VARCHAR(max),
MealRisks VARCHAR(max)
);";
com.ExecuteNonQuery();
con.Close();
}
}
InsertTestMeals();
}
}
public static void CheckDataFile(string datasource)
{
//string datasource = Application.StartupPath + "\\data.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE billInfo (BillID varchar(50) PRIMARY KEY,SiteName varchar(50),SiteUserName varchar(50),TotalMoney decimal(5,2),YearRete decimal(5,2),Deadline interger ,DeadlineType interger,ReceivedPeriod interger,ReceivablePeriod interger,ReceivablePrincipalAndInterest decimal(5,2),ReceivedPrincipalAndInterest decimal(5,2),WayOfRepayment interger,Reward decimal(5,2),BeginDay varchar(20),EndDay varchar(20),YuQiCount interger,Deleted interger,Flag interger,Remark varchar(200),UpdateTime datetime,CreateTime datetime)";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "Create Table billdetail(BillDetailID varchar(50) PRIMARY KEY,BillID varchar(50),Periods varchar(50),ReceivableDay varchar(20),ReceivedDay varchar(20),ReceivablePrincipalAndInterest decimal(5,2),ReceivableInterest decimal(5,2),ReceivedPrincipalAndInterest decimal(5,2),IsYuQi interger ,Deleted interger,Flag interger,UpdateTime datetime,CreateTime datetime)";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
conn.Close();
}
public void createDB(string createQuery)
{
try
{
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 = createQuery;
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Prima connessione al database stabilita, per favore entra con le credenziali Username: Admin e Password: Password" +
" e aggiungi un account utente per iniziare ad utilizzare questo software!",
"Prima connessione stabilita!");
}
}
}
catch (SQLiteException e)
{
MessageBox.Show("Impossibile connettersi al database! contattare l'amministratore del sistema.",
"Errore Connessione DB");
}
}
private void BindGrid()
// Esse Form de Consulta vai realizar a conexão com banco de dados e mostrar o que tem na tabela com os dados
//inseridos no Form de cadastro. Basicamente é realizar um Select e associalos ao Fill para que possa ser compreendido
//pelo Data Grid View , o Data Grid View é um recurso para manipular dados provenientes de um banco de dados.
//Algumas adaptações via dll e Program.cs(https://stackoverflow.com/questions/3179028/mixed-mode-assembly-in-net-4) foram realizadas para
//que o Data Grid View pudesse ser utilizado com Drive ODBC do SQLite. Devido a isso a sintaxe de conexão foi alterada...De todo modo funcionou
{
String connectionString = @"Data Source=C:\EncontreUmTrampo\cadastro.db";
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("select ID,Vaga,Empresa,Area,Local,Data,Etapa from Trampo");
cmd.Connection = conn;
conn.Open();
cmd.ExecuteScalar();
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds;
dataGridView1.DataMember = ds.Tables[0].TableName;
conn.Close();
}
public override bool Login(string Address, string Data, string UserName, string Password)
{
lock (lockObject)
{
bool result = true;
try
{
conn = new SQLiteConnection(string.Format("Data Source={0}\\{1};Password={2}",
Address, Data, Password));
conn.Open();
result = (conn.State == ConnectionState.Open);
}
catch (Exception e)
{
if (conn != null)
{
All.Class.Error.Add("连接字符", string.Format("Data Source={0}\\{1};Password={2}",
Address, Data, Password));
}
All.Class.Error.Add(e);
}
return(result);
}
}
private void SqliteTest(object sender, RoutedEventArgs e)
{
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.CacheSize = 1024;
// connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "INSERT INTO test VALUES('a','b')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
/// <summary>
/// Connect to the SQLite database
/// </summary>
/// <param name="close">If true : Close database connection. If false : Open database connection</param>
/// <returns>SQLiteCommand cmd if database is getting opened.</returns>
/// <returns>Null if database is getting closed</returns>
private static SQLiteCommand ConnectToDatabase(bool close)
{
if (!File.Exists("GLdb.db3"))
{
System.Data.SQLite.SQLiteConnection.CreateFile(
Path.Combine(
Directory.GetCurrentDirectory(), "GLdb.db3")); //Create the database
}
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=GLdb.db3");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
if (!close)
{
conn.Open(); //Open connection to the SQLite database
return(cmd);
}
else
{
cmd = null;
conn.Close();
return(cmd);
}
}
private static void CreateDatabase()
{
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(_dataBase);
string connectionString = string.Format("Data Source={0};Version=3;", _dataBase);
System.Data.SQLite.SQLiteConnection dbConnection = new System.Data.SQLite.SQLiteConnection(connectionString);
dbConnection.Open();
string sql;
SQLiteCommand command;
PatientTable(dbConnection, out sql, out command);
ScheduleTable(dbConnection, out sql, out command);
dbConnection.Close();
}
catch (Exception ex)
{
string er = ex.ToString();
}
}
protected override DbConnection _getdbconn()
{
//return base._getdbconn();
if (dbname.Split('_').Length < 1)
{
forms.utls.alert("db_name error!");
}
String fileName = this.getAppPath() + @"\" + dbname;
FileInfo finfo = new FileInfo(fileName);
_connstr = "Data Source=" + fileName;
if (!finfo.Exists)
{
System.Data.SQLite.SQLiteConnection.CreateFile(fileName);
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
string sql = File.ReadAllText(ofd.FileName);
using (SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(_connstr))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
MessageBox.Show(String.Format("Create DB {0}", cmd.ExecuteNonQuery()));
}
using (SQLiteCommand cmd = new SQLiteCommand("insert into sl_ctrldata(curr_ym)values('" + dbname.Split('_')[1] + "')", conn))
{
MessageBox.Show(String.Format("Create DB {0}", cmd.ExecuteNonQuery()));
}
conn.Close();
}
}
}
return(new System.Data.SQLite.SQLiteConnection(_connstr));
}
private void Form6_Load(object sender, EventArgs e)
{
//stores countries to compare medal winners
string first, second, third;
string[] countries = { "Team America", "Team Great Britain", "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];
}
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The datatable schema to add.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
SqliteClient.SQLiteConnection sqlConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (sqlConnection = new SqliteClient.SQLiteConnection(connectionString))
{
// Open the connection.
sqlConnection.Open();
// Create the command and assign any parameters.
dbCommand = new SqliteClient.SQLiteCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.SqlDataType, queryText), sqlConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (SqliteClient.SQLiteParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
dataSet = new System.Data.DataSet();
dataSet.Tables.AddRange(tables);
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
sqlConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (sqlConnection != null)
{
sqlConnection.Close();
}
}
}
void OpenIt()
{
conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbpath + ";New=False");
conn.Open();
}
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 = "@TransactionNum",
Value = this.IdentificationType,
});
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 button4_Click(object sender, EventArgs e)
{
if (comboBox2.SelectedIndex == 1)
{
int cantidad;
if (int.TryParse(textBox12.Text, out cantidad))
{
float peso;
if (float.TryParse(textBox14.Text, out peso))
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.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 Salidas (Numero, Salida, Descripcion, Cantidad, Peso, Equivalentes, Nota, TipoDeApoyo, Responsable, Huella) values ('" + label12.Text + "', '" + textBox9.Text + "', '" + textBox11.Text + "', '" + textBox12.Text + "', '" + textBox14.Text + "', '" + textBox16.Text + "', '" + textBox15.Text + "', '" + textBox13.Text + "', '" + textBox10.Text + "',@0 )";
SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary);
cmd.Connection = sqlConnection1;
param.Value = fp_image;
cmd.Parameters.Add(param);
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Salida guardada con exito.");
textBox9.Text = "";
textBox11.Text = "";
textBox12.Text = "";
textBox14.Text = "";
textBox16.Text = "";
textBox15.Text = "";
textBox13.Text = "";
textBox10.Text = "";
///create the connection string
string connString = @"Data Source= " + appPath + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Salidas";
//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);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["Numero"].ToString();
int autonum = Int32.Parse(num);
label12.Text = (autonum + 1).ToString();
}
else
{
label12.Text = "1";
}
}
else
{
MessageBox.Show("Revise de nuevo su campo de peso, solo se permiten numeros flotantes.");
}
}
else
{
MessageBox.Show("Revise de nuevo su campo de cantidad, solo se permiten numeros.");
}
}
}
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)
{
//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();
}
}
/// <summary>
/// Open失敗時は例外を投げる
/// </summary>
public void Open()
{
conn_.Open();
}
static public void CreateNewDbase()
{
#region OrderTable creator string
// This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns
string createTableQuery1 = @"CREATE TABLE IF NOT EXISTS [OrderTable] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[PO] NVARCHAR(80) NULL,
[Date] VARCHAR(10) NULL,
[EndUser] VARCHAR(512) NULL,
[Equipment] VARCHAR(512) NULL,
[VendorName] VARCHAR(512) NULL,
[JobNumber] VARCHAR(512) NULL,
[CustomerPO] VARCHAR(512) NULL,
[VendorNumber] VARCHAR(512) NULL,
[SalesAss] VARCHAR(512) NULL,
[SoldTo] VARCHAR(512) NULL,
[Street1] VARCHAR(512) NULL,
[Street2] VARCHAR(512) NULL,
[City] VARCHAR(512) NULL,
[State] VARCHAR(512) NULL,
[Zip] VARCHAR(512) NULL,
[ShipTo] VARCHAR(512) NULL,
[ShipStreet1] VARCHAR(512) NULL,
[ShipStreet2] VARCHAR(512) NULL,
[ShipCity] VARCHAR(512) NULL,
[ShipState] VARCHAR(512) NULL,
[ShipZip] VARCHAR(512) NULL,
[Carrier] VARCHAR(512) NULL,
[ShipDate] VARCHAR(10) NULL,
[IsComOrder] VARCHAR(1) NULL,
[IsComPaid] VARCHAR(1) NULL,
[Grinder] VARCHAR(512) NULL,
[SerialNo] VARCHAR(512) NULL,
[PumpStk] VARCHAR(512) NULL,
[ReqDate] VARCHAR(10) NULL,
[SchedShip] VARCHAR(512) NULL,
[PODate] VARCHAR(10) NULL,
[POShipVia] VARCHAR(512) NULL,
[TrackDate1] VARCHAR(10) NULL,
[TrackBy1] VARCHAR(512) NULL,
[TrackSource1] VARCHAR(512) NULL,
[TrackNote1] VARCHAR(512) NULL,
[TrackDate2] VARCHAR(10) NULL,
[TrackBy2] VARCHAR(512) NULL,
[TrackSource2] VARCHAR(512) NULL,
[TrackNote2] VARCHAR(512) NULL,
[TrackDate3] VARCHAR(10) NULL,
[TrackBy3] VARCHAR(512) NULL,
[TrackSource3] VARCHAR(512) NULL,
[TrackNote3] VARCHAR(512) NULL,
[TrackDate4] VARCHAR(10) NULL,
[TrackBy4] VARCHAR(512) NULL,
[TrackSource4] VARCHAR(512) NULL,
[TrackNote4] VARCHAR(512) NULL,
[TrackDate5] VARCHAR(10) NULL,
[TrackBy5] VARCHAR(512) NULL,
[TrackSource5] VARCHAR(512) NULL,
[TrackNote5] VARCHAR(512) NULL,
[TrackDate6] VARCHAR(10) NULL,
[TrackBy6] VARCHAR(512) NULL,
[TrackSource6] VARCHAR(512) NULL,
[TrackNote6] VARCHAR(512) NULL,
[TrackDate7] VARCHAR(10) NULL,
[TrackBy7] VARCHAR(512) NULL,
[TrackSource7] VARCHAR(512) NULL,
[TrackNote7] VARCHAR(512) NULL,
[TrackDate8] VARCHAR(10) NULL,
[TrackBy8] VARCHAR(512) NULL,
[TrackSource8] VARCHAR(512) NULL,
[TrackNote8] VARCHAR(512) NULL,
[TrackDate9] VARCHAR(10) NULL,
[TrackBy9] VARCHAR(512) NULL,
[TrackSource9] VARCHAR(512) NULL,
[TrackNote9] VARCHAR(512) NULL,
[TrackDate10] VARCHAR(10) NULL,
[TrackBy10] VARCHAR(512) NULL,
[TrackSource10] VARCHAR(512) NULL,
[TrackNote10] VARCHAR(512) NULL,
[TrackDate11] VARCHAR(10) NULL,
[TrackBy11] VARCHAR(512) NULL,
[TrackSource11] VARCHAR(512) NULL,
[TrackNote11] VARCHAR(512) NULL,
[TrackDate12] VARCHAR(10) NULL,
[TrackBy12] VARCHAR(512) NULL,
[TrackSource12] VARCHAR(512) NULL,
[TrackNote12] VARCHAR(512) NULL,
[TrackDate13] VARCHAR(10) NULL,
[TrackBy13] VARCHAR(512) NULL,
[TrackSource13] VARCHAR(512) NULL,
[TrackNote13] VARCHAR(512) NULL,
[TrackDate14] VARCHAR(10) NULL,
[TrackBy14] VARCHAR(512) NULL,
[TrackSource14] VARCHAR(512) NULL,
[TrackNote14] VARCHAR(512) NULL,
[TrackDate15] VARCHAR(10) NULL,
[TrackBy15] VARCHAR(512) NULL,
[TrackSource15] VARCHAR(512) NULL,
[TrackNote15] VARCHAR(512) NULL,
[TrackDate16] VARCHAR(10) NULL,
[TrackBy16] VARCHAR(512) NULL,
[TrackSource16] VARCHAR(512) NULL,
[TrackNote16] VARCHAR(512) NULL,
[TrackDate17] VARCHAR(10) NULL,
[TrackBy17] VARCHAR(512) NULL,
[TrackSource17] VARCHAR(512) NULL,
[TrackNote17] VARCHAR(512) NULL,
[TrackDate18] VARCHAR(10) NULL,
[TrackBy18] VARCHAR(512) NULL,
[TrackSource18] VARCHAR(512) NULL,
[TrackNote18] VARCHAR(512) NULL,
[QuotePrice] VARCHAR(512) NULL,
[Credit] VARCHAR(512) NULL,
[Freight] VARCHAR(512) NULL,
[ShopTime] VARCHAR(512) NULL,
[TotalCost] VARCHAR(512) NULL,
[GrossProfit] VARCHAR(512) NULL,
[Profit] VARCHAR(512) NULL,
[Description] VARCHAR(512) NULL,
[Quant1] VARCHAR(512) NULL,
[Descr1] VARCHAR(512) NULL,
[Costs1] VARCHAR(512) NULL,
[ECost1] VARCHAR(512) NULL,
[Quant2] VARCHAR(512) NULL,
[Descr2] VARCHAR(512) NULL,
[Costs2] VARCHAR(512) NULL,
[ECost2] VARCHAR(512) NULL,
[Quant3] VARCHAR(512) NULL,
[Descr3] VARCHAR(512) NULL,
[Costs3] VARCHAR(512) NULL,
[ECost3] VARCHAR(512) NULL,
[Quant4] VARCHAR(512) NULL,
[Descr4] VARCHAR(512) NULL,
[Costs4] VARCHAR(512) NULL,
[ECost4] VARCHAR(512) NULL,
[Quant5] VARCHAR(512) NULL,
[Descr5] VARCHAR(512) NULL,
[Costs5] VARCHAR(512) NULL,
[ECost5] VARCHAR(512) NULL,
[Quant6] VARCHAR(512) NULL,
[Descr6] VARCHAR(512) NULL,
[Costs6] VARCHAR(512) NULL,
[ECost6] VARCHAR(512) NULL,
[Quant7] VARCHAR(512) NULL,
[Descr7] VARCHAR(512) NULL,
[Costs7] VARCHAR(512) NULL,
[ECost7] VARCHAR(512) NULL,
[Quant8] VARCHAR(512) NULL,
[Descr8] VARCHAR(512) NULL,
[Costs8] VARCHAR(512) NULL,
[ECost8] VARCHAR(512) NULL,
[Quant9] VARCHAR(512) NULL,
[Descr9] VARCHAR(512) NULL,
[Costs9] VARCHAR(512) NULL,
[ECost9] VARCHAR(512) NULL,
[Quant10] VARCHAR(512) NULL,
[Descr10] VARCHAR(512) NULL,
[Costs10] VARCHAR(512) NULL,
[ECost10] VARCHAR(512) NULL,
[Quant11] VARCHAR(512) NULL,
[Descr11] VARCHAR(512) NULL,
[Costs11] VARCHAR(512) NULL,
[ECost11] VARCHAR(512) NULL,
[Quant12] VARCHAR(512) NULL,
[Descr12] VARCHAR(512) NULL,
[Costs12] VARCHAR(512) NULL,
[ECost12] VARCHAR(512) NULL,
[Quant13] VARCHAR(512) NULL,
[Descr13] VARCHAR(512) NULL,
[Costs13] VARCHAR(512) NULL,
[ECost13] VARCHAR(512) NULL,
[Quant14] VARCHAR(512) NULL,
[Descr14] VARCHAR(512) NULL,
[Costs14] VARCHAR(512) NULL,
[ECost14] VARCHAR(512) NULL,
[Quant15] VARCHAR(512) NULL,
[Descr15] VARCHAR(512) NULL,
[Costs15] VARCHAR(512) NULL,
[ECost15] VARCHAR(512) NULL,
[Quant16] VARCHAR(512) NULL,
[Descr16] VARCHAR(512) NULL,
[Costs16] VARCHAR(512) NULL,
[ECost16] VARCHAR(512) NULL,
[Quant17] VARCHAR(512) NULL,
[Descr17] VARCHAR(512) NULL,
[Costs17] VARCHAR(512) NULL,
[ECost17] VARCHAR(512) NULL,
[Quant18] VARCHAR(512) NULL,
[Descr18] VARCHAR(512) NULL,
[Costs18] VARCHAR(512) NULL,
[ECost18] VARCHAR(512) NULL,
[Quant19] VARCHAR(512) NULL,
[Descr19] VARCHAR(512) NULL,
[Costs19] VARCHAR(512) NULL,
[ECost19] VARCHAR(512) NULL,
[Quant20] VARCHAR(512) NULL,
[Descr20] VARCHAR(512) NULL,
[Costs20] VARCHAR(512) NULL,
[ECost20] VARCHAR(512) NULL,
[Quant21] VARCHAR(512) NULL,
[Descr21] VARCHAR(512) NULL,
[Costs21] VARCHAR(512) NULL,
[ECost21] VARCHAR(512) NULL,
[Quant22] VARCHAR(512) NULL,
[Descr22] VARCHAR(512) NULL,
[Costs22] VARCHAR(512) NULL,
[ECost22] VARCHAR(512) NULL,
[Quant23] VARCHAR(512) NULL,
[Descr23] VARCHAR(512) NULL,
[Costs23] VARCHAR(512) NULL,
[ECost23] VARCHAR(512) NULL,
[InvInstructions] VARCHAR(512) NULL,
[InvNotes] VARCHAR(512) NULL,
[VendorNotes] VARCHAR(512) NULL,
[CrMemo] VARCHAR(512) NULL,
[InvNumber] VARCHAR(512) NULL,
[InvDate] VARCHAR(10) NULL,
[Status] VARCHAR(512) NULL,
[CheckNumbers] VARCHAR(512) NULL,
[CheckDates] VARCHAR(512) NULL,
[ComDate1] VARCHAR(10) NULL,
[ComCheckNumber1] VARCHAR(512) NULL,
[ComPaid1] VARCHAR(512) NULL,
[ComDate2] VARCHAR(10) NULL,
[ComCheckNumber2] VARCHAR(512) NULL,
[ComPaid2] VARCHAR(512) NULL,
[ComDate3] VARCHAR(10) NULL,
[ComCheckNumber3] VARCHAR(512) NULL,
[ComPaid3] VARCHAR(512) NULL,
[ComDate4] VARCHAR(10) NULL,
[ComCheckNumber4] VARCHAR(512) NULL,
[ComPaid4] VARCHAR(512) NULL,
[ComDate5] VARCHAR(10) NULL,
[ComCheckNumber5] VARCHAR(512) NULL,
[ComPaid5] VARCHAR(512) NULL,
[ComAmount] VARCHAR(512) NULL,
[ComBalance] VARCHAR(512) NULL,
[DeliveryNotes] VARCHAR(512) NULL,
[PONotes] VARCHAR(512) NULL,
[Spare1] VARCHAR(512) NULL,
[Spare2] VARCHAR(512) NULL,
[Spare3] VARCHAR(512) NULL,
[Spare4] VARCHAR(512) NULL,
[Spare5] VARCHAR(512) NULL
)";
#endregion
#region QuoteTable creator string
// This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns
string createTableQuery2 = @"CREATE TABLE IF NOT EXISTS [QuoteTable] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[PO] NVARCHAR(80) NULL,
[Date] VARCHAR(10) NULL,
[Status] VARCHAR(512) NULL,
[Company] VARCHAR(512) NULL,
[VendorName] VARCHAR(512) NULL,
[JobName] VARCHAR(512) NULL,
[CustomerPO] VARCHAR(512) NULL,
[VendorNumber] VARCHAR(512) NULL,
[SalesAss] VARCHAR(512) NULL,
[SoldTo] VARCHAR(512) NULL,
[Street1] VARCHAR(512) NULL,
[Street2] VARCHAR(512) NULL,
[City] VARCHAR(512) NULL,
[State] VARCHAR(512) NULL,
[Zip] VARCHAR(512) NULL,
[Delivery] VARCHAR(512) NULL,
[Terms] VARCHAR(10) NULL,
[FreightSelect] VARCHAR(512) NULL,
[IsQManual] VARCHAR(2) NULL,
[IsPManual] VARCHAR(2) NULL,
[Location] VARCHAR(512) NULL,
[Equipment] VARCHAR(10) NULL,
[EquipCategory] VARCHAR(512) NULL,
[QuotePrice] VARCHAR(512) NULL,
[Credit] VARCHAR(512) NULL,
[Freight] VARCHAR(512) NULL,
[ShopTime] VARCHAR(512) NULL,
[TotalCost] VARCHAR(512) NULL,
[GrossProfit] VARCHAR(512) NULL,
[Profit] VARCHAR(512) NULL,
[MarkUp] VARCHAR(512) NULL,
[Description] VARCHAR(512) NULL,
[Quant1] VARCHAR(512) NULL,
[Descr1] VARCHAR(512) NULL,
[Costs1] VARCHAR(512) NULL,
[ECost1] VARCHAR(512) NULL,
[Price1] VARCHAR(512) NULL,
[Quant2] VARCHAR(512) NULL,
[Descr2] VARCHAR(512) NULL,
[Costs2] VARCHAR(512) NULL,
[ECost2] VARCHAR(512) NULL,
[Price2] VARCHAR(512) NULL,
[Quant3] VARCHAR(512) NULL,
[Descr3] VARCHAR(512) NULL,
[Costs3] VARCHAR(512) NULL,
[ECost3] VARCHAR(512) NULL,
[Price3] VARCHAR(512) NULL,
[Quant4] VARCHAR(512) NULL,
[Descr4] VARCHAR(512) NULL,
[Costs4] VARCHAR(512) NULL,
[ECost4] VARCHAR(512) NULL,
[Price4] VARCHAR(512) NULL,
[Quant5] VARCHAR(512) NULL,
[Descr5] VARCHAR(512) NULL,
[Costs5] VARCHAR(512) NULL,
[ECost5] VARCHAR(512) NULL,
[Price5] VARCHAR(512) NULL,
[Quant6] VARCHAR(512) NULL,
[Descr6] VARCHAR(512) NULL,
[Costs6] VARCHAR(512) NULL,
[ECost6] VARCHAR(512) NULL,
[Price6] VARCHAR(512) NULL,
[Quant7] VARCHAR(512) NULL,
[Descr7] VARCHAR(512) NULL,
[Costs7] VARCHAR(512) NULL,
[ECost7] VARCHAR(512) NULL,
[Price7] VARCHAR(512) NULL,
[Quant8] VARCHAR(512) NULL,
[Descr8] VARCHAR(512) NULL,
[Costs8] VARCHAR(512) NULL,
[ECost8] VARCHAR(512) NULL,
[Price8] VARCHAR(512) NULL,
[Quant9] VARCHAR(512) NULL,
[Descr9] VARCHAR(512) NULL,
[Costs9] VARCHAR(512) NULL,
[ECost9] VARCHAR(512) NULL,
[Price9] VARCHAR(512) NULL,
[Quant10] VARCHAR(512) NULL,
[Descr10] VARCHAR(512) NULL,
[Costs10] VARCHAR(512) NULL,
[ECost10] VARCHAR(512) NULL,
[Price10] VARCHAR(512) NULL,
[Quant11] VARCHAR(512) NULL,
[Descr11] VARCHAR(512) NULL,
[Costs11] VARCHAR(512) NULL,
[ECost11] VARCHAR(512) NULL,
[Price11] VARCHAR(512) NULL,
[Quant12] VARCHAR(512) NULL,
[Descr12] VARCHAR(512) NULL,
[Costs12] VARCHAR(512) NULL,
[ECost12] VARCHAR(512) NULL,
[Price12] VARCHAR(512) NULL,
[Quant13] VARCHAR(512) NULL,
[Descr13] VARCHAR(512) NULL,
[Costs13] VARCHAR(512) NULL,
[ECost13] VARCHAR(512) NULL,
[Price13] VARCHAR(512) NULL,
[Quant14] VARCHAR(512) NULL,
[Descr14] VARCHAR(512) NULL,
[Costs14] VARCHAR(512) NULL,
[ECost14] VARCHAR(512) NULL,
[Price14] VARCHAR(512) NULL,
[Quant15] VARCHAR(512) NULL,
[Descr15] VARCHAR(512) NULL,
[Costs15] VARCHAR(512) NULL,
[ECost15] VARCHAR(512) NULL,
[Price15] VARCHAR(512) NULL,
[Quant16] VARCHAR(512) NULL,
[Descr16] VARCHAR(512) NULL,
[Costs16] VARCHAR(512) NULL,
[ECost16] VARCHAR(512) NULL,
[Price16] VARCHAR(512) NULL,
[Quant17] VARCHAR(512) NULL,
[Descr17] VARCHAR(512) NULL,
[Costs17] VARCHAR(512) NULL,
[ECost17] VARCHAR(512) NULL,
[Price17] VARCHAR(512) NULL,
[Quant18] VARCHAR(512) NULL,
[Descr18] VARCHAR(512) NULL,
[Costs18] VARCHAR(512) NULL,
[ECost18] VARCHAR(512) NULL,
[Price18] VARCHAR(512) NULL,
[Quant19] VARCHAR(512) NULL,
[Descr19] VARCHAR(512) NULL,
[Costs19] VARCHAR(512) NULL,
[ECost19] VARCHAR(512) NULL,
[Price19] VARCHAR(512) NULL,
[Quant20] VARCHAR(512) NULL,
[Descr20] VARCHAR(512) NULL,
[Costs20] VARCHAR(512) NULL,
[ECost20] VARCHAR(512) NULL,
[Price20] VARCHAR(512) NULL,
[Quant21] VARCHAR(512) NULL,
[Descr21] VARCHAR(512) NULL,
[Costs21] VARCHAR(512) NULL,
[ECost21] VARCHAR(512) NULL,
[Price21] VARCHAR(512) NULL,
[Quant22] VARCHAR(512) NULL,
[Descr22] VARCHAR(512) NULL,
[Costs22] VARCHAR(512) NULL,
[ECost22] VARCHAR(512) NULL,
[Price22] VARCHAR(512) NULL,
[Quant23] VARCHAR(512) NULL,
[Descr23] VARCHAR(512) NULL,
[Costs23] VARCHAR(512) NULL,
[ECost23] VARCHAR(512) NULL,
[Price23] VARCHAR(512) NULL,
[InvNotes] VARCHAR(512) NULL,
[DeliveryNotes] VARCHAR(512) NULL,
[QuoteNotes] VARCHAR(512) NULL,
[Spare1] VARCHAR(512) NULL,
[Spare2] VARCHAR(512) NULL,
[Spare3] VARCHAR(512) NULL,
[Spare4] VARCHAR(512) NULL,
[Spare5] VARCHAR(512) NULL
)";
#endregion
#region CompanyTable creator string
// This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns
string createTableQueryCompany = @"CREATE TABLE IF NOT EXISTS [CompanyTable] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Company] NVARCHAR(512) NULL,
[Street1] VARCHAR(512) NULL,
[Street2] VARCHAR(512) NULL,
[City] VARCHAR(512) NULL,
[State] VARCHAR(512) NULL,
[Zip] VARCHAR(512) NULL,
[Phone] VARCHAR(80) NULL,
[Fax] VARCHAR(80) NULL
)";
#endregion
#region PartsTable creator string
// This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns
string createTableQueryParts = @"CREATE TABLE IF NOT EXISTS [PartsTable] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Category] NVARCHAR(512) NULL,
[Description] VARCHAR(512) NULL,
[Price] VARCHAR(512) NULL
)";
#endregion
#region ReportsTable creator string
// This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns
string createTableQueryReports = @"CREATE TABLE IF NOT EXISTS [ReportsTable] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[TableName] NVARCHAR(512) NULL,
[Name] NVARCHAR(512) NULL,
[Columns] VARCHAR(512) NULL
)";
#endregion
Killconnection();
System.Data.SQLite.SQLiteConnection.CreateFile(dbName); // Create the file which will be hosting our database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + dbName))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = createTableQuery1; // Set CommandText to our query that will create a table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createTableQuery2; // Set CommandText to our query that will create a table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createTableQueryCompany; // Set CommandText to our query that will create a table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createTableQueryParts; // Set CommandText to our query that will create a table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createTableQueryReports; // Set CommandText to our query that will create a table
com.ExecuteNonQuery(); // Execute the query
}
}
}
public void connectToDatabase()
{
m_dbConnection = new SQLiteConnection("Data Source=STOCKSSQLITE_DB.db;Version=3;");
m_dbConnection.Open();
}
private void button1_Click(object sender, EventArgs e)
{
//创建一个数据库文件
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES(’dotnetthink’,'mypassword’)";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
private void button1_Click(object sender, EventArgs e)
{
string selected = listBox1.GetItemText(listBox1.SelectedItem);
if (selected == "Mens 500m Speed Skating")
{
//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; 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 == "Womens 500m Speed Skating")
{
//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 == "Mens 1000m Speed Skating")
{
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; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "Womens 1000m Speed Skating")
{
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; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "Mens 1500m Speed Skating")
{
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; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "Womens 1500m Speed Skating")
{
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; i++)
{
dataGridView1.Rows[i].Cells[0].Value = i + 1;
}
}
if (selected == "Ice Dance Mens")
{
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; 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")
{
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; 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 Mens")
{
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; 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")
{
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; 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; 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 button1_Click(object sender, EventArgs e)
{
if (comboBox2.SelectedIndex == 0)
{
int cantidad;
if (int.TryParse(textBox3.Text, out cantidad))
{
float peso;
if (float.TryParse(textBox4.Text, out peso))
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.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 Entradas values ('" + label1.Text + "', '" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + comboBox1.Text + "', '" + textBox8.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Entrada guardada con exito.");
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox7.Text = "";
comboBox1.SelectedIndex = 0;
textBox8.Text = "";
///create the connection string
string connString = @"Data Source= " + appPath + @"\DBBIT.s3db ;Version=3;";
//create the database query
string query = "SELECT * FROM Entradas";
//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);
if (dTable.Rows.Count != 0)
{
DataRow Row = dTable.Rows[dTable.Rows.Count - 1];
string num = Row["Numero"].ToString();
int autonum = Int32.Parse(num);
label1.Text = (autonum + 1).ToString();
}
else
{
label1.Text = "1";
}
}
else
{
MessageBox.Show("Revise de nuevo su campo de peso, solo se permiten numeros flotantes.");
}
}
else
{
MessageBox.Show("Revise de nuevo su campo de cantidad, solo se permiten numeros.");
}
}
}
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 = "@IdentificationType",
Value = this.IdentificationType,
});
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();
}
}
}
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();
}
}
