public static void AddUUID(Npgsql.NpgsqlCommand cmd, string field, string value)
{
if (string.IsNullOrWhiteSpace(value)) {
cmd.Parameters.AddWithValue(field, DBNull.Value);
} else {
cmd.Parameters.AddWithValue(field, new Guid(value));
}
}
public static void AddInteger(Npgsql.NpgsqlCommand cmd, string field, string value)
{
Int64 v;
if (Int64.TryParse(value, out v)) {
cmd.Parameters.AddWithValue(field, v);
} else {
cmd.Parameters.AddWithValue(field, DBNull.Value);
}
}
public static Npgsql.NpgsqlDataAdapter GetDataAdapter(Npgsql.NpgsqlCommand command)
{
if(command != null)
{
using(Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DBFactory.DBConnection.ConnectionString()))
{
command.Connection = connection;
using(Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(command))
{
return adapter;
}
}
}
return null;
}
public static Npgsql.NpgsqlDataReader GetDataReader(Npgsql.NpgsqlCommand command)
{
if(command != null)
{
Npgsql.NpgsqlDataReader reader = default(Npgsql.NpgsqlDataReader);
using(Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DBFactory.DBConnection.ConnectionString()))
{
command.Connection = connection;
command.Connection.Open();
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
}
return null;
}
public static bool ExecuteNonQuery(Npgsql.NpgsqlCommand command)
{
if(command != null)
{
using(Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DBFactory.DBConnection.ConnectionString()))
{
try
{
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
return true;
}
catch
{
throw;
}
}
}
return false;
}
public static void WriteError(Exception ex, Npgsql.NpgsqlCommand cmd)
{
if (System.Configuration.ConfigurationManager.AppSettings["Debug"] == "true") {
HttpContext.Current.Response.Write(ex.Message + "<br />" + ex.StackTrace + "<br />" + cmd.CommandText);
} else {
HttpContext.Current.Response.Write(ex.Message + "<br />" + ex.StackTrace);
}
}
private static async Task<List<LcboStore>> GetStoresForSubdivisions(IEnumerable<int> subdivisionIds, Npgsql.NpgsqlConnection conn)
{
var result = new List<LcboStore>();
using (var getStoresCmd = conn.CreateCommand())
{
var subdivIds = string.Join(", ", subdivisionIds);
getStoresCmd.CommandText = $@"select sd.id as subdivision_id,
s.id as id,
s.name as name,
s.city as city,
ST_AsGeoJSON(s.location) as location,
s.beer_volume / 1000.0 as beer_volume,
s.wine_volume / 1000.0 as wine_volume,
s.spirits_volume / 1000.0 as spirits_volume
from subdivisions sd
inner join stores s on ST_Intersects(ST_Transform(s.location::geometry, 4269), sd.boundry)
where sd.id in ({subdivIds})
order by subdivision_id";
using (var storesReader = await getStoresCmd.ExecuteReaderAsync())
{
while (storesReader.Read())
{
result.Add(new LcboStore(storesReader));
}
}
}
return result;
}
protected static string GetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand("select version from versions where name='application' limit 1;", conn, trans))
{
cmd.CommandType = System.Data.CommandType.Text;
return (string)cmd.ExecuteScalar();
}
}
protected static bool FunctionExists(string functionName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(FUNCTION_EXISTS, conn, trans))
{
cmd.Parameters.Add("@procname", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = functionName;
using (var r = cmd.ExecuteReader())
{
return r.HasRows;
}
}
}
public static object GetScalarValue(Npgsql.NpgsqlCommand command)
{
if(command != null)
{
using(Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DatabaseLayer.DBFactory.DBConnection.ConnectionString()))
{
command.Connection = connection;
command.CommandTimeout = 300;
connection.Open();
return command.ExecuteScalar();
}
}
return null;
}
public MyClass(Npgsql.NpgsqlCommand cmd)
{
Console.WriteLine(cmd);
}
public UdfDbFunctionNpgsqlTests(Npgsql fixture, ITestOutputHelper testOutputHelper)
: base(fixture)
{
fixture.TestSqlLoggerFactory.Clear();
//fixture.TestSqlLoggerFactory.SetTestOutputHelper(testOutputHelper);
}
public static DataTable GetDataTable(Npgsql.NpgsqlCommand command)
{
if(command != null)
{
using(Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DatabaseLayer.DBFactory.DBConnection.ConnectionString()))
{
command.Connection = connection;
command.CommandTimeout = 300;
using(NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
using(DataTable dataTable = new DataTable())
{
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
return dataTable;
}
}
}
}
return null;
}
protected static bool IsSuperUser(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(IS_SUPERUSER, conn, trans))
{
using (var r = cmd.ExecuteReader())
{
return r.HasRows;
}
}
}
protected static bool TableExists(string tableName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(TABLE_EXISTS, conn, trans))
{
cmd.Parameters.Add("@tableName", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = tableName;
using (var r = cmd.ExecuteReader())
{
return r.HasRows;
}
}
}
protected static void SetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans, string versionNumber)
{
using (var cmd = new Npgsql.NpgsqlCommand("update versions set version = @version where name = 'application';", conn, trans))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@version", NpgsqlTypes.NpgsqlDbType.Varchar, 15).Value = versionNumber;
cmd.ExecuteNonQuery();
}
}
protected static void RunStatement(string statement, IDictionary<string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null) parameters = new Dictionary<string, Npgsql.NpgsqlParameter>();
using (var cmd = new Npgsql.NpgsqlCommand(statement, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (statement.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
protected static void RunScript(string script, IDictionary<string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null) parameters = new Dictionary<string, Npgsql.NpgsqlParameter>();
foreach (var commandText in script.Split(';'))
{
var sql = commandText + ";";
Log.Debug(d => d("Script command: {0}", sql));
using (var cmd = new Npgsql.NpgsqlCommand(sql, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (sql.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
Log.Debug(d => d("Script complete."));
}
public void nNpg(Npgsql.NpgsqlConnection nNpgConn, Npgsql.NpgsqlConnection nNpgCmd)
{
nNpgConn = nNpgConn;
nNpgCmd = nNpgCmd;
}
internal ViewHolder(Npgsql.Designer.Design.View parent)
{
_triggers = parent.Triggers as List<ViewTrigger>;
}
public void TestProcWithOutParameter()
{
connection.Execute(
@"CREATE PROCEDURE #TestProcWithOutParameter
@ID int output,
@Foo varchar(100),
@Bar int
AS
SET @ID = @Bar + LEN(@Foo)");
var obj = new
{
ID = 0,
Foo = "abc",
Bar = 4
};
var args = new DynamicParameters(obj);
args.Add("ID", 0, direction: ParameterDirection.Output);
connection.Execute("#TestProcWithOutParameter", args, commandType: CommandType.StoredProcedure);
args.Get<int>("ID").IsEqualTo(7);
}
public static DataTable GetDataTable(Npgsql.NpgsqlCommand command, string connectionString)
{
if(command != null)
{
using(Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
{
command.Connection = connection;
using(NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
using(DataTable dataTable = new DataTable())
{
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
return dataTable;
}
}
}
}
return null;
}
public static DataView GetDataView(Npgsql.NpgsqlCommand command)
{
if(command != null)
{
using(DataView view = new DataView(GetDataTable(command)))
{
return view;
}
}
return null;
}
public static DataTable GetDataTable(Npgsql.NpgsqlCommand command)
{
return GetDataTable(command, MixERP.Net.DBFactory.DBConnection.ConnectionString());
}
public static DataSet GetDataSet(Npgsql.NpgsqlCommand command)
{
if(command != null)
{
using(Npgsql.NpgsqlDataAdapter adapter = GetDataAdapter(command))
{
using(DataSet set = new DataSet())
{
adapter.Fill(set);
set.Locale = CultureInfo.CurrentUICulture;
return set;
}
}
}
return null;
}
private void BindVars(string sql, List<Bind> bind_var, Npgsql.NpgsqlCommand c)
{
int n = 0;
foreach (Bind bind in bind_var)
{
switch (bind.Type)
{
case DataType.Boolean:
c.Parameters.Add(new Npgsql.NpgsqlParameter(bind.Name, NpgsqlTypes.NpgsqlDbType.Boolean));
c.Parameters[n++].Value = bool.Parse(bind.Value);
break;
case DataType.Integer:
c.Parameters.Add(new Npgsql.NpgsqlParameter(bind.Name, NpgsqlTypes.NpgsqlDbType.Integer));
c.Parameters[n++].Value = int.Parse(bind.Value);
break;
case DataType.Varchar:
c.Parameters.Add(new Npgsql.NpgsqlParameter(bind.Name, NpgsqlTypes.NpgsqlDbType.Varchar));
c.Parameters[n++].Value = bind.Value;
break;
case DataType.Text:
c.Parameters.Add(new Npgsql.NpgsqlParameter(bind.Name, NpgsqlTypes.NpgsqlDbType.Text));
c.Parameters[n++].Value = bind.Value;
break;
case DataType.Date:
c.Parameters.Add(new Npgsql.NpgsqlParameter(bind.Name, NpgsqlTypes.NpgsqlDbType.Timestamp));
c.Parameters[n++].Value = DateTime.Parse(bind.Value);
break;
}
}
}
