/// <summary>
/// Получение данных об устройства
/// </summary>
/// <param name="param">Параметр поиска (что ищем)</param>
/// <param name="InventoryNumber">Инвентарный номер устройства</param>
/// <returns></returns>
public string getGetDeviceData(string param, string InventoryNumber)
{
int countRead = 0;
string deviceData = " ";
string query = "SELECT " + param + " FROM device_manager WHERE inventnumber = \"" + InventoryNumber + "\"";
//Создаём новое соединение
Connection = new MySqlConnection(GetConnectionString.getStringConnectionData());
if (OpenConnection())
{
MySqlCommand mySqlCommand = new MySqlCommand(query, Connection);
MySqlDataReader dataReader = mySqlCommand.ExecuteReader();
while (dataReader.Read())
{
deviceData = Convert.ToString(dataReader[param] + "");
countRead++;
}
if (countRead == 0)
{
throw new Exception("Устройство с заданым id не найдено!");
}
dataReader.Close();
CloseConnection();
return(deviceData);
}
else
{
throw new Exception("Возникла ошибка при соединении с сервером.");
return(deviceData);
}
}
/// <summary>
/// Авторизация
/// </summary>
/// <returns>Возвращает кортеж - первый элемент булево значение, второй - список элементов</returns>
public Tuple <bool, List <string> > LogIn()
{
List <string> userData = new List <string>();
string HLogin, HPassword;
try
{
HLogin = EncryptionData.EncryptLogin(Login, Password);
HPassword = EncryptionData.EncryptPassword(Login, Password);
DataBaseConnect DBC = new DataBaseConnect(GetConnectionString.getStringConnectionData());
userData = new List <string>(DBC.getUserInfoFromDataBase(HLogin, HPassword));
getEmergencyAuthData();
if (userData != null)
{
return(Tuple.Create(true, userData));
}
else
{
return(Tuple.Create(false, userData));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Пользователь не найден", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(Tuple.Create(false, userData));
}
}
private static string GetBlobSasUri(string settingKey, string containerName, string blobName)
{
string strgConnStr = GetConnectionString.GetValueFomKeyVault(settingKey);
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(strgConnStr);
//Create the blob client object.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
//Get a reference to a container to use for the sample code, and create it if it does not exist.
CloudBlobContainer container = blobClient.GetContainerReference(containerName);
//Get a reference to a blob within the container.
CloudBlockBlob blob = container.GetBlockBlobReference(blobName);
//Set the expiry time and permissions for the blob.
//In this case the start time is specified as a few minutes in the past, to mitigate clock skew.
//The shared access signature will be valid immediately.
SharedAccessBlobPolicy sasConstraints = new SharedAccessBlobPolicy();
sasConstraints.SharedAccessStartTime = DateTime.UtcNow.AddMinutes(-5);
sasConstraints.SharedAccessExpiryTime = DateTime.UtcNow.AddHours(24);
sasConstraints.Permissions = SharedAccessBlobPermissions.Read | SharedAccessBlobPermissions.Write;
//Generate the shared access signature on the blob, setting the constraints directly on the signature.
string sasBlobToken = blob.GetSharedAccessSignature(sasConstraints);
//Return the URI string for the container, including the SAS token.
return(blob.Uri + sasBlobToken);
}
public bool InsertNewUser(User _user)
{
try
{
using (SqlConnection con = new SqlConnection(GetConnectionString.connect()))
{
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO [DhwUsers] (FirstName,LastName,Email,[Mobile],[Address],Pincode) Values (@Firstname,@lastname,@email,@mobile,@address,@pincode)", con);
cmd.Parameters.AddWithValue("@Firstname", _user.FirstName);
cmd.Parameters.AddWithValue("@lastname", _user.LastName);
cmd.Parameters.AddWithValue("@email", _user.Email);
cmd.Parameters.AddWithValue("@mobile", _user.MobileNO);
cmd.Parameters.AddWithValue("@address", _user.Address);
cmd.Parameters.AddWithValue("@pincode", _user.Pincode);
cmd.ExecuteNonQuery();
con.Close();
}
return(true);
}
catch
{
//Log(ex)
return(false);
}
}
public void GetConnectionStringTest(string server, string database, bool sspi, string username, string pwd, string expected)
{
string data = new GetConnectionString().Get(new Database {
DataSource = server,
InitialCatalog = database,
SSPI = sspi,
UserName = username,
Password = pwd,
});
Assert.Equal(expected, data);
}
/* TODO: UPDATE
* private bool InsertIntoDeviceManager(string table, string inventNumber, string id)
* {
* string query = "INSERT INTO `" + table + "` (`" + inventNumber + "`, `"+ id +"`, `"++"`)";
* Connection = new MySqlConnection(GetConnectionString.getStringConnectionData());
* if (OpenConnection())
* {
* MySqlCommand mCom = new MySqlCommand(query, Connection);
* CloseConnection();
* return true;
* }
* return false;
* }*/
private int SelectMaxId(string table)
{
Connection = new MySqlConnection(GetConnectionString.getStringConnectionData());
if (OpenConnection())
{
string query = "SELECT max(`id`) FROM \"" + table + "\"";
MySqlCommand mySqlCommand = new MySqlCommand(query, Connection);
CloseConnection();
return(Convert.ToInt32(mySqlCommand.ExecuteScalar().ToString()));
}
return(0);
}
/// <summary>
/// Выполняет проверку соеднения с сервером баз данных
/// </summary>
/// <param name="field">List textbox'ов</param>
/// <returns>Если соединение установлено, возвращает true, иначе false</returns>
private bool TestConnectToDb(List <TextBox> field)
{
//TODO: codereview + error Connect;
DataBaseConnect DBC =
new DataBaseConnect(GetConnectionString.getStringConnectionData(field[0].Text, field[1].Text,
field[2].Text, field[3].Text));
if (DBC.TestConnection())
{
return(true);
}
return(false);
}
private void GetTableList()
{
//get possible tables to edit, exclude large ones and ones better managed in arcCatalog etc...
GetConnectionString connString2 = new GetConnectionString(this.txtServer.Text, cboDbs.SelectedItem.ToString());
string ConnStringValue2 = connString2.ConnectionString;
List <string> list = new List <string>();
if (!CheckProcedure())
{
CreateProcedure();
}
try
{
using (SqlConnection con = new SqlConnection(ConnStringValue2))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand("exec dbo.usp_Arvada_GetEditableTables @ReturnType = 0", con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
this.cboTable.DisplayMember = "TableName";
this.cboTable.ValueMember = "TableName";
this.cboTable.DataSource = list;
this.cboTable.BindingContext = this.BindingContext;
if (list.Contains("dbo.AssetIDTableMap"))
{
this.cboTable.SelectedIndex = cboTable.FindStringExact("dbo.AssetIDTableMap");
cboTable.Select();
cboTable.Focus();
}
}
/// <summary>
/// Отправка в базу данных результатов тестирования
/// </summary>
/// <param name="resultDataDevices">TestDataStructure.TestDataStruct[] - массив результатов</param>
/// <returns></returns>
/*public bool InsertDataDevice(TestDataStructure.TestDataStruct[] resultDataDevices)
* {
* int countDevice = resultDataDevices.Count(); //Число устройств
* int counterDev = 0;
* int maxId;
* List<string> tableList = new List<string>();
* List<int> valueList = new List<int>();
*
* try
* {
*
* while (counterDev != countDevice)
* {
* //Получаем id
* maxId = SelectMaxId("minus12v");
* InsertIntoDeviceParamData("minus12v", SelectMaxId("minus12v"), tableList);
* InsertIntoDeviceParamData("plus5v", SelectMaxId("plus5v"), tableList);
* InsertIntoDeviceParamData(" plus12vpause", SelectMaxId("plus12vpause"), tableList);
* InsertIntoDeviceParamData("plus12v25time", SelectMaxId("plus12v25time"), tableList);
* InsertIntoDeviceParamData("plus12v50time", SelectMaxId("plus12v50time"), tableList);
* InsertIntoDeviceParamData("plus12v100time", SelectMaxId("plus12v100time"), tableList);
* InsertIntoDeviceParamData("reciver_in_diff_v", SelectMaxId("reciver_in_diff_v"), tableList);
* InsertIntoDeviceParamData("rise_and_fall_time_signal", SelectMaxId("rise_and_fall_time_signal"), tableList);
* InsertIntoDeviceParamData("out_diff_volt", SelectMaxId("out_diff_volt"), tableList);
* InsertIntoDeviceParamData("transmitter", SelectMaxId("transmitter"), tableList, valueList);
* ///Добавка в другую дичь
* InsertIntoDeviceParamData("temperature", SelectMaxId("temperature"), tableList);
* //query = "INSERT INTO [counterDev].DeviceInventNumber";
* counterDev++;
* }
* return true;
*
* //Min|Max|Aver
* }
* catch (Exception exp)
* {
* return false;
* }
* }*/
/// <summary>
/// Вставка данных в таблицу
/// </summary>
/// <param name="table">Имя таблицы</param>
/// <param name="maxId">Предыдущий номер записи в таблице</param>
/// <param name="paramValue">List параметров</param>
/// <returns>true в случае удачной вставки</returns>
private bool InsertIntoDeviceParamData(string table, int maxId, List <double> paramValue)
{
string query = "INSERT INTO `" + table + "` (`id`, `minimum`, `medium`, `maximum`) VALUES(" + (maxId + 1) +
"," + paramValue[0] + " , " + paramValue[1] + ", " + paramValue[2] + ")";
Connection = new MySqlConnection(GetConnectionString.getStringConnectionData());
if (OpenConnection())
{
MySqlCommand mCom = new MySqlCommand(query, Connection);
mCom.ExecuteScalar();
CloseConnection();
return(true);
}
return(false);
}
/// <summary>
/// Опрос счётчика запусков программы.
/// Если 0 - запуск в первый раз, вход по дежурному паролю.
/// Иначе через БД
/// </summary>
/// <param name="count">Число запусков программы</param>
private void PollCounterLaunchesProgram(int count)
{
if (count == 0)
{
Properties.Application_data.user.Default.field_db_host = "";
Properties.Application_data.user.Default.field_db_host = "";
Properties.Application_data.user.Default.field_db_name = "";
Properties.Application_data.user.Default.field_db_user = "";
Properties.Application_data.user.Default.field_db_password = "";
}
else
{
_connection = GetConnectionString.getStringConnectionData();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
//set the connection string, store in app.config
GetConnectionString connString = new GetConnectionString(this.txtServer.Text, cboDbs.SelectedItem.ToString());
string ConnStringValue = connString.ConnectionString;
// Bind the DataGridView to the BindingSource
// and load the data from the database.
dgAssetTable.DataSource = bindingSource1;
string TableName = this.cboTable.SelectedItem.ToString();
//gets a list of columns in t-sql syntax so we can edit a table
string SQL = GetSQL(ConnStringValue, TableName);
//go populate the data grid view
GetData(SQL, ConnStringValue);
}
private bool CheckProcedure()
{
//checks if the procedure this app needs exist, if it does return true else false
GetConnectionString connString2 = new GetConnectionString(this.txtServer.Text, cboDbs.SelectedItem.ToString());
string ConnStringValue2 = connString2.ConnectionString;
int ProcExists = 0;
try
{
using (SqlConnection con = new SqlConnection(ConnStringValue2))
{
con.Open();
string SQL = "select 1 from sys.objects where name = 'usp_Arvada_GetEditableTables' ";
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand(SQL, con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ProcExists = (int)dr[0];
}
}
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
if (ProcExists == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Вставка данных в таблицу (связка множества подтаблиц в одну
/// </summary>
/// <param name="table">Имя таблицы</param>
/// <param name="paramList">List имён таблиц, которые необходимо вставить</param>
/// <example>queryPart1 `plus12vPause_id`, `plus5v_id`, `minus12v_id`, `plus12v100time_id`, `plus12v50time_id`, `plus12v25time_id`)</example>
/// <returns></returns>
private bool InsertIntoDeviceParamData(string table, int maxId, List <string> tableList, List <int> valueList)
{
string query = "INSERT INTO `" + table + "` (`id`, ";
//Конструирование строки
query += GenerationOfFirstPartQueryString(tableList);
query += GenerationOfTwoPartQueryString(valueList);
Connection = new MySqlConnection(GetConnectionString.getStringConnectionData());
if (OpenConnection())
{
MySqlCommand mCom = new MySqlCommand(query, Connection);
mCom.ExecuteScalar();
CloseConnection();
return(true);
}
return(false);
}
private static void Main()
{
AppDomain currentDomain = AppDomain.CurrentDomain;
currentDomain.UnhandledException += MyHandler;
GetConnectionString getConnectionString = new GetConnectionString();
if (getConnectionString.GetDataList().Count != 0)
{
EmailSettings emailSettings = new EmailSettings(getConnectionString.GetDataList());
}
void MyHandler(object sender, UnhandledExceptionEventArgs args)
{
Exception exception = (Exception)args.ExceptionObject;
ExceptionLogger.Log(exception);
}
}
internal bool InsertCommandData(CommandService cmdService)
{
using (SqlConnection con = new SqlConnection(GetConnectionString.connect()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(string.Format("INSERT INTO [dbo].[MalayalamLanguageBase]([MalayalamWord],[Manglish])VALUES(N'{0}' ,@Manglish)", cmdService.Malayalam), con);
cmd.Parameters.AddWithValue("@Manglish", cmdService.Manglish);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception)
{
return(false);
}
finally
{
con.Close();
}
}
}
internal List <CommandService> GetAllCommands()
{
List <CommandService> listcmd = new List <CommandService>();
using (SqlConnection con = new SqlConnection(GetConnectionString.connect()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * from MalayalamLanguageBase", con);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
CommandService MalCmd = new CommandService
{
Malayalam = rdr["MalayalamWord"].ToString(),
Manglish = rdr["Manglish"].ToString()
};
listcmd.Add(MalCmd);
}
}
}
catch (Exception)
{
throw;
}
finally
{
}
}
return(listcmd);
}
private void GetDatabaseList()
{
GetConnectionString connString2 = new GetConnectionString(this.txtServer.Text);
string ConnStringValue2 = connString2.ConnectionStringStart;
List <string> list = new List <string>();
try
{
using (SqlConnection con = new SqlConnection(ConnStringValue2))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases where database_id > 4 order by name", con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
this.cboDbs.DisplayMember = "name";
this.cboDbs.ValueMember = "name";
this.cboDbs.DataSource = list;
this.cboDbs.BindingContext = this.BindingContext;
}
public bool InsertData(CommandService cmdService)
{
try
{
using (SqlConnection con = new SqlConnection(GetConnectionString.connect()))
{
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[DhwCommand] ([Manglish],[Malayalam] )VALUES (@Manglish,@Malayalam)", con);
cmd.Parameters.AddWithValue("@Manglish", cmdService.Manglish);
cmd.Parameters.AddWithValue("@Malayalam", cmdService.Malayalam);
cmd.ExecuteNonQuery();
con.Close();
}
return(true);
}
catch
{
//Log(ex)
return(false);
}
}
/// <summary>
/// Запрос на выборку данных о пользователе
/// </summary>
/// <returns></returns>
public List <string> getUserInfoFromDataBase(string login, string password)
{
int countRead = 0;
List <string> list = new List <string>();
string query = "SELECT * FROM employees WHERE login=\"" + login + "\" AND password=\"" + password + "\"";
//Создаём новое соединение
Connection = new MySqlConnection(GetConnectionString.getStringConnectionData());
if (OpenConnection())
{
MySqlCommand mySqlCommand = new MySqlCommand(query, Connection);
MySqlDataReader dataReader = mySqlCommand.ExecuteReader();
while (dataReader.Read())
{
list.Add(dataReader["firstname"] + " ");
list.Add(dataReader["secondname"] + " ");
list.Add(dataReader["position"] + " ");
list.Add(dataReader["login"] + " ");
list.Add(dataReader["password"] + " ");
countRead++;
}
if (countRead == 0)
{
throw new Exception("Пользователь не зарегистрирован в системе.");
}
dataReader.Close();
CloseConnection();
return(list);
}
else
{
throw new Exception("Возникла ошибка при соединении с сервером.");
return(list);
}
}
private void CreateProcedure()
{
//create the procedure we need to get tables and table sql
string sqltext = Resources.MyStoredProcedure;
GetConnectionString connString2 = new GetConnectionString(this.txtServer.Text, cboDbs.SelectedItem.ToString());
string ConnStringValue2 = connString2.ConnectionString;
List <string> list = new List <string>();
try
{
using (SqlConnection connection = new SqlConnection(
ConnStringValue2))
{
SqlCommand command = new SqlCommand(sqltext, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// Получает описание устройства по инвентарному номеру
/// </summary>
/// <param name="InventoryId">String, инвентарный номер устройства</param>
/// <returns></returns>
public static string GetDeviceDescription(string InventoryId)
{
DataBaseConnect DBC = new DataBaseConnect(GetConnectionString.getStringConnectionData());
return(DBC.getGetDeviceData("description", InventoryId));
}
/// <summary>
/// Получает имя производителя устройства по инвентарному номеру
/// </summary>
/// <param name="invent"></param>
/// <returns></returns>
public static string GetDeviceManufacturer(string InventoryId)
{
DataBaseConnect DBC = new DataBaseConnect(GetConnectionString.getStringConnectionData());
return(DBC.getGetDeviceData("manufacturer", InventoryId));
}
/// <summary>
/// Pulls all the data from the SQL server
/// </summary>
/// <returns>A list of ContactModel</returns>
public List <MenuItemModel> Startup()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GetConnectionString.CnnVal("Contacts")))
{
/* var output = connection.Query<Person>($"select * from People where LastName = '{aLastName}'").ToList();
* this is bad its called sql injection it gives access to attacks
*/
var output = connection.Query <MenuItemModel>("dbo.Startup").ToList();
return(output);
}
}
