整理了一个数据库读取数据辅助类 DataHelperIoT
1.原来读取数据代码:
int tempInt;
if (int.TryParse(dataReader[this.DBInt].ToString(), out tempInt))
{
model.objInt= tempInt;
}
以int类型为例,其他类型类似,这样写每次都会重复一大堆的转换代码
2.使用DataHelperIoT 辅助类代码:
DataHelperIoT DataHelper = DataHelperIoT.CreateInstance(dataReader);
model.objInt= DataHelper.ReadInt32(this.DBInt);
model.objInt= DataHelperIoT.ReadInt32(dataReader,this.DBInt);
以上可选择两种调用方法
DataHelperIoT源代码:
/// <summary>
/// 数据库读取数据辅助类
/// </summary>
public class DataHelperIoT
{
private IDataReader _iDataReader;
private static DataHelperIoT _dataHelperIoT;
public DataHelperIoT() { }
public DataHelperIoT(IDataReader reader)
{
this._iDataReader = reader;
}
public IDataReader ThisDataReader {
get { return this._iDataReader; }
set { this._iDataReader = value; }
}
public static DataHelperIoT CreateInstance(IDataReader reader) {
_dataHelperIoT = new DataHelperIoT(reader);
return _dataHelperIoT;
}
#region 实例化 reader
/// <summary>
/// 从reader 读取guid
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public Guid ReadGuid(string columnName)
{
return ReadGuid(columnName, Guid.Empty);
}
/// <summary>
/// 从reader 读取guid
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public Guid ReadGuid(string columnName, Guid defaultValue)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return (Guid)ThisDataReader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
///从reader 里读取Int32类型数据
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public int ReadInt32(string columnName)
{
return ReadInt32(columnName, 0);
}
/// <summary>
/// 从DataReader中读取整型数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">DataReader</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns>数据</returns>
public int ReadInt32(string columnName, int defaultValue)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return int.Parse(ThisDataReader[columnName].ToString());
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
///从DataReader中读取长整型数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns>数据</returns>
public long ReadInt64(string columnName)
{
return ReadInt64(columnName, 0L);
}
/// <summary>
/// 从DataReader中读取长整型数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public long ReadInt64( string columnName, long defaultValue)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return (long)ThisDataReader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
/// 从DataReader中读取decimal数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public decimal ReadDecimal( string columnName)
{
return ReadDecimal(columnName, 0);
}
/// <summary>
/// 从DataReader中读取decimal数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public decimal ReadDecimal(string columnName, decimal defaultValue)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return (decimal)ThisDataReader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
///从DataReader中读取布尔数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns>数据</returns>
public bool ReadBoolean(string columnName)
{
return ReadBoolean(columnName, false);
}
/// <summary>
///从DataReader中读取布尔数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public bool ReadBoolean(string columnName, bool defaultValue)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return (bool)ThisDataReader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
/// 从DataReader中读取可空int数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public int? ReadNullableInt(string columnName)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return int.Parse(ThisDataReader[columnName].ToString());
else
return null;
}
catch (IndexOutOfRangeException)
{
return null;
}
}
/// <summary>
/// 读取字符串
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public string ReadString(string columnName)
{
return ReadString(columnName, string.Empty);
}
/// <summary>
/// 读取字符串
/// </summary>
/// <param name="reader">reader</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public string ReadString(string columnName, string defaultValue)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return (string)ThisDataReader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
/// 读取IP地址信息
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public IPAddress ReadIpAddress(string columnName)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return IPAddress.Parse((string)ThisDataReader[columnName]);
else
return IPAddress.None;
}
catch (System.FormatException)
{
return IPAddress.None;
}
catch (IndexOutOfRangeException)
{
return IPAddress.None;
}
}
/// <summary>
/// 从DataReader中读取URI数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public Uri ReadUri(string columnName)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return new Uri((string)ThisDataReader[columnName]);
else
return null;
}
catch (System.IndexOutOfRangeException)
{
return null;
}
catch (System.FormatException)
{
return null;
}
}
/// <summary>
/// 从DataReader中读取DateTime数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public DateTime ReadDateTime(string columnName)
{
return ReadDate(columnName, DateTime.MinValue);
}
/// <summary>
///从DataReader中读取DateTime数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public DateTime ReadDate(string columnName, DateTime defaultValue)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return (DateTime)ThisDataReader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
///从DataReader中读取可空DateTime数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public DateTime? ReadNullableDate(string columnName)
{
return ReadNullableDate(columnName, null);
}
/// <summary>
/// 从DataReader中读取可空DateTime数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public DateTime? ReadNullableDate(string columnName, DateTime? defaultValue)
{
try
{
if (ThisDataReader[columnName] != DBNull.Value)
return (DateTime)ThisDataReader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
#endregion
# region 指定 reader
/// <summary>
/// 从reader 读取guid
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static Guid ReadGuid(IDataReader reader, string columnName)
{
return ReadGuid(reader, columnName, Guid.Empty);
}
/// <summary>
/// 从reader 读取guid
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public static Guid ReadGuid(IDataReader reader, string columnName, Guid defaultValue)
{
try
{
if (reader[columnName] != DBNull.Value)
return (Guid)reader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
///从reader 里读取Int32类型数据
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public static int ReadInt32(IDataReader reader, string columnName)
{
return ReadInt32(reader, columnName, 0);
}
/// <summary>
/// 从DataReader中读取整型数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">DataReader</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns>数据</returns>
public static int ReadInt32(IDataReader reader, string columnName, int defaultValue)
{
try
{
if (reader[columnName] != DBNull.Value)
return int.Parse(reader[columnName].ToString());
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
///从DataReader中读取长整型数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns>数据</returns>
public static long ReadInt64(IDataReader reader, string columnName)
{
return ReadInt64(reader, columnName, 0L);
}
/// <summary>
/// 从DataReader中读取长整型数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public static long ReadInt64(IDataReader reader, string columnName, long defaultValue)
{
try
{
if (reader[columnName] != DBNull.Value)
return (long)reader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
/// 从DataReader中读取decimal数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static decimal ReadDecimal(IDataReader reader, string columnName)
{
return ReadDecimal(reader, columnName, 0);
}
/// <summary>
/// 从DataReader中读取decimal数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public static decimal ReadDecimal(IDataReader reader, string columnName, decimal defaultValue)
{
try
{
if (reader[columnName] != DBNull.Value)
return (decimal)reader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
///从DataReader中读取布尔数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns>数据</returns>
public static bool ReadBoolean(IDataReader reader, string columnName)
{
return ReadBoolean(reader, columnName, false);
}
/// <summary>
///从DataReader中读取布尔数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public static bool ReadBoolean(IDataReader reader, string columnName, bool defaultValue)
{
try
{
if (reader[columnName] != DBNull.Value)
return (bool)reader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
/// 从DataReader中读取可空int数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public static int? ReadNullableInt(IDataReader reader, string columnName)
{
try
{
if (reader[columnName] != DBNull.Value)
return int.Parse(reader[columnName].ToString());
else
return null;
}
catch (IndexOutOfRangeException)
{
return null;
}
}
/// <summary>
/// 读取字符串
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static string ReadString(IDataReader reader, string columnName)
{
return ReadString(reader, columnName, string.Empty);
}
/// <summary>
/// 读取字符串
/// </summary>
/// <param name="reader">reader</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public static string ReadString(IDataReader reader, string columnName, string defaultValue)
{
try
{
if (reader[columnName] != DBNull.Value)
return (string)reader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
/// 读取IP地址信息
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public static IPAddress ReadIpAddress(IDataReader reader, string columnName)
{
try
{
if (reader[columnName] != DBNull.Value)
return IPAddress.Parse((string)reader[columnName]);
else
return IPAddress.None;
}
catch (System.FormatException)
{
return IPAddress.None;
}
catch (IndexOutOfRangeException)
{
return IPAddress.None;
}
}
/// <summary>
/// 从DataReader中读取URI数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static Uri ReadUri(IDataReader reader, string columnName)
{
try
{
if (reader[columnName] != DBNull.Value)
return new Uri((string)reader[columnName]);
else
return null;
}
catch (System.IndexOutOfRangeException)
{
return null;
}
catch (System.FormatException)
{
return null;
}
}
/// <summary>
/// 从DataReader中读取DateTime数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public static DateTime ReadDateTime(IDataReader reader, string columnName)
{
return ReadDate(reader, columnName, DateTime.MinValue);
}
/// <summary>
///从DataReader中读取DateTime数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public static DateTime ReadDate(IDataReader reader, string columnName, DateTime defaultValue)
{
try
{
if (reader[columnName] != DBNull.Value)
return (DateTime)reader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
/// <summary>
///从DataReader中读取可空DateTime数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public static DateTime? ReadNullableDate(IDataReader reader, string columnName)
{
return ReadNullableDate(reader, columnName, null);
}
/// <summary>
/// 从DataReader中读取可空DateTime数据,如果值为空,则返回默认值
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">列名</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
public static DateTime? ReadNullableDate(IDataReader reader, string columnName, DateTime? defaultValue)
{
try
{
if (reader[columnName] != DBNull.Value)
return (DateTime)reader[columnName];
else
return defaultValue;
}
catch (IndexOutOfRangeException)
{
return defaultValue;
}
}
#endregion
/// <summary>
/// 转换成安全的sql时间
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static string GetSafeSqlDateTimeFormat(DateTime date)
{
return date.ToString(System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.SortableDateTimePattern);
}
/// <summary>
/// 转换成安全的SQL时间
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static DateTime GetSafeSqlDateTime(DateTime date)
{
if (date < DateTime.MinValue)
return (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
else if (date > DateTime.MaxValue)
return (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue;
return date;
}
/// <summary>
/// 转换成安全的SQL整数
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
public static int GetSafeSqlInt(int i)
{
if (i <= (int)System.Data.SqlTypes.SqlInt32.MinValue)
return (int)System.Data.SqlTypes.SqlInt32.MinValue + 1;
else if (i >= (int)System.Data.SqlTypes.SqlInt32.MaxValue)
return (int)System.Data.SqlTypes.SqlInt32.MaxValue - 1;
return i;
}
}
DataHelperIoTDataHelperIoT