1、类库

一、

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Text;

namespace DbHelper
{
    public sealed class MySqlHelper
    {
        //数据库连接字符串

        public static string Conn = "Data Source=120.79.21.96;User ID=Test;Password=Test;Database=Test;Allow User Variables=True;Charset=utf8;";

        //ExecuteNonQuery 主要用在插入,更新,删除 一般情况用在查询的时候返回的是-1 
        //ExecuteScalar 返回的是 查询结果的一个第一行第一列的值 
        //MySqlDataReader 
        //MySqlDataAdapter 数据集

        /// <summary>
        /// 插入,更新,删除主要返回影响的行数-影响的结果
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sqlStr, params MySqlParameter[] paras)
        {
            using (MySqlConnection conn = new MySqlConnection(Conn))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
                foreach (MySqlParameter p in paras)
                {
                    cmd.Parameters.Add(p);
                }
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        public static int ExecuteNonQueryId(string sqlStr, params MySqlParameter[] paras)
        {
            using (MySqlConnection conn = new MySqlConnection(Conn))
            {
                conn.Open();//ExecuteNonQuery 必须加
                MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
                foreach (MySqlParameter p in paras)
                {
                    cmd.Parameters.Add(p);
                }
                int val = cmd.ExecuteNonQuery();
                //获取插入后的数据ID
                long newid = cmd.LastInsertedId;  //插入仅可以修改
                cmd.Parameters.Clear();
                return Convert.ToInt32(newid);
            }
        }

        /// <summary>
        /// sql的时候可以查询 insert into TEST(VAL1) values('AAAAAAAAAAAAAA')  select @@identity as Id   --;可选
        /// 或者只想结果集第一个
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlStr, params MySqlParameter[] paras)
        {
            using (MySqlConnection conn = new MySqlConnection(Conn))
            {
                conn.Open();//ExecuteScalar 必须加
                MySqlCommand cmd = new MySqlCommand(sqlStr, conn);//建立命令
                foreach (MySqlParameter p in paras)
                {
                    cmd.Parameters.Add(p);
                }
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        //查询数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="paras">参数可选</param>
        /// <returns></returns>
        public static DataSet GetDataDataSet(string sql, params MySqlParameter[] paras)
        {
            using (MySqlConnection conn = new MySqlConnection(Conn))
            {
                MySqlCommand command = new MySqlCommand(sql, conn);
                DataSet dt = new DataSet();//DataSet方便取Datatable
                MySqlDataAdapter sda = new MySqlDataAdapter(command);
                sda.SelectCommand.Parameters.AddRange(paras);
                sda.Fill(dt);
                return dt;
            }
        }


    }
}

使用方式：

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Ban.Models;
using DbHelper;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace Ban.Controllers
{
    public class HomeController : Controller
    {
        public IActionResult Index()
        {
            //查询无参数
            //DataSet ds = DbHelper.MySqlHelper.GetDataDataSet("select * from Account");

            //有参数
            //MySqlParameter[] paras = new MySqlParameter[1];
            //paras[0] = new MySqlParameter("@Acco_ID", MySqlDbType.Int32);
            //paras[0].Value = 7;
            //DataSet ds1 = DbHelper.MySqlHelper.GetDataDataSet("select * from Account where Acco_ID=@Acco_ID", paras);

            //插入
            //string sql = "INSERT INTO `Account` (`Acco_ID`, `Acco_Name`, `Acco_NickName`, `Acco_PASSWORD`, `Acco_Sex`, `Acco_Birthday`, `Acco_Job`, `Acco_Email`, `Acco_Status`, `Acco_Remark`, `Acco_Avatar`, `Acco_Phone`, `Acco_IsValid`, `Acco_Created`, `images`) VALUES (NULL, '测试2', '测试2昵称', '测试2密码', NULL, NULL, NULL, '', '', '', '', NULL, NULL, CURRENT_TIMESTAMP, NULL);";
            //int result=DbHelper.MySqlHelper.ExecuteNonQuery(sql);

            //插入可带参数
            ////MySqlParameter[] paras = new MySqlParameter[1];
            ////paras[0] = new MySqlParameter("@Acco_Name", MySqlDbType.VarChar);
            ////paras[0].Value = "测试3";
            ////string sql = "INSERT INTO `Account` (`Acco_ID`, `Acco_Name`, `Acco_NickName`, `Acco_PASSWORD`, `Acco_Sex`, `Acco_Birthday`, `Acco_Job`, `Acco_Email`, `Acco_Status`, `Acco_Remark`, `Acco_Avatar`, `Acco_Phone`, `Acco_IsValid`, `Acco_Created`, `images`) VALUES (NULL, @Acco_Name, '测试2昵称', '测试2密码', NULL, NULL, NULL, '', '', '', '', NULL, NULL, CURRENT_TIMESTAMP, NULL);";
            ////int result = DbHelper.MySqlHelper.ExecuteNonQuery(sql,paras);

            string sql = "INSERT INTO `Account` (`Acco_ID`, `Acco_Name`, `Acco_NickName`, `Acco_PASSWORD`, `Acco_Sex`, `Acco_Birthday`, `Acco_Job`, `Acco_Email`, `Acco_Status`, `Acco_Remark`, `Acco_Avatar`, `Acco_Phone`, `Acco_IsValid`, `Acco_Created`, `images`) VALUES (NULL, '测试6', '测试2昵称', '测试2密码', NULL, NULL, NULL, '', '', '', '', NULL, NULL, CURRENT_TIMESTAMP, NULL);select @@identity as Id";
            var result=DbHelper.MySqlHelper.ExecuteScalar(sql);

            return View();
        }

        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}