使用WebAPI直接对MySQL数据库进行增删改查操作,也是部署中的重要一步。
<1>安装mysql数据库,本人是直接下载的免安装包,通过cmd控制台进入解压路径下,执行
mysqld --install
接下来启动mysql服务
net start mysql
<2>在Modles文件夹中新建一个类DUser,名字随便起,内容如下
public class DUser
{
public int UserID { get; set; }
public string UserName { get; set; }
public string UserEmail { get; set; }
}
<3>编写连接服务器的WebAPI代码,在Controllers文件夹下新建脚本ConnectMySQLController脚本,内容如下
public class ConnectMySQLController : ApiController
{
// GET api/values
//SQL语句中的user是表的名字
public IEnumerable<DUser> Get()
{
List<DUser> listUser = new List<DUser>();
MySqlConnection mysql = getMySqlConnection();
MySqlCommand mySqlCommand = getSqlCommand("select * from user",mysql);
mysql.Open();
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
if (reader.HasRows)
{
DUser user = new DUser();
user.UserID = reader.GetInt32("ID");
user.UserName = reader.GetString("username");
user.UserEmail = reader.GetString("useremail");
listUser.Add(user);
}
}
}
catch
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
finally
{
mysql.Close();
}
return listUser;
}
// GET api/DUser/5
public DUser GetUserByID(int id)
{
DUser user = new DUser();
MySqlConnection mysql = getMySqlConnection();
MySqlCommand mySqlCommand = getSqlCommand("select * from user where ID=" + id, mysql);
mysql.Open();
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
if (reader.HasRows)
{
user.UserID = reader.GetInt32(0);
user.UserName = reader.GetString(1);
user.UserEmail = reader.GetString(2);
}
}
reader.Close();
}
catch
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
finally
{
reader.Close();
}
return user;
}
//GET api/DUser/?username=xx
public IEnumerable<DUser> GetUserByName(string userName)
{
List<DUser> listuser = new List<DUser>();
MySqlConnection msql = getMySqlConnection();
MySqlCommand mySqlCommand = getSqlCommand("select * from user where username like'%" + userName + "%'", msql);
msql.Open();
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
if (reader.HasRows)
{
DUser user = new DUser();
user.UserID = reader.GetInt32("ID");
user.UserName = reader.GetString("USERNAME");
user.UserEmail = reader.GetString("USEREMAIL");
listuser.Add(user);
}
}
reader.Close();
}
catch
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
finally
{
reader.Close();
}
return listuser;
}
// POST api/values
public void Post([FromBody]string value)
{
}
// PUT api/values/5
public void Put(int id, [FromBody]string value)
{
}
// DELETE api/values/5
public void Delete(int id)
{
}
private static MySqlConnection getMySqlConnection()
{
//Web.config文件配置中的name与["MySqlConnection"]一致,后面会讲到
MySqlConnection mysql = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString);
return mysql;
}
public static MySqlCommand getSqlCommand(String sql, MySqlConnection mysql)
{
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysql);
return mySqlCommand;
}
}
<4>找到解决方案下的Web.config文件,打开,添加如下面的语句
<connectionStrings>
<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-IndasBimApi-20200417185648;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-IndasBimApi-20200417185648.mdf" />
<add name="MySqlConnection" (代码中有讲,这个名字要与ConnectMySQLController中的连接名一致)providerName="System.Data.SqlClient" connectionString="server=127.0.0.1;User Id=数据库登录帐户名;password=数据库密码;persistsecurityinfo=True;Database=数据库名称;charset=utf8;" />
</connectionStrings>
<5>往数据库里加数据,使用可视化工具Navicat for Mysql比较方便,添加后user表中的数据如下
<6>进入测试环节,vs点击运行,通过Postman来访问get接口数据
非常尴尬,Http Error 400,此时不要慌,返回vs查看错误提示,内容如下
Authentication method 'caching_sha2_password' not supported by any of the av
原因很清楚了,mysql8.0以上的版本的密码认证方式已经不是mysql_native_password ,需要配置成caching_sha2_password
配置方式:在mysql解压路径下,新建一个文件my.ini,通过编辑器打开,填充以下内容
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\mySetup\mysql-8.0.19-winx64
# 切记此处一定要用双斜杠\\,单斜杠这里会出错。
# 设置mysql数据库的数据的存放目录
datadir=D:\mySetup\mysql-8.0.19-winx64\\Data
# 此处同上
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
里面的安装路径,是要填写自己的安装路径的,这个需要自己修改好然后保存
<7>重启mysql服务,这个非常简单,无需多言
<8>再次启动vs调试,通过mysql来访问接口
成功访问到数据库里的数据,其他接口均可自行测试,今天的任务完成