void saveNew()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL && DataConfig.typeDatabase == DataConstants.TYPE_OLEDB)
{
// OLEDB
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, pass, permissions, info FROM Users WHERE (id = 0)";
oleDb.ExecuteFill("Users");
DataRow newRow = oleDb.dataSet.Tables["Users"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["pass"] = passTextBox1.Text;
newRow["permissions"] = setPermissions(permissionsComboBox.Text);
newRow["info"] = infoTextBox.Text;
oleDb.dataSet.Tables["Users"].Rows.Add(newRow);
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Users (name, pass, permissions, info) VALUES (@name, @pass, @permissions, @info)";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@pass", OleDbType.VarChar, 255, "pass");
oleDb.oleDbCommandInsert.Parameters.Add("@permissions", OleDbType.VarChar, 255, "permissions");
oleDb.oleDbCommandInsert.Parameters.Add("@info", OleDbType.LongVarChar, 0, "info");
if (oleDb.ExecuteUpdate("Users"))
{
DataForms.FClient.updateHistory("Users");
Utilits.Console.Log("Создан новый пользователь.");
Close();
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER && DataConfig.typeDatabase == DataConstants.TYPE_MSSQL)
{
// MSSQL SERVER
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, pass, permissions, info FROM Users WHERE (id = 0)";
sqlServer.ExecuteFill("Users");
DataRow newRow = sqlServer.dataSet.Tables["Users"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["pass"] = passTextBox1.Text;
newRow["permissions"] = setPermissions(permissionsComboBox.Text);
newRow["info"] = infoTextBox.Text;
sqlServer.dataSet.Tables["Users"].Rows.Add(newRow);
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO Users (name, pass, permissions, info) VALUES (@name, @pass, @permissions, @info)";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@pass", SqlDbType.VarChar, 255, "pass");
sqlServer.sqlCommandInsert.Parameters.Add("@permissions", SqlDbType.VarChar, 255, "permissions");
sqlServer.sqlCommandInsert.Parameters.Add("@info", SqlDbType.Text, 0, "info");
if (sqlServer.ExecuteUpdate("Users"))
{
DataForms.FClient.updateHistory("Users");
Utilits.Console.Log("Создан новый пользователь.");
Close();
}
}
}
void Button1Click(object sender, EventArgs e)
{
if (comboBox1.Text != "" && comboBox2.Text != "")
{
oleDb.dataSet.Clear();
dataGrid1.DataSource = oleDb.dataSet;
oleDb.oleDbCommandSelect.CommandText = textBox1.Text;
oleDb.ExecuteFill(comboBox2.Text);
}
else
{
MessageBox.Show("Вы не выбрали файл базы данных или не указали имя таблицы!");
}
}
void getFolders()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT name FROM Nomenclature WHERE (type = '" + DataConstants.FOLDER + "')";
if (oleDb.ExecuteFill("Nomenclature"))
{
foldersComboBox.Items.Clear();
foreach (DataRow row in oleDb.dataSet.Tables["Nomenclature"].Rows)
{
foldersComboBox.Items.Add(row["name"].ToString());
}
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT name FROM Nomenclature WHERE (type = '" + DataConstants.FOLDER + "')";
if (sqlServer.ExecuteFill("Nomenclature"))
{
foldersComboBox.Items.Clear();
foreach (DataRow row in sqlServer.dataSet.Tables["Nomenclature"].Rows)
{
foldersComboBox.Items.Add(row["name"].ToString());
}
}
}
}
public DataServerUpdate()
{
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, represent, datetime, error, user FROM History";
oleDb.oleDbCommandUpdate.CommandText = "UPDATE History SET " +
"name = @name, " +
"represent = @represent, " +
"datetime = @datetime, " +
"error = @error, " +
"user = @user " +
"WHERE (id = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandUpdate.Parameters.Add("@represent", OleDbType.VarChar, 255, "represent");
oleDb.oleDbCommandUpdate.Parameters.Add("@datetime", OleDbType.VarChar, 255, "datetime");
oleDb.oleDbCommandUpdate.Parameters.Add("@error", OleDbType.VarChar, 255, "error");
oleDb.oleDbCommandUpdate.Parameters.Add("@user", OleDbType.VarChar, 255, "user");
if (oleDb.ExecuteFill("History") == true)
{
tables = new List <Table>();
Table table;
foreach (DataRow row in oleDb.dataSet.Tables["History"].Rows)
{
table.name = row["name"].ToString();
table.represent = row["represent"].ToString();
table.datetime = row["datetime"].ToString();
table.error = row["error"].ToString();
table.user = row["user"].ToString();
tables.Add(table);
}
}
else
{
Utilits.Console.Log("ПРЕДУПРЕЖДЕНИЕ: Служба истории обновлений базы данных не запущена!!!");
}
}
void TableRefreshLocal()
{
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Numeral";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Numeral ORDER BY ID ASC";
if (oleDb.ExecuteFill("Numeral"))
{
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow rowElement in oleDb.dataSet.Tables[0].Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(rowElement["Numeral_English"].ToString());
ListViewItem_add.StateImageIndex = 1;
ListViewItem_add.SubItems.Add(rowElement["Numeral_Translation"].ToString());
ListViewItem_add.SubItems.Add(rowElement["ID"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
else
{
Utilits.Console.LogError("[ОШИБКА] Ошибка выполнения запроса.", false, true);
oleDb.Error();
return;
}
// ВЫБОР: выдиляем ранее выбранный элемент.
listView1.SelectedIndices.IndexOf(selectTableLine);
}
void TableRefreshLocal()
{
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Units";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Units ORDER BY name ASC";
if (oleDb.ExecuteFill("Units"))
{
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow rowElement in oleDb.dataSet.Tables[0].Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(rowElement["name"].ToString());
ListViewItem_add.StateImageIndex = 1;
ListViewItem_add.SubItems.Add("");
ListViewItem_add.SubItems.Add(rowElement["id"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
else
{
Utilits.Console.Log("[ОШИБКА] Ошибка выполнения запроса к таблице Единицы измерения.");
oleDb.Error();
return;
}
// ВЫБОР: выдиляем ранее выбранный элемент.
listView1.SelectedIndices.IndexOf(selectTableLine);
}
void searchLocal()
{
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "IrregularVerbs";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM IrregularVerbs WHERE (Verb_Translation LIKE '%" + toolStripComboBox1.Text +
"%' OR V1_Infivitive LIKE '%" + toolStripComboBox1.Text +
"%' OR V2_PastSimple LIKE '%" + toolStripComboBox1.Text +
"%' OR V3_PastParticiple LIKE '%" + toolStripComboBox1.Text +
"%' OR V4_PresentParticiple LIKE '%" + toolStripComboBox1.Text +
"%' ) ORDER BY V1_Infivitive ASC";
if (oleDb.ExecuteFill("IrregularVerbs"))
{
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow rowElement in oleDb.dataSet.Tables[0].Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(rowElement["V1_Infivitive"].ToString());
ListViewItem_add.StateImageIndex = 1;
ListViewItem_add.SubItems.Add(rowElement["V2_PastSimple"].ToString());
ListViewItem_add.SubItems.Add(rowElement["V3_PastParticiple"].ToString());
ListViewItem_add.SubItems.Add(rowElement["V4_PresentParticiple"].ToString());
ListViewItem_add.SubItems.Add(rowElement["Verb_Translation"].ToString());
ListViewItem_add.SubItems.Add(rowElement["ID"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
else
{
Utilits.Console.Log("[ОШИБКА] Ошибка выполнения запроса.");
oleDb.Error();
return;
}
}
SqlServer sqlServer; // MSSQL
void loadData()
{
//Подключение базы данных
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL && DataConfig.typeDatabase == DataConstants.TYPE_OLEDB)
{
// OLEDB
try{
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Users";
oleDb.ExecuteFill("Users");
}catch (Exception ex) {
oleDb.Error();
MessageBox.Show(ex.ToString());
Application.Exit();
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER && DataConfig.typeDatabase == DataConstants.TYPE_MSSQL)
{
// MSSQL SERVER
try{
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT * FROM Users";
sqlServer.ExecuteFill("Users");
}catch (Exception ex) {
sqlServer.Error();
MessageBox.Show(ex.ToString());
Application.Exit();
}
}
readData();
}
public static void ReadDatabaseSettings()
{
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = ";Jet OLEDB:Database Password="******"12345";
OleDb oleDb;
oleDb = new OleDb(DataConfig.configFile);
try{
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM DatabaseSettings";
oleDb.ExecuteFill("DatabaseSettings");
DataConfig.localDatabase = oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["localDatabase"].ToString();
DataConfig.typeDatabase = oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["typeDatabase"].ToString();
DataConfig.typeConnection = oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["typeConnection"].ToString();
DataConfig.serverConnection = oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["serverConnection"].ToString();
oleDb.Dispose();
Utilits.Console.Log("Настройки соединения с базой данных успешно загружены.");
}catch (Exception ex) {
oleDb.Error();
MessageBox.Show("[ReadDatabaseSettings]: " + ex.ToString(), "Ошибка");
Application.Exit();
}
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
}
void saveNewFolder()
{
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice";
oleDb.ExecuteFill("Practice");
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Practice (type, name, parent) VALUES (@type, @name, @parent)";
oleDb.oleDbCommandInsert.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@parent", OleDbType.VarChar, 255, "parent");
DataRow newRow = oleDb.dataSet.Tables["Practice"].NewRow();
newRow["type"] = Constants.TYPE_FOLDER;
newRow["name"] = textBox2.Text;
newRow["parent"] = "";
oleDb.dataSet.Tables["Practice"].Rows.Add(newRow);
if (oleDb.ExecuteUpdate("Practice"))
{
Utilits.Console.Log("Новая папка - успешно сохранена.");
Close();
(ParentForm as PracticeForm).TableRefresh("");
}
}
void searchLocal()
{
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Numeral";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Numeral WHERE (Numeral_Translation LIKE '%" + toolStripComboBox1.Text +
"%' OR Numeral_English LIKE '%" + toolStripComboBox1.Text + "%' ) ORDER BY ID ASC";
if (oleDb.ExecuteFill("Numeral"))
{
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow rowElement in oleDb.dataSet.Tables[0].Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(rowElement["Numeral_English"].ToString());
ListViewItem_add.StateImageIndex = 1;
ListViewItem_add.SubItems.Add(rowElement["Numeral_Translation"].ToString());
ListViewItem_add.SubItems.Add(rowElement["ID"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
else
{
Utilits.Console.Log("[ОШИБКА] Ошибка выполнения запроса.");
oleDb.Error();
return;
}
}
void TestFormLoad(object sender, EventArgs e)
{
DataTable table;
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Tests";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Tests WHERE (IDPractice = " + ID + ") ORDER BY ID ASC";
if (oleDb.ExecuteFill("Tests"))
{
table = oleDb.dataSet.Tables["Tests"];
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow row in table.Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(row["condition"].ToString());
ListViewItem_add.StateImageIndex = 0;
ListViewItem_add.SubItems.Add(row["question"].ToString());
ListViewItem_add.SubItems.Add("");
ListViewItem_add.SubItems.Add("");
ListViewItem_add.SubItems.Add(row["hint"].ToString());
ListViewItem_add.SubItems.Add(row["ID"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
}
void searchLocal()
{
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Units";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Units WHERE (name LIKE '%" + toolStripComboBox1.Text + "%') ORDER BY name ASC";
if (oleDb.ExecuteFill("Units"))
{
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow rowElement in oleDb.dataSet.Tables[0].Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(rowElement["name"].ToString());
ListViewItem_add.StateImageIndex = 1;
ListViewItem_add.SubItems.Add("");
ListViewItem_add.SubItems.Add(rowElement["id"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
else
{
Utilits.Console.Log("[ОШИБКА] Ошибка выполнения запроса к таблице Единицы измерения.");
oleDb.Error();
return;
}
}
public static bool SaveSettings()
{
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = ";Jet OLEDB:Database Password="******"12345";
OleDb oleDb;
oleDb = new OleDb(DataConfig.configFile);
try{
oleDb.oleDbCommandSelect.CommandText = "SELECT [id], [autoUpdate], [showConsole], [period] FROM Settings";
oleDb.oleDbCommandUpdate.CommandText = "UPDATE Settings SET " +
"[autoUpdate] = @autoUpdate, " +
"[showConsole] = @showConsole, " +
"[period] = @period " +
"WHERE ([id] = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@autoUpdate", OleDbType.VarChar, 255, "autoUpdate");
oleDb.oleDbCommandUpdate.Parameters.Add("@showConsole", OleDbType.VarChar, 255, "showConsole");
oleDb.oleDbCommandUpdate.Parameters.Add("@period", OleDbType.VarChar, 255, "period");
oleDb.oleDbCommandUpdate.Parameters.Add("@id", OleDbType.Integer, 10, "id");
oleDb.ExecuteFill("Settings");
oleDb.dataSet.Tables["Settings"].Rows[0]["autoUpdate"] = DataConfig.autoUpdate.ToString();
oleDb.dataSet.Tables["Settings"].Rows[0]["showConsole"] = DataConfig.showConsole.ToString();
oleDb.dataSet.Tables["Settings"].Rows[0]["period"] = DataConfig.period;
if (oleDb.ExecuteUpdate("Settings"))
{
oleDb.Dispose();
Utilits.Console.Log("Сохранение настроек программы прошло успешно.");
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(true);
}
else
{
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] Настройки программы не сохранены.", false, true);
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(false);
}
}catch (Exception ex) {
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] " + ex.Message.ToString(), false, true);
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(false);
}
}
void saveNew()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, type FROM Nomenclature WHERE (id = 0)";
oleDb.ExecuteFill("Nomenclature");
DataRow newRow = oleDb.dataSet.Tables["Nomenclature"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["type"] = DataConstants.FOLDER;
oleDb.dataSet.Tables["Nomenclature"].Rows.Add(newRow);
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Nomenclature (name, type) VALUES (@name, @type)";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@type", OleDbType.VarChar, 255, "type");
if (oleDb.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Создана новая папка.");
Close();
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, type FROM Nomenclature WHERE (id = 0)";
sqlServer.ExecuteFill("Nomenclature");
DataRow newRow = sqlServer.dataSet.Tables["Nomenclature"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["type"] = "folder";
sqlServer.dataSet.Tables["Nomenclature"].Rows.Add(newRow);
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO Nomenclature (name, type) VALUES (@name, @type)";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@type", SqlDbType.VarChar, 255, "type");
if (sqlServer.ExecuteUpdate("Nomenclature"))
{
DataForms.FClient.updateHistory("Nomenclature");
Utilits.Console.Log("Создана новая папка.");
Close();
}
}
}
void saveNew()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, info FROM Units WHERE (id = 0)";
oleDb.ExecuteFill("Units");
DataRow newRow = oleDb.dataSet.Tables["Units"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["info"] = infoTextBox.Text;
oleDb.dataSet.Tables["Units"].Rows.Add(newRow);
oleDb.oleDbCommandInsert.CommandText = "INSERT INTO Units (name, info) VALUES (@name, @info)";
oleDb.oleDbCommandInsert.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandInsert.Parameters.Add("@info", OleDbType.LongVarChar, 0, "info");
if (oleDb.ExecuteUpdate("Units"))
{
DataForms.FClient.updateHistory("Units");
Utilits.Console.Log("Создана новая единица измерения.");
Close();
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, info FROM Units WHERE (id = 0)";
sqlServer.ExecuteFill("Units");
DataRow newRow = sqlServer.dataSet.Tables["Units"].NewRow();
newRow["name"] = nameTextBox.Text;
newRow["info"] = infoTextBox.Text;
sqlServer.dataSet.Tables["Units"].Rows.Add(newRow);
sqlServer.sqlCommandInsert.CommandText = "INSERT INTO Units (name, info) VALUES (@name, @info)";
sqlServer.sqlCommandInsert.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandInsert.Parameters.Add("@info", SqlDbType.Text, 0, "info");
if (sqlServer.ExecuteUpdate("Units"))
{
DataForms.FClient.updateHistory("Units");
Utilits.Console.Log("Создана новая единица измерения.");
Close();
}
}
}
void open()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, type, " +
"code, series, article, manufacturer, price, units, parent " +
"FROM Nomenclature WHERE (id = " + ID + ")";
if (oleDb.ExecuteFill("Nomenclature"))
{
idTextBox.Text = oleDb.dataSet.Tables["Nomenclature"].Rows[0]["id"].ToString();
nameTextBox.Text = oleDb.dataSet.Tables["Nomenclature"].Rows[0]["name"].ToString();
codeTextBox.Text = oleDb.dataSet.Tables["Nomenclature"].Rows[0]["code"].ToString();
seriesTextBox.Text = oleDb.dataSet.Tables["Nomenclature"].Rows[0]["series"].ToString();
articleTextBox.Text = oleDb.dataSet.Tables["Nomenclature"].Rows[0]["article"].ToString();
manufacturerTextBox.Text = oleDb.dataSet.Tables["Nomenclature"].Rows[0]["manufacturer"].ToString();
priceTextBox.Text = oleDb.dataSet.Tables["Nomenclature"].Rows[0]["price"].ToString();
String Value = priceTextBox.Text;
priceTextBox.Clear();
priceTextBox.Text = Conversion.StringToMoney(Math.Round(Conversion.StringToDouble(Value), 2).ToString());
if (priceTextBox.Text == "" || Conversion.checkString(priceTextBox.Text) == false)
{
priceTextBox.Text = "0,00";
}
unitsTextBox.Text = oleDb.dataSet.Tables["Nomenclature"].Rows[0]["units"].ToString();
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER && DataConfig.typeDatabase == DataConstants.TYPE_MSSQL)
{
// MSSQL SERVER
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, type, " +
"code, series, article, manufacturer, price, units, parent " +
"FROM Nomenclature WHERE (id = " + ID + ")";
if (sqlServer.ExecuteFill("Nomenclature"))
{
idTextBox.Text = sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["id"].ToString();
nameTextBox.Text = sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["name"].ToString();
codeTextBox.Text = sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["code"].ToString();
seriesTextBox.Text = sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["series"].ToString();
articleTextBox.Text = sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["article"].ToString();
manufacturerTextBox.Text = sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["manufacturer"].ToString();
priceTextBox.Text = sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["price"].ToString();
String Value = priceTextBox.Text;
priceTextBox.Clear();
priceTextBox.Text = Conversion.StringToMoney(Math.Round(Conversion.StringToDouble(Value), 2).ToString());
if (priceTextBox.Text == "" || Conversion.checkString(priceTextBox.Text) == false)
{
priceTextBox.Text = "0,00";
}
unitsTextBox.Text = sqlServer.dataSet.Tables["Nomenclature"].Rows[0]["units"].ToString();
}
}
}
void initEditFolder()
{
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice WHERE (ID = " + ID + ")";
oleDb.ExecuteFill("Practice");
textBox1.Text = oleDb.dataSet.Tables["Practice"].Rows[0]["ID"].ToString();
textBox2.Text = oleDb.dataSet.Tables["Practice"].Rows[0]["name"].ToString();
oldName = textBox2.Text;
}
public static bool SaveDatabaseSettings()
{
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = ";Jet OLEDB:Database Password="******"12345";
OleDb oleDb;
oleDb = new OleDb(DataConfig.configFile);
try{
oleDb.oleDbCommandSelect.CommandText = "SELECT [id], [name], [localDatabase], [typeDatabase], [typeConnection], [serverConnection] FROM DatabaseSettings";
oleDb.oleDbCommandUpdate.CommandText = "UPDATE DatabaseSettings SET " +
"[name] = @name, " +
"[localDatabase] = @localDatabase, " +
"[typeDatabase] = @typeDatabase, " +
"[typeConnection] = @typeConnection, " +
"[serverConnection] = @serverConnection " +
"WHERE ([id] = @id)";
oleDb.oleDbCommandUpdate.Parameters.Add("@name", OleDbType.VarChar, 255, "name");
oleDb.oleDbCommandUpdate.Parameters.Add("@localDatabase", OleDbType.VarChar, 255, "localDatabase");
oleDb.oleDbCommandUpdate.Parameters.Add("@typeDatabase", OleDbType.VarChar, 255, "typeDatabase");
oleDb.oleDbCommandUpdate.Parameters.Add("@typeConnection", OleDbType.VarChar, 255, "typeConnection");
oleDb.oleDbCommandUpdate.Parameters.Add("@serverConnection", OleDbType.VarChar, 255, "serverConnection");
oleDb.oleDbCommandUpdate.Parameters.Add("@id", OleDbType.Integer, 10, "id");
oleDb.ExecuteFill("DatabaseSettings");
oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["localDatabase"] = DataConfig.localDatabase;
oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["typeDatabase"] = DataConfig.typeDatabase;
oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["typeConnection"] = DataConfig.typeConnection;
oleDb.dataSet.Tables["DatabaseSettings"].Rows[0]["serverConnection"] = DataConfig.serverConnection;
oleDb.ExecuteUpdate("DatabaseSettings");
oleDb.Dispose();
Utilits.Console.Log("Сохранение настроек соединения с базой данных прошло успешно.");
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(true);
}catch (Exception ex) {
oleDb.Error();
Utilits.Console.Log("[ОШИБКА] Сохранение настроек соединения с базой данных. " + ex.Message.ToString(), false, true);
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
return(false);
}
}
void TableRefreshLocal()
{
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Orders";
// Дата в формате: BETWEEN #месяц/день/год# AND #месяц/день/год#
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Orders WHERE (docDate BETWEEN #" +
dateTimePicker1.Value.ToString("MM.dd.yyyy").Replace(".", "/") + "# AND #" +
dateTimePicker2.Value.ToString("MM.dd.yyyy").Replace(".", "/") + "#) " +
"AND (docNumber LIKE '%" + toolStripComboBox1.Text +
"%' OR docSum LIKE '%" + toolStripComboBox1.Text +
"%' OR docVat LIKE '%" + toolStripComboBox1.Text +
"%' OR docTotal LIKE '%" + toolStripComboBox1.Text +
"%' OR docAutor LIKE '%" + toolStripComboBox1.Text +
"%' OR docCounteragent LIKE '%" + toolStripComboBox1.Text +
"%') ORDER BY docDate DESC";
if (oleDb.ExecuteFill("Orders"))
{
listView1.Items.Clear();
DateTime dt;
ListViewItem ListViewItem_add;
foreach (DataRow rowElement in oleDb.dataSet.Tables[0].Rows)
{
ListViewItem_add = new ListViewItem();
dt = new DateTime();
DateTime.TryParse(rowElement["docDate"].ToString(), out dt);
ListViewItem_add.SubItems.Add(dt.ToString("dd.MM.yyyy"));
ListViewItem_add.StateImageIndex = 0;
ListViewItem_add.SubItems.Add(rowElement["docNumber"].ToString());
ListViewItem_add.SubItems.Add(rowElement["docName"].ToString());
ListViewItem_add.SubItems.Add(Conversion.StringToMoney(Conversion.StringToDouble(rowElement["docSum"].ToString()).ToString()));
ListViewItem_add.SubItems.Add(Conversion.StringToMoney(Conversion.StringToDouble(rowElement["docVat"].ToString()).ToString()));
ListViewItem_add.SubItems.Add(Conversion.StringToMoney(Conversion.StringToDouble(rowElement["docTotal"].ToString()).ToString()));
ListViewItem_add.SubItems.Add(rowElement["docCounteragent"].ToString());
ListViewItem_add.SubItems.Add(rowElement["docAutor"].ToString());
ListViewItem_add.SubItems.Add(rowElement["docPurchasePlan"].ToString());
ListViewItem_add.SubItems.Add(rowElement["id"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
else
{
Utilits.Console.Log("[ОШИБКА] Ошибка выполнения запроса к таблице Заказы.");
oleDb.Error();
return;
}
// ВЫБОР: выдиляем ранее выбранный элемент.
listView1.SelectedIndices.IndexOf(selectTableLine);
}
void loadFolders()
{
oleDb = new OleDb(Config.databaseFile);
oleDb.oleDbCommandSelect.CommandText = "SELECT name FROM Practice WHERE (type = '" + Constants.TYPE_FOLDER + "')";
if (oleDb.ExecuteFill("Practice"))
{
comboBox1.Items.Clear();
foreach (DataRow row in oleDb.dataSet.Tables["Practice"].Rows)
{
comboBox1.Items.Add(row["name"].ToString());
}
}
comboBox1.Text = ParentFolder;
}
public void TableRefresh()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.dataSet.Clear();
oleDb.oleDbCommandSelect.CommandText = "SELECT id, name, pass, permissions FROM Users";
oleDb.oleDbCommandDelete.CommandText = "DELETE FROM Users WHERE (id = @id)";
oleDb.oleDbCommandDelete.Parameters.Add("@id", OleDbType.Integer, 10, "id");
if (oleDb.ExecuteFill("Users"))
{
listView1.Items.Clear();
foreach (DataRow row in oleDb.dataSet.Tables["Users"].Rows)
{
ListViewItem listViewItem = new ListViewItem();
listViewItem.SubItems.Add(row["name"].ToString());
listViewItem.SubItems.Add(getPermissions(row["permissions"].ToString()));
listViewItem.SubItems.Add(row["id"].ToString());
listViewItem.StateImageIndex = 0;
listView1.Items.Add(listViewItem);
}
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer = new SqlServer();
sqlServer.dataSet.Clear();
sqlServer.sqlCommandSelect.CommandText = "SELECT id, name, pass, permissions FROM Users";
sqlServer.sqlCommandDelete.CommandText = "DELETE FROM Users WHERE (id = @id)";
sqlServer.sqlCommandDelete.Parameters.Add("@id", SqlDbType.Int, 10, "id");
if (sqlServer.ExecuteFill("Users"))
{
listView1.Items.Clear();
foreach (DataRow row in sqlServer.dataSet.Tables["Users"].Rows)
{
ListViewItem listViewItem = new ListViewItem();
listViewItem.SubItems.Add(row["name"].ToString());
listViewItem.SubItems.Add(getPermissions(row["permissions"].ToString()));
listViewItem.SubItems.Add(row["id"].ToString());
listViewItem.StateImageIndex = 0;
listView1.Items.Add(listViewItem);
}
}
}
}
void initEditFile()
{
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice WHERE (ID = " + ID + ")";
oleDb.ExecuteFill("Practice");
textBox1.Text = oleDb.dataSet.Tables["Practice"].Rows[0]["ID"].ToString();
textBox2.Text = oleDb.dataSet.Tables["Practice"].Rows[0]["name"].ToString();
comboBox1.Text = oleDb.dataSet.Tables["Practice"].Rows[0]["parent"].ToString();
oldName = textBox2.Text;
/* Загрузить вложенные тесты */
DataTable table;
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Tests";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Tests WHERE (IDPractice = " + textBox1.Text + ") ORDER BY ID ASC";
if (oleDb.ExecuteFill("Tests"))
{
table = oleDb.dataSet.Tables["Tests"];
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow row in table.Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(row["condition"].ToString());
ListViewItem_add.StateImageIndex = 1;
ListViewItem_add.SubItems.Add(row["question"].ToString());
ListViewItem_add.SubItems.Add(row["answer"].ToString());
ListViewItem_add.SubItems.Add(row["hint"].ToString());
ListViewItem_add.SubItems.Add(row["ID"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
else
{
Utilits.Console.LogError("Ошибка загрузки вложенных тестов.", false, true);
oleDb.Error();
return;
}
}
void readPrice()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM " + PriceName;
if (oleDb.ExecuteFill(PriceName))
{
oleDb.dataSet.DataSetName = PriceName;
renameColumn();
dataGrid1.CaptionText = PriceName;
dataGrid1.DataSource = oleDb.dataSet;
dataGrid1.DataMember = oleDb.dataSet.Tables[0].TableName;
dataGrid1.Enabled = true;
Utilits.Console.Log("Прайс " + PriceName + " был успешно загружен.");
}
else
{
oleDb.Error();
Utilits.Console.Log("[ПРЕДУПРЕЖДЕНИЕ] Прайс " + PriceName + " не удалось открыть.");
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT * FROM " + PriceName;
if (sqlServer.ExecuteFill(PriceName))
{
sqlServer.dataSet.DataSetName = PriceName;
renameColumn();
dataGrid1.CaptionText = PriceName;
dataGrid1.DataSource = sqlServer.dataSet;
dataGrid1.DataMember = sqlServer.dataSet.Tables[0].TableName;
dataGrid1.Enabled = true;
Utilits.Console.Log("Прайс " + PriceName + " был успешно загружен.");
}
else
{
sqlServer.Error();
Utilits.Console.Log("[ПРЕДУПРЕЖДЕНИЕ] Прайс " + PriceName + " не удалось открыть.");
}
}
}
/* =================================================================================================
* РАЗДЕЛ: СОБЫТИЙ
* =================================================================================================
*/
bool openPrice()
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
// OLEDB
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM " + textBox1.Text;
if (oleDb.ExecuteFill(textBox1.Text))
{
oleDb.dataSet.DataSetName = textBox1.Text;
dataSet = oleDb.dataSet.Copy();
Utilits.Console.Log("Прайс " + textBox1.Text + " был успешно загружен.");
return(true);
}
else
{
oleDb.Error();
Utilits.Console.Log("[ПРЕДУПРЕЖДЕНИЕ] Прайс " + textBox1.Text + " не удалось открыть.");
return(false);
}
}
else if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
// MSSQL SERVER
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT * FROM " + textBox1.Text;
if (sqlServer.ExecuteFill(textBox1.Text))
{
sqlServer.dataSet.DataSetName = textBox1.Text;
dataSet = sqlServer.dataSet.Copy();
Utilits.Console.Log("Прайс " + textBox1.Text + " был успешно загружен.");
return(true);
}
else
{
sqlServer.Error();
Utilits.Console.Log("[ПРЕДУПРЕЖДЕНИЕ] Прайс " + textBox1.Text + " не удалось открыть.");
return(false);
}
}
Utilits.Console.Log("[ПРЕДУПРЕЖДЕНИЕ] Прайс " + textBox1.Text + " не удалось открыть.");
return(false);
}
public static void ReadSettings()
{
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = ";Jet OLEDB:Database Password="******"12345";
OleDb oleDb;
oleDb = new OleDb(DataConfig.configFile);
try{
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Settings";
oleDb.ExecuteFill("Settings");
if (oleDb.dataSet.Tables["Settings"].Rows[0]["autoUpdate"].ToString() == "True")
{
DataConfig.autoUpdate = true;
}
else
{
DataConfig.autoUpdate = false;
}
if (oleDb.dataSet.Tables["Settings"].Rows[0]["showConsole"].ToString() == "True")
{
DataConfig.showConsole = true;
}
else
{
DataConfig.showConsole = false;
}
DataConfig.period = oleDb.dataSet.Tables["Settings"].Rows[0]["period"].ToString();
oleDb.Dispose();
Utilits.Console.Log("[Настройки]: Настройки программы успешно загружены.");
}catch (Exception ex) {
oleDb.Error();
MessageBox.Show("[Настройки]: " + ex.ToString(), "Ошибка");
Application.Exit();
}
DataConfig.oledbConnectLineBegin = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
DataConfig.oledbConnectLineEnd = "";
DataConfig.oledbConnectPass = "";
}
void searchLocal()
{
DataTable table;
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice WHERE (name LIKE '%" + toolStripComboBox1.Text + "%') ORDER BY name ASC";
if (oleDb.ExecuteFill("Practice"))
{
table = oleDb.dataSet.Tables["Practice"];
}
else
{
Utilits.Console.LogError("Ошибка поиска.", false, true);
oleDb.Error();
return;
}
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow row in table.Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(row["name"].ToString());
if (row["type"].ToString() == Constants.TYPE_FOLDER)
{
ListViewItem_add.StateImageIndex = 0;
ListViewItem_add.SubItems.Add("Папка");
}
else
{
ListViewItem_add.StateImageIndex = 1;
ListViewItem_add.SubItems.Add("");
}
ListViewItem_add.SubItems.Add(row["ID"].ToString());
ListViewItem_add.SubItems.Add(row["name"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
void searchLocal()
{
DataTable table;
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Nomenclature";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Nomenclature WHERE (name LIKE '%" + toolStripComboBox1.Text + "%') ORDER BY name ASC";
if (oleDb.ExecuteFill("Nomenclature"))
{
table = oleDb.dataSet.Tables["Nomenclature"];
}
else
{
Utilits.Console.Log("[ОШИБКА] Ошибка поиска.");
oleDb.Error();
return;
}
listView1.Items.Clear();
ListViewItem ListViewItem_add;
foreach (DataRow row in table.Rows)
{
ListViewItem_add = new ListViewItem();
ListViewItem_add.SubItems.Add(row["name"].ToString());
if (row["type"].ToString() == "folder")
{
ListViewItem_add.StateImageIndex = 0;
ListViewItem_add.SubItems.Add("Папка");
}
else
{
ListViewItem_add.StateImageIndex = 1;
ListViewItem_add.SubItems.Add("");
}
ListViewItem_add.SubItems.Add(row["id"].ToString());
ListViewItem_add.SubItems.Add(row["units"].ToString());
listView1.Items.Add(ListViewItem_add);
}
}
/* Проверка обновленных таблиц в базе данных */
public void check()
{
oleDb.dataSet.Clear();
if (oleDb.ExecuteFill("History") == true)
{
Table table;
for (int i = 0; i < tables.Count; i++)
{
if (tables[i].datetime != oleDb.dataSet.Tables["History"].Rows[i]["datetime"].ToString())
{
refresh(tables[i].name, tables[i].represent);
table = tables[i];
table.datetime = oleDb.dataSet.Tables["History"].Rows[i]["datetime"].ToString();
tables[i] = table;
}
}
}
else
{
Utilits.Console.Log("ПРЕДУПРЕЖДЕНИЕ: Служба истории обновлений базы данных не удалось получить обновленные данные!");
}
}
void reportOleDb()
{
OleDb oleDb = null;
try{
oleDb = new OleDb(DataConfig.localDatabase);
oleDb.dataSet.Clear();
oleDb.oleDbCommandSelect.CommandText = getCommandSelectOleDb();
/*
* oleDb.oleDbCommandSelect.CommandText = "SELECT " +
* "OrderNomenclature.name " +
* "FROM OrderNomenclature, Orders "+
* "WHERE (OrderNomenclature.counteragentName = '" + counteragentTextBox.Text + "') "+
* "AND (Orders.docCounteragent = '" + counteragentTextBox.Text + "') "+
* "AND (OrderNomenclature.docOrder = Orders.docNumber) " +
* "AND (Orders.docDate BETWEEN #" +
* dateTimePicker1.Value.ToString("MM.dd.yyyy").Replace(".", "/") + "# AND #" +
* dateTimePicker2.Value.ToString("MM.dd.yyyy").Replace(".", "/") + "#) ";
*/
if (oleDb.ExecuteFill("OrderNomenclature"))
{
if (oleDb.dataSet.Tables.Count > 0)
{
dataGridView1.DataSource = oleDb.dataSet;
dataGridView1.DataMember = oleDb.dataSet.Tables[0].TableName;
settingsColumns();
}
}
}catch (Exception ex) {
if (oleDb != null)
{
oleDb.Dispose();
}
Utilits.Console.Log("[ОШИБКА]: " + ex.Message, false, true);
}
}
