public List <IEntity> GetCountries()
{
if (_connection.State != System.Data.ConnectionState.Closed)
{
throw new Exception("_connection is not in Closed state!!!");
}
List <IEntity> l = new List <IEntity>();
try
{
_connection.Open();
}
catch (Exception e)
{
System.Windows.MessageBox.Show(e.Message); // здесь ошибка
}
SqlCeCommand command = _connection.CreateCommand();
command.CommandText = String.Format("SELECT ID, Name FROM Countries");
SqlCeDataReader r = command.ExecuteReader();
while (r.Read())
{
l.Add(new Country(r.GetString(1))
{
ID = r.GetInt32(0)
});
}
_connection.Close();
return(l);
}
private void button2_Click(object sender, EventArgs e) //delete
{
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection();
System.Data.SqlServerCe.SqlCeDataAdapter daHangmanDB;
DataRow drow;
DataSet dsSavedGame_Words = new DataSet();
con.ConnectionString = "Data Source=HangmanDB.sdf";
con.Open();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From SavedGame_Words", con);
daHangmanDB.Fill(dsSavedGame_Words, "SavedGame_Words");
con.Close();
int selectedID = System.Convert.ToInt32(listView1.SelectedItems[0].SubItems[0].Text);
for (int i = 0; i < dsSavedGame_Words.Tables["SavedGame_Words"].Rows.Count; ++i)
{
drow = dsSavedGame_Words.Tables["SavedGame_Words"].Rows[i];
if (System.Convert.ToInt32(drow[0]) == selectedID)
{
dsSavedGame_Words.Tables["SavedGame_Words"].Rows[i].Delete(); //Marks row for deletion (does not remove)
}
}
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "DELETE FROM SavedGame_Words";
cmd.ExecuteNonQuery();
con.Close();
con.Open();
System.Data.SqlServerCe.SqlCeCommandBuilder cb;
cb = new System.Data.SqlServerCe.SqlCeCommandBuilder(daHangmanDB);
cb.DataAdapter.Update(dsSavedGame_Words.Tables["SavedGame_Words"]);
con.Close();
MessageBox.Show("Database Updated.");
con.Close();
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "DELETE FROM SavedGames WHERE ID=" + System.Convert.ToString(selectedID);
cmd.ExecuteNonQuery();
con.Close();
listView1.Items.Remove(listView1.SelectedItems[0]);
}
} //ExecuteUppdate
//_______________________________________________________________________________________________________________
// executer la requete select sans ouvrir la connexion
internal static DataSet ExecuteSelectN(string requête, SqlCeConnection connexion)
{
connexion = Connexion.getInstance().OpenConnection();
//using (connexion = Connexion.getInstance().OpenConnection())
//{
try
{
SqlCeCommand cmd = connexion.CreateCommand();
cmd.CommandText = requête;
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
catch (Exception ex)
{
FileInfo fi = new FileInfo(Program.pathExe + "\\log.txt");
using (StreamWriter sw = fi.AppendText())
{
sw.WriteLine(DateTime.Now + "/ " + ex.Message + " / " + requête + " / Classe Requete M.ExecuteSelectN \r\n");
sw.Close();
}
//MessageBox.Show(a.ToString());
return(null);
}
finally
{
// Connexion.getInstance().closeConnection();
}
//}
}
private void button1_Click(object sender, System.EventArgs e)
{
cn = new System.Data.SqlServerCe.SqlCeConnection("Data Source=\\My Documents\\prac3.sdf");
cn.Open();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT TitleName FROM Titles";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MessageBox.Show(reader.GetString(0));
}
}
private void button3_Click(object sender, EventArgs e) //Loading a new wordlist
{
OpenFileDialog loadlist = new OpenFileDialog();
loadlist.Filter = "Text Files (.txt)|*.txt|All Files (*.*)|*.*";
loadlist.FilterIndex = 1;
loadlist.Multiselect = false;
loadlist.InitialDirectory = "";
if (loadlist.ShowDialog() == DialogResult.OK) //user clicked ok
{
String Filepath = loadlist.FileName;
System.IO.StreamReader inpfile = new System.IO.StreamReader(Filepath);
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection();
System.Data.SqlServerCe.SqlCeDataAdapter daHangmanDB;
DataRow drow;
DataSet dsWordlist = new DataSet();
con.ConnectionString = "Data Source=HangmanDB.sdf";
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "DELETE FROM Wordlist";
cmd.ExecuteNonQuery();
daHangmanDB = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT * From Wordlist", con);
daHangmanDB.Fill(dsWordlist, "Wordlist");
con.Close();
String data, category, words;
while ((data = inpfile.ReadLine()) != null)
{
category = data.Split(',')[0];
words = data.Split(',')[1];
drow = dsWordlist.Tables["Wordlist"].NewRow();
drow[1] = category;
drow[2] = words;
dsWordlist.Tables["Wordlist"].Rows.Add(drow);
}
con.Open();
System.Data.SqlServerCe.SqlCeCommandBuilder cb;
cb = new System.Data.SqlServerCe.SqlCeCommandBuilder(daHangmanDB);
cb.DataAdapter.Update(dsWordlist.Tables["Wordlist"]);
con.Close();
MessageBox.Show("Database Updated.");
}
}
private void button3_Click(object sender, EventArgs e)
{
con = new System.Data.SqlServerCe.SqlCeConnection();
con.ConnectionString = "DataSource=|DataDirectory|cidb13.sdf";
SqlCeCommand cmd2 = con.CreateCommand();
cmd2.CommandText = "SELECT * From Employee_reg ";
int flag = 0, f1 = 0;
con.Open();
cmd2.ExecuteNonQuery();
SqlCeDataReader readData = cmd2.ExecuteReader();
if (readData != null)
{
while (readData.Read())
{
if (readData.GetString(8) == "Reviewed")
{
if (readData.GetString(2) == textBox2.Text && (readData.GetString(1) == textBox1.Text))
{
/* if (textBox1.Text == "Admin")
* {
* string u1 = readData.GetString(0);
* this.Hide();
* adminPanel ad = new adminPanel();
* LoginInfo.UserID = u1;
* ad.Show();
* flag=1;
*
* }
* else*/
string u = readData.GetString(0);
this.Hide();
User_DashBoard er = new User_DashBoard();
LoginInfo.UserID = u;
er.Show();
flag = 1;
}
}
else if (readData.GetString(8).Equals("Not Reviewed"))
{
if (readData.GetString(2) == textBox2.Text && (readData.GetString(1) == textBox1.Text))
{
f1 = 1;
flag = 1;
}
}
}
}
if (flag == 0)
{
MessageBox.Show("Wrong password or username.Try again ");
}
else if (f1 == 1)
{
MessageBox.Show("Login Verfication in process.\nCan't access.\nContact Admin for Login Details");
}
//ds1 = new DataSet();
//string sql = "SELECT * From Employee_reg where UserName='******'";
//da = new System.Data.SqlServerCe.SqlCeDataAdapter(sql, con);
//da.Fill(ds1, "Employee_reg");
//DataRow drow = ds1.Tables["Employee_reg"].Rows[0];
/*foreach (DataRow drow in ds1.Employee_reg)
* {
* if (drow.ItemArray[0].Equals(textBox1.Text) && drow.ItemArray[1].Equals(textBox2.Text))
* {
* this.Hide();
* User_DashBoard er = new User_DashBoard();
* er.str1 = drow.ItemArray.GetValue(1).ToString();
* er.Show();
* }
* else
* {
* MessageBox.Show("Wrong password or username.Try again ");
* }
* }*/
con.Close();
}
