/* ----------------------------------------------------------------------------
* Description: Get 1 row of data back in a Name Value Pairs MyDictionary...
* Author: Mark Higbee
* Usage:
*
* MyDictionary<string, string> Params = new MyDictionary<string, string>();
* Params.Add("Name", "Value");
*
* MyDictionary<string, string> Results = new MyDictionary<string, string>();
* Results = Database.query(SQL, Params, true);
*
* ---------------------------------------------------------------------------------*/
public static MyDictionary <string, string> query(DbContext context)
{
MyDictionary <string, string> Results = new MyDictionary <string, string>();
using (MySqlConnection conn = new MySqlConnection(context.ConnectionString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
if (context.isStoredProc)
{
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.CommandText = context.SQL;
cmd.Connection = conn;
foreach (var pair in context.Params)
{
if (pair.Value != null && pair.Value.StartsWith("@"))
{
cmd.Parameters.AddWithValue("@" + pair.Key, pair.Value).Direction = ParameterDirection.Output;
}
else
{
cmd.Parameters.AddWithValue("@" + pair.Key, pair.Value);
}
}
try
{
if (conn.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
for (var i = 0; i < reader.FieldCount; i++)
{
if (!reader.IsDBNull(i))
{
Results.Add(reader.GetName(i), reader.GetValue(i).ToString());
}
else
{
Results.Add(reader.GetName(i), "N/A");
}
}
}
reader.Close();
}
}
catch (Exception error)
{
Results["ErrorMsg"] = error.ToString();
throw new Exception(error.ToString());
}
}
}
return(Results);
}
/* ----------------------------------------------------------------------------
* Description: get multiple rows of data back in a List of Name Value Pairs ...
* Author: Mark Higbee
* Usage:
*
* MyDictionary<string, string> Params = new MyDictionary<string, string>();
* Params.Add("Name", "Value");
*
* List<MyDictionary<string, string>> Results = new List<MyDictionary<string, string>>();
* Results = Database.whileQuery(SQL,Params, true);
*
* --------------------------------------------------------------------------------*/
public static List <MyDictionary <string, string> > whileQuery(DbContext context)
{
List <MyDictionary <string, string> > Results = new List <MyDictionary <string, string> >();
using (SqlConnection conn = new SqlConnection(context.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
if (context.isStoredProc)
{
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.CommandText = context.SQL;
cmd.Connection = conn;
foreach (var pair in context.Params)
{
cmd.Parameters.AddWithValue("@" + pair.Key, pair.Value);
}
if (conn.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MyDictionary <string, string> Row = new MyDictionary <string, string>();
//if (reader.Read())
//{
for (var i = 0; i < reader.FieldCount; i++)
{
if (!reader.IsDBNull(i))
{
Row.Add(reader.GetName(i), reader.GetValue(i).ToString());
}
else
{
Row.Add(reader.GetName(i), "N/A");
}
}
// }
Results.Add(Row);
}
reader.Close();
}
}
catch (Exception error)
{
MyDictionary <string, string> Row = new MyDictionary <string, string>();
Row.Add("ErrorMsg", error.ToString());
Results.Add(Row);
}
}
}
return(Results);
}