以前做易语言/PHP的. 最近刚入门C#, 就简单的封装了一个类库, 边学边玩才容易学到东西嘛, 比起sqlserver, 我还是觉得mysql更加有亲切感;
于是模仿ThinkPHP编写了一个”MySql”数据库操作类.
东西做的比较简单, 学习过程中也有大佬指导了一些问题. 在此非常感谢
这个类库应该来说还有一些问题需要解决, 异常捕捉什么的. 不懂怎么弄. 欢迎热心大佬们指正调教
此类库用到了 MySql.Data.dll
- 下载mysql.data.dll
- 下载完整项目
数据表
DROP TABLE IF EXISTS `table_user`;
CREATE TABLE `table_user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
PRIMARY KEY (`userid`)
)内裤(类库)代码
using System;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Data;
namespace DbModel
{
public class MySQL
{
protected string m_table, m_where, m_field = "*", m_limit, m_group, m_order, m_having, m_alias, m_prefix, m_comment, m_distinct;
protected bool m_fetchSql = false;
public string LastSql = ""; //最后生成的SQL
protected ArrayList m_join = new ArrayList(), m_union = new ArrayList();
protected Hashtable m_data = new Hashtable();
public Int64 AffectCount { get; protected set; }
public Int64 RecordCount { get; protected set; }
public Int64 FieldCount { get; protected set; }
public string ConnectionString;
public MySqlConnection Connection;
public MySqlCommand Command;
public string Exception;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="table"></param>
/// <param name="prefix"></param>
public MySQL(string table, string prefix = "")
{
this.m_table = table;
this.m_prefix = prefix;
}
/// <summary>
/// 置表名
/// </summary>
/// <param name="table"></param>
/// <param name="alias">default null</param>
/// <returns>DbModel</returns>
public MySQL Table(String table, String alias = null)
{
this.m_table = table;
if (alias != null)
{
this.m_alias = " AS "+alias;
}
return this;
}
/// <summary>
/// 置选取字段
/// </summary>
/// <param name="field"></param>
/// <returns>DbModel</returns>
public MySQL Field(String field = "*")
{
this.m_field = field;
return this;
}
/// <summary>
/// 设置表别名
/// </summary>
/// <param name="alias"></param>
/// <returns></returns>
public MySQL Alias(String alias)
{
this.m_alias = " AS " + alias;
return this;
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public MySQL Where(String where)
{
this.m_where = where;
return this;
}
/// <summary>
/// 排序方法
/// </summary>
/// <param name="order">如 user_id [desc/asc]</param>
/// <returns></returns>
public MySQL Order(String order)
{
this.m_order = " ORDER BY " + order;
return this;
}
/// <summary>
/// 设置表名前缀， 对所有表有效。如果前缀是“table_”那么需要加前缀的表前后加两个下划线“__user__”，就会得到“table_user”
/// </summary>
/// <param name="prefix"></param>
/// <returns></returns>
public MySQL Prefix(String prefix)
{
this.m_prefix = prefix;
return this;
}
/// <summary>
/// 选取部分数据
/// </summary>
/// <param name="start">开始位置，默认为第一条, 如果留空参数2则直接"LIMIT start"</param>
/// <param name="num">选取数量， 默认为0</param>
/// <returns></returns>
public MySQL Limit(int start = 0, int num = 0)
{
if (num == 0)
{
this.m_limit = " LIMIT " + Convert.ToString(start);
}
else
{
this.m_limit = " LIMIT " + Convert.ToString(start) + ", " + Convert.ToString(num);
}
return this;
}
/// <summary>
/// 分页，本方法等同limit
/// </summary>
/// <param name="page">第N页</param>
/// <param name="showNum">每页显示数量</param>
/// <returns></returns>
public MySQL Page(int page = 1, int showNum = 1000)
{
this.Limit((page-1) * showNum, showNum);
return this;
}
/// <summary>
/// 分组条件（相当于where），配合group使用（group by）
/// </summary>
/// <param name="having"></param>
/// <returns></returns>
public MySQL Having(String having)
{
this.m_having = " HAVING " + having;
return this;
}
/// <summary>
/// 分组
/// </summary>
/// <param name="group"></param>
/// <returns></returns>
public MySQL Group(String group)
{
this.m_group = " GROUP BY " + group;
return this;
}
/// <summary>
/// 连接多条SQL(多条SQL查询结果合并)
/// </summary>
/// <param name="sql"></param>
/// <param name="all">是否保留全部, 默认false (union / union all)</param>
/// <param name="clear">是否清空</param>
/// <returns></returns>
public MySQL Union(string sql, bool all = false, bool clear = false)
{
if (clear)
{
this.m_union.Clear();
}
this.m_union.Add((all == true ? "union all " : "union ") + sql);
return this;
}
/// <summary>
/// 关联多个表
/// </summary>
/// <param name="join">例：LEFT JOIN __user__ on 1 = 1</param>
/// <param name="clear">是否清除原先的join</param>
/// <returns></returns>
public MySQL Join(String join, bool clear = false)
{
if (clear)
{
this.m_join.Clear();
}
this.m_join.Add(join);
return this;
}
/// <summary>
/// 注释
/// </summary>
/// <param name="text"></param>
/// <returns></returns>
public MySQL Comment(string text)
{
this.m_comment = " /* " + text + " */ ";
return this;
}
/// <summary>
/// 唯一
/// </summary>
/// <param name="distinct"></param>
/// <returns></returns>
public MySQL Distinct(bool distinct)
{
this.m_distinct = (distinct == false ? "" : "DISTINCT ");
return this;
}
/// <summary>
/// 获取SQL, 如果为True. Select/Add/Save/Del 将返回SQL语句, 并且不执行
/// </summary>
/// <param name="fetch"></param>
/// <returns></returns>
public MySQL FetchSql(bool fetch)
{
this.m_fetchSql = fetch;
return this;
}
/// <summary>
/// 绑定字段
/// </summary>
/// <param name="field"></param>
/// <param name="value"></param>
public MySQL Data(string field, object value)
{
string temp = "";
if (value == null)
{
temp = "null";
}
else
{
switch (value.GetType().Name)
{
case "String":
temp = "'" + this.FilterSQL(value.ToString()) + "'";
break;
case "DateTime":
temp = "'" + value.ToString() + "'";
break;
default:
temp = value.ToString();
break;
}
}
//如果存在:删除
if (this.m_data.ContainsKey(field))
{
this.m_data.Remove(field);
this.m_data.Add(field, temp);
return this;
}
//新增
this.m_data.Add(field, temp);
return this;
}
/// <summary>
/// 过滤SQL
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
private string FilterSQL(string value)
{
value.Replace("/", "\\/").Replace("\"", "\\\"").Replace("'", "\\'");
return value;
}
/// <summary>
/// 重置所有字段信息
/// </summary>
protected void Clear()
{
this.m_field = "*";
this.m_join.Clear();
this.m_union.Clear();
this.m_data.Clear();
this.m_group = this.m_having = this.m_order = this.m_where = this.m_distinct = this.m_order = this.m_limit = this.LastSql = this.m_comment = "";
}
/// <summary>
/// 打开连接(连接数据库)
/// </summary>
/// <param name="ConnectionString"></param>
/// <returns></returns>
public bool Open(string ConnectionString=null)
{
if (ConnectionString != null)
{
this.ConnectionString = ConnectionString;
}
Connection = new MySqlConnection(this.ConnectionString);
try
{
this.Connection.Open();
this.Command = this.Connection.CreateCommand();
return true;
}
catch(MySqlException e)
{
this.Exception = e.ToString();
return false;
}
}
/// <summary>
/// 关闭连接
/// </summary>
public void Close()
{
this.Connection.Close();
}
/// <summary>
/// 执行SQL （只能执行 insert、update、delete），
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool Execute(string sql = "")
{
this.Command.CommandText = sql;
this.AffectCount = this.Command.ExecuteNonQuery();
return this.AffectCount > 0;
}
/// <summary>
/// 查询第一列第一条记录
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object QueryOne(string sql)
{
this.Command.CommandText = sql;
return this.Command.ExecuteScalar();
}
/// <summary>
/// 查询SQL
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable Query(string sql)
{
this.Command.CommandText = sql;
MySqlDataAdapter adpter = new MySqlDataAdapter(this.Command);
DataTable dt = new DataTable("Table");
adpter.Fill(dt);
this.RecordCount = dt.Rows.Count;
this.FieldCount = dt.Columns.Count;
return dt;
}
/// <summary>
/// 查询（select）
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public object Select(string table = null, string where = null)
{
this.LastSql = this.GetSelectSql(table, where);
if (this.m_fetchSql)
{
return this.LastSql;
}
else
{
DataTable data = this.Query(this.LastSql);
this.Clear();
return data;
}
}
/// <summary>
/// 新增记录（插入记录 insert）
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public object Add(string table = null)
{
this.LastSql = this.GetInsertSql(table);
if (this.m_fetchSql)
{
return this.LastSql;
}
else
{
bool ret = this.Execute(this.LastSql);
this.Clear();
return ret;
}
}
/// <summary>
/// 保存记录（更新记录 update）
/// </summary>
/// <param name="table"></param>
/// <param name="where"></param>
/// <returns></returns>
public object Save(string table = null, string where = null)
{
this.LastSql = this.GetUpdateSql(table, where);
if (this.m_fetchSql)
{
return this.LastSql;
}
else
{
bool ret = this.Execute(this.LastSql);
this.Clear();
return ret;
}
}
/// <summary>
/// 删除记录 （delete）
/// </summary>
/// <param name="table"></param>
/// <param name="where"></param>
/// <returns></returns>
public object Delete(string table = null, string where = null)
{
this.LastSql = this.GetDeleteSql(table, where);
if (this.m_fetchSql)
{
return this.LastSql;
}
else
{
bool ret = this.Execute(this.LastSql);
this.Clear();
return ret;
}
}
/// <summary>
/// 获取查询记录语句
/// </summary>
/// <param name="table"></param>
/// <param name="where"></param>
/// <returns></returns>
protected string GetSelectSql(string table = null, string where = null)
{
table = table ?? this.m_table;
where = " WHERE " + (where ?? this.m_where);
/*
* 0: 唯一
* 1:选取字段
* 2:表前缀
* 3:表名
* 4:表别称
* 5:join
* 6:where
* 7:group
* 8:having
* 9:order
* 10:limit
* 11:union
* 12:注释
*/
this.LastSql = "SELECT {0}{1} FROM `{2}{3}`{4}{5}{6}{7}{8}{9}{10}{11}{12}";
//join
string join = "";
for (int i = 0; i < this.m_join.Count; i++)
{
join += " " + this.m_join[i];
}
//union
string union = "";
for (int i = 0; i < this.m_union.Count; i++)
{
union += " " + this.m_union[i];
}
string sql = string.Format(this.LastSql, this.m_distinct, this.m_field, this.m_prefix, table, this.m_alias, join, where, this.m_group, this.m_having, this.m_order, this.m_limit, union, this.m_comment);
return sql;
}
/// <summary>
/// 获取插入记录语句
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
protected string GetInsertSql(string table = null)
{
table = table ?? this.m_table;
string fields = "", values = "";
foreach (string key in this.m_data.Keys)
{
fields += "`"+key + "`, ";
values += this.m_data[key] + ", ";
}
if (fields.Length > 3)
{
fields = fields.Substring(0, fields.Length - 2);
values = values.Substring(0, values.Length - 2);
}
string sql = string.Format("INSERT INTO `{0}{1}` ({2}) VALUES({3})", this.m_prefix, table, fields, values);
return sql;
}
/// <summary>
/// 获取更新记录语句
/// </summary>
/// <param name="table"></param>
/// <param name="where"></param>
/// <returns></returns>
protected string GetUpdateSql(string table = null, string where = null)
{
table = table ?? this.m_table;
where = " WHERE " + (where ?? this.m_where);
string data = "";
foreach (string key in this.m_data.Keys)
{
data += "`" + key + "` = " + this.m_data[key] + ", ";
}
if (data.Length > 3)
{
data = data.Substring(0, data.Length - 2);
}
string sql = string.Format("UPDATE `{0}{1}` SET {2}{3}", this.m_prefix, table, data, where);
return sql;
}
/// <summary>
/// 获取删除记录语句
/// </summary>
/// <param name="table"></param>
/// <param name="where"></param>
/// <returns></returns>
protected string GetDeleteSql(string table = null, string where = null)
{
table = table ?? this.m_table;
where = " WHERE " + (where ?? this.m_where);
string sql = string.Format("DELETE FROM `{0}{1}`{2}{3}", this.m_prefix, table, where, this.m_limit);
return sql;
}
}
}
测试代码
using System;
using System.Data;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DbModel;
namespace Test
{
class Program
{
static void Main(string[] args)
{
DbModel.MySQL m = new DbModel.MySQL("user", "table_");
m.ConnectionString = "server=localhost;User Id=root;password=;Database=test;Charset=utf8";
m.Open();
//m.Open("server=localhost;User Id=root;password=;Database=test;Charset=utf8");
bool fetch = false; //返回SQL字符串, 并不执行
Console.WriteLine("新增测试");
m.Data("username", "剑齿虎");
m.Data("password", "123456");
m.Data("userid", null);
Console.WriteLine(m.FetchSql(fetch).Add());
m.Data("username", "剑齿虎");
m.Data("password", "123456");
m.Data("userid", 1);
Console.WriteLine(m.FetchSql(fetch).Add());
Console.WriteLine("影响行数:" + m.AffectCount);
Console.WriteLine("\n\n修改测试");
m.Data("username", "yuxiaobo64@gmail.com");
Console.WriteLine(m.FetchSql(fetch).Where("userid = 1").Save());
Console.WriteLine("影响行数:" + m.AffectCount);
Console.WriteLine("\n\n查询测试");
DataTable dt = (DataTable)m.Where("1").Field("userid, username, password").Distinct(true).FetchSql(fetch).Order("username DESC").Select();
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(item["userid"] + ", " + item["username"] + ", " + item["password"]);
}
Console.WriteLine("记录数量:" + m.RecordCount);
Console.WriteLine("字段数量:" + m.FieldCount);
Console.WriteLine("\n\n删除测试");
Console.WriteLine(m.FetchSql(fetch).Where("password = '123456'").Delete());
Console.WriteLine("影响行数:" + m.AffectCount);
Console.ReadKey();
}
}
}
测试结果
新增测试
True
True
影响行数:1
修改测试
True
影响行数:1
查询测试
107, 剑齿虎, 123456
1, yuxiaobo64@gmail.com, 123456
记录数量:2
字段数量:3
删除测试
True
影响行数:2
扫一扫
504
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
