/// <summary>
/// Lấy giá trị tiếp theo của Sequence.
/// </summary>
/// <param name="sequenceName">The query.</param>
/// <param name="commandType">The commandType.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public object GetNextValueOfSquence(string sequenceName, CustomConnectionState connectionState)
{
_oraCommand.CommandText = "SELECT " + sequenceName + ".nextval FROM DUAL";
_oraCommand.CommandType = CommandType.Text;
object o = null;
try
{
if (_oraConnection.State == System.Data.ConnectionState.Closed)
{
_oraConnection.Open();
}
o = _oraCommand.ExecuteScalar();
}
catch (Exception ex)
{
HandleExceptions(ex);
}
finally
{
_oraCommand.Parameters.Clear();
if (connectionState == CustomConnectionState.CloseOnExit)
{
_oraConnection.Close();
}
}
return(o);
}
/// <summary>
/// Executes the data set.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="commandType">The commandType.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string query, CommandType commandType, CustomConnectionState connectionState)
{
OracleDataAdapter adapter = new OracleDataAdapter();
_oraCommand.CommandText = query;
_oraCommand.CommandType = commandType;
adapter.SelectCommand = _oraCommand;
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception ex)
{
HandleExceptions(ex);
}
finally
{
_oraCommand.Parameters.Clear();
if (connectionState == CustomConnectionState.CloseOnExit)
{
if (_oraConnection.State == System.Data.ConnectionState.Open)
{
_oraConnection.Close();
}
}
}
return(ds);
}
/// <summary>
/// Executes the reader.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="commandType">The commandType.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public DbDataReader ExecuteReader(string query, CommandType commandType, CustomConnectionState connectionState)
{
_oraCommand.CommandText = query;
_oraCommand.CommandType = commandType;
DbDataReader reader = null;
try
{
if (_oraConnection.State == System.Data.ConnectionState.Closed)
{
_oraConnection.Open();
}
if (connectionState == CustomConnectionState.CloseOnExit)
{
reader = _oraCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
reader = _oraCommand.ExecuteReader();
}
}
catch (Exception ex)
{
HandleExceptions(ex);
}
finally
{
_oraCommand.Parameters.Clear();
}
return(reader);
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="commandType">The commandType.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public object ExecuteScalar(string query, CommandType commandType, CustomConnectionState connectionState)
{
_oraCommand.CommandText = query;
_oraCommand.CommandType = commandType;
object o = null;
try
{
if (_oraConnection.State == System.Data.ConnectionState.Closed)
{
_oraConnection.Open();
}
o = _oraCommand.ExecuteScalar();
}
catch (Exception ex)
{
HandleExceptions(ex);
}
finally
{
_oraCommand.Parameters.Clear();
if (connectionState == CustomConnectionState.CloseOnExit)
{
_oraConnection.Close();
}
}
return(o);
}
/// <summary>
/// Executes the non query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="commandType">The commandType.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public int ExecuteNonQuery(string query, CommandType commandType, CustomConnectionState connectionState)
{
_oraCommand.CommandText = query;
_oraCommand.CommandType = commandType;
int i = -1;
try
{
if (_oraConnection.State == System.Data.ConnectionState.Closed)
{
_oraConnection.Open();
}
i = _oraCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
HandleExceptions(ex);
}
finally
{
_oraCommand.Parameters.Clear();
if (connectionState == CustomConnectionState.CloseOnExit)
{
_oraConnection.Close();
}
}
return(i);
}
/// <summary>
/// Lấy kết quả dạng List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="commandType"></param>
/// <param name="connectionState"></param>
/// <returns></returns>
public List <T> GetList <T>(string query, CommandType commandType, CustomConnectionState connectionState, bool ignoreCaseMapping = false)
{
try
{
var dr = ExecuteReader(query, commandType);
if (dr == null || dr.FieldCount == 0)
{
return(null);
}
var fCount = dr.FieldCount;
var mType = typeof(T);
var mList = new List <T>();
while (dr.Read())
{
var obj = Activator.CreateInstance(mType);
for (var i = 0; i < fCount; i++)
{
if (dr[i] == DBNull.Value)
{
continue;
}
PropertyInfo p = null;
if (ignoreCaseMapping)
{
p = mType.GetProperty(dr.GetName(i),
BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
}
else
{
p = mType.GetProperty(dr.GetName(i));
}
if (p == null)
{
continue;
}
try
{
var type = p.PropertyType;
//var value = Convert.ChangeType(dr[i], type);
var value = ChangeType(dr[i], type);
p.SetValue(obj, value, null);
}
catch (Exception)
{
// ignored
}
}
mList.Add((T)obj);
}
dr.Close();
return(mList);
}
catch (Exception ex)
{
HandleExceptions(ex);
}
finally
{
Command.Parameters.Clear();
if (connectionState == CustomConnectionState.CloseOnExit)
{
Connection.Close();
}
}
return(null);
}
/// <summary>
/// Executes the data set.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string query, CustomConnectionState connectionState)
{
return(ExecuteDataSet(query, CommandType.Text, connectionState));
}
/// <summary>
/// Executes the reader.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public DbDataReader ExecuteReader(string query, CustomConnectionState connectionState)
{
return(ExecuteReader(query, CommandType.Text, connectionState));
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public object ExecuteScalar(string query, CustomConnectionState connectionState)
{
return(ExecuteScalar(query, CommandType.Text, connectionState));
}
/// <summary>
/// Executes the non query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="connectionstate">The connectionstate.</param>
/// <returns></returns>
public int ExecuteNonQuery(string query, CustomConnectionState connectionState)
{
return(ExecuteNonQuery(query, CommandType.Text, connectionState));
}