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; } } }