/// <summary>
/// 获取数据列表 带参数化
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="commandText">SQL语句</param>
/// <param name="countTable">分页获取总数的表名及条件</param>
/// <returns></returns>
public List <T> GetList <T>(string commandText, UtilityPager utilityPager = null, string countTable = null, SqlParameter[] parmarr = null) where T : BaseTO, new()
{
MSSQLSqlHelper db = new MSSQLSqlHelper(connectionkey);
List <T> list = new List <T>();
//是否需要分页
if (utilityPager != null)
{
string sqlcount = string.Format("select count(*) from {0}", countTable);
utilityPager.Count = int.Parse(db.ExecuteScalar(sqlcount, CommandType.Text, parmarr).ToString());
commandText = PagingAll(commandText, utilityPager.PageIndex, utilityPager.PageSize);
}
using (SqlDataReader reader = db.ExecuteReader(commandText, CommandType.Text, parmarr))
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
list.Add(to);
}
}
}
return(list);
}
/// <summary>
/// 获取数组数据列表
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns></returns>
public List <object> GetListObject(string commandText)
{
MSSQLSqlHelper db = new MSSQLSqlHelper(connectionkey);
List <object> list = new List <object>();
using (SqlDataReader reader = db.ExecuteReader(commandText))
{
if (reader.HasRows)
{
while (reader.Read())
{
if (reader.FieldCount > 1)
{
object[] obj = new object[reader.FieldCount];
reader.GetValues(obj);
list.Add(obj);
}
else
{
list.Add(reader.GetValue(0));
}
}
}
}
return(list);
}
/// <summary>
/// 设置数据库连接
/// </summary>
/// <param name="key">ConnectionString To Key(or Name)</param>
public static void SetConnection(string key = null)
{
key = key == null ? defalutkey : key;
CAS.DataAccess.BaseDAModels.SqlServerSet.CustomGetConnectionString = delegate(string connName)
{
//直接从config中获取
return(MSSQLSqlHelper.GetConfigurationManager(key));
};
}
public List <T> GetList <T>(string commandText) where T : new()
{
MSSQLSqlHelper db = new MSSQLSqlHelper(connectionkey);
List <T> list = new List <T>();
using (SqlDataReader reader = db.ExecuteReader(commandText))
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
for (int i = 0; i < reader.FieldCount; i++)
{
string field = reader.GetName(i);
if (field.Equals("rownum"))
{
continue;
}
object value = reader.GetValue(i);
if (value != null && value != DBNull.Value)
{
var property = to.GetType().GetProperty(field);
bool IsNull = value == null || string.IsNullOrEmpty(value.ToString());
if (!property.PropertyType.IsGenericType)
{
//非泛型
property.SetValue(to,
IsNull ? null : Convert.ChangeType(value, property.PropertyType),
null);
}
else
{
//泛型Nullable<>
Type genericTypeDefinition = property.PropertyType.GetGenericTypeDefinition();
if (genericTypeDefinition == typeof(Nullable <>))
{
property.SetValue(to,
IsNull ? null : Convert.ChangeType(value, Nullable.GetUnderlyingType(property.PropertyType)),
null);
}
}
}
}
list.Add(to);
}
}
}
return(list);
}
/// <summary>
/// 增删改
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="param">参数</param>
/// <returns></returns>
public int CUD(string commandText, SqlParameter[] param = null)
{
MSSQLSqlHelper db = new MSSQLSqlHelper(connectionkey);
return(db.ExecuteNonQuery(commandText, System.Data.CommandType.Text, param));
}
/// <summary>
/// 返回结果中第一行第一列结果
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns></returns>
public object GetUniqueResult(string commandText)
{
MSSQLSqlHelper db = new MSSQLSqlHelper(connectionkey);
return(db.ExecuteScalar(commandText));
}