C#连接mysql 数据库

最新推荐文章于 2024-07-20 15:50:39 发布

HelloWorldww

最新推荐文章于 2024-07-20 15:50:39 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql .net 文章标签： mysql c# string exception sql database

本文链接：https://blog.youkuaiyun.com/HelloWorldww/article/details/7530958

.net 同时被 2 个专栏收录

10 篇文章

订阅专栏

mysql

1 篇文章

订阅专栏

本文展示了如何使用C#连接和操作MySQL数据库，包括建立数据库连接、执行SQL命令（插入、更新、删除、查询）以及处理异常。提供了用于执行非查询和查询操作的方法，并给出了插入数据的具体示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public class SqlHelpe
{

        /// <summary>
        /// 建立mysql数据库链接
        /// </summary>
        /// <returns></returns>
        public static MySqlConnection getMySqlCon()
        {
           // string conn = "Data Source=localhost;User ID=root;Password=123;DataBase=googlesearch;Charset=gbk;";
            string conn = "Data Source=192.168.10.80;User ID=admin;Password=admin123;DataBase=googlesearch;Charset=gbk;";

            MySqlConnection con = new MySqlConnection(conn);

            con.Open();
            return con;

}

        /// <summary>
        /// 建立执行命令语句对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="mysql"></param>
        /// <returns></returns>
        public static MySqlCommand getSqlCommand(String sql, MySqlConnection mysql)
        {
            MySqlCommand cmd = new MySqlCommand(sql, mysql);
            return cmd;
        }

        public static int ExecuteNonQuery(string sql, MySqlParameter[] pars)
        {
            MySqlConnection conn = getMySqlCon();
            MySqlCommand comm = new MySqlCommand(sql, conn);
            if (pars != null)
            {
                foreach (MySqlParameter p in pars)
                    comm.Parameters.AddWithValue(p.ParameterName, p.Value);
            }

            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }

        public static object ExecuteScalar(string sql, MySqlParameter[] pars)
        {
            MySqlConnection conn = getMySqlCon();

            MySqlCommand comm = new MySqlCommand(sql, conn);
            if (pars != null)
            {
                foreach (MySqlParameter p in pars)
                    comm.Parameters.AddWithValue(p.ParameterName, p.Value);
            }

            object o = comm.ExecuteScalar();

            conn.Close();
            return o;
        }

       /// <summary>
       /// 执行SQL语句，返回影响的记录数
        /// </summary>
       /// <param name="sqlString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string sql)
        {
            using (MySqlConnection conn = getMySqlCon())
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    try
                    {
                        //conn.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        conn.Close();
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        cmd.Dispose(); conn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 查询并获得结果集并遍历
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public static void getResultset(MySqlCommand mySqlCommand)
        {
            MySqlDataReader reader = mySqlCommand.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        Console.WriteLine("编号:" + reader.GetInt32(0) + "|姓名:" + reader.GetString(1) + "|年龄:" + reader.GetInt32(2) + "|学历:" + reader.GetString(3));
                    }
                }
            }
            catch (Exception)
            {

                Console.WriteLine("查询失败了！");
            }
            finally
            {
                reader.Close();
            }
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public static void getInsert(MySqlCommand mySqlCommand)
        {
            try
            {
                mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                String message = ex.Message;
                Console.WriteLine("插入数据失败了！" + message);
            }

        }
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public static void getUpdate(MySqlCommand mySqlCommand)
        {
            try
            {
                mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                String message = ex.Message;
                Console.WriteLine("修改数据失败了！" + message);
            }
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public static void getDel(MySqlCommand mySqlCommand)
        {
            try
            {
                mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                String message = ex.Message;
                Console.WriteLine("删除数据失败了！" + message);
            }
        }

}

public static object InsertKSOthers(string Kname, string Kvicinity, string Ktype, string Kformatted_phone_number, string Kformatted_address, string Klat, string Klng, string Kurl, string Kicon, string Kreference, string Kid, string Kinternational_phone_number, string Kwebsite)
        {
            string sql = @"insert into KSOthers(Kname,Kvicinity,Ktype,Kformatted_phone_number,Kformatted_address,Klat,Klng,Kurl,Kicon,Kreference,Kid,Kinternational_phone_number,Kwebsite)
                              values(?Kname,?Kvicinity,?Ktype,?Kformatted_phone_number,?Kformatted_address,?Klat,?Klng,?Kurl,?Kicon,?Kreference,?Kid,?Kinternational_phone_number,?Kwebsite)";
            MySqlParameter[] pars = new MySqlParameter[]
           {
               new MySqlParameter("Kname",Kname),
               new MySqlParameter("Kvicinity",Kvicinity),
               new MySqlParameter("Ktype",Ktype),
               new MySqlParameter("Kformatted_phone_number",Kformatted_phone_number),
               new MySqlParameter("Kformatted_address",Kformatted_address),
               new MySqlParameter("Klat",Klat),
               new MySqlParameter("Klng",Klng),
               new MySqlParameter("Kurl",Kurl),
               new MySqlParameter("Kicon",Kicon),
               new MySqlParameter("Kreference",Kreference),
               new MySqlParameter("Kid",Kid),
               new MySqlParameter("Kinternational_phone_number",Kinternational_phone_number),
               new MySqlParameter("Kwebsite",Kwebsite)

           };
            //返回受影响行数
            //return DAL.ExecuteNonQuery(sql, pars);
            return SqlHelpe.ExecuteNonQuery(sql, pars);

}