public static bool DropUser(string Login)
{
SqlHandle sql = new SqlHandle(DataService.connectionString);
sql.SqlStatement = $"DROP USER IF EXISTS {Login}";
sql.Connect();
bool success = sql.Execute();
if (!sql.Execute())
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
/*
* sql.SqlStatement = $"DROP LOGIN {Login}";
*
* if (!sql.Execute())
* {
* MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
* return false;
* }
*/
sql.Disconnect();
return(success);
}
public static bool IsUserExist(string DB, string User)
{
bool success;
SqlHandle sql = new SqlHandle(DataService.connectionString);
sql.Connect();
/*
* sql.SqlStatement = $"USE {DB}";
* sql.IsResultSet = false;
* success = sql.Execute();
*/
sql.IsResultSet = true;
sql.SqlStatement = $"select count(*) from {DB}.sys.database_principals where type = 'S' and name = '{User}'";
success = sql.Execute();
success = sql.Reader.Read() && (bool)(sql.Reader.GetSqlInt32(0) > 0);
/*
* sql.SqlStatement = $"USE {DataService.setting.BaseName}";
* sql.IsResultSet = false;
* sql.Execute();
*/
sql.Disconnect();
return(success);
}
public static bool IsLoginExist(string Login)
{
SqlHandle sql = new SqlHandle(DataService.connectionString);
sql.SqlStatement = $"select name from sys.server_principals where type = 'S' and name = '{Login}' ";
sql.IsResultSet = true;
bool success = sql.Connect() && sql.Execute();
success = sql.Reader.HasRows && sql.Reader.Read() && (bool)(sql.Reader.GetSqlString(0) != null);
sql.Disconnect();
return(success);
}
public static bool CreateLogin(string Login, string Pswd, bool IsWindowsUser)
{
/*if (IsLoginExist(Login))
* return true;
* SqlHandle sql = new SqlHandle(DataService.connectionString);
*
* StringBuilder sqlStatement = new StringBuilder($"CREATE LOGIN { Login } ");
*
* if (!IsWindowsUser)
* sqlStatement.Append($"WITH PASSWORD = '******';");
* sql.SqlStatement = sqlStatement.ToString();
* sql.Connect();
*
* bool success = sql.Execute();
*
* if (!success)
* MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
* sql.Disconnect();
*
* return success;
*/
SqlHandle sql = new SqlHandle(DataService.connectionString);
sql.Connect();
sql.TypeCommand = CommandType.StoredProcedure;
sql.SqlStatement = "CreateLogin";
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@Login", Value = Login
});
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@Psw", Value = Pswd
});
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@IsWnd", Value = IsWindowsUser
});
bool success = sql.Execute();
if (!success)
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
sql.Disconnect();
return(success);
}
public static bool ForceMergeLVAttribute(int ShpId)
{
SqlHandle sql = new SqlHandle(DataService.connectionString);
sql.Connect();
sql.TypeCommand = CommandType.StoredProcedure;
sql.SqlStatement = "SP_PL_ForceMergeLVAttribute";
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@ShpID", Value = ShpId
});
bool success = sql.Execute();
if (!success)
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
sql.Disconnect();
return(success);
}
public static bool CreateDBUser(string DB, string User, string Login)
{
if (IsUserExist(DB, User))
{
return(true);
}
bool success;
SqlHandle sql = new SqlHandle(DataService.connectionString);
StringBuilder sqlStatement = new StringBuilder($"USE {DB}; CREATE USER [{Login}] FOR LOGIN [{Login}]");
sql.SqlStatement = sqlStatement.ToString();
success = sql.Connect() && sql.Execute();
if (!success)
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
sql.SqlStatement = "sp_addrolemember";
sql.Parameters.Add(new SqlParameter {
ParameterName = "@rolename"
});
sql.Parameters.Add(new SqlParameter {
ParameterName = "@membername"
});
sql.TypeCommand = CommandType.StoredProcedure;
sql.IsResultSet = false;
sql.Parameters["@rolename"].Value = "db_datareader";
sql.Parameters["@membername"].Value = User;
success = sql.Execute();
if (!success)
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
sql.Parameters["@rolename"].Value = "db_datawriter";
//sql.Parameters["@membername"].Value = User;
success = sql.Execute();
if (!success)
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (DB == DataService.setting.BaseName)
{
sql.Parameters["@rolename"].Value = "pl_user";
success = sql.Execute();
if (!success)
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
sql.TypeCommand = CommandType.Text;
sql.SqlStatement = $"USE { DataService.setting.BaseName}";
success = sql.Execute();
sql.Disconnect();
return(true);
}
public void Populate()
{
string UserId = cmbUser.Text;
int ShpType = cmbShpType.SelectedIndex - 1;
SqlHandle sql = new SqlHandle(DataService.connectionString);
#region ДатыОтгрузки
if (_shipmentId >= 0)
{
sql.Connect();
string table_name = !_isShipment ? "movement_log" : "shipments_log";
string field_name = !_isShipment ? "movement_id" : "shipment_id";
sql.TypeCommand = CommandType.Text;
sql.IsResultSet = true;
sql.SqlStatement = $@"select min(dml_date) min_dml_date, max(dml_date) max_dml_date
from {table_name}
where {field_name} = " + _shipmentId;
bool Success = sql.Execute() && sql.Reader != null && sql.Reader.Read() && sql.Reader.HasRows;
if (Success)
{
dtBegin.Value = sql.Reader.IsDBNull(0) ? DateTime.Now: sql.Reader.GetDateTime(0);
dtEnd.Value = sql.Reader.IsDBNull(1) ? DateTime.Now:sql.Reader.GetDateTime(1);
}
sql.Disconnect();
}
#endregion
DateTime DateFrom = dtBegin.Value;
DateTime DateTill = dtEnd.Value;
sql.SqlStatement = "SP_PL_GetShipmentLog";
sql.Connect();
sql.TypeCommand = CommandType.StoredProcedure;
sql.IsResultSet = true;
object shpType = null;
if (ShpType >= 0)
{
shpType = ShpType;
}
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@ShpId", Value = _shipmentId
});
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@From", Value = DateFrom
});
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@Till", Value = DateTill
});
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@In", Value = shpType
});
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@UserId", Value = UserId
});
bool success = sql.Execute();
if (!success)
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
DataSet ds = new DataSet();
ds.Tables.Add();
ds.Tables[0].Load(sql.Reader);
tblShipmentLog.AutoGenerateColumns = false;
tblShipmentLog.DataSource = ds.Tables[0];
sql.Disconnect();
CalcRowColor();
tblShipmentItemLog.AutoGenerateColumns = false;
tblMovementItemLog.AutoGenerateColumns = false;
if (tblShipmentLog.Rows.Count > 0)
{
ShowShipmentLogDetail();
}
/*tblShipmentItemLog.Visible = false;
* tblMovementItemLog.Visible = false;*/
}