Winform上位机开发--数据库操作

Winform上位机开发–数据库

一、开篇

使用C#开发Winform上位机效率是业界公认的高，上位机一般少不了使用数据库。经过多年的Java开发，已经习惯了Mysql数据库而很少再使用Sqlserver了。

跟web开发一样，数据库操作也使用三层架构：实体层，业务层和展示层。实体层和业务层很容易通过代码生成器来实现，展示层则需要根据业务来写sql，但多数还是CRUD操作。

二、MysqlHelper类

把数据库常用的插入、删除和查询等封装成一个MysqlHelper类，由业务层来调用，代码如下：

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

namespace ***.BAL
{
    /// <summary>
    /// 
    /// </summary>
    public class MysqlTools
    {       
        
        private string sqlcon="server=192.168.0.1;User Id = root; password=admin;Database=***;Charset=utf8";
        private Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

        public MysqlTools()
        {
            
        }

        /// <summary>
        ///  函数里通过配置文件配置数据库链接
        /// </summary>
        /// <param name="type">wdm/fbt</param>
        public MysqlTools(string type) {
                sqlcon = config.AppSettings.Settings["connString"].Value;
        }
        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int insert(string sql, MySqlParameter[] param)
        {

            //Console.WriteLine(string.Format(">>>>>>>>>>>insert sql={0}", sql));
            int res = 0;
            using (MySqlConnection conn = new MySqlConnection(sqlcon))
            {
                MySqlCommand cmd = new MySqlCommand();
                MySqlTransaction transaction = null;
                try
                {
                    cmd.Connection = conn;
                    foreach (var item in param)
                    {
                        cmd.Parameters.Add(item);
                        Console.WriteLine(string.Format(">>>>>>>>>>>insert  key={0:G}, value={1:G}", item.ParameterName, item.Value));
                    }
                    cmd.CommandText = sql;

                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;

                    res = cmd.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch (MySqlException ex) {
                    //事务回滚
                    transaction.Rollback();
                    Console.WriteLine(string.Format("MySqlException={0}", ex.Message));
                }
                catch (Exception ex)
                {
                    Console.WriteLine(string.Format("insert exception={0}", ex.Message));
                }

            }

            return res;
        }

        /// <summary>
        /// 执行Sql语句,写入操作使用事务
        /// 编辑和删除可通过这个函数来执行
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int execSql(string sql, MySqlParameter[] param)
        {
            //LogisTrac.WriteInfo("执行Sql语句,写入", String.Format("保存, IP址址={0}", sqlcon));
            int res = 0;
            using (MySqlConnection conn = new MySqlConnection(sqlcon))
            {
                MySqlCommand cmd = new MySqlCommand();
                MySqlTransaction transaction = null;
                try
                {
                    cmd.Connection = conn;
                    if(param != null)
                    foreach (var item in param)
                    {
                        cmd.Parameters.Add(item);
                    }
                    cmd.CommandText = sql;

                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;

                    res = cmd.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    //事务回滚
                    if(transaction != null)
                        transaction.Rollback();
                    Console.WriteLine(ex.Message);
                }

            }

            return res;
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable getDataTable(string sql, MySqlParameter[] param)
        {
            Console.WriteLine(string.Format(">>>>>>>>>>>getDataTable  sql={0}", sql));
            
            DataTable dt = new DataTable();
            try
            {
                using (MySqlConnection conn = new MySqlConnection(sqlcon))
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    if (param != null)
                        foreach (var item in param)
                        {
                            cmd.Parameters.Add(item);
                            Console.WriteLine(string.Format(">>>>>>>>>>>execSql  key={0:G}, value={1:G}", item.ParameterName, item.Value));

                        }
                    conn.Open();
                    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);

                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    dt = ds.Tables[0];
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(string.Format("MySqlException={0}", ex.Message));
            }
            catch (Exception ex)
            {
            }
            return dt;
        }
        /// <summary>
        /// 查询数量
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int getCount(string sql, MySqlParameter[] param)
        {
            Console.WriteLine(string.Format(">>>>>>>>>>>getCount = {0}", sql));
            int count = 0;
            try
            {
                using (MySqlConnection conn = new MySqlConnection(sqlcon))
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    if (param != null)
                        foreach (var item in param)
                        {
                            cmd.Parameters.Add(item);
                            Console.WriteLine(string.Format(">>>>>>>>>>>execSql  key={0:G}, value={1:G}", item.ParameterName, item.Value));
                        }
                    conn.Open();
                    count = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Dispose();
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(string.Format("MySqlException={0}", ex.Message));
            }
            catch (Exception ex)
            {
                Console.WriteLine(string.Format("getCount exception={0}", ex.Message));
            }

            return count;
        }
    }
}

三、函数说明

由于查询了来的数据一般给DataGridView显示使用，而DataGridView可以通过Source属性直接赋值DataTable类型，所以定义了getDataTable函数返回DataTable类型表格。

插入、删除等数据库更新操作使用数据库事务处理，出现异常回滚。在开发调试阶段最好加上日志，有问题可远程通过日志来排查相应问题。日志会在本系列后面讲到。