/// <summary>
/// 判断是否存在
/// </summary>
public static bool Has(this MySqlConnection conn, ZinSQL sql)
{
var fields = sql.GetFields();
var count = conn.ExecuteScalar(string.Format("select COUNT({1}) from {0} where {2} = {3}", sql.TableName, fields, fields, sql.GetValues()));
return(Convert.ToInt32(count) > 0);
}
/// <summary>
/// 追加信息
/// </summary>
public static void Append(this MySqlConnection conn, ZinSQL sql, List <MySqlParameter> paramList)
{
var field = sql.GetAppendFiled();
var value = sql.GetAppendValue();
var sqlStr = string.Format("update {0} set {1} = concat({2}, {3}) where {4}", sql.TableName, field, field, value, sql.Where);
conn.ExecuteNonQuery(sqlStr, paramList);
}
public void ExecuteNonQuery(ZinSQL sql)
{
using (var conn = GetConnection(sql))
{
conn.Open();
conn.ExecuteNonQuery(sql);
conn.Close();
}
}
public void InsertOrUpdate(ZinSQL sql, List <MySqlParameter> paramList)
{
#if NoneWriteSQL
return;
#endif
sql.With(paramList);
BatchInsertOrUpdate(new List <ZinSQL>()
{
sql
}, sql.Config);
}
/// <summary>
/// 计数
/// </summary>
public int Count(ZinSQL sql, List <MySqlParameter> paramList = null)
{
using (var conn = GetConnection(sql))
{
conn.Open();
var count = conn.Count(sql, paramList ?? new List <MySqlParameter>());
conn.Close();
return(count);
}
}
/// <summary>
/// 直接执行SQL
/// </summary>
public object Execute(ZinSQL sql, List <MySqlParameter> paramList)
{
using (var conn = GetConnection(sql))
{
conn.Open();
var obj = conn.Execute(sql, paramList);
conn.Close();
return(obj);
}
}
public object GetScalarResult(ZinSQL sql)
{
using (var conn = GetConnection(sql))
{
conn.Open();
var list = conn.GetScalarResult(sql, sql.ParamList);
conn.Close();
return(list);
}
}
/// <summary>
/// 获取数据
/// </summary>
public object[] Get(ZinSQL sql, List <MySqlParameter> paramList = null)
{
var list = GetList(sql, paramList ?? new List <MySqlParameter>());
if (list.Count > 0)
{
return(list[0]);
}
return(null);
}
public static MySqlParameter AddQueryParam(this ZinSQL sql, string field, object value, bool addParam = false, string op = "=")
{
sql.SetParamQuery(field, op);
if (addParam)
{
sql.AddParam(field);
}
return(new MySqlParameter(string.Format("@{0}", field), value));
}
/// <summary>
/// 获取数据列表
/// </summary>
public List <object[]> GetList(ZinSQL sql, List <MySqlParameter> paramList = null)
{
using (var conn = GetConnection(sql))
{
conn.Open();
var list = conn.Get(sql, paramList ?? new List <MySqlParameter>());
conn.Close();
return(list);
}
}
public static object GetScalarResult(this MySqlConnection conn, ZinSQL sql, List <MySqlParameter> paramList)
{
if (string.IsNullOrEmpty(sql.Where))
{
return(conn.ExecuteScalar(string.Format("select {1} from {0}", sql.TableName, sql.GetFields()), paramList));
}
else
{
return(conn.ExecuteScalar(string.Format("select {1} from {0} where {2}", sql.TableName, sql.GetFields(), sql.Where), paramList));
}
}
/// <summary>
/// 插入数据
/// </summary>
public void Insert(ZinSQL sql, List <MySqlParameter> paramList)
{
#if NoneWriteSQL
return;
#endif
using (var conn = GetConnection(sql))
{
conn.Open();
conn.Insert(sql, paramList);
conn.Close();
}
}
public static List <MySqlParameter> AddMultiQueryParam <T>(this ZinSQL sql, string field, List <T> values)
{
sql.SetMultiQuery(field, values.Count);
var list = new List <MySqlParameter>();
for (int i = 0, n = values.Count; i < n; i++)
{
list.Add(new MySqlParameter(string.Format("@{0}{1}", field, i), values[i]));
}
return(list);
}
/// <summary>
/// 获取
/// </summary>
public static List <object[]> Get(this MySqlConnection conn, ZinSQL sql, List <MySqlParameter> paramList)
{
var sqlStr = string.Empty;
if (string.IsNullOrEmpty(sql.Where))
{
sqlStr = string.Format("select {1} from {0}", sql.TableName, sql.GetFields());
}
else
{
sqlStr = string.Format("select {1} from {0} where {2}", sql.TableName, sql.GetFields(), sql.Where);
}
return(conn.ExecuteQuery(sqlStr, paramList));
}
/// <summary>
/// 计数
/// </summary>
public static int Count(this MySqlConnection conn, ZinSQL sql, List <MySqlParameter> paramList)
{
var sqlStr = string.Empty;
if (string.IsNullOrEmpty(sql.Where))
{
sqlStr = string.Format("select count({1}) from {0}", sql.TableName, sql.GetFields());
}
else
{
sqlStr = string.Format("select count({1}) from {0} where {2}", sql.TableName, sql.GetFields(), sql.Where);
}
return(conn.ExecuteScalar(sqlStr, paramList).ToInt());
}
public void ResetData(string type)
{
try
{
var sql = new ZinSQL("game_data");
var paramList = new List <MySqlParameter>();
sql.SetQuery("DataType", type);
Delete(sql, paramList);
}
catch (Exception ex)
{
// LogHelper.WriteErrorLog(ex);
// LogHelper.WriteErrorLog(string.Format("Delete Data {0} Error", type));
}
}
public List <string> LoadDataTypeList(DatabaseConfig config = null)
{
var typeList = new List <string>();
// 生成SQL
var sql = new ZinSQL("game_data");
sql.AddField("DataType");
sql.Config = config;
var dataList = MySqlServer.Instance().GetList(sql);
foreach (var data in dataList)
{
typeList.Add((string)data[0]);
}
return(typeList);
}
/// <summary>
/// 追加数据
/// </summary>
public void Append(ZinSQL sql, List <MySqlParameter> paramList)
{
#if NoneWriteSQL
return;
#endif
using (var conn = GetConnection(sql))
{
conn.Open();
if (conn.Get(sql, paramList).Count == 0)
{
conn.Insert(sql, paramList);
}
else
{
conn.Append(sql, paramList);
}
conn.Close();
}
}
/// <summary>
/// 获取数据列表
/// </summary>
public List <string> GetTableName(DatabaseConfig config, string database)
{
var sql = new ZinSQL();
sql.Config = config;
sql.ExecuteSql = string.Format("SELECT table_name FROM information_schema.tables WHERE table_schema = '{0}'", database);
using (var conn = GetConnection(sql))
{
conn.Open();
var list = conn.Get(sql, new List <MySqlParameter>());
conn.Close();
var tableList = new List <string>();
foreach (var data in list)
{
tableList.Add(data[0].ToString());
}
return(tableList);
}
}
/// <summary>
/// 保存数据
/// </summary>
public void SaveData <T>(string type, T data, DatabaseConfig config = null)
where T : new()
{
#if NoneWriteSQL
return;
#endif
try
{
var sql = new ZinSQL("game_data");
var paramList = new List <MySqlParameter>();
paramList.Add(sql.AddParam("DataValue", SerializationHelper.Serialize <T>(data)));
paramList.Add(sql.AddParam("DataType", type));
sql.SetQuery("DataType", type);
sql.Config = config;
InsertOrUpdate(sql, paramList);
}
catch (Exception ex)
{
// LogHelper.WriteErrorLog(ex);
// LogHelper.WriteErrorLog(string.Format("Save Data {0} Error", type));
}
}
/// <summary>
/// 初始化数据
/// </summary>
public T LoadData <T>(string type, DatabaseConfig config = null)
where T : new()
{
// 生成SQL
var sql = new ZinSQL("game_data");
sql.AddField("DataValue");
sql.SetQuery("DataType", type);
sql.Config = config;
// 获取数据
var data = Get(sql);
if (data != null && data[0] != DBNull.Value)
{
var value = SerializationHelper.Deserialize <T>((byte[])data[0]);
if (value != null)
{
return(value);
}
}
return(new T());
}
public static void ExecuteNonQuery(this MySqlConnection conn, ZinSQL sql)
{
conn.ExecuteNonQuery(sql.ExecuteSql, sql.ParamList);
}
/// <summary>
/// 应对出现where条件参数也是要被修改参数
/// </summary>
public static MySqlParameter AddKeyQueryParam(this ZinSQL sql, string field, object value)
{
sql.SetKeyQuery(field);
return(new MySqlParameter(string.Format("@{0}1", field), value));
}
public MySqlConnection GetConnection(ZinSQL sql)
{
return(GetConnection(sql.Config));
}
/// <summary>
/// 插入信息
/// </summary>
public static void Insert(this MySqlConnection conn, ZinSQL sql, List <MySqlParameter> paramList)
{
conn.ExecuteNonQuery(string.Format("insert into {0}({1}) values({2})", sql.TableName, sql.GetFields(), sql.GetValues()), paramList);
}
/// <summary>
/// 更新信息
/// </summary>
public static void Update(this MySqlConnection conn, ZinSQL sql, List <MySqlParameter> paramList)
{
conn.ExecuteNonQuery(string.Format("update {0} set {1} where {2}", sql.TableName, sql.GetUpdateValues(), sql.Where), paramList);
}
/// <summary>
/// 执行SQL
/// </summary>
public static object Execute(this MySqlConnection conn, ZinSQL sql, List <MySqlParameter> paramList)
{
return(conn.ExecuteScalar(string.Format(sql.ExecuteSql, sql.TableName), paramList));
}
/// <summary>
/// 删除信息
/// </summary>
/// <param name="whereStr">ID = 1</param>
public static void Delete(this MySqlConnection conn, ZinSQL sql, List <MySqlParameter> paramList)
{
conn.ExecuteNonQuery(string.Format("delete from {0} where {1}", sql.TableName, sql.Where), paramList);
}
