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(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;
}
}
private void button_CheckDBversion_Click(object sender, EventArgs e)
{
try
{
_oleDb = new OleDb();
_oleDb.Open();
_oleDb.CreateDbCommand("SELECT TOP(1)* FROM [DbVersion] ORDER BY ID DESC");
_oleDb.ExecuteReader();
_oleDb.Reader.Read();
button_CheckDBversion.Text = Convert.ToString(_oleDb.Reader["Build"]);
_oleDb.Close();
}
catch (Exception exception)
{
button_CheckDBversion.Text = @"Ошибка";
_mainForm.toolStripStatusLabel1.Text = @"Ошибка чтения версии базы " + exception.Message;
if (_oleDb != null)
{
_mainForm.toolStripStatusLabel1.Text += @" SQL: " + _oleDb.MessErr;
}
}
finally
{
if (_oleDb != null && _oleDb.SqlConnected())
{
_oleDb.Close();
}
}
}
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;
}
}
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();
}
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 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;
}
}
private async void Button2_Click(object sender, EventArgs e)
{
OleDb.GetSheetsNames(excel);
var table = await Task.Run(() => OleDb.ReadData(excel, txtnmlst.Text));
dataGridView1.DataSource = excel.Data;
for (var i = 0; i < dataGridView1.Columns.Count; i++)
{
dataGridView1.Columns[i].HeaderCell.Style.BackColor = Color.Gray;
dataGridView1.Columns[i].HeaderCell.Style.ForeColor = Color.DarkGray;
}
for (var i = 0; i < dataGridView1.RowCount; i++)
{
dataGridView1.Rows[i].HeaderCell.Value = i.ToString();
}
lstColNames.Items.Clear();
foreach (DataColumn col in excel.Data.Columns)
{
lstColNames.Items.Add(col.ColumnName);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
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("ПРЕДУПРЕЖДЕНИЕ: Служба истории обновлений базы данных не запущена!!!");
}
}
/// <summary>
/// 根据程序集名称和数据访问对象类型创建一个新的数据访问对象实例。
/// </summary>
/// <param name="HelperAssembly">程序集名称</param>
/// <param name="HelperType">数据访问对象类型</param>
/// <param name="ConnectionString">连接字符串</param>
/// <returns>数据访问对象</returns>
public static AdoHelper GetDBHelper(string HelperAssembly, string HelperType, string ConnectionString)
{
AdoHelper helper = null;// CommonDB.CreateInstance(HelperAssembly, HelperType);
if (HelperAssembly == "PWMIS.Core")
{
switch (HelperType)
{
case "PWMIS.DataProvider.Data.SqlServer": helper = new SqlServer(); break;
case "PWMIS.DataProvider.Data.Oracle": helper = new Oracle(); break;
case "PWMIS.DataProvider.Data.OleDb": helper = new OleDb(); break;
case "PWMIS.DataProvider.Data.Odbc": helper = new Odbc(); break;
case "PWMIS.DataProvider.Data.Access": helper = new Access(); break;
//case "PWMIS.DataProvider.Data.SqlServerCe": helper = new SqlServerCe(); break;
default: helper = new SqlServer(); break;
}
}
else
{
helper = CommonDB.CreateInstance(HelperAssembly, HelperType);
}
helper.ConnectionString = ConnectionString;
return(helper);
}
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 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 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);
}
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);
}
}
private void Form1_Load(object sender, EventArgs e)
{
OleDb.GetOleDbProvidersCompleted += OleDb_GetOleDbProvidersCompleted;
OleDb.ConnectionStringGenerated += OleDb_ConnectionStringGenerated;
OleDb.GetSheetsNameCompleted += OleDb_GetSheetsNameCompleted;
OleDb.ReadDataFinished += OleDb_ReadDataFinished;
OleDb.GetOleDBProviders();
}
void initNewFolder()
{
oleDb = new OleDb(Config.databaseFile);
oleDb.dataSet.Clear();
oleDb.dataSet.DataSetName = "Practice";
oleDb.oleDbCommandSelect.CommandText = "SELECT * FROM Practice";
//oleDb.ExecuteFill("Practice");
}
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();
}
}
}
private void Button1_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
excel = new ExcelFile(openFileDialog1.FileName);
}
OleDb.GenerateConnectionString(excel);
GC.Collect();
GC.WaitForPendingFinalizers();
}
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
var db = new OleDb("empty.accdb");
Console.WriteLine("All Tables:");
foreach (var t in db.GetAllTables())
{
Console.WriteLine($"> {t}");
}
}
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 Test_Type_OleDbServerDatabase()
{
// Define tipo OleDb
AbstractDataBase dataBase = new OleDb();
// Atribuindo command
DbCommand cmd = dataBase.Command;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT, DELETE, INSERT, UPDATE";
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
reader.Close();
cmd.Connection.Close();
Assert.IsInstanceOfType(dataBase, typeof(OleDb));
}
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 + " не удалось открыть.");
}
}
}
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 = "";
}
/* =================================================================================================
* РАЗДЕЛ: СОБЫТИЙ
* =================================================================================================
*/
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);
}
/* =================================================================================================
* РАЗДЕЛ: СОБЫТИЙ
* =================================================================================================
*/
void FormUsersEditLoad(object sender, EventArgs e)
{
if (DataConfig.typeConnection == DataConstants.CONNETION_LOCAL)
{
oleDb = new OleDb(DataConfig.localDatabase);
}
if (DataConfig.typeConnection == DataConstants.CONNETION_SERVER)
{
sqlServer = new SqlServer();
}
if (ID == null)
{
Text = "Создать";
}
else
{
Text = "Изменить";
open();
}
Utilits.Console.Log(Text);
}
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);
}
}
/// <summary>
/// Retrieve a parameter array from the cache
/// </summary>
/// <param name="connectionString">A valid connection string for a OleDbConnection</param>
/// <param name="commandText">The stored procedure name or T-OleDb command</param>
/// <returns>An array of OleDbParamters</returns>
public static OleDbParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
ArrayList tempValue = new ArrayList();
IDataParameter[] OleDbP = new OleDb().GetCachedParameterSet(connectionString, commandText);
foreach( IDataParameter parameter in OleDbP )
{
tempValue.Add( parameter );
}
return (OleDbParameter[])tempValue.ToArray( typeof(OleDbParameter) );
}
