Шустрый
Профиль
Группа: Участник
Сообщений: 71
Регистрация: 12.2.2010
Репутация: нет
Всего: нет
|
| Код |
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace Shedrost
{
public partial class Form1 : Form
{
private DataSet dataset;
private MySqlDataAdapter adapter, help_adapter;
private string connectionstring = "SERVER = localhost; DATABASE = Shedrost; USER ID = root;";
private MySqlConnection connect;
DataRelation relation;
public Form1()
{
InitializeComponent();
connect = new MySqlConnection(connectionstring);
}
private void Form1_Load(object sender, EventArgs e)
{
dataset = new DataSet("Shedrost");
adapter = new MySqlDataAdapter("SELECT * FROM User_info;", connectionstring);
help_adapter = new MySqlDataAdapter("SELECT * FROM User_history;", connectionstring);
adapter.Fill(dataset, "UserInfo");
help_adapter.FillSchema(dataset, SchemaType.Source, "UserHistory");
relation = new DataRelation("User_history_id", dataset.Tables[0].Columns["user_id"],
dataset.Tables[1].Columns["user_id"]);
dataset.Relations.Add(relation);
dataset.Tables[0].Columns["Balance"].DefaultValue = 0;
dataset.Tables[0].PrimaryKey = new DataColumn[] {dataset.Tables[0].Columns[0]};
dataset.Tables[0].Columns[0].AutoIncrement = true;
dataset.Tables[0].Columns[0].AutoIncrementSeed = -1;
dataset.Tables[0].Columns[0].AutoIncrementStep = -1;
DataGridView1.DataSource = dataset.Tables["UserInfo"];
DataGridView1.Columns["user_id"].ReadOnly = true;
DataGridView1.Columns["Balance"].ReadOnly = true;
dataset.Tables[1].Columns[0].AutoIncrement = true;
dataset.Tables[1].Columns[0].AutoIncrementSeed = -1;
dataset.Tables[1].Columns[0].AutoIncrementStep = -1;
dataset.Tables[1].PrimaryKey = new DataColumn[] { dataset.Tables[1].Columns[0] };
DataGridView2.DataSource = dataset.Tables["UserHistory"];
DataGridView2.Columns["user_id"].ReadOnly = true;
DataGridView2.Columns["Deposit_date"].ReadOnly = true;
DataGridView2.Columns["Used_water"].ReadOnly = true;
DataGridView2.Columns["First_rate"].ReadOnly = true;
DataGridView2.Columns["Second_rate"].ReadOnly = true;
DataGridView2.Columns["Sum_rate"].ReadOnly = true;
DataGridView2.Visible = false;
GetTotalValues();
}
private void GetTotalValues()
{
MySqlCommand command = new MySqlCommand();
connect.Open();
command.Connection = connect;
command.CommandText = "SELECT SUM(used_water) FROM User_history;";
totalUsedWaterLabel.Text = "The Sum of used_water: ";
totalUsedWaterLabel.Text += command.ExecuteScalar();
command.CommandText = "SELECT SUM(first_rate) FROM User_history;";
firstRateLabel.Text = "The Sum of first_rate: ";
firstRateLabel.Text += command.ExecuteScalar();
command.CommandText = "SELECT SUM(second_rate) FROM User_history;";
SecondRateLabel.Text = "The Sum of second_rate: ";
SecondRateLabel.Text += command.ExecuteScalar();
command.CommandText = "SELECT SUM(sum_rate) FROM User_history;";
SumRateLabel.Text = "The Sum of sum_rate: ";
SumRateLabel.Text += command.ExecuteScalar();
connect.Close();
}
private void SaveButton_Click(object sender, EventArgs e)
{
try
{
adapter.InsertCommand = CreateDataAdapterParentInsertCommand();
adapter.UpdateCommand = CreateDataAdapterParentUpdateCommand();
adapter.DeleteCommand = CreateDataAdapterParentDeleteCommand();
help_adapter.InsertCommand = CreateDataAdapterChildInsertCommand();
help_adapter.UpdateCommand = CreateDataAdapterChildUpdateCommand();
help_adapter.DeleteCommand = CreateDataAdapterChildDeleteCommand();
adapter.Update(dataset.Tables["UserInfo"].Select("","",DataViewRowState.Added));
help_adapter.Update(dataset.Tables["UserHistory"].Select("", "", DataViewRowState.Added));
adapter.Update(dataset.Tables["UserInfo"].Select("", "", DataViewRowState.ModifiedCurrent));
help_adapter.Update(dataset.Tables["UserHistory"].Select("", "", DataViewRowState.ModifiedCurrent));
help_adapter.Update(dataset.Tables["UserHistory"].Select("", "", DataViewRowState.Deleted));
adapter.Update(dataset.Tables["UserInfo"].Select("", "", DataViewRowState.Deleted));
GetTotalValues();
DataGridView1.Visible = true;
DataGridView2.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show("Изменения в базе данных выполнить не удалось!\r\n" + ex.ToString(),
"Уведомление о результатах" , MessageBoxButtons.OK);
}
}
private void DataGridView2_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
try
{
DataGridViewRow row = DataGridView2.Rows[e.RowIndex];
if ((e.ColumnIndex == DataGridView2.Columns["Last_indication"].Index ||
e.ColumnIndex == DataGridView2.Columns["New_indication"].Index) &&
!string.IsNullOrEmpty(row.Cells["New_indication"].Value.ToString()))
{
row.Cells["Used_water"].Value = (int)row.Cells["New_indication"].Value -
(int)row.Cells["Last_indication"].Value;
if ((int)row.Cells["Used_water"].Value <= 100)
{
row.Cells["First_rate"].Value = (float)(int)row.Cells["Used_water"].Value * 0.004F;
row.Cells["Second_rate"].Value = 0;
row.Cells["Sum_rate"].Value = (decimal)row.Cells["First_rate"].Value;
}
else
{
row.Cells["First_rate"].Value = 100 * 0.004F;
row.Cells["Second_rate"].Value = (float)((int)row.Cells["Used_water"].Value - 100) * 0.003F;
row.Cells["Sum_rate"].Value = (decimal)row.Cells["First_rate"].Value +
(decimal)row.Cells["Second_rate"].Value;
}
}
if (!string.IsNullOrEmpty(row.Cells["New_indication"].Value.ToString()))
{
decimal rateSum = 0, depositSum = 0;
foreach (DataRow dataRow in dataset.Tables[1].Rows)
{
depositSum += (decimal)dataRow["Deposit"];
rateSum += (decimal)dataRow["Sum_rate"];
}
dataset.Tables[0].Rows[(int)DataGridView1.SelectedRows[0].Index]["Balance"] = depositSum - rateSum;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void ChangeButton_Click(object sender, EventArgs e)
{
if (DataGridView1.SelectedRows != null)
{
string str = DataGridView1.SelectedRows[0].Cells[0].Value.ToString();
dataset.Tables["UserHistory"].Clear();
help_adapter.SelectCommand = new MySqlCommand("SELECT * FROM User_history WHERE user_id = " + str, connect);
help_adapter.Fill(dataset, "UserHistory");
dataset.Tables[1].Columns["user_id"].DefaultValue = Convert.ToInt32(str);
DataGridView2.Visible = true;
DataGridView1.Visible = false;
}
}
private void FindTextBox_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == (char)Keys.Enter)
{
string findstr = FindTextBox.Text;
if (findstr == "")
dataset.Tables["UserInfo"].DefaultView.RowFilter = "";
else
dataset.Tables["UserInfo"].DefaultView.RowFilter = "Surname = \'" + findstr + "\'";
}
}
/***************************************************PARENT******************************************************/
private MySqlCommand CreateDataAdapterParentUpdateCommand()
{
string strSQL = "UPDATE Shedrost.User_info SET Surname = @Surname_New, Balance = @Balance_New " +
"WHERE user_id = @User_id_Orig;";
MySqlCommand cmd = new MySqlCommand(strSQL, connect);
MySqlParameterCollection paramColl = cmd.Parameters;
paramColl.Add("@Surname_New", MySqlDbType.VarChar, 40, "Surname");
paramColl.Add("@Balance_New", MySqlDbType.Decimal, 0, "Balance");
MySqlParameter param;
param = paramColl.Add("@User_id_Orig", MySqlDbType.Int16, 4, "user_id");
param.SourceVersion = DataRowVersion.Original;
return cmd;
}
private MySqlCommand CreateDataAdapterParentInsertCommand()
{
string strSQL = "INSERT INTO Shedrost.User_info VALUES(" +
"0, @Surname_New, @Balance_New);"+
"SELECT * FROM Shedrost.User_info WHERE Surname = @Surname_New AND Balance = @Balance_New";
MySqlCommand cmd = new MySqlCommand(strSQL, connect);
MySqlParameterCollection paramColl = cmd.Parameters;
paramColl.Add("@Surname_New", MySqlDbType.VarChar, 40, "Surname");
paramColl.Add("@Balance_New", MySqlDbType.Decimal, 0, "Balance");
return cmd;
}
private MySqlCommand CreateDataAdapterParentDeleteCommand()
{
string strSQL = "DELETE FROM Shedrost.User_info WHERE user_id = @user_id;";
MySqlCommand cmd = new MySqlCommand(strSQL, connect);
MySqlParameterCollection paramColl = cmd.Parameters;
MySqlParameter param;
param = paramColl.Add("@user_id", MySqlDbType.Int16, 4, "user_id");
param.SourceVersion = DataRowVersion.Original;
return cmd;
}
/***************************************************CHILD******************************************************/
private MySqlCommand CreateDataAdapterChildUpdateCommand()
{
string strSQL = "UPDATE Shedrost.User_history SET Deposit = @Deposit_New, Deposit_date = @Deposit_date_New, " +
"Last_indication = @Last_indication_New, New_indication = @New_indication_New, Used_water = @Used_water_New, " +
"First_rate = @First_rate_New, Second_rate = @Second_rate_New, Sum_rate = @Sum_rate_New WHERE " +
"history_id = @history_id_orig;";
MySqlCommand cmd = new MySqlCommand(strSQL, connect);
MySqlParameterCollection paramColl = cmd.Parameters;
paramColl.Add("@Deposit_New", MySqlDbType.Decimal, 0, "Deposit");
paramColl.Add("@Deposit_date_New", MySqlDbType.DateTime, 0, "Deposit_date");
paramColl.Add("@Last_indication_New", MySqlDbType.Int32, 4, "Last_indication");
paramColl.Add("@New_indication_New", MySqlDbType.Int32, 4, "New_indication");
paramColl.Add("@Used_water_New", MySqlDbType.Int32, 4, "Used_water");
paramColl.Add("@First_rate_New", MySqlDbType.Decimal, 0, "First_rate");
paramColl.Add("@Second_rate_New", MySqlDbType.Decimal, 0, "Second_rate");
paramColl.Add("@Sum_rate_New", MySqlDbType.Decimal, 0, "Sum_rate");
MySqlParameter param;
param = paramColl.Add("@history_id_orig", MySqlDbType.Int16, 4, "history_id");
param.SourceVersion = DataRowVersion.Original;
return cmd;
}
private MySqlCommand CreateDataAdapterChildInsertCommand()
{
string strSQL = "INSERT INTO Shedrost.User_history VALUES(0, @User_id_New, @Deposit_New, @Deposit_date_New, " +
"@Last_indication_New, @New_indication_New, @Used_water_New, @First_rate_New, @Second_rate_New, " +
"@Sum_rate_New);" +
"SELECT * FROM Shedrost.User_history WHERE Deposit = @Deposit_New AND Deposit_date = @Deposit_date_New AND " +
"Last_indication = @Last_indication_New AND New_indication = @New_indication_New AND Used_water = @Used_water_New AND " +
"First_rate = @First_rate_New AND Second_rate = @Second_rate_New AND Sum_rate = @Sum_rate_New;";
MySqlCommand cmd = new MySqlCommand(strSQL, connect);
MySqlParameterCollection paramColl = cmd.Parameters;
paramColl.Add("@User_id_New", MySqlDbType.Int32, 0, "user_id");
paramColl.Add("@Deposit_New", MySqlDbType.Decimal, 0, "Deposit");
paramColl.Add("@Deposit_date_New", MySqlDbType.DateTime, 0, "Deposit_date");
paramColl.Add("@Last_indication_New", MySqlDbType.Int32, 4, "Last_indication");
paramColl.Add("@New_indication_New", MySqlDbType.Int32, 4, "New_indication");
paramColl.Add("@Used_water_New", MySqlDbType.Int32, 4, "Used_water");
paramColl.Add("@First_rate_New", MySqlDbType.Decimal, 0, "First_rate");
paramColl.Add("@Second_rate_New", MySqlDbType.Decimal, 0, "Second_rate");
paramColl.Add("@Sum_rate_New", MySqlDbType.Decimal, 0, "Sum_rate");
return cmd;
}
private MySqlCommand CreateDataAdapterChildDeleteCommand()
{
string strSQL = "DELETE FROM Shedrost.User_history WHERE history_id = @history_id_orig;";
MySqlCommand cmd = new MySqlCommand(strSQL, connect);
MySqlParameterCollection paramColl = cmd.Parameters;
MySqlParameter param;
param = paramColl.Add("@history_id_orig", MySqlDbType.Int16, 4, "history_id");
param.SourceVersion = DataRowVersion.Original;
return cmd;
}
}
}
|
| Код |
DROP DATABASE IF EXISTS Shedrost;
CREATE DATABASE Shedrost;
USE Shedrost;
CREATE TABLE User_info(
user_id INT(4) NOT NULL AUTO_INCREMENT,
Surname VARCHAR(40) NOT NULL DEFAULT "",
Balance DECIMAL(6,3) DEFAULT 0,
PRIMARY KEY (user_id)) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE User_history (
history_id INT(4) NOT NULL AUTO_INCREMENT,
user_id INT(4) NOT NULL,
Deposit DECIMAL(6,3) DEFAULT 0,
Deposit_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Last_indication INT(4) DEFAULT 0,
New_indication INT(4) DEFAULT 0,
Used_water INT(4) DEFAULT 0,
First_rate DECIMAL(6,3) DEFAULT 0,
Second_rate DECIMAL(6,3) DEFAULT 0,
Sum_rate DECIMAL(6,3) DEFAULT 0,
PRIMARY KEY (history_id),
FOREIGN KEY fk (user_id) REFERENCES User_info(user_id)
ON UPDATE CASCADE
ON DELETE CASCADE ) ENGINE=InnoDB CHARACTER SET=UTF8;
|
НА форме имеються : 2 DataGridView, 2 Button, 1 TextBox, 4 Label; Имеются две связные таблицы в БД(смотри код выше). Создаю два DataGridView и связываю с таблицами. Работающее приложение заносит в БД все, кроме значения ячейки Balance(это значение я выщитываю в обработчике DataGridView2_CellEndEdit). НО во время отладки значение ячейки Balance заноситься НЕнормально, го заноситься. В DataGridView2_CellEndEdit все считается отлично, но когда нажимаю сохранить, то в Balance в БД появляется только вычисления, связанные с первой строкой(во время редактирования - только со строкой редактирования). Помогите. Неделю мучаюсь. Продебажьте пожалуйста. Это сообщение отредактировал(а) andDavid - 7.3.2011, 17:13 |
Не заноситься значение ячейки 
Загрузка ...
