c# - 使用CSharp三层架构MVC将数据插入mysql数据库(MysqlParameter插入空值)
问题描述
大家好,我的cities
表有 4 列cityId
是“AUTO_INCREMENT”,,,,cityName
是governate
表的conId
外键引用countries
它们是用于将数据插入此表的存储过程last_insert_id()
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCity`(in cityNamee varchar(45),bGovernate varchar(45),cconId int(5),out ccityId int(5))BEGIN insert into cities(`cityName`,`governate`,`conId`)values (@cityNamee,@bGovernate,@cconId);set @ccityId=last_insert_id();END
sp 是从业务层(控制器)调用的,并将 throw 传递给模型,然后执行这里的过程是模型传递给 insert、update、delete 的命令
public void Transfare(string _chanPoro, MySqlParameter[] _dataContainer)
{
MySqlCommand transmeter = new MySqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = _chanPoro,
Connection = dbcon
};
transmeter.Parameters.AddRange(_dataContainer);
connect();
transmeter.ExecuteNonQuery();
disConnect();
}
我尝试使用插入数据,MySqlParameter
但它总是失败并给我一个错误
public static void insertCity(string cityName, string governate, int countryId)
{
try
{
_socket = new _ctrlChannel();
MySqlParameter[] zair = new MySqlParameter[4];
zair[0] = new MySqlParameter("@cityNamee", MySqlDbType.VarChar, 45)
{ Value = cityName, Direction = ParameterDirection.Input
};
zair[1] = new MySqlParameter("@bGovernate", MySqlDbType.VarChar)
{ Value = governate, Direction = ParameterDirection.Input
};
zair[2] = new MySqlParameter("@cconId", MySqlDbType.VarChar)
{ Value = countryId, Direction = ParameterDirection.Input
};
zair[3] = new MySqlParameter("@ccityId", MySqlDbType.Int32,5)
{ Direction = ParameterDirection.Output
};
_socket.connect();
_socket.Transfare("addCity", zair);
_socket.disConnect();
}
catch (MySql.Data.MySqlClient.MySqlException we) { MessageBox.Show("error" + we); }
}
cityName
未在集合中找到
我尝试使用这种方法插入数据的传统方式,这里的主要问题是将空值插入数据库(不起作用),而表设计不支持
public static void insertNewCity(string cityName, string governate, int countryId)
{
try
{
_socket = new _ctrlChannel();
_ctrlChannel.InitializeDb();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = _ctrlChannel.dbcon;
cmd.CommandText = "addCity";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cityNamee", cityName);
cmd.Parameters["@cityNamee"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@bGovernate", governate);
cmd.Parameters["@bGovernate"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@cconId", countryId);
cmd.Parameters["@cconId"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@ccityId", MySqlDbType.Int32);
cmd.Parameters["@ccityId"].Direction = ParameterDirection.Output;
_socket.connect();
cmd.ExecuteNonQuery();
_socket.disConnect();
}
catch (MySql.Data.MySqlClient.MySqlException we) { MessageBox.Show("error" + we); }
}
注意:当我在服务器上测试它时,该程序是完整的
就这些了 非常感谢...
解决方案
确保有 getter 和 setter 将视图中的值保存到业务层,然后业务层交付以dataAccessLayer
将其插入数据库
string _cityName;
string _governate;
int _countryId;
private string cityName { get; set; }
private string governate { get; set; }
private int countryId { get; set; }
public void prepare()
{
_cityName = cityName;
_governate = governate;
_countryId = countryId;
}
public void insertCity( string _cityName ,string _governate, int _countryId)
{
try
{
_socket = new _ctrlChannel();
MySqlParameter[] zair = new MySqlParameter[4];
zair[0] = new MySqlParameter("@cityNamee", MySqlDbType.VarChar, 45)
{ Value = _cityName, Direction = ParameterDirection.Input
};
zair[1] = new MySqlParameter("@bGovernate", MySqlDbType.VarChar)
{ Value = _governate, Direction = ParameterDirection.Input
};
zair[2] = new MySqlParameter("@cconId", MySqlDbType.VarChar)
{ Value = _countryId, Direction = ParameterDirection.Input
};
zair[3] = new MySqlParameter("@ccityId", MySqlDbType.Int32,5)
{ Direction = ParameterDirection.Output,Value=DBNull.Value
};
_socket.connect();
_socket.Transfare("addCity", zair);
_socket.disConnect();
}
catch (MySql.Data.MySqlClient.MySqlException we) { MessageBox.Show("error" + we); }
}
推荐阅读
- c# - 有没有一种方法可以通过使用反射来获取使用字符串的方法来访问不同脚本中方法的局部变量?
- netsuite - NetSuite Advanced PDF - 使用记录中的多选字段过滤项目表列表
- http - 拦截**传出** HTTP(S) 流量
- kotlin - 如何为 Kotlin 1.4.20 增加 Kotlin Daemon JVM 堆?
- https - 如何拒绝 http 端口(80)Apache 上的 https 请求?
- log4j2 - Log4j2 JsonTemplateLayout 问题
- python - 在python中将视频转换为音频
- r - 将人类可读的连接表读取到 tibble
- permissions - colab中的权限被拒绝
- java - Spring:“获取 org.hibernate.HibernateException:使用不同的 postgresql 用户时无法访问 lob 流”