Creating a project for school, I have a form with some user controls.
3 textboxes a checkbox and 2 buttons for navigating through the records.
When I change the text on one of the textboxes, the data will only reflect to the database when I click the "Next" or "Prev" button.
public partial class Navigeren : UserControl
{
private readonly SqLiteDataAccess _sqLiteDataAccess;
private Timer _saveTimer;
private DataViewManager _dsView;
public Navigeren()
{
InitializeComponent();
_sqLiteDataAccess = new SqLiteDataAccess();
DataBinding();
_saveTimer = new Timer {Interval = 1000};
_saveTimer.Tick += _saveTimer_Tick;
_saveTimer.Start();
}
private void _saveTimer_Tick(object sender, EventArgs e)
{
_sqLiteDataAccess.UpdatePersonen(_dsView.DataSet);
// Tried using SqLiteDataAccess.PersonenDataSet gives me the same result.
}
private void DataBinding()
{
_dsView = SqLiteDataAccess.PersonenDataSet.DefaultViewManager;
textId.DataBindings.Add("Text", _dsView, "Personen.id", false, DataSourceUpdateMode.OnPropertyChanged);
textNaam.DataBindings.Add("Text", _dsView, "Personen.name", false, DataSourceUpdateMode.OnPropertyChanged);
textAdres.DataBindings.Add("Text", _dsView, "Personen.adres", false, DataSourceUpdateMode.OnPropertyChanged);
checkGehuwd.DataBindings.Add("Checked", _dsView, "Personen.gehuwd", false, DataSourceUpdateMode.OnPropertyChanged);
}
private void buttonNext_Click(object sender, EventArgs e)
{
CurrencyManager cm = (CurrencyManager)this.BindingContext[_dsView, "Personen"];
if (cm.Position < cm.Count - 1)
{
cm.Position++;
}
}
private void buttonPrev_Click(object sender, EventArgs e)
{
if (this.BindingContext[_dsView, "Personen"].Position > 0)
{
this.BindingContext[_dsView, "Personen"].Position--;
}
}
}
Sorry for my english.
Greetings Andy
EDIT: SQLiteDataAccess:
class SqLiteDataAccess
{
private SQLiteConnection _sqliteconnection;
private string _database = @"Database.sqlite";
private static DataSet _personenDataSet;
public static DataSet PersonenDataSet
{
get { return _personenDataSet; }
set { _personenDataSet = value; }
}
public SqLiteDataAccess()
{
OpenConnection();
CreateTable();
CreateTableGeslachten();
CreateTableLanden();
FillDataSet();
}
private void OpenConnection()
{
if (!File.Exists(_database))
SQLiteConnection.CreateFile(_database);
_sqliteconnection = new SQLiteConnection("Data Source=" + _database + ";Version=3;");
_sqliteconnection.Open();
}
private void CreateTable()
{
try
{
string sql = "CREATE TABLE Personen (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"name VARCHAR, " +
"adres VARCHAR, " +
"gehuwd INT, " +
"land INT, " +
"geslacht INT, " +
"telnr VARCHAR, " +
"studies VARCHAR, " +
"geboorteDatum DATETIME, " +
"foto BLOB)";
SQLiteCommand command = new SQLiteCommand(sql, _sqliteconnection);
command.ExecuteNonQuery();
}
catch (SQLiteException sle)
{
}
}
private void CreateTableGeslachten()
{
try
{
string sql = "CREATE TABLE Geslachten (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"geslacht TEXT," +
"FOREIGN KEY(id) REFERENCES Personen(geslacht));";
SQLiteCommand command = new SQLiteCommand(sql, _sqliteconnection);
command.ExecuteNonQuery();
InsertTableGeslachten();
}
catch (SQLiteException sle)
{
}
}
private void InsertTableGeslachten()
{
string sql = "INSERT INTO Geslachten(geslacht) VALUES('Man'), ('Vrouw');";
SQLiteCommand command = new SQLiteCommand(sql, _sqliteconnection);
command.ExecuteNonQuery();
}
private void CreateTableLanden()
{
try
{
string sql = "CREATE TABLE Landen (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"land TEXT," +
"FOREIGN KEY(id) REFERENCES Personen(land));";
SQLiteCommand command = new SQLiteCommand(sql, _sqliteconnection);
command.ExecuteNonQuery();
InsertTableLanden();
}
catch (SQLiteException sle)
{
}
}
public void Insert(string name, string adres, bool gehuwd, int land, int geslacht, string telnr, string studies,
string geboorteDatum, byte[] foto)
{
var sql =
new StringBuilder(
"insert into Personen (name, adres, gehuwd, land, geslacht, telnr, studies, geboorteDatum) values ('");
sql.Append(name).Append("','")
.Append(adres).Append("','")
.Append(Convert.ToInt32(gehuwd)).Append("','")
.Append(land).Append("','")
.Append(geslacht).Append("','")
.Append(telnr).Append("','")
.Append(studies).Append("','")
.Append(geboorteDatum).Append("');");
// .Append(foto).Append(");");
SQLiteCommand command = new SQLiteCommand(sql.ToString(), _sqliteconnection);
command.ExecuteNonQuery();
}
public void FillDataSet()
{
SqLiteDataAccess.PersonenDataSet = new DataSet();
try
{
string query = "select * from Personen";
SQLiteDataAdapter da = new SQLiteDataAdapter(query, _sqliteconnection);
da.Fill(PersonenDataSet, "Personen");
string query2 = "select id, geslacht from geslachten";
SQLiteDataAdapter da2 = new SQLiteDataAdapter(query2, _sqliteconnection);
da2.Fill(PersonenDataSet, "Geslachten");
string query3 = "select id, land from Landen";
SQLiteDataAdapter da3 = new SQLiteDataAdapter(query3, _sqliteconnection);
da3.Fill(PersonenDataSet, "Landen");
}
catch (Exception ex)
{
}
}
public void UpdatePersonen(DataSet ds)
{
try
{
string query = "select * from Personen";
SQLiteDataAdapter da = new SQLiteDataAdapter(query, _sqliteconnection);
SQLiteCommandBuilder sqLiteCommandBuilder = new SQLiteCommandBuilder(da);
da.Update(ds, "Personen");
}
catch (Exception ex)
{
MessageBox.Show("Test");
}
}
