/// <summary>
/// Used to get the data from the database into tabular form.
/// </summary>
/// <param name="query">Query command to fetch the data</param>
/// <param name="type">Type of the command</param>
/// <param name="parameters">Parameters if required</param>
/// <returns>Iterative Dataset</returns>
protected IEnumerable <IDataRecord> GetIteratableData(string query, SQLCommandTypes type, params SqlParameter[] parameters)
{
if (type == SQLCommandTypes.StoredProcedure)
{
SqlCommand command = new SqlCommand(query, dbConnection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.AddRange(parameters);
dbConnection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return(reader);
}
}
dbConnection.Close();
}
else
{
SqlCommand command = new SqlCommand(query, dbConnection);
dbConnection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return(reader);
}
}
dbConnection.Close();
}
}
/// <summary>
/// Used to execute query.
/// </summary>
/// <param name="query">Query command to fetch the data</param>
/// <param name="type">Type of the command</param>
/// <param name="parameters">Parameters if required</param>
protected void ExecuteQuery(string query, SQLCommandTypes type, params SqlParameter[] parameters)
{
if (type == SQLCommandTypes.StoredProcedure)
{
SqlCommand command = new SqlCommand(query, dbConnection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.AddRange(parameters);
dbConnection.Open();
try
{
command.ExecuteNonQuery();
}
catch (SqlException)
{
throw;
}
dbConnection.Close();
}
else
{
SqlCommand command = new SqlCommand(query, dbConnection);
dbConnection.Open();
try
{
command.ExecuteNonQuery();
}
catch (SqlException)
{
throw;
}
dbConnection.Close();
}
}
private string GetCmdText(SQLCommandTypes cmdtype, string db, string table)
{
if (_cmdCache == null)
{
LoadCache();
}
var ret = _cmdCache.ContainsKey(cmdtype) ? _cmdCache[cmdtype].CreateCmd(db, table) : null;
CmdBuilder.Append(ret);
return(ret);
}
/// <summary>
/// Used to get the data from the database as a single value.
/// </summary>
/// <param name="query">Query command to fetch the data</param>
/// <param name="type">Type of the command</param>
/// <param name="parameters">Parameters if required</param>
/// <returns>Single Object Value</returns>
protected object GetValue(string query, SQLCommandTypes type, params SqlParameter[] parameters)
{
object value;
if (type == SQLCommandTypes.StoredProcedure)
{
SqlCommand command = new SqlCommand(query, dbConnection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.AddRange(parameters);
dbConnection.Open();
try
{
value = command.ExecuteScalar();
}
catch (SqlException)
{
throw;
}
dbConnection.Close();
}
else
{
SqlCommand command = new SqlCommand(query, dbConnection);
dbConnection.Open();
try
{
value = command.ExecuteScalar();
}
catch (SqlException)
{
throw;
}
dbConnection.Close();
}
return(value);
}
/// <summary>
/// Used to get the data from the database into tabular form.
/// </summary>
/// <param name="query">Query command to fetch the data</param>
/// <param name="type">Type of the command</param>
/// <param name="parameters">Parameters if required</param>
/// <returns>Iterative Dataset</returns>
protected SqlDataReader GetIteratableData(string query, SQLCommandTypes type, params SqlParameter[] parameters)
{
SqlDataReader data;
if (type == SQLCommandTypes.StoredProcedure)
{
SqlCommand command = new SqlCommand(query, dbConnection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
dbConnection.Open();
try
{
data = command.ExecuteReader();
}
catch (SqlException)
{
throw;
}
dbConnection.Close();
}
else
{
SqlCommand command = new SqlCommand(query, dbConnection);
dbConnection.Open();
try
{
data = command.ExecuteReader();
}
catch (SqlException)
{
throw;
}
dbConnection.Close();
}
return(data);
}
public SQLCmd(DbConnection connection, SQLCommandTypes cmdtype)
{
Connection = connection;
cmdTpye = cmdtype;
_builder = new MySQLCommandBuilder(cmdTpye, _db, _table);
}
public MySQLCommandBuilder(SQLCommandTypes cmdtype, string db, string table)
{
SQLCmdType = cmdtype;
GetCmdText(cmdtype, db, table);
}
