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 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 int SQLGetIntValue(string SQLExpr)
{
SqlHandle sql = new SqlHandle(DataService.connectionString);
sql.SqlStatement = SQLExpr;
sql.IsResultSet = true;
bool success = sql.Connect() && sql.Execute() && sql.Reader.Read();
if (success)
{
return(sql.Reader.IsDBNull(0)?0:sql.Reader.GetSqlInt32(0).Value);
}
return(0);
/*
* using (IDbConnection db = new SqlConnection(connectionString))
* {
* if (db.State == ConnectionState.Closed)
* {
* db.Open();
* }
* //
* int? result = db.Query<int>(SQLExpr).FirstOrDefault();
* return result;
*
* } */
}
private void PopulateMovementItem()
{
tblMovementItem.AutoGenerateColumns = false;
SqlHandle sqlHandle1 = new SqlHandle(DataService.connectionString);
sqlHandle1.SqlStatement = "SP_PL_GetMovementItems";
sqlHandle1.Connect();
sqlHandle1.TypeCommand = CommandType.StoredProcedure;
sqlHandle1.IsResultSet = true;
SqlHandle sqlHandle2 = sqlHandle1;
SqlParameter sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "@MovementID";
sqlParameter.Value = (object)this._movement.Id;
sqlHandle2.AddCommandParametr(sqlParameter);
if (!sqlHandle1.Execute())
{
int num = (int)MessageBox.Show(sqlHandle1.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
else
{
DataSet dataSet = new DataSet();
dataSet.Tables.Add();
dataSet.Tables[0].Load((IDataReader)sqlHandle1.Reader);
this.tblMovementItem.DataSource = (object)dataSet.Tables[0];
}
}
private SqlDataReader GetCurrentTask(DateTime DateFrom, DateTime?DateTill)
{
SqlHandle sql = new SqlHandle(DataService.connectionString);
sql.SqlStatement = "SP_PL_CurrentTaskQuery";
sql.Connect();
sql.TypeCommand = CommandType.StoredProcedure;
sql.IsResultSet = true;
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@From", Value = DateFrom
});
sql.AddCommandParametr(new SqlParameter {
ParameterName = "@Till", Value = DateTill
});
bool success = sql.Execute();
if (!success)
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
return(sql.Reader);
}
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);
}
private bool AlterLogin(string Login, string NewPassword)
{
SqlHandle sql = new SqlHandle(DataService.connectionString);
sql.SqlStatement = $"ALTER USER {Login} WITH PASSWORD = '******'";
if (!sql.Execute())
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
sql.SqlStatement = $"ALTER LOGIN {Login} WITH PASSWORD = '******'";
if (!sql.Execute())
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
return(true);
}
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);
}
private bool CreateUser(string Login, string Password, bool IsWindowsUser)
{
SqlHandle sql = new SqlHandle(DataService.connectionString);
StringBuilder sqlStatement = new StringBuilder($"CREATE LOGIN { Login } ");
if (!IsWindowsUser)
{
sqlStatement.Append($"WITH PASSWORD = '******';");
}
sql.SqlStatement = sqlStatement.ToString();
if (!sql.Execute())
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
sqlStatement.Clear();
sqlStatement.Append($"CREATE USER {Login} FOR LOGIN {Login};");
sql.SqlStatement = sqlStatement.ToString();
if (!sql.Execute())
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
sqlStatement.Clear();
sqlStatement.Append($" EXEC [sp_addrolemember] 'pl_user', '{Login}';");
sql.SqlStatement = sqlStatement.ToString();
if (!sql.Execute())
{
MessageBox.Show(sql.LastError, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
return(true);
}
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;*/
}