22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT Product.编号, Product.型号, Product.建议售价, Product.适用机型, Product.质量, Product.出厂价, Product.分类, Product.备注, Sum(Storage.数量) AS 库存总数
FROM Storage LEFT JOIN Product ON Storage.[产品编号]=Product.编号
GROUP BY Product.编号, Product.型号, Product.适用机型, Product.出厂价, Product.分类, Product.建议售价, Product.备注, Product.质量, Storage.[产品编号];
StandardCommands commands = new StandardCommands();
CommandDict.Add("productstorage", commands);
OleDbCommand productInsertCommand = new OleDbCommand();
productInsertCommand.CommandText = "INSERT INTO product (型号, 适用机型, 出厂价,质量, 分类, 建议售价, 备注) VALUES (?, ?,?, ?, ?, ?, ?)";
productInsertCommand.CommandType = CommandType.Text;
DataBaseManager.AddInputParamToCommand(productInsertCommand, "型号", OleDbType.VarWChar);
DataBaseManager.AddInputParamToCommand(productInsertCommand, "适用机型", OleDbType.VarWChar);
DataBaseManager.AddInputParamToCommand(productInsertCommand, "出厂价", OleDbType.Single);
DataBaseManager.AddInputParamToCommand(productInsertCommand, "质量", OleDbType.Single);
DataBaseManager.AddInputParamToCommand(productInsertCommand, "分类", OleDbType.VarWChar);
DataBaseManager.AddInputParamToCommand(productInsertCommand, "建议售价", OleDbType.Single);
DataBaseManager.AddInputParamToCommand(productInsertCommand, "备注", OleDbType.VarWChar);
commands.InsertCommands.Add(productInsertCommand);
OleDbCommand storageInsertCommand = new OleDbCommand();
storageInsertCommand.CommandText = "INSERT INTO storage (产品编号, 仓库编号, 数量) select max(编号),0,? from product";
storageInsertCommand.CommandType = CommandType.Text;
DataBaseManager.AddInputParamToCommand(storageInsertCommand, "库存总数", OleDbType.Integer);
commands.InsertCommands.Add(storageInsertCommand);
OleDbCommand productUpdateCommand = new OleDbCommand();
productUpdateCommand.CommandText = @"UPDATE `Product` SET `型号` = ?, `适用机型` = ?, `出厂价` = ?, 质量 = ?, `分类` = ?, `建议售价` = ?, `备注` = ? WHERE ((`编号` = ?));";
productUpdateCommand.CommandType = CommandType.Text;
DataBaseManager.AddInputParamToCommand(productUpdateCommand, "型号", OleDbType.VarWChar, "型号");
DataBaseManager.AddInputParamToCommand(productUpdateCommand, "适用机型", OleDbType.VarWChar, "适用机型");
DataBaseManager.AddInputParamToCommand(productUpdateCommand, "出厂价", OleDbType.Single, "出厂价");
DataBaseManager.AddInputParamToCommand(productUpdateCommand, "质量", OleDbType.Single);
DataBaseManager.AddInputParamToCommand(productUpdateCommand, "分类", OleDbType.VarWChar, "分类");
DataBaseManager.AddInputParamToCommand(productUpdateCommand, "建议售价", OleDbType.Single, "建议售价");
DataBaseManager.AddInputParamToCommand(productUpdateCommand, "备注", OleDbType.VarWChar, "备注");
DataBaseManager.AddInputParamToCommand(productUpdateCommand, "编号", OleDbType.Integer, "Original_编号");
commands.UpdateCommands.Add(productUpdateCommand);
OleDbCommand storageUpdateCommand = new OleDbCommand();
storageUpdateCommand.CommandText = "Update storage Set 数量 = ? where 产品编号 = ? and 仓库编号 = 0";
storageUpdateCommand.CommandType = CommandType.Text;
DataBaseManager.AddInputParamToCommand(storageUpdateCommand, "库存总数", OleDbType.Integer);
DataBaseManager.AddInputParamToCommand(storageUpdateCommand, "编号", OleDbType.Integer);
commands.UpdateCommands.Add(storageUpdateCommand);
OleDbCommand deleteCommand = new OleDbCommand();
deleteCommand.CommandText = "Delete from product where 编号 = ?"; // 由于已经有了级联删除,这里不需要其他命令
deleteCommand.CommandType = CommandType.Text;
DataBaseManager.AddInputParamToCommand(deleteCommand, "编号", OleDbType.Integer);
commands.DeleteCommands.Add(deleteCommand);