private double GetClientSum(int clientId)
{
DataTable clientSumDataTable = new DataTable();
string sql = $@"SELECT
Clients.Sum
FROM Clients
WHERE Clients.Id = {clientId}";
ClientsSqlDataAdapter.SelectCommand = new SqlCommand(sql, commonSqlConnection);
ClientsSqlDataAdapter.SafelyFill(clientSumDataTable);
return(clientSumDataTable.Rows[0].Field <double>("Sum"));
}
/// <summary>
/// Возвращает словарь клиента виде имени и его идентифтикатора.
/// </summary>
/// <returns></returns>
public Dictionary <int, string> GetAllClientNamesWithId()
{
DataTable clientNamesDataTable = new DataTable();
string sql = @"SELECT
Clients.Id,
Clients.Name
FROM Clients";
ClientsSqlDataAdapter.SelectCommand = new SqlCommand(sql, commonSqlConnection);
ClientsSqlDataAdapter.SafelyFill(clientNamesDataTable);
return(clientNamesDataTable
.AsEnumerable()
.ToDictionary(row => row.Field <int>("Id"), row => row.Field <string>("Name"))
.ToDictionary(x => x.Key, y => y.Value.Trim()));
}
private void UpdateClientSum(int id, double sum)
{
foreach (DataRow row in PhysDataTable.Rows)
{
if (Int32.Parse(row["Id"].ToString()) == id)
{
row["Sum"] = sum;
ClientsSqlDataAdapter.SafelyUpdate(PhysDataTable);
return;
}
}
foreach (DataRow row in LegalDataTable.Rows)
{
if (Int32.Parse(row["Id"].ToString()) == id)
{
row["Sum"] = sum;
ClientsSqlDataAdapter.SafelyUpdate(LegalDataTable);
return;
}
}
}
private DataView Prepare(string commonTableSql, DataTable table)
{
ClientsSqlDataAdapter.SelectCommand = new SqlCommand(commonTableSql, commonSqlConnection);
// Insert.
string sql = @"INSERT INTO Clients (Vip, PhysicalPersonsDepartment, Name, Sum, DepositPercent, CreditPercent)
VALUES(@Vip, @PhysicalPersonsDepartment, @Name, @Sum, @DepositPercent, @CreditPercent)
SET @Id = @@IDENTITY";
ClientsSqlDataAdapter.InsertCommand = new SqlCommand(sql, commonSqlConnection);
ClientsSqlDataAdapter.InsertCommand.Parameters.Add("@Id", SqlDbType.Int, 4, "Id").Direction = ParameterDirection.Output;
ClientsSqlDataAdapter.InsertCommand.Parameters.Add("@Vip", SqlDbType.Bit, 4, "Vip");
ClientsSqlDataAdapter.InsertCommand.Parameters.Add("@PhysicalPersonsDepartment", SqlDbType.Bit, 4, "PhysicalPersonsDepartment");
ClientsSqlDataAdapter.InsertCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 30, "Name");
ClientsSqlDataAdapter.InsertCommand.Parameters.Add("@Sum", SqlDbType.Int, 8, "Sum");
ClientsSqlDataAdapter.InsertCommand.Parameters.Add("@DepositPercent", SqlDbType.Float, 4, "DepositPercent");
ClientsSqlDataAdapter.InsertCommand.Parameters.Add("@CreditPercent", SqlDbType.Float, 4, "CreditPercent");
// Update.
sql = @"UPDATE Clients SET
Name = @Name,
Sum = @Sum
WHERE Id=@Id";
ClientsSqlDataAdapter.UpdateCommand = new SqlCommand(sql, commonSqlConnection);
ClientsSqlDataAdapter.UpdateCommand.Parameters.Add("@Id", SqlDbType.Int, 4, "Id").SourceVersion =
DataRowVersion.Original;
ClientsSqlDataAdapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 30, "Name");
ClientsSqlDataAdapter.UpdateCommand.Parameters.Add("@Sum", SqlDbType.Int, 8, "Sum");
// Delete.
sql = "DELETE FROM Clients WHERE Id = @Id";
ClientsSqlDataAdapter.DeleteCommand = new SqlCommand(sql, commonSqlConnection);
ClientsSqlDataAdapter.DeleteCommand.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");
ClientsSqlDataAdapter.SafelyFill(table);
return(table.DefaultView);
}