/// <summary>
/// 数据填充方式查询数据
/// </summary>
/// <param name="manager">查询参数</param>
/// <returns>返回DataTable数据集</returns>
public DataTable ExecuteFill(MySqlManager manager)
{
MySqlConnection con = new MySqlConnection(ConnectionString);
try
{
MySqlCommand com = new MySqlCommand(manager.Sql, con);
if (manager.CommandTimeout != -1)
{
com.CommandTimeout = manager.CommandTimeout;
}
com.CommandType = manager.Type;
foreach (MySqlParameter item in manager)
{
com.Parameters.Add(item);
}
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = com;
DataTable table = new DataTable();
da.Fill(table);
return(table);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.State == ConnectionState.Open || con.State == ConnectionState.Broken)
{
con.Close();
}
}
}
public virtual int InsertToID(ModelBase model)
{
if (model.Count() == 0)
{
throw new Exception("无任何数据进行添加，无法执行添加操作");
}
Type type = model.GetType();
StringBuilder sql1 = new StringBuilder();
StringBuilder sql2 = new StringBuilder();
MySqlManager manager = new MySqlManager();
for (int i = 0; i < model.Count(); i++)
{
//取出已赋值的属性的名称
string name = model.GetValue(i);
sql1.Append(name + ",");
sql2.Append("@" + name + ",");
//根据属性名获取属性对象
PropertyInfo pinfo = type.GetProperty(name);
object value = pinfo.GetValue(model, null);
manager.Add("@" + name, value);
}
sql1.Remove(sql1.Length - 1, 1);
sql2.Remove(sql2.Length - 1, 1);
string sql = string.Format("insert into {0}({1}) values({2});select @@identity", type.Name + model.Extend, sql1, sql2);
manager.Sql = sql;
int id = int.Parse(sqlHelp.ExecuteScalar(manager).ToString());
return(id);
}
public object ExecuteScalar(MySqlManager manager)
{
using (MySqlConnection con = new MySqlConnection(ConnectionString))
{
try
{
MySqlCommand com = new MySqlCommand(manager.Sql, con);
com.CommandType = manager.Type;
if (manager.CommandTimeout != -1)
{
com.CommandTimeout = manager.CommandTimeout;
}
foreach (MySqlParameter item in manager)
{
com.Parameters.Add(item);
}
con.Open();
return(com.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.State == ConnectionState.Open || con.State == ConnectionState.Broken)
{
con.Close();
}
}
}
}
/// <summary>
/// 查询数据返回实体集合
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="tableExtend">数据表扩展名</param>
/// <returns></returns>
public virtual List <T> SelectAll <T>(string tableExtend) where T : ModelBase
{
Type type = typeof(T);
string sql = string.Format("select * from {0}", type.Name + tableExtend);
MySqlManager manager = new MySqlManager(sql);
return(Select <T>(manager));
}
public virtual bool Delete(ModelBase model)
{
//验证是否有主键（实体类上是否表示了主键特征）
Type type = model.GetType();
string keyName = ValidateKey(model);
string sql = string.Format("delete {0} where {1}=@{1}", type.Name + model.Extend, keyName);
MySqlManager manager = new MySqlManager(sql);
PropertyInfo pinfo = type.GetProperty(keyName);
object value = pinfo.GetValue(model, null);
manager.Add("@" + keyName, value);
int count = sqlHelp.ExecuteNonQuery(manager);
return(count == 1);
}
/// <summary>
/// SqlDataReader(类似游标) 查询数据
/// </summary>
/// <param name="manager">查询参数</param>
/// <returns></returns>
public MySqlDataReader ExecuteReader(MySqlManager manager)
{
MySqlConnection con = new MySqlConnection(ConnectionString);
MySqlCommand com = new MySqlCommand(manager.Sql, con);
com.CommandType = manager.Type;
if (manager.CommandTimeout != -1)
{
com.CommandTimeout = manager.CommandTimeout;
}
foreach (MySqlParameter item in manager)
{
com.Parameters.Add(item);
}
con.Open();
return(com.ExecuteReader(CommandBehavior.CloseConnection));
}
public virtual void SelectForID(ModelBase model)
{
string keyName = ValidateKey(model);
Type type = model.GetType();
string sql = string.Format("select * from {0} where {1}=@{1}", type.Name + model.Extend, keyName);
MySqlManager manager = new MySqlManager(sql);
PropertyInfo pinfo = type.GetProperty(keyName);
object keyValue = pinfo.GetValue(model, null);
manager.Add("@" + keyName, keyValue);
using (MySqlDataReader read = sqlHelp.ExecuteReader(manager))
{
if (read.Read())
{
//取出实体类中所有的属性名
PropertyInfo[] pinfos = type.GetProperties();
//根据属性名取出查询结果中的值
foreach (PropertyInfo item in pinfos)
{
string name = item.Name;
//验证该属性名在查询结果中
//read.fieldCount表示查询结果中的列数
for (int i = 0; i < read.FieldCount; i++)
{
//根据列的索引取出列的名称
string colName = read.GetName(i);
if (name.ToLower() == colName.ToLower())//属性名与查询结果的列名匹配
{
object value = read[name];
if (value != DBNull.Value)
{
item.SetValue(model, value, null);
}
break;
}
}
}
}
else
{
throw new Exception("无法根据ID查询数据");
}
}
}
public virtual bool Update(ModelBase model)
{
//主键验证
string keyName = ValidateKey(model);
//除主键外至少还有一个属性赋值
if (model.Count() < 2)
{
throw new Exception("没有给主键以外的其他列赋值，无法执行修改操作");
}
Type type = model.GetType();
StringBuilder sql1 = new StringBuilder();
MySqlManager manager = new MySqlManager();
for (int i = 0; i < model.Count(); i++)
{
//取出已赋值的属性名称
string name = model.GetValue(i);
if (name != keyName)//除主键外其他的属性应该拼凑到Sql中
{
sql1.Append(name + "=@" + name + ",");
//根据属性名获取属性对象，再取出属性值
PropertyInfo pinfo = type.GetProperty(name);
object value = pinfo.GetValue(model, null);
manager.Add("@" + name, value);
}
}
sql1.Remove(sql1.Length - 1, 1);
string sql = string.Format("update {0} set {1} where {2}=@{2}", type.Name + model.Extend, sql1, keyName);
PropertyInfo keyPInfo = type.GetProperty(keyName);
object keyValue = keyPInfo.GetValue(model, null);
manager.Add("@" + keyName, keyValue);
manager.Sql = sql;
int count = sqlHelp.ExecuteNonQuery(manager);
return(count == 1);
}
protected internal virtual List <T> Select <T>(MySqlManager manager) where T : ModelBase
{
Type type = typeof(T);
using (MySqlDataReader read = sqlHelp.ExecuteReader(manager))
{
List <T> list = new List <T>();
while (read.Read())
{
//根据类型来创建类型的对象
T t = Activator.CreateInstance(type) as T;
PropertyInfo[] pinfos = type.GetProperties();
foreach (PropertyInfo item in pinfos)
{
string name = item.Name;
//验证该属性名在查询结果中
//read.fieldCount表示查询结果中的列数
for (int i = 0; i < read.FieldCount; i++)
{
//根据列的索引取出列的名称
string colName = read.GetName(i);
if (name.ToLower() == colName.ToLower())//属性名与查询结果的列名匹配
{
object value = read[name];
if (value != DBNull.Value)
{
item.SetValue(t, value, null);
}
break;
}
}
}
list.Add(t);
}
return(list);
}
}
/// <summary>
/// 通用分页(适用于MySql数据库)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public List <T> GetPageDataForMySql <T>() where T : ModelBase
{
string sql = "ProcPage";
MySqlManager manager = new MySqlManager(sql, System.Data.CommandType.StoredProcedure);
manager.CommandTimeout = this.TimeOut;
manager.Add("tableName", this.TableName);
manager.Add("showField", this.ShowField);
manager.Add("whereText", this.WhereText.Replace("-", ""));
manager.Add("orderText", this.OrderText);
manager.Add("pageSize", this.PageSize);
manager.Add("pageIndex", this.PageIndex);
manager.AddOutput("dataCount", MySqlDbType.Int32);
MySqlDALBase dal = new MySqlDALBase();
dal.sqlHelp.ConnectionString = this.ConnectionString;
List <T> list = dal.Select <T>(manager);
this.DataCount = int.Parse(manager[manager.Count - 1].Value.ToString());
this.PageCount = (DataCount - 1) / PageSize + 1;
return(list);
#region MySql分页存储过程
/*
* CREATE PROCEDURE ProcPage(
* in tableName varchar(20),#表名
* in showField varchar(100),#要显示的列名
* in whereText varchar(500),#where条件(只需要写where后面的语句)
* in orderText varchar(500),#排序条件（只需要写order by后面的语句）
* in pageSize int,#每一页显示的记录数
* in pageIndex int,#当前页
* out dataCount int#总记录数
* )
* BEGIN
*
* if (pageSize<1)then
* set pageSize=20;
* end if;
*
* if (pageIndex < 1)then
* set pageIndex = 1;
* end if;
*
* if(LENGTH(whereText)>0)then
* set whereText=CONCAT(' where 1=1 ',whereText);
* end if;
*
* if(LENGTH(orderText)>0)then
* set orderText = CONCAT(' ORDER BY ',orderText);
* end if;
*
* set @strsql = CONCAT('select ',showField,' from ',tableName,' ',whereText,' ',orderText,' limit ',pageIndex*pageSize-pageSize,',',pageSize);
*
* prepare stmtsql from @strsql;
* execute stmtsql;
* deallocate prepare stmtsql;
*
* set @strsqlcount=concat('select count(1) as count into @datacount from ',tableName,'',whereText);
* prepare stmtsqlcount from @strsqlcount;
* execute stmtsqlcount;
* deallocate prepare stmtsqlcount;
* set datacount=@datacount;
* END;*/
#endregion
}
