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