C#连接mysql 数据库

本文展示了如何使用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);

        }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值